Hibernate Native Query with example

Hibernate Native Query with example

by

You can express a query in SQL, using createSQLQuery() and let Hibernate manage the mapping from result sets to objects.

Although we should use HQL wherever possible but there could be a few reasons where you want to use native SQL funtionalities, such as :

  1. Calling stored procedures
  2. Calling stored functions
  3. DB support some special features which are not present in HQL

How to write a native query in hibernate ?

You can specify all properties on an object with {objectname.*}, or you can specify the aliases directly with {objectname.property}.

public SQLQuery createSQLQuery(String queryString) throws 
                                                                                                   HibernateException

If using Oracle DB: using ROWNUM

List emps = session.createSQLQuery("SELECT {emp.*} FROM Employee 
                {emp} WHERE ROWNUM<10")
                .addEntity("emp", Employee.class)
                .list();
        System.out.println("Native Query result list :: "+emps);

If using mySql DB: using LIMIT

List emps = session.createSQLQuery("SELECT {emp.*} FROM Employee 
                {emp} LIMIT 10")
                .addEntity("emp", Employee.class)
                .list();
        System.out.println("Native Query result list :: "+emps);

Output

Native Query result list ::
[Employee{id=2, firstName='Mak', lastName='S', address='mak@pt.com'}, 
Employee{id=3, firstName='Mak', lastName='S', address='mak@pt.com'},  
Employee{id=4, firstName='mak', lastName='robins', address='NA'}]

Summary

In this article, we looked into Hibernate Native Query with some example, the reason why to use native query and when to use it.
I hope you liked the article !