Call Stored Procedure Multiple Resultset Spring JdbcTemplate

Call Stored Procedure Multiple Resultset Spring JdbcTemplate

Here you will see how to call stored procedure using spring jdbctemplate which return multiple resultset and process it. Please follow steps below:

Create three table script:

• person table:

CREATE TABLE `person` (
  `FirstName` varchar(50) DEFAULT NULL,
  `LastName` varchar(50) DEFAULT NULL,
  `street` varchar(50) DEFAULT NULL,
  `city` varchar(25) DEFAULT NULL,
  `state` varchar(25) DEFAULT NULL,
  `country` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• employee table:

CREATE TABLE `employee` (
  `LastName` varchar(25) DEFAULT NULL,
  `DepartmentID` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• department table:

CREATE TABLE `department` (
  `DepartmentID` varchar(25) DEFAULT NULL,
  `DepartmentName` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now create stored procedure which will take one input parameter for this example and select data from all three table and return multiple result set:

DELIMITER $
CREATE DEFINER=`root`@`localhost` PROCEDURE `SelectMultiResultSet`( in departId varchar(25))
BEGIN
Select * from person;
Select * from department where DepartmentID = departId;
Select * from employee;

END$
DELIMITER ;

You could call stored procedure using spring jdbctemplate different way here you will three way to call it:

• Using SimpleJdbcCall:

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

		Map<String, Object> inParamMap = new HashMap<String, Object>();
		inParamMap.put("departId", "31");
		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);
		    }

• Using CallableStatement:

final String procedureCall = "{call SelectMultiResultSet(?)}";
		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("departId", "31");
			
			// Call Stored Procedure
			boolean results = callableSt.execute();
			
			while (results) {
				ResultSet rs = callableSt.getResultSet();
				 ResultSetMetaData rsmd = rs.getMetaData();
				 String tableName = "";
				// Display the column name and type.
			      int cols = rsmd.getColumnCount();
			      for (int i = 1; i <= cols; i++) {
			         System.out.println("NAME: " + rsmd.getColumnName(i) + " " + "TYPE: " + rsmd.getColumnTypeName(i));	
			         tableName = rsmd.getTableName(i);
			         
			      }
			      while (rs.next()) {
					if (tableName.equals("person")) {
						System.out.println("Table name is: "+tableName);
					} else if (tableName.equals("employee")) {
						System.out.println("Table name is: "+tableName);
					}else {
						System.out.println("Table name is: "+tableName);
					}
					
			      }
			     rs.close();				

		        //Check for next result set
		        results = callableSt.getMoreResults();
			}			

		} catch (SQLException e) {

			e.printStackTrace();

		} finally {

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

• Using CallableStatementCreator:

SqlParameter departId = new SqlParameter(Types.VARCHAR);
		
		List<SqlParameter> paramList = new ArrayList<SqlParameter>();
		paramList.add(departId);	

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

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

						CallableStatement callableStatement = connection.prepareCall(procedureCall);
						callableStatement.setString(1, "31");
						return callableStatement;

					}
				}, paramList);
		System.out.println(resultMap);

• Complete class code:

package com.javahonk.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
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.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

import com.javahonk.domain.Person;

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 departId = new SqlParameter(Types.VARCHAR);
		
		List<SqlParameter> paramList = new ArrayList<SqlParameter>();
		paramList.add(departId);	

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

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

						CallableStatement callableStatement = connection.prepareCall(procedureCall);
						callableStatement.setString(1, "31");
						return callableStatement;

					}
				}, paramList);
		System.out.println(resultMap);
		return resultMap;
	}

	private void useCallableStatement() {
		final String procedureCall = "{call SelectMultiResultSet(?)}";
		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("departId", "31");
			
			// Call Stored Procedure
			boolean results = callableSt.execute();
			
			while (results) {
				ResultSet rs = callableSt.getResultSet();
				 ResultSetMetaData rsmd = rs.getMetaData();
				 String tableName = "";
				// Display the column name and type.
			      int cols = rsmd.getColumnCount();
			      for (int i = 1; i <= cols; i++) {
			         System.out.println("NAME: " + rsmd.getColumnName(i) + " " + "TYPE: " + rsmd.getColumnTypeName(i));	
			         tableName = rsmd.getTableName(i);
			         
			      }
			      while (rs.next()) {
					if (tableName.equals("person")) {
						System.out.println("Table name is: "+tableName);
					} else if (tableName.equals("employee")) {
						System.out.println("Table name is: "+tableName);
					}else {
						System.out.println("Table name is: "+tableName);
					}
					
			      }
			     rs.close();				

		        //Check for next result set
		        results = callableSt.getMoreResults();
			}			

		} 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("SelectMultiResultSet");

		Map<String, Object> inParamMap = new HashMap<String, Object>();
		inParamMap.put("departId", "31");
		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;
	}


}

• For more information calling stored procedure please use this link 
• Download complete Spring MVC application with above working example: SpringMVCJDBCTemplateTutorial
• Once you download application please use below URL to run example in web browser: