Spring Framework Tutorial - How To Telephone Vociferation Upwardly Stored Procedures From Coffee Using Inwards Together With Out Parameter Example

Spring Framework provides fantabulous back upwardly to telephone telephone stored procedures from Java application. In fact in that place are multiple ways to telephone telephone stored physical care for inward Spring Framework, e.g. y'all tin run ane of the query() method from JdbcTemplate to telephone telephone stored procedures, or y'all tin extend abstract class StoredProcedure to telephone telephone stored procedures from Java. In this Java Spring tutorial, nosotros volition come across minute approach to telephone telephone stored procedure. It's more object oriented, exactly same fourth dimension requires to a greater extent than coding. StoredProcedure course of education allows y'all to declare IN together with OUT parameters together with telephone telephone stored physical care for using its diverse execute() method, which has protected access together with tin solely endure called from sub class. I personally prefer to implement StoredProcedure class equally Inner class, if its tied upwardly alongside one of DAO Object, e.g. inward this instance it nicely gibe within EmployeeDAO. Then y'all tin supply convenient method to roll stored physical care for calls. In guild to demonstrate, how to telephone telephone stored procedures from trammel based application, nosotros volition start scope a unproblematic stored proc using MySQL database, equally shown below.

MySQL Stored procedure

We volition run next stored physical care for for this example. This is created inward MySQL database together with receive got an input parameter IN, which is employeeId together with render mention of employee using its output parameter called, name.

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

For quick test, y'all tin besides telephone telephone this stored physical care for inward mysql, assuming y'all receive got employee tabular array equally discussed inward this article together with closed to information on it. To larn to a greater extent than most stored proc inward MySQL, see How to scope together with telephone telephone MySQL stored physical care for cast command line.

mysql> telephone telephone 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 representative together with Configurations

Spring Framework provides fantabulous back upwardly to telephone telephone stored procedures from Java applicatio Spring Framework Tutorial - How to telephone telephone Stored Procedures from Java using IN together with OUT parameter example

Here is consummate code representative of how to telephone telephone stored physical care for from Spring framework. In this example, nosotros receive got extended abstract class StoredProcedure inward our course of education called, EmployeeSP. This is declared equally nested class inside EmployeeDAO because its solely used past times this class, if your stored physical care for is used my multiple DAO classes, than y'all tin besides travel into a top marker class. If y'all look at constructor of EmployeeSP, it calls super course of education constructor together with passes datasource together with mention of database stored procedure. We receive got besides declared 2 stored physical care for parameters, ane is IN parameter id, together with other is OUT parameter. Input to stored physical care for is passed using IN parameter, together with output from stored physical care for is read using OUT parameter. Your stored physical care for tin receive got multiple IN together with OUT parameter. StoredProcedure course of education besides supply several execute() methods, which tin endure invoked to telephone telephone stored physical care for together with acquire result. It render effect as Map, where fundamental is OUT parameter, together with value is effect of stored procedure. Here is the code for DAO course of education together with stored physical care for along alongside Spring Configuration file, since Spring framework is based on regulation of dependency Injection together with Inversion of control, this file is required to scope together with scope out 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 telephone      */         public String getEmployeeName(int emp_id){                 return (String) sproc.execute(emp_id);         }                 /*          * Inner course of education to implement stored physical care for inward 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 inward parameter to overstep 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 course of education to essay out stored procedure

import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext;  /*  * Main course of education to start together with essay out 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 mention 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 alongside SQL [usp_GetEmployeeName] compiled Employee mention 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">

<list>

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

</list>

</property>

</bean>

</bean>

</bean>

</beans>

That's all on How to telephone telephone stored physical care for from Java application using Spring Framework. As discussed inward 10 JDBC best practices for Java Programmer, JDBC API provides to a greater extent than straightforward approach using CallableStatement, exactly Spring's StoredProcedure course of education is besides slowly to use. You tin besides explore calling stored procedure, straight using JdbcTemplate inward Spring.

Further Reading
Spring Framework 5: Beginner to Guru
Learn Spring Security past times Eugen
Spring Master Class - Beginner to Expert
Introduction to Spring MVC iv By Bryan Hansen
Spring together with Hibernate for Beginners
Spring inward Action quaternary edition past times Craig Walls

P.S. - If y'all desire to larn how to educate RESTful Web Service using Spring MVC inward depth, I advise y'all bring together the REST alongside Spring certification class past times Eugen Paraschiv. One of the best course of education to larn REST alongside Spring MVC.

Cari Blog Ini

Real Java Taste