How to call Stored Procedure using Spring StoredProcedure?

In the previous example we used org.springframework.jdbc.core.simple.SimpleJdbcCall to call stored procedures. In this example we will see how to use org.springframework.jdbc.object.StoredProcedure to call stored procedures.

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 class which extends spring Stored Procedure Class

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.object.StoredProcedure;
 
public class StoredProcedureCall extends StoredProcedure {
 public StoredProcedureCall(JdbcTemplate jdbcTemplate, String spName) {
  super(jdbcTemplate, spName);
  setFunction(false);
 
 }
}

 

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

import java.sql.Types;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;

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() {
        StoredProcedureCall storedProcedureCall = new StoredProcedureCall(template, "customerDetails");

        SqlParameter idparam = new SqlParameter("id", Types.INTEGER);
        SqlOutParameter outParam = new SqlOutParameter("firstname", Types.VARCHAR);

        SqlParameter[] paramArray = { idparam, outParam };

        storedProcedureCall.setParameters(paramArray);
        storedProcedureCall.compile();

        // Call stored procedure
        Map storedProcResult = storedProcedureCall.execute(1);
        System.out.println(storedProcResult);
    }
}

4. 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>

5. 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();

    }

}

6. 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}