How to execute native query in Hibernate Java when relationship not established. In this Hibernate native SQL query, you learn how to execute query in Hibernate Java.
1. Overview of native query in Hibernate
In this article, you learn how to execute query in Hibernate Java specially select queries. It is a powerful way of writing your own native SQL query in Hibernate, However, you can execute query using relationship, Query Criteria, and HQL (Hibernate Query Language). If your relationship is well defined, then you can use HQL else you have to use your own database-specific native SQL query.
Hibernate provides you to write your own native SQL query to execute by using its own class i.e. SQLQuery. Hibernate SQL Query is very simple to write when we have to execute different database vendors ( MySQL, Oracle, SQLServer etc ) specific queries that are not supported by Hibernate API.
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 the Employee table, we can do it through the below code. you can visit Hello world hibernate example.
2. Example to execute sql query in Hibernate
In this section you learn about Hibernate query for select * from table to execute query in Hibernate Java.
// open session from sessionFactory
Session session = HibernateUtil.getSessionFactory().getCurrentSession();
// Get all Employees list without condition
Transaction tx = session.beginTransaction();
SQLQuery query = session.createSQLQuery("select * from Employee");
List rows = query.list();
for(Object[] row : rows){
Employee employee = new Employee();
employee.setId(Integer.parseInt(String.valueOf(row[0]));
employee.setName(String.valueOf(row[1]));
}
3. pros and cons of Hibernate native SQL query
Following are the key points which you need to consider for executing of native SQL query in hibernate for any of your use cases.
- This is most powerful to pull the data when your relationship is not defined well
- data base specific keywords can be used to pull data.
- Usually you should use a native SQL query in hibernate when the relationship between entities not defined in good manner.
- Avoid using of native SQL query when you use Hibernate or any ORM
- why should you avoid because you are leveraging the power of ORM tool so they generate efficient query on behalf of yours
- when you switch from one database to another then you don’t have to do anything if you are not using native SQL query.
4. mapping (.hbm.xml) files and POJO (domain model) classes
Following is the example of Hibernate native SQL query, let us follow each class step by step.
File: Employee.java
package com.mysoftkey.hibernate;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
public class Employee implements Serializable {
private int id;
private String firstName;
private String lastName;
private int salary;
private Date dateOfBirth;
private BigDecimal basicComponent;
public Employee() {
}
public Employee(String fname, String lname, int salary, Date dob) {
this.firstName = fname;
this.lastName = lname;
this.salary = salary;
this.dateOfBirth = dob;
}
// generate getter/setter method, removed in this example to clean simple and shorter
}
File: hibernate.cfg.xml
This class contains the employee detail.
5. Execute native SQL query example
First of all, I run example without native sql query just to save few data in database. so run following codebase.
File: NativeSQLQueryExampleClient.java
package com.mysoftkey.misc.client;
import java.util.Date;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import com.mysoftkey.util.HibernateUtil;
import com.mysoftkey.hibernate.Employee;
public class NativeSQLQueryExampleClient{
public static void main(String[] args) {
Employee employee = new Employee("Rakesh", "Mishra", 50000, new Date());
Employee employee2 = new Employee("Anila", "jha", 10000, new Date());
Employee employee3 = new Employee("Mansi", "jain", 20000, new Date());
NativeSQLQueryExampleClient client = new NativeSQLQueryExampleClient();
int empId1 = client.saveEmployee(employee);
empId1 = client.saveEmployee(employee2);
empId1 = client.saveEmployee(employee3);
}
public int saveEmployee(Employee employee) {
Session session = HibernateUtil.getSessionFactory().openSession();
Transaction transaction = null;
int empId = 0;
try {
transaction = session.beginTransaction();
empId = (Integer) session.save(employee);
transaction.commit();
} catch (HibernateException e) {
transaction.rollback();
e.printStackTrace();
} finally {
session.close();
}
return empId;
}
}
example of native sql query as:
File: NativeSQLClient.java
package com.mysoftkey.misc.client;
import java.util.Iterator;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import com.mysoftkey.nativequery.MainClient;
import com.mysoftkey.util.HibernateUtil;
import com.mysoftkey.hibernate.Employee;
public class NativeSQLClient {
/**
* before run uncomment Employee and Department hbm.xml in hibernate.cfg.xml
*
* @param args
*/
public static void main(String[] args) {
NativeSQLClient client = new NativeSQLClient();
int empid = 1;
// Get Employee by id.
Employee employee = client.getEmployeeByNativeQuery(empid);
if (employee != null) {
System.out.println(" Name: " + employee.getFirstName() + " , Last Name:" + employee.getLastName());
System.out.print(" ,salary: -" + employee.getSalary());
}
}
/**
* Get Employee by empid using native SQL Query
*
* @param empId
* @return
*/
public Employee getEmployeeByNativeQuery(int empId) {
Session session = HibernateUtil.getSessionFactory().openSession();
Transaction transaction = null;
Employee employee = null;
try {
transaction = session.beginTransaction();
String sql = "SELECT * FROM employee WHERE id = :employee_id";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Employee.class);
query.setParameter("employee_id", empId);
List employees = query.list();
if (employees != null && employees.size() > 0) {
employee = (Employee) employees.get(0);
}
transaction.commit();
} catch (HibernateException e) {
if (transaction != null) {
transaction.commit();
}
e.printStackTrace();
} finally {
session.close();
}
return employee;
}
}
6. Tables used in native SQL query example
table used in this example as follows.
CREATE TABLE IF NOT EXISTS employee ( id INT(11) NOT NULL AUTO_INCREMENT, firstName VARCHAR(50) DEFAULT NULL, lastName VARCHAR(50) DEFAULT NULL, salary int DEFAULT NULL, dateOfBirth DATE DEFAULT NULL, PRIMARY KEY (id) )
7. multiple tables join in native sql query
query = session.createSQLQuery("select e.id, e.name, e.salary,a.city, a.state,
a.zipcode from Employee e, Address a where a.emp_id=e.id");
List rows = query.list();
8. AddScalar in native SQL query
you can use addScalar() method while using native SQL query to say type of column to hibernate framework.
SQLQuery query = session.createSQLQuery("select id, name, salary from Employee")
.addScalar("id", new LongType())
.addScalar("name", new StringType())
.addScalar("salary", new DoubleType());
List rows = query.list();
9. Output of Hibernate native sql query
Eclipse console output:
Hibernate: SELECT * FROM employee WHERE id = ? Name: Rakesh , Last Name: Mishra ,salary: 50000
10. Reference
Thanks for visiting this post for learning Hibernate native SQL query example. You can also visit Java Hibernate ORM Tutorial Listing page for more articles on Hibernate ORM framework.
Happy Learning 🙂 for executing native select query by example.
Your comments are welcome to improve this Hibernate Query example in Hibernate Java. Happy Learning 🙂
