Hibernate Native SQL Query with Example

Native SQL with Hibernate allows you to execute plain SQL statements directly against the database. This can be especially useful when you need to perform operations that might be cumbersome with HQL (Hibernate Query Language) or when dealing with legacy systems.

Let's explore this with a hands-on example:

1. Setting Up:

For this example, let's assume you're already set up with a Hibernate project. We'll work with a simple Employee entity.

Employee.java:

@Entity
@Table(name = "employee")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

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

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

    @Column(name = "salary")
    private double salary;

    // Constructors, getters, setters, etc.
}

2. Native SQL Example:

Let's say you want to retrieve all employees with a specific salary using native SQL.

Fetching Using Native SQL:

Session session = sessionFactory.getCurrentSession();
session.beginTransaction();

// Native SQL query
String sql = "SELECT * FROM employee WHERE salary > 50000";

List<Object[]> result = session.createNativeQuery(sql).list();

for (Object[] row : result) {
    System.out.println("First name: " + row[1] + ", Last name: " + row[2] + ", Salary: " + row[3]);
}

session.getTransaction().commit();

Fetching Entities with Native SQL:

It's also possible to retrieve entities directly:

Session session = sessionFactory.getCurrentSession();
session.beginTransaction();

String sql = "SELECT * FROM employee WHERE salary > 50000";

List<Employee> employees = session.createNativeQuery(sql, Employee.class).list();

for (Employee employee : employees) {
    System.out.println(employee.getFirstName() + " " + employee.getLastName());
}

session.getTransaction().commit();

3. Parameterized Native SQL:

To make your native SQL query safer and prevent SQL injection, use parameters:

Session session = sessionFactory.getCurrentSession();
session.beginTransaction();

String sql = "SELECT * FROM employee WHERE first_name = :firstName";

List<Employee> employees = session.createNativeQuery(sql, Employee.class)
                                 .setParameter("firstName", "John")
                                 .list();

for (Employee employee : employees) {
    System.out.println(employee.getFirstName() + " " + employee.getLastName());
}

session.getTransaction().commit();

Conclusion:

Native SQL is a powerful feature in Hibernate that allows direct SQL operations. It can be particularly useful when you need operations that aren't straightforward with HQL or Criteria API, or when working with legacy systems or complex queries. Always ensure you parameterize your queries to prevent SQL injection risks.

Executing native SQL queries with Hibernate:
- Hibernate allows you to execute native SQL queries directly against the database.
- Use session.createNativeQuery() to create a native SQL query.
```
String sqlQuery = "SELECT * FROM employees";
SQLQuery<Employee> query = session.createNativeQuery(sqlQuery, Employee.class);
List<Employee> employees = query.getResultList();
```

Configuring Hibernate native SQL query annotations:

Native SQL queries can be configured using annotations in Hibernate.
Use @NamedNativeQuery to define a named native query.

@NamedNativeQuery(
   name = "getEmployeesByDepartment",
   query = "SELECT * FROM employees WHERE department_id = :deptId",
   resultClass = Employee.class
)

Query query = session.getNamedQuery("getEmployeesByDepartment");
query.setParameter("deptId", 1);
List<Employee> employees = query.getResultList();

Handling parameters in Hibernate native SQL queries:

Native SQL queries can have parameters, and they can be set using the setParameter() method.
Use :paramName placeholders for named parameters.

String sqlQuery = "SELECT * FROM employees WHERE department_id = :deptId";
SQLQuery<Employee> query = session.createNativeQuery(sqlQuery, Employee.class);
query.setParameter("deptId", 1);
List<Employee> employees = query.getResultList();

Mapping native SQL query results to entities in Hibernate:

Native SQL query results can be mapped to entities using the addEntity() method.
Specify the entity class to map the result set.

String sqlQuery = "SELECT * FROM employees";
SQLQuery<Employee> query = session.createNativeQuery(sqlQuery);
query.addEntity(Employee.class);
List<Employee> employees = query.getResultList();

Using named native queries in Hibernate with examples:

Named native queries provide a way to define and reuse native SQL queries.
Define the query in the mapping file or using annotations.

@NamedNativeQuery(
   name = "getEmployeesByDepartment",
   query = "SELECT * FROM employees WHERE department_id = :deptId",
   resultClass = Employee.class
)

Query query = session.getNamedQuery("getEmployeesByDepartment");
query.setParameter("deptId", 1);
List<Employee> employees = query.getResultList();

Executing stored procedures with Hibernate native SQL:

Hibernate allows you to execute stored procedures using native SQL queries.
Use session.createStoredProcedureQuery().

StoredProcedureQuery<Employee> query = session.createStoredProcedureQuery("getEmployeesByDepartment", Employee.class);
query.registerStoredProcedureParameter("deptId", Integer.class, ParameterMode.IN);
query.setParameter("deptId", 1);
List<Employee> employees = query.getResultList();

Hibernate native SQL query vs HQL example:

Hibernate Query Language (HQL) is an object-oriented query language, while native SQL queries are SQL statements.
HQL is more abstract and doesn't depend on the underlying database.

// HQL example
String hqlQuery = "FROM Employee WHERE department.id = :deptId";
Query hql = session.createQuery(hqlQuery);
hql.setParameter("deptId", 1);
List<Employee> hqlResult = hql.getResultList();

// Native SQL example
String sqlQuery = "SELECT * FROM employees WHERE department_id = :deptId";
SQLQuery<Employee> nativeQuery = session.createNativeQuery(sqlQuery, Employee.class);
nativeQuery.setParameter("deptId", 1);
List<Employee> nativeResult = nativeQuery.getResultList();