How to call Stored Procedures from Java using IN and OUT parameter - Spring Framework Example Tutorial (original) (raw)

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.

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.

By the way, if you are new to the Spring framework then I also suggest you join a comprehensive and up-to-date course to learn Spring in depth. If you need recommendations, I highly suggest you take a look at Spring Framework 5: Beginner to Guru, one of the comprehensive and hands-on course to learn modern Spring.

It' also the most up-to-date and covers Spring 5. It's also very affordable and you can buy in just $10 on Udemy sales which happen every now and then.

MySQL Stored procedure

We will use the following stored procedure for this example. This is created in MySQL database and accept an input parameter IN, which is employeeId and return name of an employee using its output parameter called, name.

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

Spring Framework tutorial and example to call stored procedures from JavaHere is a complete code example of how to call a stored procedure from the Spring framework. In this example, we have extended abstract class StoredProcedure in our class called, EmployeeSP. This is declared as a nested class inside EmployeeDAO because it's only used by this class, if your stored procedure is used in my multiple DAO classes than you can also make it a top-level class.

If you look at the constructor of EmployeeSP, it calls super class constructor and passes data source and name of database stored procedure. We have also declared two stored procedure parameters, one is IN parameter id, and the other is the OUT parameter.

Input to the stored the procedure is passed using the IN parameter, and output from the stored procedure is read using the OUT parameter.

Your stored procedure can have multiple IN and OUT parameters. StoredProcedure class also provides several execute() methods, which can be invoked to call a stored procedure and get a result. It returns the result as Map, where the key is OUT parameter, and value is the result of a stored procedure.

Here is the code for DAO class and stored procedure along with the Spring Configuration file, since the Spring framework is based on the principle of Dependency Injection and Inversion of control, this file is required to create and manage objects.

Java Class which wraps Stored procedure

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

}

Main class to test stored procedure

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

/*

}

Output 2013-01-17 23:56:34,408 0 [main] DEBUG EmployeeDao$EmployeeSP

Spring configuration file:

<beans xmlns="http:**//**www.springframework.org/schema/beans"

xmlns:xsi="http:**//www.w3.org/2001/XMLSchema-instance" xmlns:p="http://**www.springframework.org/schema/p"

xmlns:jms="http:**//www.springframework.org/schema/jms" xmlns:context="http://**www.springframework.org/schema/context"

xsi:schemaLocation="

http:**//www.springframework.org/schema/beans http://**www.springframework.org/schema/beans/spring-beans-3.0.xsd

http:**//www.springframework.org/schema/jms http://**www.springframework.org/schema/jms/spring-jms-3.0.xsd

http:**//www.springframework.org/schema/context http://**" title="undefined" rel="noopener noreferrer">www.springframework.org/schema/context/spring-context-3.0.xsd"\>

<bean id="propertyPlaceholderConfigurer"

class="org.springframework.beans.factory.config
.PropertyPlaceholderConfigurer">

<property name="locations">

<list>

<value>classpath:jdbc.properties</value>

</list>

</property>

</bean>

<bean id="springDataSource" class="org.springframework.jdbc.datasource
.SingleConnectionDataSource">

<property name="driverClassName" value="${db.driver}" />

<property name="url" value="${db.url}" />

<property name="username" value="root" />

<property name="password" value="root" />

</bean>

<bean id="employeeDao" class ="EmployeeDao">

<property name="dataSource" ref="springDataSource"/>

</bean>

</beans>

That's all on How to call a stored procedure from Java application using Spring Framework. As discussed in 10 JDBC best practices for Java Programmer, JDBC API provides a more straightforward approach using CallableStatement, but Spring's StoredProcedure class is also easy to use. You can also explore calling a stored procedure, directly using JdbcTemplate in Spring.

Other Spring tutorials from Javarevisited Blog

P.S. - If you want to learn how to develop RESTful Web Service using Spring MVC in-depth, I suggest you join the REST with Spring certification class by Eugen Paraschiv. One of the best courses to learn REST with Spring MVC.