Hibernate Native Query Update Example

Author:

This tutorial will guide you through setting up and demonstrating the use of native SQL queries to update records in Hibernate 6+ using a MySQL database. 

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>
        <!-- Optional in Hibernate 6: Hibernate can auto-detect these based on JDBC URL or driver dependency -->
        <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.

In Hibernate 6, the hibernate.dialect and hibernate.connection.driver_class properties are optional, as Hibernate automatically infer these based on the JDBC URL or the JDBC driver dependency. This reduces the need for explicit configuration of these properties.

  • Dialect: Hibernate determines the correct dialect (e.g., MySQL, PostgreSQL) from the JDBC URL.
  • Driver Class: Hibernate infers the JDBC driver from the driver dependency, making this property unnecessary unless specific customization is needed.

You can still specify these properties if you want explicit control over the configuration, but for most use cases, Hibernate handles it dynamically.

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.

Comments

Post a Comment

Leave Comment