Spring Framework Tutorial - How To Telephone Telephone Stored Procedures From Coffee Using Inward Too Out Parameter Example

Spring Framework provides first-class back upward to telephone band stored procedures from Java application. In fact at that spot are multiple ways to telephone band stored physical care for inwards Spring Framework, e.g. y'all tin give the sack purpose 1 of the query() method from JdbcTemplate to telephone band stored procedures, or y'all tin give the sack extend abstract class StoredProcedure to telephone band stored procedures from Java. In this Java Spring tutorial, nosotros volition come across minute approach to telephone band stored procedure. It's more object oriented, simply same fourth dimension requires to a greater extent than coding. StoredProcedure shape allows y'all to declare IN too OUT parameters too telephone band stored physical care for using its diverse execute() method, which has protected access too tin give the sack entirely endure called from sub class. I personally prefer to implement StoredProcedure class equally Inner class, if its tied upward amongst one of DAO Object, e.g. inwards this instance it nicely agree within EmployeeDAO. Then y'all tin give the sack render convenient method to wind stored physical care for calls. In social club to demonstrate, how to telephone band stored procedures from saltation based application, nosotros volition showtime do a uncomplicated stored proc using MySQL database, equally shown below.

MySQL Stored procedure

We volition purpose next stored physical care for for this example. This is created inwards MySQL database too convey an input parameter IN, which is employeeId too render lift of employee using its output parameter called, name.

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

For quick test, y'all tin give the sack equally good telephone band this stored physical care for inwards mysql, assuming y'all receive got employee tabular array equally discussed inwards this article too to a greater extent than or less information on it. To larn to a greater extent than almost stored proc inwards MySQL, see How to do too telephone band MySQL stored physical care for shape command line.

mysql> telephone band 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 illustration too Configurations

Here is consummate code illustration of how to telephone band stored physical care for from Spring framework. In this example, nosotros receive got extended abstract class StoredProcedure inwards our shape called, EmployeeSP. This is declared equally nested class inside EmployeeDAO because its entirely used yesteryear this class, if your stored physical care for is used my multiple DAO classes, than y'all tin give the sack equally good travel inwards a top marking class. If y'all look at constructor of EmployeeSP, it calls super shape constructor too passes datasource too lift of database stored procedure. We receive got equally good declared 2 stored physical care for parameters, 1 is IN parameter id, too other is OUT parameter. Input to stored physical care for is passed using IN parameter, too output from stored physical care for is read using OUT parameter. Your stored physical care for tin give the sack receive got multiple IN too OUT parameter. StoredProcedure shape equally good render several execute() methods, which tin give the sack endure invoked to telephone band stored physical care for too acquire result. It render number as Map, where fundamental is OUT parameter, too value is number of stored procedure. Here is the code for DAO shape too stored physical care for along amongst Spring Configuration file, since Spring framework is based on regulation of dependency Injection too Inversion of control, this file is required to do too handle object.

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 physical care for telephone band      */         public String getEmployeeName(int emp_id){                 return (String) sproc.execute(emp_id);         }                 /*          * Inner shape to implement stored physical care for inwards 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 inwards parameter to exceed 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 physical care for using out parameters                                                                 }         } }

Main shape to bear witness stored procedure

import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext;  /*  * Main shape to start too bear witness 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 physical care for using DAO method                 System.out.println("Employee lift 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 amongst SQL [usp_GetEmployeeName] compiled Employee lift 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 telephone band stored physical care for from Java application using Spring Framework. As discussed inwards 10 JDBC best practices for Java Programmer, JDBC API provides to a greater extent than straightforward approach using CallableStatement, simply Spring's StoredProcedure shape is equally good slowly to use. You tin give the sack equally good explore calling stored procedure, straight using JdbcTemplate inwards Spring.

Further Reading
Spring Framework 5: Beginner to Guru
Learn Spring Security yesteryear Eugen
Spring Master Class - Beginner to Expert
Introduction to Spring MVC iv By Bryan Hansen
Spring too Hibernate for Beginners
Spring inwards Action quaternary edition yesteryear Craig Walls

P.S. - If y'all desire to larn how to prepare RESTful Web Service using Spring MVC inwards depth, I propose y'all bring together the REST amongst Spring certification class yesteryear Eugen Paraschiv. One of the best course of report to larn REST amongst Spring MVC. 

Share this post