Menu

Execute native sql query in hibernate

Execute native sql query in hibernate

Yes, you can execute native SQL queries in Hibernate. While Hibernate Query Language (HQL) is the preferred way to interact with the database due to its object-oriented approach and database independence, there are situations where you might need to execute raw SQL queries. This can be necessary for complex queries, performance optimizations, or when using database-specific features.

Benefits of Using Native SQL Queries

  • Complex Queries : Some queries are too complex to be expressed in HQL.
  • Database-Specific Features : You can utilize database-specific functions and features.
  • Performance : In some cases, native SQL queries can be optimized for better performance.

Limitations

  1. Database Dependency : Native SQL queries are specific to the database being used and may need to be modified if you switch databases.
  2. Object-Relational Mapping : You need to manually handle the result set mapping to entity objects.

Example in Java Using Native SQL Queries

Consider an Employee entity and the need to execute a native SQL query to fetch employees based on certain 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: 

Sometimes you may 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