benefit of native sql query in hibernate

benefit of native sql query in hibernate

While Hibernate Query Language (HQL) is the preferred method for querying in Hibernate due to its object-oriented approach and database independence, there are several benefits to using native SQL queries in Hibernate:

1.  Complex Queries : Some SQL queries are too complex to be expressed easily in HQL. Native SQL allows you to write these complex queries directly.

2.  Performance Optimization : Native SQL queries can be optimized for specific databases to achieve better performance compared to HQL.

3.  Database-Specific Features : You can leverage database-specific functions and features that may not be available or easily accessible through HQL.

4.  Legacy Systems Integration : When working with legacy databases or systems that already have a significant number of SQL queries, using native SQL allows for easier integration.

5.  Flexibility : Native SQL provides more flexibility in certain scenarios, such as bulk updates, complex joins, and the use of database-specific constructs.

Example in Java Using Native SQL Queries

Consider an Employee entity and a scenario where you need to execute a native SQL query to fetch employees based on specific criteria.

native sql query

Entity Definition:

 Employee.java 
java
@Entity
@Table(name = "employee")
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name")
    private String name;

    @ManyToOne
    @JoinColumn(name = "department_id")
    private Department department;

    // Getters and Setters
}

Executing a Native SQL Query:
1.  Basic Native SQL Query: 
java
Session session = sessionFactory.openSession();
Transaction transaction = session.beginTransaction();

String sql = "SELECT * FROM employee WHERE department_id = :departmentId";
SQLQuery query = session.createSQLQuery(sql);
query.setParameter("departmentId", 1);
query.addEntity(Employee.class); // Maps result set to Employee entity
List<Employee> employees = query.list();

for (Employee employee : employees) {
    System.out.println(employee.getName());
}

transaction.commit();
session.close();

Using Native SQL with ResultSet Mapping:
2.  Using Native SQL with ResultSet Mapping: 

In cases where you need to map the result to a custom object or use a complex query that requires manual mapping:
	
 EmployeeDTO.java 
java
public class EmployeeDTO {
    private Long id;
    private String name;

    public EmployeeDTO(Long id, String name) {
        this.id = id;
        this.name = name;
    }

    // Getters and Setters
}

Executing and Mapping the Result:
java
Session session = sessionFactory.openSession();
Transaction transaction = session.beginTransaction();

String sql = "SELECT e.id, e.name FROM employee e WHERE e.department_id = :departmentId";
SQLQuery query = session.createSQLQuery(sql);
query.setParameter("departmentId", 1);
query.setResultTransformer(new ResultTransformer() {
    @Override
    public Object transformTuple(Object[] tuple, String[] aliases) {
        return new EmployeeDTO(((BigInteger) tuple[0]).longValue(), (String) tuple[1]);
    }

    @Override
    public List transformList(List collection) {
        return collection;
    }
});
List<EmployeeDTO> employeeDTOs = query.list();

for (EmployeeDTO dto : employeeDTOs) {
    System.out.println(dto.getName());
}

transaction.commit();
session.close();

Homepage

Readmore