How to call Stored Procedure using Spring SimpleJdbcCall ?

In this example we will see how to call stored procedure using org.springframework.jdbc.core.simple.SimpleJdbcCall

1. Database setup

For this example we will use one simple customer table and one stored procedure. Run below sql script to set up data in MySql

 

--
-- Create schema customerdb
--
 
CREATE DATABASE IF NOT EXISTS customerdb;
USE customerdb;
 
--
-- Definition of table `customer`
--
 
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `address` VARCHAR(45) NOT NULL,
  `phone` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;
 
--
-- Definition of procedure `customerDetails`
--
 
DROP PROCEDURE IF EXISTS `customerDetails`;
 
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `customerDetails`( IN id INT,  OUT firstname VARCHAR(255))
BEGIN
 SELECT name INTO firstname
   FROM customer
   WHERE id = id;
END $$
DELIMITER ; 

2. Create a class for Data base operation (CustomerDataService.java)

Note: During the callStoredProcedure method we are using the Spring SimpleJdbcCall

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

public class CustomerDataService {
    private JdbcTemplate template;
    public void setTemplate(JdbcTemplate template) {
        this.template = template;
    }

    public void insertCustomerDetails(int id, String name, String address,
            String phone) {
        String query = "insert into customer (id,name,address,phone) values (?,?,?,?)";
        template.update(query, id, name, address, phone);
        System.out.println("Record inserted successfully");
    }

    public List<?> selectAllCustomerDetails() {
        List<?> customerList = template.queryForList("select * from customer");
        return customerList;
    }

    public void callStoredProcedure() {
        SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(template)
                .withProcedureName("customerDetails");
        Map<String, Object> inParamMap = new HashMap<String, Object>();
        inParamMap.put("id", 1);
        SqlParameterSource in = new MapSqlParameterSource(inParamMap);
        Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
        System.out.println(simpleJdbcCallResult);
    }

}

3. Create spring configuration (SpringConfig.xml)

<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
   <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName"  value="com.mysql.jdbc.Driver"></property>
  <property name="url" value="jdbc:mysql://localhost:3306/customerDB"></property>
  <property name="username" value="root"></property>
  <property name="password" value="root"></property>
 </bean>
  <bean id="jdbctemplate" class="org.springframework.jdbc.core.JdbcTemplate">
     <constructor-arg>
        <ref bean="dataSource"/>
     </constructor-arg>    
     </bean>
    <bean id="customerDataService" class="com.pretech.jdbc.CustomerDataService">
       <property name="template">
       <ref bean="jdbctemplate"/>
    </property>
</bean>
</beans>

4. Create a test class (CustomerMaster.java)

import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

public class CustomerMaster {

    public static void main(String[] args) {
        Resource resource = new ClassPathResource("SpringConfig.xml");
        BeanFactory factory = new XmlBeanFactory(resource);
        CustomerDataService customerDataService = (CustomerDataService) factory.getBean("customerDataService");

        // inserting data

        customerDataService.insertCustomerDetails(1, "VINOD", "BANGALORE", "90909090");
        // selecting data
        System.out.println("Customer Details" + customerDataService.selectAllCustomerDetails());

        // Calling stored procedure

        customerDataService.callStoredProcedure();

    }

}

5. Run it

After running the CustomerMaster we will get below output.

 

Record inserted successfully
Customer Details[{id=1, name=VINOD, address=BANGALORE, phone=90909090}]
{#update-count-1=1, firstname=VINOD}