Work With IN, OUT And INOUT Parameters In Stored Procedures Using Spring Data JPA

IN, OUT, INOUT Params

In this tutorial I will show you how to work with IN, OUT and INOUT parameters in Stored Procedure using Spring Data JPA. I will also call a stored procedure having no parameter at all.

I am going to use MySQL server, so there may be some limitations on MySQL server or there may be some limitations on Spring Data JPA itself on calling the stored procedure but I will at least show you the work around how to call stored procedures in different ways depending upon the type of parameters passed to stored procedures.

For example, Unlike Oracle database, MySQL server does not support REF_CURSOR, so you cannot use REF_CURSOR as parameter type in the @NamedStoredProcedureQuery on the entity class. In this case you cannot use Spring Data JPA repository style method to call stored procedure and you need to call as native query or you need to create StoredProcedureQuery instance from EntityManager.

Related Posts:

Prerequisites

Java 8+, Gradle 6.1.1 – 6.7.1, Maven 3.6.3, MySQL 8.0.17 – 8.0.22, Spring Data JPA 2.2.5 – 2.4.3

Java 19, Maven 3.8.5, MySQL 8.1.0, Spring Boot 3.2.0

Project Setup

You can create either gradle or maven based project in your favorite IDE or tool. The name of the project is spring-data-jpa-stored-procedure.

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

buildscript {
	ext {
		springBootVersion = '2.2.5.RELEASE' to 2.4.3
	}
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

plugins {
    id 'java-library'
    id 'org.springframework.boot' version "${springBootVersion}"
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    mavenCentral()
}

dependencies {
    implementation("org.springframework.boot:spring-boot-starter:${springBootVersion}")
	implementation("org.springframework.boot:spring-boot-starter-data-jpa:${springBootVersion}")
	implementation('mysql:mysql-connector-java:8.0.17') to 8.0.22
	//required only if jdk 9 or higher version is used
    runtimeOnly('javax.xml.bind:jaxb-api:2.4.0-b180830.0359')
}

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

For spring boot 3, use the following pom.xml file:

<?xml version="1.0" encoding="UTF-8"?>

<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>spring-data-jpa-stored-procedure</artifactId>
  <version>0.0.1-SNAPSHOT</version>

  <properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>19</maven.compiler.source>
		<maven.compiler.target>19</maven.compiler.target>
	</properties>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.2.0</version>
	</parent>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>com.mysql</groupId>
			<artifactId>mysql-connector-j</artifactId>			
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

For spring boot 2 use the following pom.xml file:

<?xml version="1.0" encoding="UTF-8"?>

<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>spring-data-jpa-stored-procedure</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>12</maven.compiler.source>
		<maven.compiler.target>12</maven.compiler.target>
	</properties>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.5.RELEASE to 2.4.3</version>
	</parent>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>

		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<scope>runtime</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

MySQL Table

I will create a table called user_details into MySQL server under roytuts database.

CREATE TABLE IF NOT EXISTS `user_details` (
  `id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `dob` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Now I will dump some data to test our application in the right away.

INSERT INTO `user_details` (`id`, `first_name`, `last_name`, `email`, `dob`) VALUES
	(7, 'Soumitra', 'Roy', 'contact@roytuts.com', '30-08-2016'),
	(8, 'Souvik', 'Sanyal', 'souvik.sanyal@email.com', '30-09-1991'),
	(9, 'Abc', 'Xyz', 'abc@xyz.com', '12-01-1998'),
	(10, 'Liton', 'Sarkar', 'liton@email.com', '30-08-2012');

Stored Procedures

I will create three procedures to work with IN, OUT and INOUT parameters in stored procedures.

The below stored procedure returns multiple rows from the table and I do not pass any parameter to it.

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_users`()
BEGIN

	SELECT * FROM user_details;

END//
DELIMITER ;

The below stored procedure takes two parameters – IN and OUT. In parameter takes input and OUT parameter gives output.

The following stored procedure returns the full name of a user after concatenating first and last names.

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user_full_name_in_out`(
	IN `user_id` INT,
	OUT `full_name` VARCHAR(50)

)
BEGIN

	SELECT concat(first_name, ' ', last_name) into full_name FROM user_details WHERE id = user_id;	

END//
DELIMITER ;

The following stored procedure takes only one parameter INOUT and this parameter takes input as well as gives output.

The following procedure also returns the full name of a user after concatenating first and last names.

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user_full_name_inout`(
	INOUT `in_out` VARCHAR(50)

)
BEGIN

	SELECT concat(first_name, ' ', last_name) INTO in_out FROm user_details WHERE dob = in_out;
	
END//
DELIMITER ;

Call Stored Procedures

Now you will see how to call stored procedures and how to pass input to In and INOUT parameters and how to receive output from OUT and INOUT parameters.

get_users

This procedure lists all the users from table user_details. Simply executing command call get_users() on database server will give you the following results:

in, out and in out in stored procedure in spring data jpa

Now you will see how to call from Spring application.

Let’s say you have the following repository interface that extends Spring’s repository interface:

public interface UserDetailsJpaRepository extends JpaRepository<UserDetails, Integer> {
    //...
}

You can use the @Query annotation to call the procedure as a native query by the following line of code.

@Query(value = "call get_users()", nativeQuery = true)
List<UserDetails> findUserDetailsList();

You can also call the stored procedure in other ways using EntityManager. Let’s say you have the following repository class.

@Repository
public class UserDetailsRepository {
    //...
}

Now you can use two different ways to call the stored procedure:

@PersistenceContext
private EntityManager entityManager;

public List<UserDetails> findUserDetailsListUsingAlias() {
	StoredProcedureQuery users = entityManager.createNamedStoredProcedureQuery("getUsers");
	return users.getResultList();
}

public List<UserDetails> findUserDetailsListUsingName() {
	StoredProcedureQuery users = entityManager.createStoredProcedureQuery("get_users");
	return users.getResultList();
}

get_user_full_name_in_out

You can use the below commands to get the result from the stored procedure:

CALL `get_user_full_name_in_out`('7', @full_name);
SELECT @full_name;

The above command will give you the following result:

Now I will call from Spring application. You can use the following line of code to call from Spring Data JPA repository interface UserDetailsJpaRepository:

@Procedure(procedureName = "get_user_full_name_in_out", outputParameterName = "full_name")
String findUserFullNameIn_OutUsingName(@Param("user_id") Integer in);

You can also use the following code snippets from UserDetailsRepository class:

public String findUserFullNameIn_OutUsingName(Integer in) {
	StoredProcedureQuery q = entityManager.createStoredProcedureQuery("get_user_full_name_in_out");

	q.registerStoredProcedureParameter("user_id", Integer.class, ParameterMode.IN);
	q.registerStoredProcedureParameter("full_name", String.class, ParameterMode.OUT);

	q.setParameter("user_id", in);

	return q.getOutputParameterValue("full_name").toString();
}

get_user_full_name_inout

You can call this stored procedure using the following commands in the MySQL server:

SET @in_out = '30-08-2016';
CALL `get_user_full_name_inout`(@in_out);
SELECT @in_out;

The above commands will give you the following output:

The following code snippets written into the UserDetailsRepository class:

public String findUserFullNameInOutUsingName(String dob) {
	StoredProcedureQuery q = entityManager.createStoredProcedureQuery("get_user_full_name_inout");

	q.registerStoredProcedureParameter("in_out", String.class, ParameterMode.INOUT);

	q.setParameter("in_out", dob);

	return q.getOutputParameterValue("in_out").toString();
}

Entity Class

The corresponding entity class that has been used in the above repository interface and class is given below.

Over the class I have declared the stored procedure using @NamedStoredProcedureQueries annotation.

I have specified the column names using @Column annotation for which the column name and Java attribute is different.

package com.roytuts.spring.data.jpa.stored.procedure.entity;

@Entity
@Table(name = "user_details")
@NamedStoredProcedureQueries({
		@NamedStoredProcedureQuery(name = "getUsers", procedureName = "get_users", resultClasses = {
				UserDetails.class }) })
//@NamedStoredProcedureQuery(name = "getUsers", procedureName = "get_users", resultClasses = { UserDetails.class })
public class UserDetails implements Serializable {

	private static final long serialVersionUID = 1L;

	@Id
	@Column
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	@Column(name = "first_name")
	private String firstName;

	@Column(name = "last_name")
	private String lastName;

	@Column
	private String dob;
        
        //getters and setters
}

Service Class

The corresponding service class could be written as:

package com.roytuts.spring.data.jpa.stored.procedure.service;

@Service
public class UserService {

	@Autowired
	private UserDetailsJpaRepository jpaRepository;

	@Autowired
	private UserDetailsRepository repository;

	public List<UserDetails> getUserListUsingNativeQuery() {
		return jpaRepository.findUserDetailsList();
	}

	public List<UserDetails> getUserDetailsListUsingProcAlias() {
		return repository.findUserDetailsListUsingAlias();
	}

	public List<UserDetails> getUserDetailsListUsingProcName() {
		return repository.findUserDetailsListUsingAlias();
	}

	public String getUserFullNameInOutUsingProcName(String dob) {
		return repository.findUserFullNameInOutUsingName(dob);
	}

	public String getUserFullNameIn_OutUsingProcName(int in) {
		return repository.findUserFullNameIn_OutUsingName(in);
		// return repository.findUserFullNameIn_OutUsingName(in);
	}

}

Main Class

A class with main method is enough to run our Spring Boot application. I am using standalone application here.

package com.roytuts.spring.data.jpa.stored.procedure;

@SpringBootApplication
@EnableJpaRepositories(basePackages = "spring.data.jpa.stored.procedure.repository")
public class SpringDataJpaStoredProcedureApp implements CommandLineRunner {

	@Autowired
	private UserService service;

	public static void main(String[] args) {
		SpringApplication.run(SpringDataJpaStoredProcedureApp.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		System.out.println("===========================================");
		System.out.println("User List using Native Query");

		service.getUserListUsingNativeQuery().stream().forEach(
				u -> System.out.println(u.getId() + " " + u.getFirstName() + " " + u.getLastName() + " " + u.getDob()));

		System.out.println("===========================================");
		System.out.println("User List using Procedure name");

		service.getUserDetailsListUsingProcName().stream().forEach(
				u -> System.out.println(u.getId() + " " + u.getFirstName() + " " + u.getLastName() + " " + u.getDob()));

		System.out.println("===========================================");
		System.out.println("User List using Procedure alias");

		service.getUserDetailsListUsingProcAlias().stream().forEach(
				u -> System.out.println(u.getId() + " " + u.getFirstName() + " " + u.getLastName() + " " + u.getDob()));

		System.out.println("===========================================");

		System.out.println(
				"IN and OUT parameters using Procedure name: " + service.getUserFullNameIn_OutUsingProcName(7));

		System.out.println("===========================================");

		System.out.println(
				"INOUT parameter using Procedure name: " + service.getUserFullNameInOutUsingProcName("30-08-2016"));

		System.out.println("===========================================");
	}

}

Database Configuration

Put the below database details into application.properties file under classpath directory src/main/resources to connect your MySQL server. make sure you change according to your database details:

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/roytuts
spring.datasource.username=root
spring.datasource.password=root

Testing the Application

Executing the above main class will give you the following output:

===========================================
User List using Native Query
7 Soumitra Roy 30-08-2016
8 Souvik Sanyal 30-09-1991
9 Abc Xyz 12-01-1998
10 Liton Sarkar 30-08-2012
===========================================
User List using Procedure name
7 Soumitra Roy 30-08-2016
8 Souvik Sanyal 30-09-1991
9 Abc Xyz 12-01-1998
10 Liton Sarkar 30-08-2012
===========================================
User List using Procedure alias
7 Soumitra Roy 30-08-2016
8 Souvik Sanyal 30-09-1991
9 Abc Xyz 12-01-1998
10 Liton Sarkar 30-08-2012
===========================================
IN and OUT parameters using Procedure name: Soumitra Roy
===========================================
INOUT parameter using Procedure name: Soumitra Roy
===========================================

The repository interface/class and service layer are decoupled from any other layer and you can easily inject into any layer.

That’s all on how to work with IN, OUT and IN OUT parameters in Stored Procedure using Spring Data JPA framework.

Source Code

Download

3 thoughts on “Work With IN, OUT And INOUT Parameters In Stored Procedures Using Spring Data JPA”

Tiru says:

18th Jun, 2022 at 10:05 pm

Hi Sir,
Please help me here to pass Collection as IN parameter of Procedure using JPAstion….

Shedrach james says:

18th May, 2022 at 7:38 am

You nailed it totally. Thanks a lot!!!

Atul says:

11th Oct, 2020 at 4:17 pm

Very nice examples. Simple and clear explanation.
Keep posting. :)

Roy Tutorials