Introduction

In this tutorial we will see how to work with IN, OUT and INOUT parameters in Stored Procedure using Spring Data JPA. We 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 we cannot use REF_CURSOR as parameter type in the @NamedStoredProcedureQuery on the entity class. In this case we cannot use Spring Data JPA repository style method to call stored procedure and we need to call as native query or we need to create StoredProcedureQuery instance from EntityManager.

Related Posts:

Prerequisites

Eclipse 2019-12, Java at least 8, Gradle 6.1.1, Maven 3.6.3, MySQL 8.0.17, Spring Data JPA 2.2.5

Create Project

You can create either gradle or maven based project in Eclipse or any other 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'
	}
    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')
	//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 in Eclipse 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>spring-data-jpa-stored-procedure</artifactId>
	<version>0.0.1-SNAPSHOT</version>

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

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

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jpa</artifactId>
		</dependency>
		
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.17</version>
		</dependency>
		
		<!--required only if jdk 9 or higher version is used-->
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.4.0-b180830.0359</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>

Create MySQL Table

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

CREATE TABLE IF NOT EXISTS `user_details` (
  `id` int(10) unsigned 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 we 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', '[email protected]', '30-08-2016'),
	(8, 'Souvik', 'Sanyal', '[email protected]', '30-09-1991'),
	(9, 'Abc', 'Xyz', '[email protected]', '12-01-1998'),
	(10, 'Liton', 'Sarkar', '[email protected]', '30-08-2012');

Create Stored Procedures

We 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 we 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 we 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:

stored procedure in spring data jpa

Now we will see how to call from Spring application.

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

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

We 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();

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

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

Now we 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

We 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:

stored procedure in spring data jpa

Now we will call from Spring application. We 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);

We 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

We 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:

stored procedure in spring data jpa

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 we have declared the stored procedure using @NamedStoredProcedureQueries annotation.

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

@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:

@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. We are using standalone application here.

@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.

Source Code

Download

Thanks for reading.

Tags:

Leave a Reply

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