Hibernate Native SQL (original) (raw)

Last Updated : 25 May, 2022

Hibernate by means of a Native SQL facility, can directly interact with the database like MySQL, Oracle, etc., and all the database-specific queries can be executed via this facility. This feature is much useful if the application is an old application and running for a long time. All of a sudden we cannot bring back new changes but instead with Native SQL facilities, we can work on that. Mainly to support older applications, we can go for Hibernate - Native SQL

Example Project

Project Structure:

We are going to see a maven-driven project. Hence as a first step, let us see pom.xml that specifies all the dependencies, properties, etc.

pom.xml

XML `

4.0.0 Hibernate-NativeSQL-SampleApplication Hibernate-NativeSQL-SampleApplication 0.0.1-SNAPSHOT

      <!-- Much essential hibernate core dependency -->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>4.3.0.Final</version>
    </dependency>
  
    <!-- As we are connecting with MySQL, this is needed -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.34</version>
    </dependency>
  
</dependencies>

<!-- Higher java versions are preferred, atleast 1.8 -->
<properties>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
</properties>

`

Important files for the project

hibernate.cfg.xml

Hibernate properties are specified here.

XML `

    <!--  We are going to connect studentsdetails.hbm.xml 
          which has the table information about studentsdetails 
          which is present in mysql -->
    <mapping resource="studentsdetails.hbm.xml" />
</session-factory>

`

studentsdetails.hbm.xml

XML `

`

Let us see the POJO class now

StudentsDetails.java

Java `

// Each and every column must represent the // column of mysql table studentsDetails public class StudentsDetails { private int id; private String name; private String caste; private int neetMarks; private String gender;

// For all columns, corresponding
// getter and setter are added
public int getId()
{
    return id;
}
public void setId(int id)
{
    this.id = id;
}
public String getName()
{
    return name;
}
public void setName(String name)
{
    this.name = name;
}
public String getCaste()
{
    return caste;
}
public void setCaste(String caste)
{
    this.caste = caste;
}
public int getNeetMarks()
{
    return neetMarks;
}
public void setNeetMarks(int neetMarks)
{
    this.neetMarks = neetMarks;
}
public String getGender()
{
    return gender;
}
public void setGender(String gender)
{
    this.gender = gender;
}

}

`

So above listed files are connecting medium. Let's see the MySQL table as well

Let us assume that we have some set of records in that

Now, let us see the way of writing queries to communicate with MySQL and get the details

Java `

package com.gfg.hibernate;

import java.util.Iterator; import java.util.List;

import org.hibernate.Filter; import org.hibernate.Hibernate; import org.hibernate.Query; import org.hibernate.SQLQuery; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.Transaction; import org.hibernate.boot.registry.StandardServiceRegistryBuilder; import org.hibernate.cfg.Configuration; import org.hibernate.type.IntegerType; import org.hibernate.type.StringType;

import com.gfg.hibernate.pojo.StudentsDetails;

public class Main {

public static void main(String[] args)
{
    // Refer the hibernate.cfg.xml
    Configuration configuration = new Configuration()
                                      .configure("hibernate.cfg.xml");
    StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()
                                                 .applySettings(configuration.getProperties());

    // SessionFactory will contain all the database property
    // details which are pulled from above hibernate.cfg.xml file
    // As application refers the database, it is required.
    // It is a threadsafe object
    SessionFactory factory = configuration.buildSessionFactory(builder
                                                                   .build());

    // To get a physical connection
    Session session = factory.openSession();

    // Query the database by means of specific tablename
    // straightaway. SQL should be like how we do
    // from SQLCommandline or workbench
    String studentsQuery = "select * from studentsdetails";

    // New instance of Query for the given SQL
    SQLQuery sqlQuery1 = session.createSQLQuery(studentsQuery);

    // We will get the details via list
    List studentsList = sqlQuery1.list();

    Iterator studentsIterator = studentsList.iterator();

    while (studentsIterator.hasNext()) {
        Object[] object = (Object[])studentsIterator.next();
        System.out.println("Student Id : " + object[0] + " Student Name : " + object[1]
                           + " Caste : " + object[2]);
    }

    // Always good to clear
    // and close the session
    session.clear();
    session.close();
}

}

`

We can get all the details because of the Complete Row facility

// Query the database by means of specific tablename straightaway. // SQL should be like how we do from SQLCommandline or workbench String studentsQuery = "select * from studentsdetails";

// New instance of Query for the given SQL SQLQuery sqlQuery1 = session.createSQLQuery(studentsQuery);

Output:

If each row is converted into an object of the POJO class, we can use addEntity() method of SQLQuery

Java `

// addEntity System.out.println("Entity**"); String entityQuery = "select * from studentsdetails"; SQLQuery sqlQuery2 = session.createSQLQuery(entityQuery); sqlQuery2.addEntity(StudentsDetails.class);

List studentsEntityList = sqlQuery2.list();

// list contains StudentsDetails class objects. // So that we can directly cast into StudentsDetails type : Iterator studentsEntityIterator = studentsEntityList.iterator();

while (studentsEntityIterator.hasNext()) { StudentsDetails student = (StudentsDetails)studentsEntityIterator.next(); System.out.println("id : " + student.getId() + " Name : " + student.getName() + " NEETMarks : " + student.getNeetMarks()); }

`

Output:

Scalar data:

In case we want to partially select a few columns alone means, it is possible by means of addScalar(). Hibernate internally uses ResultSetMetaData. We need to specify the data type when we are using addScalar()

Java `

// addSclar System.out.println("SCALAR**");

String scalarQuery = "select * from studentsdetails"; SQLQuery sqlQuery3 = session.createSQLQuery(scalarQuery);

// As we are taking first 2 columns alone, we need to // specify the name of the column and datatype here sqlQuery3.addScalar("id", IntegerType.INSTANCE); sqlQuery3.addScalar("name", StringType.INSTANCE); List studentsScalarList = sqlQuery3.list(); Iterator studentsScalarIterator = studentsScalarList.iterator();

while (studentsScalarIterator.hasNext()) { Object[] object = (Object[])studentsScalarIterator.next(); // Here also we can see only 2 column // retrieval because of addScalar() System.out.println("Id : " + object[0] + " Name : " + object[1]); }

`

Output:

Finally, let us see how to update the data

Java `

// update System.out.println("Update");

Transaction mySqlTransaction = session.beginTransaction();

// straight away righting update query specific to db String updateQuery = "update studentsdetails set neetmarks=:neetmarks where id=:id"; SQLQuery sqlQuery4 = session.createSQLQuery(updateQuery); sqlQuery4.setParameter("id", 1); sqlQuery4.setParameter("neetmarks", 650); sqlQuery4.executeUpdate();

// will do the changes permanent to database mySqlTransaction.commit(); System.out.println("Completed");

// again check after doing committing entityQuery = "select * from studentsdetails"; sqlQuery2 = session.createSQLQuery(entityQuery); sqlQuery2.addEntity(StudentsDetails.class);

List studentsEntityList1 = sqlQuery2.list(); Iterator studentsEntityIterator1 = studentsEntityList1.iterator();

while (studentsEntityIterator1.hasNext()) { StudentsDetails student = (StudentsDetails)studentsEntityIterator1.next(); System.out.println("id : " + student.getId() + " Name : " + student.getName() + " NEETMarks : " + student.getNeetMarks()); }

`

Output:

Checking the DB data as well:

The Complete Code

Java `

import java.util.Iterator; import java.util.List; import org.hibernate.Filter; import org.hibernate.Hibernate; import org.hibernate.Query; import org.hibernate.SQLQuery; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.Transaction; import org.hibernate.boot.registry.StandardServiceRegistryBuilder; import org.hibernate.cfg.Configuration; import org.hibernate.type.IntegerType; import org.hibernate.type.StringType; import com.gfg.hibernate.pojo.StudentsDetails;

public class Main {

public static void main(String[] args)
{
    // Refer the hibernate.cfg.xml
    Configuration configuration = new Configuration()
                                      .configure("hibernate.cfg.xml");
    StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()
                                                 .applySettings(configuration.getProperties());

    // SessionFactory will contain all the database property
    // details which are pulled from above hibernate.cfg.xml file
    // As application refers the database, it is required.
    // It is a threadsafe object
    SessionFactory factory = configuration.buildSessionFactory(builder
                                                                   .build());

    // To get a physical connection
    Session session = factory.openSession();

    // Query the database by means of specific tablename straightaway.
    // SQL should be like how we do from SQLCommandline or workbench
    String studentsQuery = "select * from studentsdetails";

    // New instance of Query for the given SQL
    SQLQuery sqlQuery1 = session.createSQLQuery(studentsQuery);

    // We will get the details via list
    List studentsList = sqlQuery1.list();

    Iterator studentsIterator = studentsList.iterator();

    while (studentsIterator.hasNext()) {
        Object[] object = (Object[])studentsIterator.next();
        System.out.println("Student Id : " + object[0] + " Student Name : " + object[1]
                           + " Caste : " + object[2]);
    }

    // addEntity
    System.out.println("***********Entity*************");
    String entityQuery = "select * from studentsdetails";
    SQLQuery sqlQuery2 = session.createSQLQuery(entityQuery);
    sqlQuery2.addEntity(StudentsDetails.class);

    List studentsEntityList = sqlQuery2.list();
    Iterator studentsEntityIterator = studentsEntityList.iterator();

    while (studentsEntityIterator.hasNext()) {
        StudentsDetails student = (StudentsDetails)studentsEntityIterator.next();
        System.out.println("id : " + student.getId() + " Name : "
                           + student.getName() + " NEETMarks : " + student.getNeetMarks());
    }

    // addSclar
    System.out.println("***********SCALAR*************");

    String scalarQuery = "select * from studentsdetails";
    SQLQuery sqlQuery3 = session.createSQLQuery(scalarQuery);

    sqlQuery3.addScalar("id", IntegerType.INSTANCE);
    sqlQuery3.addScalar("name", StringType.INSTANCE);

    List studentsScalarList = sqlQuery3.list();
    Iterator studentsScalarIterator = studentsScalarList.iterator();

    while (studentsScalarIterator.hasNext()) {
        Object[] object = (Object[])studentsScalarIterator.next();
        System.out.println("Id : " + object[0] + " Name : " + object[1]);
    }

    // update
    System.out.println("**********Update**********");

    // A transaction is associated with a Session and
    // is usually instantiated by a call to Session.beginTransaction()
    Transaction mySqlTransaction = session.beginTransaction();
    String updateQuery = "update studentsdetails set neetmarks=:neetmarks where id=:id";
    SQLQuery sqlQuery4 = session.createSQLQuery(updateQuery);
    sqlQuery4.setParameter("id", 1);
    sqlQuery4.setParameter("neetmarks", 650);
    sqlQuery4.executeUpdate();

    // will do the changes permanent to database
    mySqlTransaction.commit();
    System.out.println("Completed");

    // again check after doing committing
    entityQuery = "select * from studentsdetails";
    sqlQuery2 = session.createSQLQuery(entityQuery);
    sqlQuery2.addEntity(StudentsDetails.class);

    List studentsEntityList1 = sqlQuery2.list();
    Iterator studentsEntityIterator1 = studentsEntityList1.iterator();

    while (studentsEntityIterator1.hasNext()) {
        StudentsDetails student = (StudentsDetails)studentsEntityIterator1.next();
        System.out.println("id : " + student.getId() + " Name : "
                           + student.getName() + " NEETMarks : " + student.getNeetMarks());
    }

    session.clear();
    session.close();
}

}

`

Conclusion

The main advantage of hibernate is totally not applicable because of this NativeSQL approach, but still, it helps in the age-old applications that are running in production and available for live means we can go for this approach. Sometimes in terms of support, we can go for NativeSQL, but newly developed applications or the applications that are planned to support multi-database supportive should not go with this approach.