Hibernate Native Query Projection Example

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

Introduction

Projection in Hibernate allows you to select specific columns rather than the entire entity. This can be useful for optimizing performance and retrieving only the necessary data. Native SQL queries in Hibernate can be used to perform projection queries that return only specific fields from the database.

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 projection.
  5. Demonstrate native SQL queries with projection 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-example</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 Projection

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

Get Employee Names and Departments

package com.example.service;

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

import java.util.List;

public class EmployeeService {

    public List<Object[]> getEmployeeNamesAndDepartments() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        List<Object[]> employees = null;

        try {
            String sql = "SELECT name, department FROM Employee";
            employees = session.createNativeQuery(sql).getResultList();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }

        return employees;
    }
}

Explanation:

  • The getEmployeeNamesAndDepartments method uses a native SQL SELECT statement to retrieve only the name and department columns from the Employee table.
  • The method returns a list of Object[], where each Object[] contains the name and department values.

Step 4: Demonstrate Native SQL Query with Projection

Create a MainApp class to demonstrate native SQL queries with projection. This class calls the getEmployeeNamesAndDepartments method of EmployeeService.

package com.example.main;

import com.example.service.EmployeeService;

import java.util.List;

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

        // Get employee names and departments
        List<Object[]> employees = employeeService.getEmployeeNamesAndDepartments();
        System.out.println("Employee Names and Departments:");
        for (Object[] employee : employees) {
            System.out.println("Name: " + employee[0] + ", Department: " + employee[1]);
        }
    }
}

Explanation of the Code in Step 4

  1. Create a EmployeeService Instance:

    EmployeeService employeeService = new EmployeeService();

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

  2. Get Employee Names and Departments:

    List<Object[]> employees = employeeService.getEmployeeNamesAndDepartments();

    The getEmployeeNamesAndDepartments method is called to retrieve the name and department columns from the Employee table.

  3. Print the Employee Names and Departments:

    System.out.println("Employee Names and Departments:");
for (Object[] employee : employees) {
    System.out.println("Name: " + employee[0] + ", Department: " + employee[1]);
}

    The retrieved data is printed to the console, showing the names and departments of the employees.

Sample Output

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

Employee Names and Departments:
Name: John Doe, Department: IT
Name: Jane Smith, Department: HR

This output indicates that the employee names and departments were successfully retrieved using a native SQL query with projection.

Conclusion

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


Comments

Post a Comment