Spring JdbcTemplate Calling Stored Procedure In Out Parameter

Spring JdbcTemplate Calling Stored Procedure In Out Parameter

In this demo you will see different way to call stored procedure using Spring JdbcTemplate which takes one input and one output parameter.

• Create stored procedure script (For this demo we are using MySQL database)

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $

CREATE DEFINER=`root`@`localhost` PROCEDURE `SampleProcedure`(IN firstName VARCHAR(50), OUT personFirstName VARCHAR(50))
BEGIN

set personFirstName = firstName;

END

• Before writing java program if you want to execute and check what value stored procedure is returning then please use below script:

CALL `javahonk`.`SampleProcedure`('Java', @FirstName);
SELECT @FirstName;

Here you will see three different way to call stored procedure:

• Using CallableStatementCreator:

SqlParameter firstName = new SqlParameter(Types.VARCHAR);
		SqlOutParameter outParameter = new SqlOutParameter("firstName", Types.VARCHAR);
		
		List<SqlParameter> paramList = new ArrayList<SqlParameter>();
		paramList.add(firstName);
		paramList.add(outParameter);	

		final String procedureCall = "{call SampleProcedure(?, ?)}";
		Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {

					@Override
					public CallableStatement createCallableStatement(Connection connection) throws SQLException {

						CallableStatement callableStatement = connection.prepareCall(procedureCall);
						callableStatement.setString(1, "Java");
						callableStatement.registerOutParameter(2, Types.VARCHAR);
						return callableStatement;

					}
				}, paramList);
		System.out.println("Return out value:"+resultMap.get("firstName"));

• Using CallableStatement:

final String procedureCall = "{call SampleProcedure(?, ?)}";
		Connection connection = null;
		Map<String, Object> result = new HashMap<String, Object>();
		try {

			// Get Connection from dataSource
			connection = jdbcTemplate.getDataSource().getConnection();
			CallableStatement callableSt = connection.prepareCall(procedureCall);
			callableSt.setString(1, "Java");
			callableSt.registerOutParameter(2, Types.VARCHAR);
			
			//Call Stored Procedure
			callableSt.executeUpdate();
			System.out.println("Out paramter value: "+callableSt.getString(2));					

		} catch (SQLException e) {

			e.printStackTrace();

		} finally {

			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

• Using SimpleJdbcCall:

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("SampleProcedure");

		Map<String, Object> inParamMap = new HashMap<String, Object>();
		inParamMap.put("firstName", "Java");
		SqlParameterSource in = new MapSqlParameterSource(inParamMap);

		Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
		
		
		 Iterator<Entry<String, Object>> it = simpleJdbcCallResult.entrySet().iterator();
		    while (it.hasNext()) {
		        Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it.next();
		        String key = (String) entry.getKey();
		        Object value = (Object) entry.getValue();
		        System.out.println("Key: "+key);
		        System.out.println("Value: "+value);
		    }

• Complete class:

package com.javahonk.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

public class PersonDAOImpl implements IPersonDAO {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public Map<String, Object> returnMultipleResultSet() {
		
		// TO use SimpleJdbcCall please un-comment below
		//return useSimpleJdbcCall();
		// TO use CallableStatement please un-comment below
		//useCallableStatement();
		
		// TO use CallableStatementCreator below is the code
		SqlParameter firstName = new SqlParameter(Types.VARCHAR);
		SqlOutParameter outParameter = new SqlOutParameter("firstName", Types.VARCHAR);
		
		List<SqlParameter> paramList = new ArrayList<SqlParameter>();
		paramList.add(firstName);
		paramList.add(outParameter);	

		final String procedureCall = "{call SampleProcedure(?, ?)}";
		Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {

					@Override
					public CallableStatement createCallableStatement(Connection connection) throws SQLException {

						CallableStatement callableStatement = connection.prepareCall(procedureCall);
						callableStatement.setString(1, "Java");
						callableStatement.registerOutParameter(2, Types.VARCHAR);
						return callableStatement;

					}
				}, paramList);
		System.out.println("Return out value:"+resultMap.get("firstName"));
		return resultMap;
	}

	private void useCallableStatement() {
		final String procedureCall = "{call SampleProcedure(?, ?)}";
		Connection connection = null;
		Map<String, Object> result = new HashMap<String, Object>();
		try {

			// Get Connection from dataSource
			connection = jdbcTemplate.getDataSource().getConnection();
			CallableStatement callableSt = connection.prepareCall(procedureCall);
			callableSt.setString(1, "Java");
			callableSt.registerOutParameter(2, Types.VARCHAR);
			
			//Call Stored Procedure
			callableSt.executeUpdate();
			System.out.println("Out paramter value: "+callableSt.getString(2));					

		} catch (SQLException e) {

			e.printStackTrace();

		} finally {

			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

	private Map<String, Object> useSimpleJdbcCall() {
		
		SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("SampleProcedure");

		Map<String, Object> inParamMap = new HashMap<String, Object>();
		inParamMap.put("firstName", "Java");
		SqlParameterSource in = new MapSqlParameterSource(inParamMap);

		Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
		
		
		 Iterator<Entry<String, Object>> it = simpleJdbcCallResult.entrySet().iterator();
		    while (it.hasNext()) {
		        Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it.next();
		        String key = (String) entry.getKey();
		        Object value = (Object) entry.getValue();
		        System.out.println("Key: "+key);
		        System.out.println("Value: "+value);
		    }
		return simpleJdbcCallResult;
	}


}

• If you want to use Spring JdbcTemplate in Spring MVC application please download complete project here: SpringMVCJDBCTemplateTutorial
• Configure project run it using below URL:

For more information calling stored procedure using spring jdbctemplate please use this tutorial