Skip to content
Related Articles

Related Articles

Hibernate – Native SQL

View Discussion
Improve Article
Save Article
Like Article
  • 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:

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




         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
                             http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>Hibernate-NativeSQL-SampleApplication</groupId>
  <artifactId>Hibernate-NativeSQL-SampleApplication</artifactId>
  <version>0.0.1-SNAPSHOT</version>
    <dependencies>
       
          <!-- 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>
   
</project>

Important files for the project

hibernate.cfg.xml

Hibernate properties are specified here.

XML




<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
<hibernate-configuration>
    <session-factory>
        <!--  As we are connecting mysql, those driver classes,
              database name, username and password are specified
              Please change the information as per your requirement -->
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost:3306/test?serverTimezone=UTC</property>       
        <property name="connection.username">root</property>
        <property name="connection.password">admin</property>
       
        <!--  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>
</hibernate-configuration>

studentsdetails.hbm.xml

XML




<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
  "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
<hibernate-mapping>
    <!--  studentsdetails is the mySQL table and corresponding
           POJO class is  com.gfg.hibernate.pojo.StudentsDetails-->
    <class name="com.gfg.hibernate.pojo.StudentsDetails" table="studentsdetails"
        schema="test">
        <id name="id" column="id">
            <generator class="increment" />
        </id>
        <property name="name" column="name" />
        <property name="caste" column="caste" />
        <property name="neetMarks" column="neetMarks" />
        <property name="gender" column="gender"/>
    </class>
</hibernate-mapping>

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:

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:

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:

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:

Output

 

Checking the DB data as well:

Output

 

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.


My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!