How to Call Stored Procedure in Spring JDBC | Spring SimpleJdbcCall Example
November 09, 2013
In this page, we will learn how to call a stored procedure in our spring application with JDBC. Spring JDBC provides SimpleJdbcCall that has execute() method. Execute method takes input IN parameter of stored procedure. SimpleJdbcCall.execute() returns a Map. And that map stores key as out parameter name and value is value returned by stored procedure. Find the example how to do it.
CompanyDao.java
package com.concretepage.dao; import java.util.Map; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.SqlParameterSource; import org.springframework.jdbc.core.simple.SimpleJdbcCall; import org.springframework.stereotype.Repository; import com.concretepage.bean.Company; @Repository public class CompanyDao { private SimpleJdbcCall simpleJdbcCall; @Autowired public void setDataSource(DataSource dataSource) { this.simpleJdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("read_company"); } public Company getCompnay(int id){ SqlParameterSource in = new MapSqlParameterSource() .addValue("in_id", id); Map<String,Object> out = simpleJdbcCall.execute(in); Company comp = new Company(); comp.setName((String)out.get("out_name")); comp.setLocation((String)out.get("out_location")); comp.setNoOfEmp(Integer.parseInt(String.valueOf(out.get("out_no_emp")))); return comp; } }
Company.java
package com.concretepage.bean; public class Company { private long id; private String name; private String location; private int noOfEmp; public long getId() { return id; } public void setId(long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getLocation() { return location; } public void setLocation(String location) { this.location = location; } public int getNoOfEmp() { return noOfEmp; } public void setNoOfEmp(int noOfEmp) { this.noOfEmp = noOfEmp; } }
package com.concretepage; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.concretepage.bean.Company; import com.concretepage.dao.CompanyDao; public class SpringTest { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("spring.xml"); CompanyDao companyDao = (CompanyDao)context.getBean("companyDao"); Company comp = companyDao.getCompnay(1); System.out.println(comp.getLocation()+" "+comp.getName()+" "+comp.getNoOfEmp()); } }
CREATE PROCEDURE `read_company` ( IN in_id INTEGER, OUT out_name VARCHAR(100), OUT out_location VARCHAR(100), OUT out_no_emp INTEGER) BEGIN SELECT name, location, no_of_emp INTO out_name, out_location, out_no_emp FROM company where id = in_id; END;
CREATE TABLE `company` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NULL DEFAULT NULL, `location` VARCHAR(50) NULL DEFAULT NULL, `no_of_emp` INT(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) )
<?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: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/context http://www.springframework.org/schema/context/spring-context-3.0.xsd"> <bean id="companyDao" class="com.concretepage.dao.CompanyDao"> <property name="dataSource" ref="dataSource"/> </bean> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/test"/> <property name="username" value="root"/> <property name="password" value=""/> </bean> </beans>
how-to-call-stored-procedure-in-spring-jdbc.zip