Hibernate CRUD Native SQL Query Example

This tutorial will guide you through setting up and demonstrating the use of native SQL queries in Hibernate 6.4 with Java 21. We'll create a simple application that performs CRUD operations using native SQL queries.

Introduction

Native SQL queries in Hibernate allow you to execute database-specific SQL statements directly. This is useful when you need to perform operations that are not easily expressed using HQL (Hibernate Query Language) or when you want to use database-specific features.

In this tutorial, we will:

  1. Set up a Maven project with Hibernate and an H2 database dependency.
  2. Configure Hibernate.
  3. Create an entity class (Product).
  4. Implement CRUD operations using native SQL queries.
  5. Demonstrate native SQL queries 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 H2 (an in-memory database for simplicity).

<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://maven.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>

        <!-- H2 Database -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>2.1.214</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.H2Dialect</property>
        <property name="hibernate.connection.driver_class">org.h2.Driver</property>
        <property name="hibernate.connection.url">jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1</property>
        <property name="hibernate.connection.username">sa</property>
        <property name="hibernate.connection.password"></property>
        <property name="hibernate.hbm2ddl.auto">update</property>
        <property name="hibernate.show_sql">true</property>
    </session-factory>
</hibernate-configuration>

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 Product 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 Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private double price;

    // 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 double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }
}

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: Create the Hibernate Utility Class

Create a utility class HibernateUtil to manage the Hibernate SessionFactory. This class ensures a single instance of SessionFactory is created and provides a method to close it.

package com.example.util;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HibernateUtil {
    private static final SessionFactory sessionFactory = buildSessionFactory();

    private static SessionFactory buildSessionFactory() {
        try {
            // Create the SessionFactory from hibernate.cfg.xml
            return new Configuration().configure().buildSessionFactory();
        } catch (Throwable ex) {
            // Make sure you log the exception, as it might be swallowed
            System.err.println("Initial SessionFactory creation failed." + ex);
            throw new ExceptionInInitializerError(ex);
        }
    }

    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }

    public static void shutdown() {
        // Close caches and connection pools
        getSessionFactory().close();
    }
}

Explanation:

  • The buildSessionFactory method creates the SessionFactory from the hibernate.cfg.xml configuration file.
  • The getSessionFactory method returns the singleton instance of SessionFactory.
  • The shutdown method closes the SessionFactory to release resources.

Step 4: Implement CRUD Operations Using Native SQL Queries

Create a class ProductService to handle database operations using native SQL queries. This class includes methods for creating, reading, updating, and deleting Product entities.

Create Product

package com.example.service;

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

public class ProductService {

    public void createProduct(Product product) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;

        try {
            transaction = session.beginTransaction();
            String sql = "INSERT INTO Product (name, price) VALUES (:name, :price)";
            session.createNativeQuery(sql)
                    .setParameter("name", product.getName())
                    .setParameter("price", product.getPrice())
                    .executeUpdate();
            transaction.commit();
            System.out.println("Product created successfully");
        } catch (Exception e) {
            if (transaction != null) {
                transaction.rollback();
            }
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    public Product getProduct(Long id) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Product product = null;

        try {
            String sql = "SELECT * FROM Product WHERE id = :id";
            Object[] result = (Object[]) session.createNativeQuery(sql)
                    .setParameter("id", id)
                    .getSingleResult();
            if (result != null) {
                product = new Product();
                product.setId(((Number) result[0]).longValue());
                product.setName((String) result[1]);
                product.setPrice((Double) result[2]);
                System.out.println("Product retrieved: " + product.getName());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }

        return product;
    }

    public void updateProduct(Product product) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;

        try {
            transaction = session.beginTransaction();
            String sql = "UPDATE Product SET name = :name, price = :price WHERE id = :id";
            session.createNativeQuery(sql)
                    .setParameter("name", product.getName())
                    .setParameter("price", product.getPrice())
                    .setParameter("id", product.getId())
                    .executeUpdate();
            transaction.commit();
            System.out.println("Product updated successfully");
        } catch (Exception e) {
            if (transaction != null) {
                transaction.rollback();
            }
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    public void deleteProduct(Long id) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;

        try {
            transaction = session.beginTransaction();
            String sql = "DELETE FROM Product WHERE id = :id";
            session.createNativeQuery(sql)
                    .setParameter("id", id)
                    .executeUpdate();
            transaction.commit();
            System.out.println("Product deleted successfully");
        } catch (Exception e) {
            if (transaction != null) {
                transaction.rollback();
            }
            e.printStackTrace();
        } finally {
            session.close();
        }
    }
}

Explanation:

  • The createProduct method uses a native SQL INSERT statement to save a Product entity to the database.
  • The getProduct method uses a native SQL SELECT statement to retrieve a Product entity by its ID.
  • The updateProduct method uses a native SQL UPDATE statement to update a Product entity in the database.
  • The deleteProduct method uses a native SQL DELETE statement to delete a Product entity from the database by its ID.

Step 5: Demonstrate Native SQL Queries

Create a MainApp class to demonstrate CRUD operations using native SQL queries. This class calls the CRUD methods of ProductService.

package com.example.main;

import com.example.entity.Product;
import com.example.service.ProductService;

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

        // Create a product
        Product product = new Product();
        product.setName("Laptop");
        product.setPrice(1500.00);
        productService.createProduct(product);

        // Retrieve the product
        Product retrievedProduct = productService.getProduct(product.getId());

        // Update the product
        if (retrievedProduct != null) {
            retrievedProduct.setPrice(1400.00);
            productService.updateProduct(retrievedProduct);
        }

        // Delete the product
        productService.deleteProduct(retrievedProduct.getId());
    }
}

Explanation of the Code in Step 5

  1. Create a ProductService Instance:

    ProductService productService = new ProductService();

    An instance of ProductService is created to call its methods for performing CRUD operations.

  2. Create a Product:

    Product product = new Product();
product.setName("Laptop");
product.setPrice(1500.00);
productService.createProduct(product);

    A Product entity is created and saved to the database using the createProduct method.

  3. Retrieve the Product:

    Product retrievedProduct = productService.getProduct(product.getId());

    The getProduct method is called to retrieve the Product entity by its ID.

  4. Update the Product:

    if (retrievedProduct != null) {
    retrievedProduct.setPrice(1400.00);
    productService.updateProduct(retrievedProduct);
}

    The retrieved product's price is updated, and the updateProduct method is called to save the changes.

  5. Delete the Product:

    productService.deleteProduct(retrievedProduct.getId());

    The deleteProduct method is called to delete the Product entity by its ID.

Sample Output

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

Product created successfully
Product retrieved: Laptop
Product updated successfully
Product deleted successfully

This output indicates that the product was successfully created, retrieved, updated, and deleted using native SQL queries in Hibernate.

Conclusion

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


Comments

Post a Comment