Hibernate Native Query Update Example

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

Introduction

Native SQL queries in Hibernate allow you to execute database-specific SQL statements directly. Using native SQL queries to perform update operations can be useful when you need to perform bulk updates or use database-specific features that are not available through HQL (Hibernate Query Language).

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 update records.
5. Demonstrate the update operation 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-update</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 for Update

Create a class EmployeeService to handle the database operation of updating an Employee record using a native SQL query.

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 void updateEmployeeSalary(Long id, double newSalary) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;

        try {
            transaction = session.beginTransaction();
            String sql = "UPDATE Employee SET salary = :salary WHERE id = :id";
            session.createNativeQuery(sql)
                    .setParameter("salary", newSalary)
                    .setParameter("id", id)
                    .executeUpdate();
            transaction.commit();
            System.out.println("Employee salary updated successfully");
        } catch (Exception e) {
            if (transaction != null) {
                transaction.rollback();
            }
            e.printStackTrace();
        } finally {
            session.close();
        }
    }
}

Explanation:

• The updateEmployeeSalary method uses a native SQL UPDATE statement to update the salary of an Employee record.
• The method uses setParameter to set the values for the salary and id parameters.
• The executeUpdate method executes the update statement.
• The transaction is committed if the update is successful, or rolled back if an exception occurs.

Step 4: Demonstrate Native SQL Query for Update

Create a MainApp class to demonstrate updating an Employee record using a native SQL query. This class calls the updateEmployeeSalary method of EmployeeService.

package com.example.main;

import com.example.service.EmployeeService;

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

        // Update the salary of an employee with ID 1
        employeeService.updateEmployeeSalary(1L, 7500.00);
    }
}

Explanation of the Code in Step 4

1. Create an EmployeeService Instance:

   EmployeeService employeeService = new EmployeeService();
   

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

2. Update Employee Salary:

   employeeService.updateEmployeeSalary(1L, 7500.00);
   

   The updateEmployeeSalary method is called to update the salary of the employee with ID 1 to 7500.00.

Sample Output

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

Employee salary updated successfully

This output indicates that the employee's salary was successfully updated using a native SQL query.

Conclusion

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