How to Call Stored Procedure in Spring JDBC | Spring SimpleJdbcCall Example

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;
    }
}
 

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>