Hibernate Native SQL Query with Example

Hibernate allows interaction with databases using HQL, but sometimes Native SQL Queries are required. These are direct SQL queries written in database-specific syntax (MySQL, PostgreSQL, etc.) and executed by Hibernate while still mapping results to entities.

• Direct Database Access: Full control over SQL execution
• Database-Specific Features: Use vendor-specific functions (LIMIT, TOP, etc.)
• Complex Query Support: Easier handling of joins, subqueries, aggregations

JPA Native Query vs Hibernate Native Query

Both JPA and Hibernate provide support for executing native SQL queries, but they differ in features.

JPA Native Query

• It is created via EntityManager.createNativeQuery(), returns Query.
• Can map results to entities or DTOs (limited).
• Purely standard, no Hibernate-specific extensions.

Hibernate Native Query

• It is created via Session.createSQLQuery(), returns SQLQuery.
• Supports extra features: addScalar() for non-entity mapping, cache control (setCacheable(), setCacheRegion(), etc.).
• Provides more flexibility than JPA.

Writing and Executing native SQL queries in Hibernate

1. Create a Hibernate Session: The first step is to obtain a Hibernate Session object. This can be done using the SessionFactory, as shown in the following code snippet:

SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
Session session = sessionFactory.openSession();

2. Create a Native Query: Once you have obtained a Hibernate Session, you can create a native SQL query using the createSQLQuery() method, as shown in the following code snippet:

String sqlQuery = "SELECT * FROM customers WHERE last_name = :lastName";
Query query = session.createNativeQuery(sqlQuery, Customer.class);

3. Set Parameters: If your query includes parameters, you can set their values using the setParameter() method, as shown in the following code snippet:

query.setParameter("lastName", "Smith");

4. Execute the Query: Once you have created and configured your native SQL query, you can execute it using the list() or uniqueResult() methods, as shown in the following code snippet:

List<Customer> customers = query.list();

5. Close the Session: Finally, once you have finished using the Hibernate Session and the results of your query, you should close the Session to release any resources it is holding, as shown in the following code snippet:

session.close();

Debugging Native SQL Queries in Hibernate

Debugging native SQL queries in Hibernate can be done in several ways. Here are some tips to help you debug native SQL queries in Hibernate:

1. Print the generated SQL query: Hibernate generates the SQL query based on the HQL or native SQL query that you write. You can print the generated SQL query to the console to see how Hibernate has transformed your query. You can do this by enabling the Hibernate show_sql property in your configuration file, as shown below:

<property name="hibernate.show_sql">true</property>

2. Use logging to track SQL queries: Hibernate provides a logging facility that you can use to track SQL queries. You can enable logging for SQL queries by setting the Hibernate logging level to debug or trace, as shown below:

<logger name="org.hibernate.SQL" level="debug"/>

3. Check for syntax errors: Native SQL queries are prone to syntax errors, just like any other SQL query. If you encounter errors when executing your native SQL query, make sure to check for syntax errors in the query.

4. Check for mapping errors: If you are mapping the results of a native SQL query to entities or non-entity classes, make sure that the mapping is correct. Any errors in the mapping can cause the query to fail or return unexpected results.

5. Use a debugger: If you are still having trouble debugging your native SQL query, you can use a debugger to step through the code and see where the query is failing. Set breakpoints in your code and step through it to see where the query is failing or returning unexpected results.

Step-by-Step Implementation

Step 1: Create Maven Project

• Create a Maven Project in IDE (IntelliJ/Eclipse)
• Used to manage dependencies automatically
• Follow standard structure: src/main/java -> Java files , src/main/resources -> config files

<dependencies>
    <!-- Hibernate Core -->
    <dependency>
        <groupId>org.hibernate.orm</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>6.4.4.Final</version>
    </dependency>

    <!-- MySQL Driver -->
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.3.0</version>
    </dependency>

    <!-- JPA API -->
    <dependency>
        <groupId>jakarta.persistence</groupId>
        <artifactId>jakarta.persistence-api</artifactId>
        <version>3.1.0</version>
    </dependency>
</dependencies>

Step 2: Create Database

• Create database in MySQL
• Hibernate will auto-create tables
• Enabled using hbm2ddl.auto=update

Syntax:

CREATE DATABASE testdb;

Step 3: Configure Hibernate

File must be inside resources folder . Used to configure DB connection . Important properties are

• driver_class -> JDBC driver
• url -> database path
• dialect -> DB-specific SQL
• show_sql -> print queries
• mapping -> entity class

hibernate.cfg.xml

<hibernate-configuration>
 <session-factory>

  <property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>
  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/testdb</property>
  <property name="hibernate.connection.username">root</property>
  <property name="hibernate.connection.password">password</property>

  <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
  <property name="hibernate.hbm2ddl.auto">update</property>
  <property name="show_sql">true</property>

  <mapping class="com.example.Employee"/>
 </session-factory>
</hibernate-configuration>

Step 4: Create Entity Class

• Represents table in database
• Annotations used: @Entity ->marks class , @Table -> table name , @Id -> primary key
• Fields -> columns

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

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

    private String name;
    private double salary;

    // Getters & Setters
}

Step 5: Create Hibernate Utility Class

• Used to create SessionFactory
• SessionFactory is: Heavy object and Created once
• Provides session to interact with DB

HibernateUtil.java

public class HibernateUtil {

    private static final SessionFactory factory;

    static {
        factory = new Configuration().configure().buildSessionFactory();
    }

    public static SessionFactory getSessionFactory() {
        return factory;
    }
}

Step 6: Insert Data

• Required for testing query
• Use transaction for DB operations
• Steps: Open session-> Begin transaction ->Save object -> Commit

Session session = HibernateUtil.getSessionFactory().openSession();
Transaction tx = session.beginTransaction();

Employee e1 = new Employee();
e1.setName("Priya");
e1.setSalary(50000);

Employee e2 = new Employee();
e2.setName("Rahul");
e2.setSalary(70000);

session.save(e1);
session.save(e2);

tx.commit();
session.close();

Step 7: Execute Native SQL Query

• Native SQL = direct database query
• Method used: createNativeQuery()
• Supports: Parameters (:salary) and Entity mapping

NativeQueryExample.java

Session session = HibernateUtil.getSessionFactory().openSession();

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

NativeQuery<Employee> query =
        session.createNativeQuery(sql, Employee.class);

query.setParameter("salary", 60000);

List<Employee> list = query.getResultList();

for (Employee emp : list) {
    System.out.println(emp.getId() + " " +
                       emp.getName() + " " +
                       emp.getSalary());
}

session.close();

Step 8: Output

• Filters records based on condition
• Returns only matching rows

2 Rahul 70000.0