Spring Framework provides excellent support to call stored procedures from Java application. In fact, there are multiple ways to call stored procedures in Spring Framework, e.g. you can use one of the query() method from JdbcTemplate to call stored procedures, or you can extend abstract class StoredProcedure to call stored procedures from Java. In this Java Spring tutorial, we will see the second approach to call a stored procedure. It's more object-oriented, but at the same time requires more coding. StoredProcedure class allows you to declare IN and OUT parameters and call stored procedure using it's various execute() method, which has protected access and can only be called from a subclass.

Video Player is loading.

Loaded: 0.04%

I personally prefer to implement StoredProcedure class as Inner class, if it's tied up with one of DAO Object, e.g. in this case it nicely fits inside EmployeeDAO. Then you can provide a convenient method to wrap stored procedure calls. 

In order to demonstrate, how to call stored procedures from the spring-based application, we will first create a simple stored proc using MySQL database, as shown below.

MySQL Stored procedure

mysql> DELIMITER //
mysql> create procedure usp_GetEmployeeName(IN id INT, OUT name VARCHAR(20))
    -> begin
    -> select emp_name into name from employee where emp_id = id;
    -> end//
Query OK, 0 rows affected (0.52 sec)

mysql> DELIMITER ;

mysql> call usp_GetEmployeeName(103, @name);
Query OK, 1 row affected (0.05 sec)

mysql> select @name;
+-------+
| @name |
+-------+
| Jack  |
+-------+
1 row in set (0.00 sec)

Spring Stored Procedure example and Configurations

import java.sql.Types;
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.object.StoredProcedure;

public class EmployeeDao {

        private JdbcTemplate jdbcTemplate;
        private EmployeeSP  sproc;
       
        public void setDataSource(DataSource source){
                this.jdbcTemplate = new JdbcTemplate(source);
                this.sproc = new EmployeeSP(jdbcTemplate.getDataSource());
        }
       
    /*
     * wraps stored procedure call
     */
        public String getEmployeeName(int emp_id){
                return (String) sproc.execute(emp_id);
        }
       
        /*
         * Inner class to implement stored procedure in spring.
         */
        private class EmployeeSP extends StoredProcedure{
                private static final String SPROC_NAME = "usp_GetEmployeeName";
               
                public EmployeeSP( DataSource datasource ){
                        super( datasource, SPROC_NAME );
                        declareParameter( new SqlParameter( "id", Types.INTEGER) ); 
                        //declaring sql in parameter to pass input

                        declareParameter( new SqlOutParameter( "name", Types.VARCHAR ) ); 
                        //declaring sql out parameter
                        compile();
                }
               
                public Object execute(int emp_id){
                        Map<String,Object> results = super.execute(emp_id);
                        return results.get("name"); 
                        //reading output of stored procedure using out parameters
                                               
                }
        }
}

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/*
 * Main class to start and test this Java application
 */
public class Main {

        public static void main(String args[]){
                ApplicationContext ctx = new ClassPathXmlApplicationContext
                                                ("spring-config.xml");
                EmployeeDao dao = (EmployeeDao) ctx.getBean("employeeDao");
               
                //calling stored procedure using DAO method
                System.out.println("Employee name for id 103 is : "
 + dao.getEmployeeName(103));
        }
}

Output
2013-01-17 23:56:34,408 0    [main] DEBUG EmployeeDao$EmployeeSP  
- Compiled stored procedure. Call string is [{call usp_GetEmployeeName(?, ?)}]
2013-01-17 23:56:34,439 31   [main] DEBUG EmployeeDao$EmployeeSP  
- RdbmsOperation with SQL [usp_GetEmployeeName] compiled
Employee name for id 103 is : Jack