Spring JdbcDaoSupport Call Stored Procedure

Spring JdbcDaoSupport Call Stored Procedure

There are many different ways you can call store procedure either using JdbcDaoSupport or JdbcTemplate. In previous example we saw by using JdbcTemplate Call Stored Procedure In Out Parameter and Call Stored Procedure Multiple Resultset using Spring JdbcTemplate. Here you will see how to call stored procedure using JdbcDaoSupport. You can directly call stored procedure as you make call to the data base to execute procedure. Call depends on types of data base for example:

• MySQL call store procedure with one parameter: CALL SelectMultiResultSet(31)
• MSSQL call store procedure with one parameter: exec SelectMultiResultSet 31

Note: For this demo we are using MySQL database.

Before use store procedure in JdbcDaoSupport execute it against the database and see if it’s working then you can directly cut and paste same statement in java code as shown in example:

• Sample store procedure to call:

DELIMITER $

CREATE DEFINER=`root`@`localhost` PROCEDURE `SelectStoreProcedure`()
BEGIN
SELECT * FROM department;
END

• Java code:

package com.javahonk.dao;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.support.JdbcDaoSupport;


/**
 * @author javahonk
 *
 */
public class SpringJdbcDaoSupportDAOImpl extends JdbcDaoSupport implements SpringJdbcDaoSupportSDAO {
	
	@Override
	public List<Map<String, Object>> storeProcedureOneParameter(Integer departId) {
		String storeProcedureName = "call SelectMultiResultSet(?)";
		return getJdbcTemplate().queryForList(storeProcedureName,departId);
	}

	@Override
	public List<Map<String, Object>> storeProcedureNoParameter() {
		String storeProcedureName = "call SelectStoreProcedure";
		return getJdbcTemplate().queryForList(storeProcedureName);
	}
	
}

• For more details on JdbcDaoSupport please visit spring site here