Spring Framework provides excellent support to call stored procedures
from Java application. In fact there are multiple ways to call stored procedure
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 second
approach to call stored procedure. It's more object oriented, but same time
requires more coding. StoredProcedure class allows you to declare IN
and OUT parameters and call stored procedure using its various execute() method,
which has protected access and can only be called from sub class. I personally
prefer to implement StoredProcedure class as Inner class, if its tied up
with one of DAO Object, e.g. in this case
it nicely fit inside EmployeeDAO. Then you can provide convenient
method to wrap stored procedure calls. In order to demonstrate, how to call
stored procedures from spring based application, we will first create a simple
stored proc using MySQL database, as shown below.
MySQL Stored procedure
We will use 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 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 ;
For quick test, you can also call this stored procedure in mysql, assuming
you have employee table as discussed in this article and some data on it. To
learn more about stored proc in MySQL, see How to create and call MySQL stored procedure
form command line.
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
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; /* * 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
Spring configuration file:
<?xml version="1.0" encoding="UTF-8"?>
<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://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 stored procedure from Java application using
Spring Framework. As discussed in 10 JDBC best practices for Java
Programmer, JDBC API provides more straightforward approach using CallableStatement, but
Spring's StoredProcedure class is also easy to use. You
can also explore calling stored procedure, directly using JdbcTemplate in
Spring.
Further Reading
Spring Framework 5: Beginner to Guru
Learn Spring Security by Eugen
Spring Master Class - Beginner to Expert
Introduction to Spring MVC 4 By Bryan Hansen
Spring and Hibernate for Beginners
Spring in Action 4th edition by Craig Walls
Spring Framework 5: Beginner to Guru
Learn Spring Security by Eugen
Spring Master Class - Beginner to Expert
Introduction to Spring MVC 4 By Bryan Hansen
Spring and Hibernate for Beginners
Spring in Action 4th edition by Craig Walls
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 course to learn REST with Spring MVC.
6 comments :
Thanks for this tutorial. It's brilliant.
Unfortunately if I try this I get the following error
java.sql.SQLException: boo!
springframework.dao.TransientDataAccessResourceException: CallableStatementCallback; SQL [{call sp_MyStoredProc(?, ?, ?)}]; boo!
I am using org.springframework.jdbc-3.1.3 and
mysql-connector-java-5.1.25
@Liberty Bell, do you have this stored procedure and table created in database? TransientDataAccessResourceException comes when resource is temporarily unavailable, could be table or stored proc. I would suggest retrying after running stored procedure in database itself, just to make sure it's working.
How do you register the Cursor as an INPUT parameter in Spring Stored Procedure?
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`name`)' at line 1
I am using MySQL-5.6.14 version.
Thanks for your tutorial, It's working perfectly in my project.
But I have a little problem, the stored procedured that it's calling since my dao class modifiy several tables and it's probably that finishes with error. In this case the java program must to do a rollback to get back the data in initial values.
How can I do that?
I tried to add the annotation @Transactional(rollbackFor=Exception.class) in the execute method, but it doesn't work.
Thanks for advance.
Regrads.
Jose Pascual Gimeno
can you please upload the project as well, it will be helpful for beginners
Post a Comment