Spring Framework Tutorial - How To Telephone Telephone Stored Procedures From Coffee Using Inward In Addition To Out Parameter Example
Thursday, December 6, 2018
Add Comment
Spring Framework provides fantabulous back upwardly to telephone telephone stored procedures from Java application. In fact at that spot are multiple ways to telephone telephone stored physical care for inwards Spring Framework, e.g. y'all tin run 1 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 encounter minute approach to telephone telephone stored procedure. It's more object oriented, only same fourth dimension requires to a greater extent than coding. StoredProcedure degree 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 alone move called from sub class. I personally prefer to implement StoredProcedure class every bit Inner class, if its tied upwardly amongst one of DAO Object, e.g. inwards this representative it nicely tally within EmployeeDAO. Then y'all tin furnish convenient method to twine stored physical care for calls. In guild to demonstrate, how to telephone telephone stored procedures from leap based application, nosotros volition commencement create a unproblematic stored proc using MySQL database, every bit shown below.
MySQL Stored procedure
We volition run next stored physical care for for this example. This is created inwards MySQL database together with convey an input parameter IN, which is employeeId together with render refer of employee using its output parameter called, name.
mysql> DELIMITER // mysql> create physical care for usp_GetEmployeeName(IN id INT, OUT refer VARCHAR(20)) -> begin -> select emp_name into refer from employee where emp_id = id; -> end// Query OK, 0 rows affected (0.52 sec) mysql> DELIMITER ;
For quick test, y'all tin too telephone telephone this stored physical care for inwards mysql, assuming y'all conduct maintain employee tabular array every bit discussed inwards this article together with closed to information on it. To larn to a greater extent than virtually stored proc inwards MySQL, see How to create together with telephone telephone MySQL stored physical care for shape 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
Here is consummate code representative of how to telephone telephone stored physical care for from Spring framework. In this example, nosotros conduct maintain extended abstract class StoredProcedure inwards our degree called, EmployeeSP. This is declared every bit nested class inside EmployeeDAO because its alone used past times this class, if your stored physical care for is used my multiple DAO classes, than y'all tin too move inwards a top bird class. If y'all look at constructor of EmployeeSP, it calls super degree constructor together with passes datasource together with refer of database stored procedure. We conduct maintain too declared 2 stored physical care for parameters, 1 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 conduct maintain multiple IN together with OUT parameter. StoredProcedure degree too furnish several execute() methods, which tin move invoked to telephone telephone stored physical care for together with acquire result. It render outcome as Map, where cardinal is OUT parameter, together with value is outcome of stored procedure. Here is the code for DAO degree together with stored physical care for along amongst Spring Configuration file, since Spring framework is based on regulation of dependency Injection together with Inversion of control, this file is required to create together with create create 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 degree 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 top 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 degree to essay stored procedure
import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; /* * Main degree to start together with essay 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 refer 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 refer 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 telephone 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, only Spring's StoredProcedure degree is too tardily to use. You tin too explore calling stored procedure, direct using JdbcTemplate inwards 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 inwards Action fourth edition past times Craig Walls
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 inwards Action fourth edition past times Craig Walls
P.S. - If y'all desire to larn how to railroad train RESTful Web Service using Spring MVC inwards depth, I advise y'all bring together the REST amongst Spring certification class past times Eugen Paraschiv. One of the best course of didactics to larn REST amongst Spring MVC.
0 Response to "Spring Framework Tutorial - How To Telephone Telephone Stored Procedures From Coffee Using Inward In Addition To Out Parameter Example"
Post a Comment