Spring JDBC - Calling Stored Procedure

Following example will demonstrate how to call a stored procedure using Spring JDBC. We'll read one of the available records in Student Table by calling a stored procedure. We'll pass an id and receive a student record.

Syntax

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("getRecord");
SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);

Map<String, Object> out = jdbcCall.execute(in);
Student student = new Student();
student.setId(id);
student.setName((String) out.get("out_name"));
student.setAge((Integer) out.get("out_age"));

Where,

jdbcCall − SimpleJdbcCall object to represent a stored procedure.
in − SqlParameterSource object to pass a parameter to a stored procedure.
student − Student object.
out − Map object to represent the output of stored procedure call result.

The SimpleJdbcCall class can be used to call a stored procedure with IN and OUT parameters. You can use this approach while working with either of the RDBMS such as Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle, and Sybase.

To understand the approach, consider the following MySQL stored procedure, which takes student Id and returns the corresponding student's name and age using OUT parameters. Let us create this stored procedure in TEST database using MySQL command prompt −

Syntax to create a Stored Procedure:-

DELIMITER $$

DROP PROCEDURE IF EXISTS 'TEST'.'getRecord' $$

CREATE PROCEDURE 'TEST'.'getRecord' (
IN in_id INTEGER,
OUT out_name VARCHAR(20),
OUT out_age  INTEGER)
BEGIN
   SELECT name, age
   INTO out_name, out_age
   FROM Student where id = in_id;
END $$

DELIMITER ;

To understand the above-mentioned concepts, let us write an example which will call a stored procedure. To write our example, let us have a working Eclipse IDE in place and use the following steps to create a Spring application.

Step Description

1 Update the project Student created under chapter Spring JDBC - First Application.

2 Update the bean configuration and run the application as explained below.

Following is the content of the Student.java file.

package com.javahubpoint;

public class Student {
   private Integer age;
   private String name;
   private Integer id;
   public void setAge(Integer age) {
      this.age = age;
   }
   public Integer getAge() {
      return age;
   }
   public void setName(String name) {
      this.name = name;
   }
   public String getName() {
      return name;
   }
   public void setId(Integer id) {
      this.id = id;
   }
   public Integer getId() {
      return id;
   }
}

Following is the content of the StudentMapper.java file.

package com.javahubpoint;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

public class StudentMapper implements RowMapper<Student> {

   public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
      Student student = new Student();
      student.setId(rs.getInt("id"));
      student.setName(rs.getString("name"));
      student.setAge(rs.getInt("age"));
      return student;
   }
}

Following is the content of the Data Access Object interface file StudentDAO.java.

package com.javahubpoint;

import java.util.List;
import javax.sql.DataSource;

public interface StudentDAO {
   /** 
      * This is the method to be used to initialize
      * database resources ie. connection.
   */
   public void setDataSource(DataSource ds);
   
   /** 
      * This is the method to be used to list down
      * a record from the Student table corresponding
      * to a passed student id.
   */
   public Student getStudent(Integer id);  
}

Following is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO.

package com.javahubpoint;

import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

public class StudentJDBCTemplate implements StudentDAO {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;
   
   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
   }

   public Student getStudent(Integer id) {
      SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("getRecord");
      SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
      Map<String, Object> out = jdbcCall.execute(in);
      Student student = new Student();
      student.setId(id);
      student.setName((String) out.get("out_name"));
      student.setAge((Integer) out.get("out_age"));
      return student;      
   }
}

The code you write for the execution of the call involves creating an SqlParameterSource containing the IN parameter. It's important to match the name provided for the input value with that of the parameter name declared in the stored procedure. The execute method takes the IN parameters and returns a Map containing any out parameters keyed by the name as specified in the stored procedure.

Following is the content of the MainApp.java file.

package com.javahubpoint;

import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.javahubpoint.StudentJDBCTemplate;

public class MainApp {

   public static void main(String[] args) {

      ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
      
      StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
      Student student = studentJDBCTemplate.getStudent(1);
      System.out.print("ID : " + student.getId() );
      System.out.print(", Name : " + student.getName() );
      System.out.println(", Age : " + student.getAge()); 
   }
}

Following is the configuration file Beans.xml.

<?xml version = "1.0" encoding = "UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
   xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" 
   xsi:schemaLocation = "http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">

   <!-- Initialization for data source -->
   <bean id = "dataSource" 
      class = "org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name = "driverClassName" value = "com.mysql.cj.jdbc.Driver"/>
      <property name = "url" value = "jdbc:mysql://localhost:3306/TEST"/>
      <property name = "username" value = "root"/>
      <property name = "password" value = "admin"/>
   </bean>

   <!-- Definition for studentJDBCTemplate bean -->
   <bean id = "studentJDBCTemplate" 
      class = "com.javahubpoint.StudentJDBCTemplate">
      <property name = "dataSource" ref = "dataSource" />    
   </bean>

</beans>

Once you are done creating the source and bean configuration files, let us run the application. If everything is fine with your application, it will print the following message.

ID : 1, Name : Zara, Age : 11