Hibernate - Native SQL

Using native SQL in Hibernate allows you to execute plain SQL statements directly against the database. While Hibernate is an ORM (Object-Relational Mapping) tool designed to work with entities, there are situations where you might need the power and flexibility of raw SQL.

In this tutorial, we'll explore how to use native SQL queries within Hibernate.

1. Dependencies:

Ensure you have the required Hibernate dependencies in your project. Typically, this means including hibernate-core for Maven users.

2. Basics:

You can use the createNativeQuery method of the Session object to execute native SQL queries.

Simple SQL Query:

Session session = sessionFactory.getCurrentSession();
List<Object[]> results = session.createNativeQuery("SELECT first_name, last_name FROM employee").list();
for(Object[] row : results) {
    System.out.println("First name: " + row[0] + ", Last name: " + row[1]);
}

3. Using SQL Result Class:

If you want to map the result to a custom class (rather than an entity), you can do so:

public class EmployeeDTO {
    private String firstName;
    private String lastName;

    public EmployeeDTO(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;
    }

    // getters and setters
}

// Then, in your query:

List<EmployeeDTO> employees = session.createNativeQuery(
    "SELECT first_name as firstName, last_name as lastName FROM employee", 
    "EmployeeDTOMapping"
).addScalar("firstName", StringType.INSTANCE)
 .addScalar("lastName", StringType.INSTANCE)
 .setResultTransformer(Transformers.aliasToBean(EmployeeDTO.class))
 .list();

For this to work, you need to define the result set mapping in your hibernate.cfg.xml:

<hibernate-mapping>
    <resultset name="EmployeeDTOMapping">
        <return alias="employee" class="com.example.EmployeeDTO">
            <return-property name="firstName" column="first_name"/>
            <return-property name="lastName" column="last_name"/>
        </return>
    </resultset>
</hibernate-mapping>

4. Using Native SQL with Entities:

If you wish to return actual entities from your native SQL, you can do so:

List<Employee> employees = session.createNativeQuery("SELECT * FROM employee", Employee.class).list();

5. Using Named Parameters:

To prevent SQL injection and make your queries more readable, use named parameters:

Query query = session.createNativeQuery("SELECT * FROM employee WHERE first_name = :firstName", Employee.class);
query.setParameter("firstName", "John");
List<Employee> employees = query.list();

6. Using Update or Delete:

You can also execute update or delete operations:

int updatedCount = session.createNativeQuery("UPDATE employee SET salary = 5000 WHERE first_name = :firstName")
                          .setParameter("firstName", "John")
                          .executeUpdate();

Conclusion:

While Hibernate provides a powerful ORM layer to interact with the database, there are situations where you might need the direct power of SQL. Native SQL queries come in handy in these scenarios, allowing you to execute raw SQL and even map results back to entities or DTOs.

However, always be cautious when using raw SQL. It can introduce platform-specific dependencies (if your SQL isn't standard across databases) and might bypass some ORM optimizations.

Hibernate native SQL query example:

Hibernate allows you to execute native SQL queries directly on the database.
Use session.createNativeQuery() to create a native SQL query.

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();

Configuring Hibernate native SQL queries:
- To execute native SQL queries, you need to create a SQLQuery using the session.createNativeQuery() method.
- You can specify the SQL query and the result class (if applicable).
```
String sqlQuery = "SELECT * FROM employees";
SQLQuery<Employee> query = session.createNativeQuery(sqlQuery, Employee.class);
List<Employee> employees = query.getResultList();
```

Mapping native SQL query results in Hibernate:

When executing native SQL queries, Hibernate allows you to map the result set to entities.
Use addEntity() to specify the entity type.

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

Named native queries in Hibernate:

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();

Using placeholders in Hibernate native SQL queries:

Placeholders in native SQL queries can be used to make them more dynamic and parameterized.
Use ? as placeholders and set parameters using setParameter().

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

Handling result sets in Hibernate native SQL:
- Result sets from native SQL queries can be handled using various methods like getResultList() or uniqueResult().
- Use appropriate methods based on the expected result.
```
String sqlQuery = "SELECT COUNT(*) FROM employees";
SQLQuery<Long> query = session.createNativeQuery(sqlQuery);
Long count = query.uniqueResult();
```