Tutorial

Hibernate Native SQL Query Example

Published on August 3, 2022
Default avatar

By Pankaj

Hibernate Native SQL Query Example

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

Welcome to the Hibernate Native SQL Query example tutorial. We looked into Hibernate Query Language and Hibernate Criteria in earlier articles, today we will look into Hibernate Native SQL query with examples.

Hibernate SQL Query

hibernate sql query, hibernate native sql, hibernate sql, hibernate native query Hibernate provide option to execute native SQL queries through the use of SQLQuery object. Hibernate SQL Query is very handy when we have to execute database vendor specific queries that are not supported by Hibernate API. For example query hints or the CONNECT keyword in Oracle Database. For normal scenarios, Hibernate SQL query is not the recommended approach because we loose benefits related to hibernate association and hibernate first level cache. I will use MySQL database and same tables and data setup as used in HQL example, so you should check out that first to understand the tables and corresponding model classes mapping.

Hibernate Native SQL Example

For Hibernate Native SQL Query, we use Session.createSQLQuery(String query) to create the SQLQuery object and execute it. For example, if you want to read all the records from Employee table, we can do it through below code.

// Prep work
SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
Session session = sessionFactory.getCurrentSession();

// Get All Employees
Transaction tx = session.beginTransaction();
SQLQuery query = session.createSQLQuery("select emp_id, emp_name, emp_salary from Employee");
List<Object[]> rows = query.list();
for(Object[] row : rows){
	Employee emp = new Employee();
	emp.setId(Long.parseLong(row[0].toString()));
	emp.setName(row[1].toString());
	emp.setSalary(Double.parseDouble(row[2].toString()));
	System.out.println(emp);
}

When we execute above code for the data setup we have, it produces following output.

Hibernate: select emp_id, emp_name, emp_salary from Employee
Id= 1, Name= Pankaj, Salary= 100.0, {Address= null}
Id= 2, Name= David, Salary= 200.0, {Address= null}
Id= 3, Name= Lisa, Salary= 300.0, {Address= null}
Id= 4, Name= Jack, Salary= 400.0, {Address= null}

Notice that list() method returns the List of Object array, we need to explicitly parse them to double, long etc. Our Employee and Address classes have following toString() method implementations.

@Override
public String toString() {
	return "Id= " + id + ", Name= " + name + ", Salary= " + salary
			+ ", {Address= " + address + "}";
}
@Override
public String toString() {
	return "AddressLine1= " + addressLine1 + ", City=" + city
			+ ", Zipcode=" + zipcode;
}

Notice that our query is not returning Address data, whereas if we use HQL query "from Employee", it returns the associated table data too.

Hibernate SQL Query addScalar

Hibernate uses ResultSetMetadata to deduce the type of the columns returned by the query, from performance point of view we can use addScalar() method to define the data type of the column. However we would still get the data in form of Object array.

//Get All Employees - addScalar example
query = session.createSQLQuery("select emp_id, emp_name, emp_salary from Employee")
		.addScalar("emp_id", new LongType())
		.addScalar("emp_name", new StringType())
		.addScalar("emp_salary", new DoubleType());
rows = query.list();
for(Object[] row : rows){
	Employee emp = new Employee();
	emp.setId(Long.parseLong(row[0].toString()));
	emp.setName(row[1].toString());
	emp.setSalary(Double.parseDouble(row[2].toString()));
	System.out.println(emp);
}

The output generated will be same, however we will see slight performance improvement when the data is huge.

Hibernate Native SQL Multiple Tables

If we would like to get data from both Employee and Address tables, we can simply write the SQL query for that and parse the result set.

query = session.createSQLQuery("select e.emp_id, emp_name, emp_salary,address_line1, city, 
	zipcode from Employee e, Address a where a.emp_id=e.emp_id");
rows = query.list();
for(Object[] row : rows){
	Employee emp = new Employee();
	emp.setId(Long.parseLong(row[0].toString()));
	emp.setName(row[1].toString());
	emp.setSalary(Double.parseDouble(row[2].toString()));
	Address address = new Address();
	address.setAddressLine1(row[3].toString());
	address.setCity(row[4].toString());
	address.setZipcode(row[5].toString());
	emp.setAddress(address);
	System.out.println(emp);
}

For above code, the output produced will be like below.

Hibernate: select e.emp_id, emp_name, emp_salary,address_line1, city, zipcode from Employee e, Address a where a.emp_id=e.emp_id
Id= 1, Name= Pankaj, Salary= 100.0, {Address= AddressLine1= Albany Dr, City=San Jose, Zipcode=95129}
Id= 2, Name= David, Salary= 200.0, {Address= AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051}
Id= 3, Name= Lisa, Salary= 300.0, {Address= AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100}
Id= 4, Name= Jack, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}

Hibernate Native SQL Entity and Join

We can also use addEntity() and addJoin() methods to fetch the data from associated table using tables join. For example, above data can also be retrieved as below.

//Join example with addEntity and addJoin
query = session.createSQLQuery("select {e.*}, {a.*} from Employee e join Address a ON e.emp_id=a.emp_id")
		.addEntity("e",Employee.class)
		.addJoin("a","e.address");
rows = query.list();
for (Object[] row : rows) {
    for(Object obj : row) {
    	System.out.print(obj + "::");
    }
    System.out.println("\n");
}
//Above join returns both Employee and Address Objects in the array
for (Object[] row : rows) {
	Employee e = (Employee) row[0];
	System.out.println("Employee Info::"+e);
	Address a = (Address) row[1];
	System.out.println("Address Info::"+a);
}

{[aliasname].*} is used to return all properties of an entity. When we use addEntity() and addJoin() with join queries like above it returns both the objects, as shown above. Output produced by above code is like below.

Hibernate: select e.emp_id as emp_id1_1_0_, e.emp_name as emp_name2_1_0_, e.emp_salary as emp_sala3_1_0_, a.emp_id as emp_id1_0_1_, a.address_line1 as address_2_0_1_, a.city as city3_0_1_, a.zipcode as zipcode4_0_1_ from Employee e join Address a ON e.emp_id=a.emp_id
Id= 1, Name= Pankaj, Salary= 100.0, {Address= AddressLine1= Albany Dr, City=San Jose, Zipcode=95129}::AddressLine1= Albany Dr, City=San Jose, Zipcode=95129::

Id= 2, Name= David, Salary= 200.0, {Address= AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051}::AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051::

Id= 3, Name= Lisa, Salary= 300.0, {Address= AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100}::AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100::

Id= 4, Name= Jack, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}::AddressLine1= City Centre, City=New Delhi, Zipcode=100100::

Employee Info::Id= 1, Name= Pankaj, Salary= 100.0, {Address= AddressLine1= Albany Dr, City=San Jose, Zipcode=95129}
Address Info::AddressLine1= Albany Dr, City=San Jose, Zipcode=95129
Employee Info::Id= 2, Name= David, Salary= 200.0, {Address= AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051}
Address Info::AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051
Employee Info::Id= 3, Name= Lisa, Salary= 300.0, {Address= AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100}
Address Info::AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100
Employee Info::Id= 4, Name= Jack, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}
Address Info::AddressLine1= City Centre, City=New Delhi, Zipcode=100100

You can run both the queries in the mysql client and notice that the output produced is same.

mysql> select e.emp_id as emp_id1_1_0_, e.emp_name as emp_name2_1_0_, e.emp_salary as emp_sala3_1_0_, a.emp_id as emp_id1_0_1_, a.address_line1 as address_2_0_1_, a.city as city3_0_1_, a.zipcode as zipcode4_0_1_ from Employee e join Address a ON e.emp_id=a.emp_id;
+--------------+----------------+----------------+--------------+----------------+-------------+---------------+
| emp_id1_1_0_ | emp_name2_1_0_ | emp_sala3_1_0_ | emp_id1_0_1_ | address_2_0_1_ | city3_0_1_  | zipcode4_0_1_ |
+--------------+----------------+----------------+--------------+----------------+-------------+---------------+
|            1 | Pankaj         |            100 |            1 | Albany Dr      | San Jose    | 95129         |
|            2 | David          |            200 |            2 | Arques Ave     | Santa Clara | 95051         |
|            3 | Lisa           |            300 |            3 | BTM 1st Stage  | Bangalore   | 560100        |
|            4 | Jack           |            400 |            4 | City Centre    | New Delhi   | 100100        |
+--------------+----------------+----------------+--------------+----------------+-------------+---------------+
4 rows in set (0.00 sec)

mysql> select e.emp_id, emp_name, emp_salary,address_line1, city, zipcode from Employee e, Address a where a.emp_id=e.emp_id;
+--------+----------+------------+---------------+-------------+---------+
| emp_id | emp_name | emp_salary | address_line1 | city        | zipcode |
+--------+----------+------------+---------------+-------------+---------+
|      1 | Pankaj   |        100 | Albany Dr     | San Jose    | 95129   |
|      2 | David    |        200 | Arques Ave    | Santa Clara | 95051   |
|      3 | Lisa     |        300 | BTM 1st Stage | Bangalore   | 560100  |
|      4 | Jack     |        400 | City Centre   | New Delhi   | 100100  |
+--------+----------+------------+---------------+-------------+---------+
4 rows in set (0.00 sec)

mysql> 

Hibernate Native SQL Query with Parameters

We can also pass parameters to the Hibernate SQL queries, just like JDBC PreparedStatement. The parameters can be set using the name as well as index, as shown in below example.

query = session
		.createSQLQuery("select emp_id, emp_name, emp_salary from Employee where emp_id = ?");
List<Object[]> empData = query.setLong(0, 1L).list();
for (Object[] row : empData) {
	Employee emp = new Employee();
	emp.setId(Long.parseLong(row[0].toString()));
	emp.setName(row[1].toString());
	emp.setSalary(Double.parseDouble(row[2].toString()));
	System.out.println(emp);
}

query = session
		.createSQLQuery("select emp_id, emp_name, emp_salary from Employee where emp_id = :id");
empData = query.setLong("id", 2L).list();
for (Object[] row : empData) {
	Employee emp = new Employee();
	emp.setId(Long.parseLong(row[0].toString()));
	emp.setName(row[1].toString());
	emp.setSalary(Double.parseDouble(row[2].toString()));
	System.out.println(emp);
}

Output produced by above code would be:

Hibernate: select emp_id, emp_name, emp_salary from Employee where emp_id = ?
Id= 1, Name= Pankaj, Salary= 100.0, {Address= null}
Hibernate: select emp_id, emp_name, emp_salary from Employee where emp_id = ?
Id= 2, Name= David, Salary= 200.0, {Address= null}

That’s all for a brief introduction of Hibernate SQL Query, you should avoid using it unless you want to execute any database specific queries.

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors
Default avatar
Pankaj

author

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
JournalDev
DigitalOcean Employee
DigitalOcean Employee badge
June 8, 2019

HI Pankaj, I want to know how it lost first level caching by using hibernate native sql query. Can you please explain it. Thanks in advance

- kanisha modi

    JournalDev
    DigitalOcean Employee
    DigitalOcean Employee badge
    December 20, 2018

    Hi pankaj, I read the sentence below at initial “For normal scenarios, Hibernate SQL query is not the recommended approach because we loose benefits related to hibernate association and hibernate first level cache.” -My understanding is if we use native sql query then we will lost the relationship. That mean it will not load childs. That is ok. What is the point in first level cache? If we use first level cache does the object will not cache in first level cache?. Please clarify.

    - Rajasekhar

      JournalDev
      DigitalOcean Employee
      DigitalOcean Employee badge
      October 26, 2018

      Hi Pankaj, We are facing some issues while using addscaler method in hibernate 5.2 with result transformer. Can post an example for reference.

      - Sanjay Singh

        JournalDev
        DigitalOcean Employee
        DigitalOcean Employee badge
        October 12, 2018

        useless example… not using native queries even tough stating so…

        - allObjects

          JournalDev
          DigitalOcean Employee
          DigitalOcean Employee badge
          December 26, 2017

          Thanks u.

          - elon

            JournalDev
            DigitalOcean Employee
            DigitalOcean Employee badge
            May 19, 2016

            Hi pankaj, im sarath,nic explnation and good examples,and i have a problem with my coding when i use createSQLquery in session factory for executing a native query. it throws an exception like no dialect mapping for jdbc type 1111. im using springs and hibernate and my database is db2, pls giv me a solution for this.

            - sarath

              JournalDev
              DigitalOcean Employee
              DigitalOcean Employee badge
              April 22, 2016

              You saved my day! Thanks for the clear examples!

              - Maurice

                JournalDev
                DigitalOcean Employee
                DigitalOcean Employee badge
                December 16, 2015

                How we use subquery like- select name ,(select state_name from Mst_State where stateid=1 ) from Mst_emp where emp_code =1000; data show in jsp

                - Kiran

                  JournalDev
                  DigitalOcean Employee
                  DigitalOcean Employee badge
                  November 24, 2015

                  Hello All, How can I call the stored procedure which is having out perimeter from hibernate native sql queries? Thanks in advance.

                  - sathish

                    JournalDev
                    DigitalOcean Employee
                    DigitalOcean Employee badge
                    March 10, 2015

                    cant we use iterate() to retrieve records from databases while using Native SQL Query??

                    - Supraja

                      Try DigitalOcean for free

                      Click below to sign up and get $200 of credit to try our products over 60 days!

                      Sign up

                      Join the Tech Talk
                      Success! Thank you! Please check your email for further details.

                      Please complete your information!

                      Get our biweekly newsletter

                      Sign up for Infrastructure as a Newsletter.

                      Hollie's Hub for Good

                      Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

                      Become a contributor

                      Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

                      Welcome to the developer cloud

                      DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

                      Learn more
                      DigitalOcean Cloud Control Panel