JPA + Hibernate - @NamedStoredProcedureQuery Example

By Arvind Rai, February 09, 2023

On this page, we will learn to use Jakarta persistence @NamedStoredProcedureQuery annotation in our Hibernate application.
1. The @NamedStoredProcedureQuery is used to run a stored procedure. It is used on an entity or mapped superclass.
2. The @NamedStoredProcedureQuery specifies and names a stored procedure, its parameters, and its result type.
3. The @NamedStoredProcedureQueries annotation is used to configure more than one @NamedStoredProcedureQuery annotations.
4. Find a sample code to use @NamedStoredProcedureQuery.

@NamedStoredProcedureQueries({
	@NamedStoredProcedureQuery(
		name = "myAddArticle", 
		procedureName = "add_article",
		parameters = {
		  @StoredProcedureParameter(name = "title", type = String.class, mode = ParameterMode.IN),
		  @StoredProcedureParameter(name = "category", type = String.class, mode = ParameterMode.IN)
		}
	),
	@NamedStoredProcedureQuery(
		name = "myReadArticle", 
		procedureName = "read_article", 
		parameters = {
		  @StoredProcedureParameter(name = "id", type = Integer.class, mode = ParameterMode.IN),
		  @StoredProcedureParameter(name = "title", type = String.class, mode = ParameterMode.OUT),
		  @StoredProcedureParameter(name = "category", type = String.class, mode = ParameterMode.OUT)
		}
	)	
})
@Entity()
@Table(name = "articles")
public class Article {
------
}

@NamedStoredProcedureQuery Elements

Find the @NamedStoredProcedureQuery elements.
1. name : Required. The name is used to access the stored procedure in EntityManager methods such as

StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("myReadArticle");

2. procedureName : Required. The name of the stored procedure in the database.
3. parameters : Optional. Information about all parameters of the stored procedure.

Note: All parameters must be defined in the same order in which they occur in the stored procedure.

Suppose we have a database stored procedure code snippet as given below.

CREATE PROCEDURE add_article ( 
  IN in_title VARCHAR(100), 
  IN in_category VARCHAR(100)
)

In parameters, first entry will be for in_title and second entry will be for in_category. The name value has not necessarily to match with database stored procedure parameter. This name is used to set and get values to parameter in our Hibernate code.

parameters = {
  @StoredProcedureParameter(name = "title", type = String.class, mode = ParameterMode.IN), // for in_title
  @StoredProcedureParameter(name = "category", type = String.class, mode = ParameterMode.IN) // for in_category
}

Use name to set and get values.

query.setParameter("title", "Stored Procedure in Hibernate");
query.setParameter("category", "Hibernate");

4. resultClasses : Optional. The class or classes that are used to map the results.

resultClasses = { Article.java }

5. resultSetMappings : The names of one or more result set mappings, as defined in metadata.
6. hints : Query properties and hints.

ParameterMode

The ParameterMode enum specifies the mode of a parameter of a stored procedure query. Find the constants of this enum.
IN : Stored procedure input parameter.

@StoredProcedureParameter(
    name = "id", 
    type = Integer.class,
    mode = ParameterMode.IN
)

OUT : Stored procedure output parameter.

@StoredProcedureParameter(
    name = "title",
    type = String.class,
    mode = ParameterMode.OUT
)

INOUT : Stored procedure input/output parameter.

@StoredProcedureParameter(
    name = "writer",
    type = String.class,
    mode = ParameterMode.INOUT
)

REF_CURSOR : Stored procedure reference cursor parameter.

@StoredProcedureParameter(
    name = "article_cursor",
    type = void.class,
    mode = ParameterMode.REF_CURSOR
)

Complete Example

1. Technologies Used
1. Java 19
2. Hibernate 6
3. Jakarta Persistence API 3
4. MySQL 5.5
2. MySQL Database

CREATE TABLE `articles` (
	`article_id` INT(5) NOT NULL AUTO_INCREMENT,
	`title` VARCHAR(200) NOT NULL,
	`category` VARCHAR(100) NOT NULL,
	PRIMARY KEY (`article_id`)
)

CREATE PROCEDURE read_article ( 
  IN in_id INTEGER, 
  OUT out_title VARCHAR(100), 
  OUT out_category VARCHAR(100)
)
BEGIN 
  SELECT title, category
  INTO out_title, out_category
  FROM articles WHERE article_id = in_id;
END; 

CREATE PROCEDURE add_article ( 
  IN in_title VARCHAR(100), 
  IN in_category VARCHAR(100)
)
BEGIN 
  INSERT INTO articles (title, category) values (in_title, in_category);
END;

3. pom.xml

<dependencies>
	<dependency>
		<groupId>org.hibernate</groupId>
		<artifactId>hibernate-core</artifactId>
		<version>6.1.6.Final</version>
	</dependency>
	<dependency>
		<groupId>org.hibernate</groupId>
		<artifactId>hibernate-entitymanager</artifactId>
		<version>6.0.0.Alpha7</version>
	</dependency>		
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>8.0.32</version>
	</dependency>
</dependencies>

4. persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence
          https://jakarta.ee/xml/ns/persistence/persistence_3_0.xsd"
	version="3.0">
	<persistence-unit name="com.concretepage">
		<description>Procedure Demo</description>
		<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
		<properties>
			<property name="hibernate.dialect"
				value="org.hibernate.dialect.MySQLDialect" />
			<property name="jakarta.persistence.jdbc.driver"
				value="com.mysql.cj.jdbc.Driver" />
			<property name="jakarta.persistence.jdbc.url"
				value="jdbc:mysql://localhost/concretepage" />
			<property name="jakarta.persistence.jdbc.user" value="root" />
			<property name="jakarta.persistence.jdbc.password"
				value="cp" />
		</properties>
	</persistence-unit>
</persistence>

5. Java Code
HibernateUtil.java

package com.concretepage.util;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.Persistence;

public class HibernateUtil {
 	private static final EntityManagerFactory emFactory;
	static {
		   emFactory = Persistence.createEntityManagerFactory("com.concretepage");
	}
	public static EntityManager getEntityManager(){
		return emFactory.createEntityManager();
	}
}

Article.java

package com.concretepage.entity;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.NamedStoredProcedureQueries;
import jakarta.persistence.NamedStoredProcedureQuery;
import jakarta.persistence.ParameterMode;
import jakarta.persistence.StoredProcedureParameter;
import jakarta.persistence.Table;

@NamedStoredProcedureQueries({
	@NamedStoredProcedureQuery(
		name = "myAddArticle", 
		procedureName = "add_article",
		parameters = {
		  @StoredProcedureParameter(name = "title", type = String.class, mode = ParameterMode.IN), // for in_title
		  @StoredProcedureParameter(name = "category", type = String.class, mode = ParameterMode.IN) // for in_category
		}
	),
	@NamedStoredProcedureQuery(
		name = "myReadArticle", 
		procedureName = "read_article", 
		parameters = {
		  @StoredProcedureParameter(name = "id", type = Integer.class, mode = ParameterMode.IN), // for in_id  
		  @StoredProcedureParameter(name = "title", type = String.class, mode = ParameterMode.OUT), // for out_title
		  @StoredProcedureParameter(name = "category", type = String.class, mode = ParameterMode.OUT) // for out_category 
		}
	)	
})
@Entity()
@Table(name = "articles")
public class Article {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "article_id")
  private int articleId;
  @Column(name = "title")
  private String title;
  @Column(name = "category")
  private String category;
  
  //setters and getters
}

RunProcedure.java

package com.concretepage;
import com.concretepage.util.HibernateUtil;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityTransaction;
import jakarta.persistence.StoredProcedureQuery;

public class RunProcedure {
  public static void main(String[] args) {
	// Run Stored procedure (add_article)
	EntityManager entityManager = HibernateUtil.getEntityManager();
	StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("myAddArticle");	
	query.setParameter("title", "Stored Procedure in Hibernate");
	query.setParameter("category", "Hibernate");
	EntityTransaction entityTransaction = entityManager.getTransaction();
	entityTransaction.begin();
	query.execute();
	entityTransaction.commit();
	
	// Run Stored procedure (read_article)
	query = entityManager.createNamedStoredProcedureQuery("myReadArticle");
	query.setParameter("id", 1);
	query.execute();
	String title = (String) query.getOutputParameterValue("title");
	String cat = (String) query.getOutputParameterValue("category");
	System.out.println(title);
	System.out.println(cat);	
  }
}

References

Annotation Type NamedStoredProcedureQuery
Hibernate ORM User Guide

POSTED BY

ARVIND RAI

LEARN MORE

JAVA SPRING BOOT ANGULAR Angular Material NGRX JAVASCRIPT ANDROID THYMELEAF FREEMARKER Hibernate Struts 2 Testing MAVEN APACHE API JACKSON API JUNIT 5

JPA + Hibernate - @NamedStoredProcedureQuery Example

@NamedStoredProcedureQuery Elements

ParameterMode

Complete Example

References

About Us

Utility