Hibernate Native SQL Queries with CRUD Operations

Author:
In this tutorial, we will demonstrate how to use native SQL queries with Hibernate to perform CRUD (Create, Read, Update, Delete) operations using a Todo entity. Native SQL queries allow you to execute SQL statements directly, bypassing HQL (Hibernate Query Language).

Prerequisites

Before we start, ensure you have the following:

  • Java Development Kit (JDK) installed
  • Apache Maven installed
  • An IDE (such as IntelliJ IDEA, Eclipse, or VS Code) installed

Step 1: Setting Up the Hibernate Project

1.1 Create a Maven Project

  1. Open your IDE and create a new Maven project.

  2. Configure the pom.xml file:

Add the following dependencies to your pom.xml:

<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-sql</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.hibernate.orm</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>6.4.0.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate.orm</groupId>
            <artifactId>hibernate-hikaricp</artifactId>
            <version>6.4.0.Final</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.32</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>1.7.32</version>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>2.1.210</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>17</source>
                    <target>17</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

1.2 Configure Hibernate

Create a file named hibernate.cfg.xml in the src/main/resources directory with the following content:

<!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>
        <property name="hibernate.format_sql">true</property>
    </session-factory>
</hibernate-configuration>

1.3 Create the Todo Entity

Create a Todo class in the com.example.hibernateexamples.model package:

package com.example.hibernateexamples.model;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class Todo {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
    private String description;
    private boolean completed;

    // Getters and Setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public boolean isCompleted() {
        return completed;
    }

    public void setCompleted(boolean completed) {
        this.completed = completed;
    }
}

1.4 Create the Hibernate Utility Class

Create a HibernateUtil class in the com.example.hibernateexamples.util package:

package com.example.hibernateexamples.util;

import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;

public class HibernateUtil {

    private static final SessionFactory sessionFactory = buildSessionFactory();

    private static SessionFactory buildSessionFactory() {
        try {
            Configuration configuration = new Configuration();
            configuration.configure("hibernate.cfg.xml");
            return configuration.buildSessionFactory(new StandardServiceRegistryBuilder()
                    .applySettings(configuration.getProperties()).build());
        } catch (Throwable ex) {
            System.err.println("Initial SessionFactory creation failed." + ex);
            throw new ExceptionInInitializerError(ex);
        }
    }

    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }

    public static void shutdown() {
        getSessionFactory().close();
    }
}

Step 2: Performing CRUD Operations with Native SQL Queries

2.1 INSERT Operation

Create a TodoDao class in the com.example.hibernateexamples.dao package to handle database operations:

package com.example.hibernateexamples.dao;

import com.example.hibernateexamples.model.Todo;
import com.example.hibernateexamples.util.HibernateUtil;
import org.hibernate.Session;
import org.hibernate.Transaction;

public class TodoDao {

    public void saveTodoUsingNativeSQL(Todo todo) {
        Transaction transaction = null;
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            transaction = session.beginTransaction();
            String sql = "INSERT INTO Todo (title, description, completed) VALUES (:title, :description, :completed)";
            session.createNativeQuery(sql)
                    .setParameter("title", todo.getTitle())
                    .setParameter("description", todo.getDescription())
                    .setParameter("completed", todo.isCompleted())
                    .executeUpdate();
            transaction.commit();
        } catch (Exception e) {
            if (transaction != null) {
                transaction.rollback();
            }
            e.printStackTrace();
        }
    }
}

2.2 SELECT Operation

Add a method to the TodoDao class to retrieve all todos:

import java.util.List;

public List<Todo> getTodosUsingNativeSQL() {
    try (Session session = HibernateUtil.getSessionFactory().openSession()) {
        String sql = "SELECT * FROM Todo";
        List<Todo> todos = session.createNativeQuery(sql, Todo.class).list();
        return todos;
    }
}

2.3 UPDATE Operation

Add a method to the TodoDao class to update a todo:

public void updateTodoUsingNativeSQL(Todo todo) {
    Transaction transaction = null;
    try (Session session = HibernateUtil.getSessionFactory().openSession()) {
        transaction = session.beginTransaction();
        String sql = "UPDATE Todo SET title = :title, description = :description, completed = :completed WHERE id = :id";
        session.createNativeQuery(sql)
                .setParameter("title", todo.getTitle())
                .setParameter("description", todo.getDescription())
                .setParameter("completed", todo.isCompleted())
                .setParameter("id", todo.getId())
                .executeUpdate();
        transaction.commit();
    } catch (Exception e) {
        if (transaction != null) {
            transaction.rollback();
        }
        e.printStackTrace();
    }
}

2.4 DELETE Operation

Add a method to the TodoDao class to delete a todo:

public void deleteTodoUsingNativeSQL(Long id) {
    Transaction transaction = null;
    try (Session session = HibernateUtil.getSessionFactory().openSession()) {
        transaction = session.beginTransaction();
        String sql = "DELETE FROM Todo WHERE id = :id";
        session.createNativeQuery(sql)
                .setParameter("id", id)
                .executeUpdate();
        transaction.commit();
    } catch (Exception e) {
        if (transaction != null) {
            transaction.rollback();
        }
        e.printStackTrace();
    }
}

Step 3: Testing the CRUD Operations

Create a Main class in the com.example.hibernateexamples package to test the CRUD operations:

package com.example.hibernateexamples;

import com.example.hibernateexamples.dao.TodoDao;
import com.example.hibernateexamples.model.Todo;

import java.util.List;

public class Main {
    public static void main(String[] args) {
        TodoDao todoDao = new TodoDao();

        // Insert a todo
        Todo todo1 = new Todo();


 todo1.setTitle("Todo 1");
        todo1.setDescription("Description 1");
        todo1.setCompleted(false);
        todoDao.saveTodoUsingNativeSQL(todo1);

        // Insert another todo
        Todo todo2 = new Todo();
        todo2.setTitle("Todo 2");
        todo2.setDescription("Description 2");
        todo2.setCompleted(false);
        todoDao.saveTodoUsingNativeSQL(todo2);

        // Select all todos
        List<Todo> todos = todoDao.getTodosUsingNativeSQL();
        todos.forEach(t -> System.out.println(t.getTitle() + " - " + t.getDescription() + " - " + t.isCompleted()));

        // Update a todo
        todo1.setCompleted(true);
        todoDao.updateTodoUsingNativeSQL(todo1);

        // Select all todos again
        todos = todoDao.getTodosUsingNativeSQL();
        todos.forEach(t -> System.out.println(t.getTitle() + " - " + t.getDescription() + " - " + t.isCompleted()));

        // Delete a todo
        todoDao.deleteTodoUsingNativeSQL(todo2.getId());

        // Select all todos again
        todos = todoDao.getTodosUsingNativeSQL();
        todos.forEach(t -> System.out.println(t.getTitle() + " - " + t.getDescription() + " - " + t.isCompleted()));
    }
}

Conclusion

In this tutorial, we set up a simple Hibernate project using Maven and demonstrated how to perform basic CRUD operations using native SQL queries. We created a Todo entity and a TodoDao class to handle database operations using native SQL. We tested the CRUD operations in a Main class. By following this structure, you can extend and customize the application as needed.

Comments

Post a Comment

Leave Comment