Native SQL Query in Hibernate
Hibernate provides HQL(Hibernate Query Language) to query the database. Along with that, Hibernate also provides a way to use native SQL statements directly against the database.
For running native queries, we can use session.createSQLQuery() method.
Syntax :
public SQLQuery createSQLQuery(String queryString) throws HibernateException
For example, following code uses Native SQL to query records from the below Employee table :
SQLQuery query = session.createSQLQuery("select * from EMPLOYEE");
List employees = query.list();
After passing a String to the createSQLQuery(), you should also associate the SQL result with an existing Hibernate entity, a join, or a scalar result using the addEntity(), addJoin() or addScalar() method.
We are referring to the following Employee table for the examples here.
Scalar Queries
Scalar queries return a list of values (scalar).
SQLQuery query = session.createSQLQuery("Select max(age) as max_age from employee");
query.addScalar("max_age", new org.hibernate.type.LongType());
List results = query.list();
System.out.println(results.get(0));
Output:
Hibernate:
Select
max(age) as max_age
from
employee
{max_age=41}
Entity Queries
When we need to map an Entity to a SQL query, we can use Entity queries.
Entity queries automatically fill an entity from the values returned by the query.
SQLQuery query = session.createSQLQuery("Select * from employee");
query.addEntity(Employee.class);
List<Employee> empList = query.list();
for(Employee emp : empList ){
System.out.println("\nEmployee Id : " + emp.getId());
System.out.println("Employee Name : " + emp.getName());
System.out.println("Employee Age : " + emp.getAge());
}
Output:
Hibernate:
Select
*
from
employee
Employee Id : 1
Employee Name : John
Employee Age : 21
Employee Id : 2
Employee Name : Dave
Employee Age : 31
Employee Id : 3
Employee Name : Joy
Employee Age : 41
Why use Native SQL when HQL is available?
HQL should help with most of the database queries. However, there may be need for special queries that dependent on database vendor’s specific functions.
We can also use native sql if we need to make stored procedure calls from the hibernate code.
© 2017, https:. All rights reserved. On republishing this post, you must provide link to original post