Spring JDBC - StoredProcedure Class

---

---

Overview

The org.springframework.jdbc.core.StoredProcedure class is the superclass for object abstractions of RDBMS stored procedures. This class is abstract and it is intended that subclasses will provide a typed method for invocation that delegates to the supplied execute(java.lang.Object...) method. The inherited SQL property is the name of the stored procedure in the RDBMS.

Class Declaration

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

public abstract class StoredProcedure
   extends SqlCall

Following example will demonstrate how to call a stored procedure using Spring StoredProcedure. 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

class StudentProcedure extends StoredProcedure{
   public StudentProcedure(DataSource dataSource, String procedureName){
      super(dataSource,procedureName);
      declareParameter(new SqlParameter("in_id", Types.INTEGER));
      declareParameter(new SqlOutParameter("out_name", Types.VARCHAR));
      declareParameter(new SqlOutParameter("out_age", Types.INTEGER));
      compile();
   }
   public Student execute(Integer id){
      Map<String, Object> out = super.execute(id);
      Student student = new Student();
      student.setId(id);
      student.setName((String) out.get("out_name"));
      student.setAge((Integer) out.get("out_age"));
      return student;  	
   }
}

Where,

• StoredProcedure − StoredProcedure object to represent a stored procedure.

• StudentProcedure − StudentProcedure object extends StoredProcedure to declare input, output variable, and map result to Student object.

• student − Student object.

Example

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 Eclipse IDE in place and use the following steps to create a Spring application.

StudentDAO.java

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

package com.tutorialspoint;

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);  
}

Student.java

Following is the content of the Student.java file.

package com.tutorialspoint;

// Student POJO for Student Table
public class Student {
   private Integer age;
   private String name;
   private Integer id;

   // setter/getter methods
   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;
   }
}

StudentMapper.java

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;

// Row Mapper Object to map Student table entry with Student Object
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;
   }
}

StudentJDBCTemplate.java

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

package com.tutorialspoint;

import java.sql.Types;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.object.StoredProcedure;

// DAO instance to persist Student values
public class StudentJDBCTemplate implements StudentDao {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;
   
   // set the datasource and jdbctemplate
   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
   }
   
   // get a student based on id 
   public Student getStudent(Integer id) {    
      StudentProcedure studentProcedure = new StudentProcedure(dataSource, "getRecord");
      return studentProcedure.execute(id);      
   }    
}

class StudentProcedure extends StoredProcedure {
   // initialize stored procedure call
   public StudentProcedure(DataSource dataSource, String procedureName) {
      super(dataSource,procedureName);
      declareParameter(new SqlParameter("in_id", Types.INTEGER));
      declareParameter(new SqlOutParameter("out_name", Types.VARCHAR));
      declareParameter(new SqlOutParameter("out_age", Types.INTEGER));
      compile();
   }
   // call the stored procedure
   public Student execute(Integer id){
      Map<String, Object> out = super.execute(id);
      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.

MainApp.java

Following is the content of the MainApp.java file.

package com.tutorialspoint;

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

public class MainApp {
   public static void main(String[] args) {
      // Create the application context by reading Beans.xml  
      ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
	  
      // Create the JDBCTemplate instance from spring context
      StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
	  
      // get the student based on id and print its details
      Student student = studentJDBCTemplate.getStudent(1);
      System.out.print("ID : " + student.getId() );
      System.out.print(", Name : " + student.getName() );
      System.out.println(", Age : " + student.getAge()); 
   }
}

Beans.xml

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

Output

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 : 10