In this post, We will learn about How to call stored procedures in the Spring Framework using a Demo Project.
The org.springframework.jdbc.core.simple.SimpleJdbcCall class is mainly used to call a stored procedure with IN and OUT parameters. We may use this approach while working with either of the databases such as MySQLApache Derby, DB2, Microsoft SQL Server, Oracle, and Sybase etc.
pom.xml
We need to add Spring and MySQL dependencies in Maven pom.xml file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
<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.kkhindigyan.org</groupId> <artifactId>SpringJdbcStoredProcedureCall</artifactId> <version>0.0.1-SNAPSHOT</version> <properties> <org.springframework.version>5.2.13.RELEASE</org.springframework.version> </properties> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.23</version> </dependency> </dependencies> </project> |
Employee.java
Employee model class to populate employee data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
package com.kkhindigyan.org.model; public class Employee { private Integer employeeId; private String employeeName; private String email; private String gender; private Double salary; public Integer getEmployeeId() { return employeeId; } public void setEmployeeId(Integer employeeId) { this.employeeId = employeeId; } public String getEmployeeName() { return employeeName; } public void setEmployeeName(String employeeName) { this.employeeName = employeeName; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public Double getSalary() { return salary; } public void setSalary(Double salary) { this.salary = salary; } @Override public String toString() { return "Employee [employeeId=" + employeeId + ", employeeName=" + employeeName + ", email=" + email + ", gender=" + gender + ", salary=" + salary + "]"; } } |
EmployeeService.java
Employee Service Interface
1 2 3 4 5 6 7 8 |
package com.kkhindigyan.org.service; import com.kkhindigyan.org.model.Employee; public interface EmployeeService { public abstract Employee getEmployeeById(Integer employeeId); } |
EmployeeServiceImpl.java
Employee Service Implementation class
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
package com.kkhindigyan.org.service; import com.kkhindigyan.org.dao.EmployeeDao; import com.kkhindigyan.org.model.Employee; public class EmployeeServiceImpl implements EmployeeService { private EmployeeDao employeeDao; public EmployeeDao getEmployeeDao() { return employeeDao; } public void setEmployeeDao(EmployeeDao employeeDao) { this.employeeDao = employeeDao; } @Override public Employee getEmployeeById(Integer employeeId) { return getEmployeeDao().fetchEmployeeById(employeeId); } } |
EmployeeDao.java
Employee DAO(Data Access Object) Interface
1 2 3 4 5 6 7 8 |
package com.kkhindigyan.org.dao; import com.kkhindigyan.org.model.Employee; public interface EmployeeDao { public abstract Employee fetchEmployeeById(Integer employeeId); } |
EmployeeDaoImpl.java
Employee DAO(Data Access Object) Interface implementation class to call Stored Procedure in MySQL database.
Spring org.springframework.jdbc.core.simple.SimpleJdbcCall is the central class in the Spring JDBC core package and it provides a method to call Stored Procedures.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
package com.kkhindigyan.org.dao; import java.util.Map; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.SqlParameterSource; import org.springframework.jdbc.core.simple.SimpleJdbcCall; import com.kkhindigyan.org.model.Employee; public class EmployeeDaoImpl implements EmployeeDao { private SimpleJdbcCall simpleJdbcCall; public SimpleJdbcCall getSimpleJdbcCall() { return simpleJdbcCall; } public void setSimpleJdbcCall(SimpleJdbcCall simpleJdbcCall) { this.simpleJdbcCall = simpleJdbcCall; } @Override public Employee fetchEmployeeById(Integer employeeId) { SimpleJdbcCall simpleJdbcCall = getSimpleJdbcCall().withProcedureName("getEmployeeDetailsById"); SqlParameterSource inputParameters = new MapSqlParameterSource("emp_id", employeeId); Map<String, Object> outputMap = simpleJdbcCall.execute(inputParameters); Employee employee = new Employee(); if(outputMap != null) { employee.setEmployeeId(employeeId); employee.setEmail((String) outputMap.get("emp_email")); employee.setEmployeeName((String) outputMap.get("emp_name")); employee.setGender((String) outputMap.get("emp_gender")); employee.setSalary((Double) outputMap.get("emp_sal")); } return employee; } } |
applicationContext.xml
The org.springframework.jdbc.datasource.DriverManagerDataSource class is mainly used to contain information about the database such as driver class name, connection URL, username, and password.
There is a property Spring bean with the name as datasource in the org.springframework.jdbc.core.simple.SimpleJdbcCall class of org.springframework.jdbc.datasource.DriverManagerDataSource type. So, we need to provide the reference of the DriverManagerDataSource object in the SimpleJdbcCall class for the data source property.
Here, we are using the SimpleJdbcCall object in the EmployeeDaoImpl class, so we are passing it by the setter injection but you can also use constructor injection.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd"> <bean id="employeeServiceImpl" class="com.kkhindigyan.org.service.EmployeeServiceImpl"> <property name="employeeDao" ref="employeeDaoImpl"></property> </bean> <bean id="employeeDaoImpl" class="com.kkhindigyan.org.dao.EmployeeDaoImpl"> <property name="simpleJdbcCall" ref="simpleJdbcCall"></property> </bean> <bean id="simpleJdbcCall" class="org.springframework.jdbc.core.simple.SimpleJdbcCall"> <constructor-arg name="dataSource" ref="dataSource"></constructor-arg> </bean> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <constructor-arg name="url" value="jdbc:mysql://localhost:3306/test"></constructor-arg> <constructor-arg name="username" value="root"></constructor-arg> <constructor-arg name="password" value="root"></constructor-arg> </bean> </beans> |
DB.sql
Here we are going to use the below database script to create employee_table and insert one record in the MySQL database.
1 2 3 4 5 6 7 8 9 10 11 |
drop table if exists employee_table; CREATE TABLE `employee_table` ( `employee_Id` int(11) NOT NULL AUTO_INCREMENT, `employee_name` varchar(50) NOT NULL, `email` varchar(40) NOT NULL, `gender` varchar(20) DEFAULT NULL, `salary` double DEFAULT NULL, PRIMARY KEY (`employee_Id`) ) ; insert into employee_table (employee_name, email, gender, salary) values ('KK', '[email protected]', 'Male', 95000); |
MySQL Database Screen Shot After running DB.sql
StoredProcedure.sql
Let’s create one Stored Procedure in MySQL. This Stored Procedure takes emp_id as input and returns the corresponding employee’s name,email,gender and salary using OUT parameters. Let us create this stored procedure in the test database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DROP PROCEDURE IF EXISTS test.getEmployeeDetailsById; CREATE PROCEDURE test.`getEmployeeDetailsById`( IN emp_id INT, OUT emp_name VARCHAR(50), OUT emp_email VARCHAR(40), OUT emp_gender VARCHAR(10), OUT emp_sal DOUBLE) BEGIN SELECT employee_name, email, gender, salary INTO emp_name, emp_email, emp_gender, emp_sal FROM employee_table WHERE employee_Id = emp_id; END; |
Screen Shot After Creating Stored Procedure in MySQL test Schema
ClientTest.java
This ClientTest class with the main method gets the bean from the applicationContext.xml file and the calls method getEmployeeById of EmployeeService to get employee details by calling Stored Procedure in MySQL database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
package com.kkhindigyan.org.client; import org.springframework.context.support.AbstractApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.kkhindigyan.org.model.Employee; import com.kkhindigyan.org.service.EmployeeService; import com.kkhindigyan.org.service.EmployeeServiceImpl; public class ClientTest { public static void main(String[] args) { AbstractApplicationContext ctx = null; try { ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); EmployeeService employeeService = ctx.getBean(EmployeeServiceImpl.class); getEmployeeById(employeeService); } catch (Exception e) { e.printStackTrace(); }finally { if(ctx != null) ctx.close(); } } private static void getEmployeeById(EmployeeService employeeService) { Employee employee = employeeService.getEmployeeById(1); System.out.println(employee); } } |
If you run ClientTest.java as Java Application then it will give the below output:
1 |
Employee [employeeId=1, employeeName=KK, email=kk.cs@gmail.com, gender=Male, salary=95000.0] |
That’s all about How to call stored procedures in the Spring Framework?
You May Also Like:
Spring BeanFactory Container Example
Spring ApplicationContext Container Example
Annotation-based Configuration in Spring Framework Example
Spring Java-based Configuration Example
Spring Setter Dependency Injection Example
Spring @Autowired Annotation With Setter Injection Example
Spring Constructor based Dependency Injection Example
Spring @Autowired Annotation With Constructor Injection Example
Spring Autowiring byName & byType Example
getBean() overloaded methods in Spring Framework
Spring Inner bean example
Spring Dependency Injection with Factory Method
Spring Framework @Qualifier example
Injecting Collections in Spring Framework Example
Spring Bean Definition Inheritance Example
Spring bean scopes with example
Spring JSR-250 Annotations with Example
Spring BeanPostProcessor Example
Spring JDBC Integration Example
Spring JDBC Annotation Example
Spring with Jdbc java based configuration example
Spring JDBC NamedParameterJdbcTemplate Example
If you have any feedback or suggestion please feel free to drop in below comment box.