Hibernate Native Query Insert and Return ID Example

This tutorial will guide you through setting up and demonstrating the use of native SQL queries with Hibernate to insert a record into a MySQL database and return the generated ID. We will use the Employee entity for this example.

Introduction

Native SQL queries in Hibernate allow you to execute database-specific SQL statements directly. Sometimes, after inserting a record, you need to retrieve the generated ID. This tutorial will show you how to perform an insert operation and return the generated ID using a native query in Hibernate.

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 an example of a native SQL query to insert a record and return the generated ID.
5. Demonstrate the insert operation and return the ID with 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 to Insert and Return ID

Create a class EmployeeService to handle the database operation of inserting an Employee record and returning the generated ID.

package com.example.service;

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

public class EmployeeService {

    public Long insertEmployee(Employee employee) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;
        Long generatedId = null;

        try {
            transaction = session.beginTransaction();
            String sql = "INSERT INTO Employee (name, department, salary) VALUES (:name, :department, :salary)";
            session.createNativeQuery(sql)
                    .setParameter("name", employee.getName())
                    .setParameter("department", employee.getDepartment())
                    .setParameter("salary", employee.getSalary())
                    .executeUpdate();

            // Retrieve the generated ID
            sql = "SELECT LAST_INSERT_ID()";
            generatedId = ((Number) session.createNativeQuery(sql).getSingleResult()).longValue();

            transaction.commit();
            System.out.println("Employee inserted with ID: " + generatedId);
        } catch (Exception e) {
            if (transaction != null) {
                transaction.rollback();
            }
            e.printStackTrace();
        } finally {
            session.close();
        }

        return generatedId;
    }
}

Explanation:

• The insertEmployee method inserts a new Employee record into the database using a native SQL INSERT statement.
• The LAST_INSERT_ID() function is used to retrieve the last auto-generated ID after the insert operation.
• The method returns the generated ID.

Step 4: Demonstrate Insert and Return ID

Create a MainApp class to demonstrate inserting an Employee record and returning the generated ID. This class calls the insertEmployee method of EmployeeService.

package com.example.main;

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

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

        // Create an employee
        Employee employee = new Employee();
        employee.setName("John Doe");
        employee.setDepartment("IT");
        employee.setSalary(5000.00);

        // Insert employee and get the generated ID
        Long generatedId = employeeService.insertEmployee(employee);

        System.out.println("Generated Employee ID: " + generatedId);
    }
}

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. Create an Employee:

   Employee employee = new Employee();
   employee.setName("John Doe");
   employee.setDepartment("IT");
   employee.setSalary(5000.00);
   

   An Employee entity is created and its properties are set.

3. Insert Employee and Get the Generated ID:

   Long generatedId = employeeService.insertEmployee(employee);
   

   The insertEmployee method is called to insert the Employee entity and return the generated ID.

4. Print the Generated ID:

   System.out.println("Generated Employee ID: " + generatedId);
   

   The generated ID is printed to the console.

Sample Output

When you run the MainApp class, you should see the following output:

Employee inserted with ID: 1
Generated Employee ID: 1

This output indicates that the employee was successfully inserted, and the generated ID was retrieved and printed.

Conclusion

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