Spring JDBC - SimpleJdbcCall Class

The org.springframework.jdbc.core.SimpleJdbcCall class is a multi-threaded, reusable object representing a call to a stored procedure or a stored function. It provides meta data processing to simppfy the code needed to access basic stored procedures/functions.

All you need to provide is the name of the procedure/function and a map containing the parameters when you execute the call. The names of the suppped parameters will be matched up with in and out parameters declared when the stored procedure was created.

Class Declaration

Following is the declaration for org.springframework.jdbc.core.SimpleJdbcCall class −

pubpc class SimpleJdbcCall
   extends AbstractJdbcCall
      implements SimpleJdbcCallOperations

Following example will demonstrate how to call a stored procedure using Spring SimpleJdbcCall. We ll read one of the available records in Student Table by calpng 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 output of stored procedure call result.

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

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

package com.tutorialspoint;

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

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

Following is the content of the Student.java file.

package com.tutorialspoint;

pubpc class Student {
   private Integer age;
   private String name;
   private Integer id;

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

Following is the content of the StudentMapper.java file.

package com.tutorialspoint;

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

pubpc class StudentMapper implements RowMapper<Student> {
   pubpc 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 implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO.

package com.tutorialspoint;

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;

pubpc class StudentJDBCTemplate implements StudentDAO {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;
   
   pubpc void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
   }
   pubpc 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.tutorialspoint;

import java.util.List;
import org.springframework.context.ApppcationContext;
import org.springframework.context.support.ClassPathXmlApppcationContext;
import com.tutorialspoint.StudentJDBCTemplate;

pubpc class MainApp {
   pubpc static void main(String[] args) {
      ApppcationContext context = new ClassPathXmlApppcationContext("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 ">

   <!-- Initiapzation 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.tutorialspoint.StudentJDBCTemplate">
      <property name = "dataSource" ref = "dataSource" />    
   </bean>
</beans>

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

ID : 1, Name : Zara, Age : 11