Simple Jdbc Call
This tutorial will show you how you can call or execute stored procedure using Spring SimpleJdbcCall
. SimpleJdbcCall
API provides flexible methods to invoke stored procedure. You can use DataSource
directly with SimpleJdbcCall
or you can create JdbcTemplate
using DataSource
and use JdbcTemplate
with SimpleJdbcCall
API for calling the stored procedures.
You can also use RowMapper
or BeanProprtyMapper
of type POJO class as a return type while executing stored procedures using Spring’s SimpleJdbcCall
API. I am also going to use @Qualifier annotation for resolving ambiguity while using SimpleJdbcCall
with DataSource
and JdbcTemplate
.
Related Posts:
Sometimes you 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 – 6.8.3, Spring Boot 2.3.1/2.4.4/3.0.6, MySQL 8.0.17/8.0.22/8.0.31
Project Setup
You need to create a project in Eclipse. The name of the project is spring-spring-stored-procedure-simplejdbccall.
If you are creating gradle based project then use below build.gradle script.
buildscript {
ext {
springBootVersion = '2.3.1.RELEASE' //to 2.4.4
}
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') // to 8.0.22
//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-simplejdbccall</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
You need to create corresponding Java configuration class for creating different beans. I have created the same bean types SimpleJdbcCall
, so I have given name to each of them to resolve ambiguity.
@Configuration
public class DbConfig {
@Autowired
private Environment environment;
@Bean
public DataSource dataSource() {
return DataSourceBuilder.create().driverClassName(environment.getProperty("spring.datasource.driverClassName"))
.url(environment.getProperty("spring.datasource.url"))
.username(environment.getProperty("spring.datasource.username"))
.password(environment.getProperty("spring.datasource.password")).build();
}
@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean(name = "simpleJdbcCall1")
public SimpleJdbcCall simpleJdbcCall1(DataSource dataSource) {
return new SimpleJdbcCall(dataSource);
}
@Bean(name = "simpleJdbcCall2")
public SimpleJdbcCall simpleJdbcCall2(JdbcTemplate jdbcTemplate) {
return new SimpleJdbcCall(jdbcTemplate);
}
}
Create Model
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
}
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 SimpleJdbcCall
API. One stored procedure will fetch single Student 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.
Notice I have used both SimpleJdbcCall API beans to fetch the single student record.
I have also used RowMapper
class (StudentRowMapper) or BeanPropertyRowMapper
class to map the rows from table into Java List<Student> class.
@Repository
public class StudentDao {
@Autowired
@Qualifier("simpleJdbcCall1")
private SimpleJdbcCall simpleJdbcCall1;
@Autowired
@Qualifier("simpleJdbcCall2")
private SimpleJdbcCall simpleJdbcCall2;
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 getStudentUsingProcMethod1(int studentId) {
// schema name
simpleJdbcCall1.withCatalogName(DB_NAME);
// procedure name
simpleJdbcCall1.withProcedureName(PROC_GET_STUDENT);
// in parameter, must be same as in the procedure
// Map with key/value pair also be passed to addValue() function
SqlParameterSource sqlParameterSource = new MapSqlParameterSource().addValue("in_student_id", studentId);
// get result in key/value pair
Map<String, Object> result = simpleJdbcCall1.execute(sqlParameterSource);
// 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 Student getStudentUsingProcMethod2(int studentId) {
// schema name
simpleJdbcCall2.withCatalogName(DB_NAME);
// procedure name
simpleJdbcCall2.withProcedureName(PROC_GET_STUDENT);
// You can opt to declare one, some, or all the parameters explicitly
simpleJdbcCall2.withoutProcedureColumnMetaDataAccess();
// in parameter, must be same as in the procedure
simpleJdbcCall2.useInParameterNames("in_student_id");
// in and out parameters, must be same as in the procedure
simpleJdbcCall2.declareParameters(new SqlParameter("in_student_id", Types.INTEGER),
new SqlOutParameter("out_student_name", Types.VARCHAR),
new SqlOutParameter("out_student_dob", Types.VARCHAR),
new SqlOutParameter("out_student_email", Types.VARCHAR),
new SqlOutParameter("out_student_address", Types.VARCHAR));
// put in parameter value in Map
Map<String, Object> map = new HashMap<String, Object>();
map.put("in_student_id", studentId);
// execute the procedure
// get result in key/value pair
Map<String, Object> result = simpleJdbcCall2.execute(map);
// 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> getAllStudentUsingProcMethod1() {
// schema name
simpleJdbcCall1.withCatalogName(DB_NAME);
// procedure name, BeanPropertyRowMapper or RowMapper
// simpleJdbcCall1.withProcedureName(PROC_GET_ALL_STUDENTS).returningResultSet("students",
// BeanPropertyRowMapper.newInstance(Student.class));
simpleJdbcCall1.withProcedureName(PROC_GET_ALL_STUDENTS).returningResultSet("students", new StudentRowMapper());
// get results in key/value pair
Map<String, Object> results = simpleJdbcCall1.execute();
@SuppressWarnings("unchecked")
List<Student> students = (List<Student>) results.get("students");
return students;
}
}
Create MySQL Table
Create a table called student under roytuts database in MySQL server.
CREATE TABLE `student` (
`student_id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
`student_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
`student_dob` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
`student_email` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
`student_address` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
To test the 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 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.getStudentUsingProcMethod1(id);
Student student = dao.getStudentUsingProcMethod2(id);
return new ResponseEntity<Student>(student, HttpStatus.OK);
}
@GetMapping("/students")
public ResponseEntity<List<Student>> getAllStudents() {
List<Student> students = dao.getAllStudentUsingProcMethod1();
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 SpringStoredProcedureSimpleJdbcCallApp {
public static void main(String[] args) {
SpringApplication.run(SpringStoredProcedureSimpleJdbcCallApp.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
HTTP Method: GET
Response:
{"studentId":1,"studentName":"Sumit","studentDob":"01-01-1980","studentEmail":"sumit@email.com","studentAddress":"Garifa"}
URL: http://localhost:8080/students
HTTP Method: GET
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"}]
That’s all about how to work with stored procedure in Spring based application using SimpleJdbcCall.