Monday, December 28, 2009

Execute stored procedure in spring with return value and out parameters

Following code example shows how to execute stored procedure in spring with return value and out parameters. The comment along with the code explains the usage.

import java.math.BigDecimal;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

public class StoredProcedureExampleDAO extends SimpleJdbcCall {

public JdbcDequeueTicketDAO(final JdbcTemplate jdbcTemplate) {
// Here declare all the IN and OUT parameters defined for the stored
// procedure
declareParameters(new SqlParameter("CountryCode", Types.VARCHAR),
new SqlOutParameter("CountryName", Types.VARCHAR));
// Register the stored procedure name
// This ensures the stored procedure return value also gets populated
// in the returned Map with key "return".

public final String getCountryName(final String countryCode) {
// Map to send the IN params values.
Map inParams = new HashMap();
inParams.put("CountryCode", countryCode);

// Map for the OUT params and return value.
Map outParams = null;
try {
outParams = execute(inParams);
} catch (Throwable t) {
throw new DataAccessException("Failed to get the country name", t);
if (null != outParams) {
BigDecimal returnValue = (BigDecimal) outParams.get("return");
// The logic of getCountryName stored procedure is that its
// returns 1 if execution is successful otherwise returns 0.
if (null != returnValue && 1 == returnValue.intValue()) {
// On success read the CountryName value
String countryName = (String) outParams.get("CountryName");
return countryName;
throw new DataAccessException("Failed to get the country name");


1 comment: