Posted in Spring JDBC

Call Stored Procedure Using Spring StoredProcedure

Stored Procedure

This tutorial will show you how you can call stored procedure using Spring StoredProcedure. I assume you have a basic idea about stored procedure and what a stored procedure does and what are the advantages of using stored procedure in database server.

Related Posts:

Sometimes you may need to call a stored procedure while a single SQL statement is unable to fulfil our requirements because, it permits to execute multiple SQL statements and it also allows us to execute these SQL statements in a transactional unit in an efficient manner to avoid any inconsistency sometimes occurs during the execution of multiple SQL statements.

Prerequisites

Java 1.8(19), Maven 3.6.3/3.8.5, Gradle 6.4.1, Spring Boot 2.3.1/3.0.6

Setup Project

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

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

buildscript {
	ext {
		springBootVersion = '2.3.1.RELEASE'
	}
	
    repositories {
    	mavenLocal()
    	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 {
	mavenLocal()
    mavenCentral()
}

dependencies {
	implementation("org.springframework.boot:spring-boot-starter-web:${springBootVersion}")
	implementation("org.springframework.boot:spring-boot-starter-jdbc:${springBootVersion}")
	implementation('mysql:mysql-connector-java:8.0.17')
	
	//required for jdk 9 or above
	runtimeOnly('javax.xml.bind:jaxb-api:2.4.0-b180830.0359')
}

If you are creating maven based project then use below 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-stored-procedure-storedprocedure</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.0.6</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-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.31</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.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

Database Configuration

In order to use database you need to configure database with Spring Boot application. I will create src/main/resources/application.properties file with the below configurations.

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

As I am using standard naming conventions for database configurations, so you don’t need to create any bean of DataSource type and Spring will automatically provide one for us.

Model Class

Model class is a plain old Java object which will represent a row in the table.

public class Student {

	private Integer studentId;
	private String studentName;
	private String studentDob;
	private String studentEmail;
	private String studentAddress;

	public Student() {
	}

	public Student(Integer studentId, String studentName, String studentDob, String studentEmail,
			String studentAddress) {
		this.studentId = studentId;
		this.studentName = studentName;
		this.studentDob = studentDob;
		this.studentEmail = studentEmail;
		this.studentAddress = studentAddress;
	}

	//getters and setters

}

Row Mapper Class

I am using Spring JDBC API, so you need a row mapper class that will map each column of the table with the Java field.

The column name you will see later when I will talk about table creation.

public class StudentRowMapper implements RowMapper<Student> {

	@Override
	public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
		Student student = new Student();
		student.setStudentId(rs.getInt("student_id"));
		student.setStudentName(rs.getString("student_name"));
		student.setStudentDob(rs.getString("student_dob"));
		student.setStudentEmail(rs.getString("student_email"));
		student.setStudentAddress(rs.getString("student_address"));
		return student;
	}

}

Call Stored Procedures

Next I will call two stored procedures using Spring StoredProcedure API. One stored procedure will fetch single Student‘s details and another stored procedure will fetch all Students‘ details.

The below class is a DAO or Repository class that interacts with the underlying database.

@Repository
public class StudentDao {

	@Autowired
	private DataSource dataSource;

	private final String DB_NAME = "roytuts";
	private final String PROC_GET_STUDENT = "get_student";
	private final String PROC_GET_ALL_STUDENTS = "get_all_students";

	public Student getStudentById(int studentId) {
		StudentProcedure studentProcedure = new StudentProcedure();
		Student student = studentProcedure.execute(studentId);
		return student;
	}

	private class StudentProcedure extends StoredProcedure {
		public StudentProcedure() {
			super(dataSource, DB_NAME + "." + PROC_GET_STUDENT);
			declareParameter(new SqlParameter("in_student_id", Types.INTEGER));
			declareParameter(new SqlOutParameter("out_student_name", Types.VARCHAR));
			declareParameter(new SqlOutParameter("out_student_dob", Types.VARCHAR));
			declareParameter(new SqlOutParameter("out_student_email", Types.VARCHAR));
			declareParameter(new SqlOutParameter("out_student_address", Types.VARCHAR));
			compile();
		}

		public Student execute(int studentId) {
			Map<String, Object> result = super.execute(studentId);
			// get Student object
			Student student = new Student();
			// simple the studentId
			student.setStudentId(studentId);
			// set Name, must be same as out param in procedure
			student.setStudentName((String) result.get("out_student_name"));
			// set Date of Birth, must be same as out param in procedure
			student.setStudentDob((String) result.get("out_student_dob"));
			// set Email, must be same as out param in procedure
			student.setStudentEmail((String) result.get("out_student_email"));
			// set Address, must be same as out param in procedure
			student.setStudentAddress((String) result.get("out_student_address"));
			return student;
		}
	}

	public List<Student> getAllStudents() {
		StudentProcedure2 procedure2 = new StudentProcedure2();
		Map<String, Object> map = procedure2.execute();
		@SuppressWarnings("unchecked")
		List<Student> students = (List<Student>) map.get("students");
		return students;
	}

	private class StudentProcedure2 extends StoredProcedure {
		public StudentProcedure2() {
			super(dataSource, DB_NAME + "." + PROC_GET_ALL_STUDENTS);
			declareParameter(new SqlReturnResultSet("students", new StudentRowMapper()));
			compile();
		}

		public Map<String, Object> execute() {
			// no IN parameter so passing empty HashMap
			Map<String, Object> results = super.execute(new HashMap<>());
			return results;
		}
	}

}

MySQL Table

Create a table called student under roytuts database in MySQL server.

CREATE TABLE `student` (
  `student_id` int unsigned NOT NULL AUTO_INCREMENT,
  `student_name` varchar(30) NOT NULL,
  `student_dob` varchar(10) NOT NULL,
  `student_email` varchar(80) NOT NULL,
  `student_address` varchar(250) NOT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

To test our application right away I need some data. So I am going to dump some sample data into the above table.

insert  into `student`(`student_id`,`student_name`,`student_dob`,`student_email`,`student_address`) values (1,'Sumit','01-01-1980','sumit@email.com','Garifa'),
(2,'Gourab','01-01-1982','gourab@email.com','Garia'),
(3,'Debina','01-01-1982','debina@email.com','Salt Lake'),
(4,'Souvik','01-01-1992','souvik@email.com','Alipore'),
(5,'Liton','01-01-1990','liton@email.com','Salt Lake');

Create Stored Procedures

I also need to create stored procedures to fetch student details. I will create two separate stored procedure in the MySQL server under roytuts database.

The below procedure fetches single student record:

DELIMITER $$
CREATE
    PROCEDURE `roytuts`.`get_student`(IN in_student_id INTEGER,
	    OUT out_student_name VARCHAR(30),
	    OUT out_student_dob VARCHAR(10),
	    OUT out_student_email VARCHAR(80),
	    OUT out_student_address VARCHAR(255))
    BEGIN
	SELECT student_name, student_dob, student_email, student_address
	INTO out_student_name, out_student_dob, out_student_email, out_student_address
	FROM student WHERE student_id = in_student_id;
    END$$
DELIMITER ;

You can execute or call the above stored procedure in MySQL server in the following way:

CALL get_student(1,@name,@dob,@email,@address)

Now get the result by executing below SELECT statement:

SELECT @name,@dob,@email,@address

Result:

@name	@dob	    @email	          @address
Sumit	01-01-1980	sumit@email.com	  Garifa

The below procedure fetches all students from the table:

DELIMITER $$
CREATE
    PROCEDURE `roytuts`.`get_all_students`()
    BEGIN
	SELECT * FROM student;
    END$$
DELIMITER ;

Execute using CALL get_all_students() command.

Output:

spring stored procedure storedprocedure

Spring REST Controller

I would like to expose two endpoints to get the results of the stored procedures.

@RestController
public class StudentRestController {

	@Autowired
	private StudentDao dao;

	@GetMapping("/student/{id}")
	public ResponseEntity<Student> getStudent(@PathVariable Integer id) {
		System.out.println("id: " + id);
		Student student = dao.getStudentById(id);

		return new ResponseEntity<Student>(student, HttpStatus.OK);
	}

	@GetMapping("/students")
	public ResponseEntity<List<Student>> getAllStudents() {
		List<Student> students = dao.getAllStudents();

		return new ResponseEntity<List<Student>>(students, HttpStatus.OK);
	}

}

Main Class

A class with main method and @SpringBootApplication is enough for deploying Spring Boot application into Tomcat server.

@SpringBootApplication
public class SpringStoredProcedureApp {

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

}

Testing the Application

Now when your application is up and running you can hit the following two URLs to get the results of the stored procedures.

URL: http://localhost:8080/student/1

Response:

{"studentId":1,"studentName":"Sumit","studentDob":"01-01-1980","studentEmail":"sumit@email.com","studentAddress":"Garifa"}
spring jdbc stored procedure

URL: http://localhost:8080/students

Response:

[{"studentId":1,"studentName":"Sumit","studentDob":"01-01-1980","studentEmail":"sumit@email.com","studentAddress":"Garifa"},{"studentId":2,"studentName":"Gourab","studentDob":"01-01-1982","studentEmail":"gourab@email.com","studentAddress":"Garia"},{"studentId":3,"studentName":"Debina","studentDob":"01-01-1982","studentEmail":"debina@email.com","studentAddress":"Salt Lake"},{"studentId":4,"studentName":"Souvik","studentDob":"01-01-1992","studentEmail":"souvik@email.com","studentAddress":"Alipore"},{"studentId":5,"studentName":"Liton","studentDob":"01-01-1990","studentEmail":"liton@email.com","studentAddress":"Salt Lake"}]
spring stored procedure

Source Code

Download

Leave a Reply

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