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.