Posted in Hibernate

Stored procedure example in Hibernate

Introduction

In this tutorial I will show you how to call stored procedure using Hibernate framework. I will show you both XML and Annotation based configurations for calling stored procedure.

We will use MySQL database to create and call stored procedure. We will also use both maven and gradle build tools to build the example application.

Prerequisites

Eclipse 4.12, At least Java 8, Gradle 6.1.1, Maven 3.6.3, Hibernate 5.4.11

Create Stored Procedure

We will create stored procedure in MySQL server under jeejava database.

DELIMITER $$
CREATE
    PROCEDURE `jeejava`.`getCds`(cdid BIGINT)
    BEGIN
    SELECT * FROM cd WHERE id=cdid;
    END$$
DELIMITER ;

Create Table

The corresponding table with sample data is given below:

CREATE TABLE IF NOT EXISTS `cd` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `artist` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table jeejava.cd: ~6 rows (approximately)
/*!40000 ALTER TABLE `cd` DISABLE KEYS */;
INSERT INTO `cd` (`id`, `title`, `artist`) VALUES
	(1, 'Title 1', 'Artist 1'),
	(2, 'Title 2', 'Artits 2'),
	(3, 'Title 3', 'Artist 3'),
	(4, 'Title 4', 'Artist 4'),
	(5, 'Title 5', 'Artist 5'),
	(10, 'Single Title', 'Single Artist');

Create Project

Create gradle or maven based project in Eclipse. The name of the project is hibernate-stored-procedure.

If you are creating gradle based project then you can use below build.gradle script:

plugins {
    id 'java-library'
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    jcenter()
}

dependencies {
    implementation('org.hibernate:hibernate-core:5.4.11.Final')
    implementation('mysql:mysql-connector-java:8.0.17')
}

If you are creating maven based project then you can use below pom.xml file:

<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.roytuts</groupId>
	<artifactId>hibernate-stored-procedure</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-core</artifactId>
			<version>5.4.11.Final</version>
		</dependency>
		
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.17</version>
		</dependency>
	</dependencies>

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

Hibernate Configuration

Create hibernate.cfg.xml file with the following content and put the file under classpath directory src/main/resources.

<?xml version="1.0" encoding="UTF-8"?>
<!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/jeejava?zeroDateTimeBehavior=convertToNull</property>
		<property name="hibernate.connection.username">root</property>
		<property name="hibernate.connection.password">root</property>
		<property name="hibernate.show_sql">true</property>
		<property name="hibernate.format_sql">true</property>
		<property name="hibernate.current_session_context_class">thread</property>
		<property name="hibernate.query.factory_class">org.hibernate.hql.internal.classic.ClassicQueryTranslatorFactory</property>
		<mapping class="com.roytuts.hibernate.stored.procedure.entity.Cd" />
	</session-factory>
</hibernate-configuration>

Hibernate Mapping

Create the hibernate mapping file and domain object for cd table which is in jeejava database.

When we are using XML based configuration then we need both XML mapping and Java class to map the table with Java object.

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="com.roytuts.hibernate.stored.procedure.entity.Cd" table="cd" catalog="jeejava">
        <id name="id" type="java.lang.Long">
            <column name="id" />
            <generator class="identity" />
        </id>
        <property name="title" type="string">
            <column name="titel" length="100" />
        </property>
        <property name="artist" type="string">
            <column name="interpret" length="100" />
        </property>
    </class>
    <sql-query name="getCds">
        <return alias="cd" class="com.roytuts.hibernate.stored.procedure.entity.Cd"/>
        <![CDATA[CALL getCds(:cdId)]]>
    </sql-query>
</hibernate-mapping>

The corresponding Java class is given below:

public class Cd implements Serializable {

	private static final long serialVersionUID = 1L;

	private int id;

	private String title;

	private String artist;

	//getters and setters

}

When we are using annotation based configuration then we can use the single class to map table and Java object:

package com.roytuts.hibernate.stored.procedure.entity;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedNativeQuery;
import javax.persistence.Table;

@Entity
@Table(name = "cd")
@NamedNativeQuery(name = "getCds", query = "CALL getCds(:cdId)", resultClass = Cd.class)
public class Cd implements Serializable {

	private static final long serialVersionUID = 1L;

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = "id")
	private int id;

	@Column(name = "title")
	private String title;

	@Column(name = "artist")
	private String artist;

	//setters and getters

}

Create SessionFactory

Create the HibernateUtil.java class for building session factory.

package com.roytuts.hibernate.stored.procedure.util;

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

public class HibernateUtil {

	private static SessionFactory sessionFactory;

	public static SessionFactory getSessionFactory() {
		if (sessionFactory == null) {
			sessionFactory = new Configuration().configure("hibernate.cfg.xml").buildSessionFactory();
		}

		return sessionFactory;
	}
}

Call Stored Procedure

Now we will create a main class which will call the stored procedure. We will call the stored procedure in two ways – using native query and using named query.

package com.roytuts.hibernate.stored.procedure;

import org.hibernate.Session;
import org.hibernate.Transaction;

import com.roytuts.hibernate.stored.procedure.entity.Cd;
import com.roytuts.hibernate.stored.procedure.util.HibernateUtil;

public class HibernateStoredProcedureApp {

	public static void main(String[] args) {
		HibernateStoredProcedureApp sp = new HibernateStoredProcedureApp();

		Cd cd1 = sp.getCdByNativeSQL(1);
		System.out.println("Title: " + cd1.getTitle());
		System.out.println("Interpret: " + cd1.getArtist());

		Cd cd2 = sp.getCdByNamedQuery(4);
		System.out.println("Title: " + cd2.getTitle());
		System.out.println("Interpret: " + cd2.getArtist());
	}

	public Cd getCdByNativeSQL(long cdId) {
		Session session = null;
		try {
			session = HibernateUtil.getSessionFactory().openSession();
			Cd cd = (Cd) session.createNativeQuery("CALL getCds(:cdId)").addEntity(Cd.class).setParameter("cdId", cdId)
					.getSingleResult();
			return cd;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		return null;
	}

	public Cd getCdByNamedQuery(long cdId) {
		Session session = null;
		Transaction tx = null;
		try {
			session = HibernateUtil.getSessionFactory().getCurrentSession();
			tx = session.beginTransaction();
			Cd cd = (Cd) session.getNamedNativeQuery("getCds").setParameter("cdId", cdId).getSingleResult();
			return cd;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			tx.commit();
			session.close();
		}
		return null;
	}

}

Testing the Application

Executing the above main class will give you below output:

Hibernate: 
    CALL getCds(?)
Title: Title 1
Interpret: Artist 1
Hibernate: 
    CALL getCds(?)
Title: Title 4
Interpret: Artist 4

Source Code

Download

That’s all. Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *