Hibernate Native Query IN Clause Example

This tutorial will guide you through setting up and demonstrating the use of native SQL queries with the IN clause in Hibernate 6.4 using a MySQL database. We will use the Employee entity for this example.

Introduction

The IN clause in SQL is used to filter records based on a list of values. Using native SQL queries in Hibernate, you can execute IN clause queries to retrieve records that match any value in a specified list. This is useful for retrieving a subset of records based on multiple criteria.

In this tutorial, we will:

  1. Set up a Maven project with Hibernate and MySQL dependencies.
  2. Configure Hibernate.
  3. Create an entity class (Employee).
  4. Implement examples of native SQL queries with the IN clause.
  5. Demonstrate native SQL queries with the IN clause using a sample application.

Step 1: Set Up Your Project

1.1 Create a Maven Project

Open your IDE and create a new Maven project.

1.2 Add Dependencies

Update your pom.xml file to include the necessary dependencies for Hibernate and MySQL.

<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://www.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>hibernate-native-query-in-clause</artifactId>
    <version>1.0-SNAPSHOT</version>

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

        <!-- MySQL Connector -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.10.1</version>
                <configuration>
                    <source>21</source>
                    <target>21</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

1.3 Configure Hibernate

Create a file named hibernate.cfg.xml in the src/main/resources directory to configure Hibernate. This file contains the database connection settings and Hibernate properties.

<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/hibernate_db</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">password</property>
        <property name="hibernate.hbm2ddl.auto">update</property>
        <property name="hibernate.show_sql">true</property>
    </session-factory>
</hibernate-configuration>

Replace hibernate_db, root, and password with your MySQL database name and credentials.

Explanation:

  • hibernate.dialect specifies the SQL dialect to be used.
  • hibernate.connection.driver_class specifies the JDBC driver class.
  • hibernate.connection.url specifies the JDBC URL for the database connection.
  • hibernate.connection.username and hibernate.connection.password specify the database credentials.
  • hibernate.hbm2ddl.auto specifies the schema generation strategy.
  • hibernate.show_sql specifies whether to show SQL statements in the logs.

Step 2: Create the Entity Class

Create an entity class Employee that will be mapped to a table in the database. This class uses annotations to define the entity and its fields.

package com.example.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String department;
    private double salary;

    // Getters and setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }
}

Explanation:

  • The @Entity annotation specifies that the class is an entity and is mapped to a database table.
  • The @Id annotation specifies the primary key of the entity.
  • The @GeneratedValue(strategy = GenerationType.IDENTITY) annotation specifies that the primary key is auto-incremented.

Step 3: Implement Native SQL Query with IN Clause

Create a class EmployeeService to handle database operations using native SQL queries with the IN clause. This class includes methods to demonstrate native SQL queries with the IN clause.

Get Employees by Department

package com.example.service;

import com.example.entity.Employee;
import com.example.util.HibernateUtil;
import org.hibernate.Session;

import java.util.List;

public class EmployeeService {

    public List<Employee> getEmployeesByDepartments(List<String> departments) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        List<Employee> employees = null;

        try {
            String sql = "SELECT * FROM Employee WHERE department IN (:departments)";
            employees = session.createNativeQuery(sql, Employee.class)
                    .setParameter("departments", departments)
                    .getResultList();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }

        return employees;
    }
}

Explanation:

  • The getEmployeesByDepartments method uses a native SQL SELECT statement with the IN clause to retrieve employees who work in specified departments.
  • The setParameter method sets the list of departments for the IN clause.
  • The method returns a list of Employee entities.

Step 4: Demonstrate Native SQL Query with IN Clause

Create a MainApp class to demonstrate native SQL queries with the IN clause. This class calls the getEmployeesByDepartments method of EmployeeService.

package com.example.main;

import com.example.entity.Employee;
import com.example.service.EmployeeService;

import java.util.Arrays;
import java.util.List;

public class MainApp {
    public static void main(String[] args) {
        EmployeeService employeeService = new EmployeeService();

        // Get employees by departments
        List<String> departments = Arrays.asList("IT", "HR");
        List<Employee> employees = employeeService.getEmployeesByDepartments(departments);

        System.out.println("Employees in IT and HR departments:");
        for (Employee employee : employees) {
            System.out.println("ID: " + employee.getId() + ", Name: " + employee.getName() + ", Department: " + employee.getDepartment() + ", Salary: " + employee.getSalary());
        }
    }
}

Explanation:

  1. Create an EmployeeService Instance:

    EmployeeService employeeService = new EmployeeService();

    An instance of EmployeeService is created to call its methods for performing database operations.

  2. Get Employees by Departments:

    List<String> departments = Arrays.asList("IT", "HR");
List<Employee> employees = employeeService.getEmployeesByDepartments(departments);

    The getEmployeesByDepartments method is called to retrieve employees who work in the specified departments (IT and HR).

  3. Print the Employees:

    System.out.println("Employees in IT and HR departments:");
for (Employee employee : employees) {
    System.out.println("ID: " + employee.getId() + ", Name: " + employee.getName() + ", Department: " + employee.getDepartment() + ", Salary: " + employee.getSalary());
}

    The retrieved employees are printed to the console.

Sample Output

When you run the MainApp class, you should see the following output (assuming there are employees in the specified departments):

Employees in IT and HR departments:
ID: 1, Name: John Doe, Department: IT, Salary: 5000.0
ID: 2, Name: Jane Smith, Department: HR, Salary: 6000.0
ID: 3, Name: Emily White, Department: IT, Salary: 5200.0
ID: 4, Name: Susan Wright, Department: HR, Salary: 5700.0

This output indicates that the employees in the specified departments were successfully retrieved using a native SQL query with the IN clause.

Conclusion

In this tutorial, we have successfully demonstrated how to perform an IN clause query using native SQL in Hibernate. We set up a Hibernate project, configured Hibernate, created an entity class, implemented the IN clause query with native SQL, and demonstrated the query with a sample application. This guide provides a solid foundation for using native SQL queries with the IN clause in your Hibernate-based applications.


Comments

Post a Comment