There are many ways we can call database stored procedure from Spring framework.
Lets us consider test stored procedure as:
DELIMITER $$
DROP PROCEDURE IF EXISTS test.PROC_TEST $$
CREATE PROCEDURE PROC_TEST(IN firstName VARCHAR(30), IN lastName VARCHAR(30), OUT message VARCHAR(100))
BEGIN
SET message= concat('Welcome: ',concat(firstName, lastName));
END $$
DELIMITER ;
We will assume that required JdbcTemplate is populated in our DAO class.
Using CallableStatement:
This is similar to plain JDBC where we create CallableStatement from Connection object and use it for calling stored procedure.
The Code:
final String procedureCall = "{call PROC_TEST(?, ?, ?)}";
Connection connection = null;
try {
//Get Connection instance from dataSource
connection = jdbcTemplate.getDataSource().getConnection();
CallableStatement callableSt = connection.prepareCall(procedureCall);
callableSt.setString(1, "Om");
callableSt.setString(2, " Singh");
callableSt.registerOutParameter(3, Types.VARCHAR);
//Call Stored Procedure
callableSt.executeUpdate();
System.out.println(callableSt.getString(3));
}catch (SQLException e) {
e.printStackTrace();
} finally {
if(connection != null)
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
In above code, as we are directly using the connection object, connection should be closed before exiting method.
Using CallableStatementCreator:
CallableStatementCreator interface has only one method:
public CallableStatement createCallableStatement(Connection connection)
This method takes Connection object as parameter. Using this connection object we can create and return the CallableStatement. We don’t need to care about closing connection object in this method.
The Code:
SqlParameter fNameParam = new SqlParameter(Types.VARCHAR);
SqlParameter lNameParam = new SqlParameter(Types.VARCHAR);
SqlOutParameter outParameter = new SqlOutParameter("msg", Types.VARCHAR);
List paramList = new ArrayList();
paramList.add(fNameParam );
paramList.add(lNameParam );
paramList.add(outParameter);
final String procedureCall = "{call PROC_TEST(?, ?, ?)}";
Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection connection)
throws SQLException {
CallableStatement callableStatement = connection.prepareCall(procedureCall);
callableStatement.setString(1, "Om");
callableStatement.setString(2, " Singh");
callableStatement.registerOutParameter(3, Types.VARCHAR);
return callableStatement;
}
}, paramList);
System.out.println(resultMap.get("msg"));
Here we have passed CallableStatementCreator instance and list of SqlParameter to jdbcTemplate.call method. Using SqlParameter we can declare name and type of the parameter which stored procedure accepts.
Note while getting result(message) beck from resultMap we have used “msg” as key, which is specified as name while creating SqlOutParameter.
Using SimpleJdbcCall:
We don’t need to create CallableStatement when using SimpleJdbcCall class. Just specify name of the procedure and map the required parameters.
The Code:
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withProcedureName("PROC_TEST");
Map<String, Object> inParamMap = new HashMap<String, Object>();
inParamMap.put("firstName", "Suresh");
inParamMap.put("lastName", "Rajput");
SqlParameterSource in = new MapSqlParameterSource(inParamMap);
Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
System.out.println(simpleJdbcCallResult);
Using org.springframework.jdbc.object.StoredProcedure class:
StoredProcedure is an abstract class provided by Spring. By subclassing StoredProcedure we need to pass name of the stored procedure and give a call to enclosing execute method with required parameters.
The Code:
First Create subclass of StoredProcedure: MyStoredProcedure
class MyStoredProcedure extends StoredProcedure {
public MyStoredProcedure(JdbcTemplate jdbcTemplate, String name) {
super(jdbcTemplate, name);
setFunction(false);
}
}
Use MyStoredProcedure to call database stored procedure:
//Pass jdbcTemlate and name of the stored Procedure.
MyStoredProcedure myStoredProcedure = new MyStoredProcedure(jdbcTemplate, "PROC_TEST");
//Sql parameter mapping
SqlParameter fNameParam = new SqlParameter("fName", Types.VARCHAR);
SqlParameter lNameParam = new SqlParameter("lName", Types.VARCHAR);
SqlOutParameter msgParam = new SqlOutParameter("msg", Types.VARCHAR);
SqlParameter[] paramArray = {fNameParam, lNameParam, msgParam};
myStoredProcedure.setParameters(paramArray);
myStoredProcedure.compile();
//Call stored procedure
Map storedProcResult = myStoredProcedure.execute("Aamir", " Roshan");
System.out.println(storedProcResult);
Conclusion
These are some of the ways by which we can call StoredProcedure using Spring. There might be some approaches which I have missed to list here. Out of these approaches I feel last two: Using SimpleJdbcCall and StoredProcedure are more powerful and easy. Suggestions are most welcome.
Lets us consider test stored procedure as:
DELIMITER $$
DROP PROCEDURE IF EXISTS test.PROC_TEST $$
CREATE PROCEDURE PROC_TEST(IN firstName VARCHAR(30), IN lastName VARCHAR(30), OUT message VARCHAR(100))
BEGIN
SET message= concat('Welcome: ',concat(firstName, lastName));
END $$
DELIMITER ;
We will assume that required JdbcTemplate is populated in our DAO class.
Using CallableStatement:
This is similar to plain JDBC where we create CallableStatement from Connection object and use it for calling stored procedure.
The Code:
final String procedureCall = "{call PROC_TEST(?, ?, ?)}";
Connection connection = null;
try {
//Get Connection instance from dataSource
connection = jdbcTemplate.getDataSource().getConnection();
CallableStatement callableSt = connection.prepareCall(procedureCall);
callableSt.setString(1, "Om");
callableSt.setString(2, " Singh");
callableSt.registerOutParameter(3, Types.VARCHAR);
//Call Stored Procedure
callableSt.executeUpdate();
System.out.println(callableSt.getString(3));
}catch (SQLException e) {
e.printStackTrace();
} finally {
if(connection != null)
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
In above code, as we are directly using the connection object, connection should be closed before exiting method.
Using CallableStatementCreator:
CallableStatementCreator interface has only one method:
public CallableStatement createCallableStatement(Connection connection)
This method takes Connection object as parameter. Using this connection object we can create and return the CallableStatement. We don’t need to care about closing connection object in this method.
The Code:
SqlParameter fNameParam = new SqlParameter(Types.VARCHAR);
SqlParameter lNameParam = new SqlParameter(Types.VARCHAR);
SqlOutParameter outParameter = new SqlOutParameter("msg", Types.VARCHAR);
List paramList = new ArrayList();
paramList.add(fNameParam );
paramList.add(lNameParam );
paramList.add(outParameter);
final String procedureCall = "{call PROC_TEST(?, ?, ?)}";
Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection connection)
throws SQLException {
CallableStatement callableStatement = connection.prepareCall(procedureCall);
callableStatement.setString(1, "Om");
callableStatement.setString(2, " Singh");
callableStatement.registerOutParameter(3, Types.VARCHAR);
return callableStatement;
}
}, paramList);
System.out.println(resultMap.get("msg"));
Here we have passed CallableStatementCreator instance and list of SqlParameter to jdbcTemplate.call method. Using SqlParameter we can declare name and type of the parameter which stored procedure accepts.
Note while getting result(message) beck from resultMap we have used “msg” as key, which is specified as name while creating SqlOutParameter.
Using SimpleJdbcCall:
We don’t need to create CallableStatement when using SimpleJdbcCall class. Just specify name of the procedure and map the required parameters.
The Code:
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withProcedureName("PROC_TEST");
Map<String, Object> inParamMap = new HashMap<String, Object>();
inParamMap.put("firstName", "Suresh");
inParamMap.put("lastName", "Rajput");
SqlParameterSource in = new MapSqlParameterSource(inParamMap);
Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
System.out.println(simpleJdbcCallResult);
Using org.springframework.jdbc.object.StoredProcedure class:
StoredProcedure is an abstract class provided by Spring. By subclassing StoredProcedure we need to pass name of the stored procedure and give a call to enclosing execute method with required parameters.
The Code:
First Create subclass of StoredProcedure: MyStoredProcedure
class MyStoredProcedure extends StoredProcedure {
public MyStoredProcedure(JdbcTemplate jdbcTemplate, String name) {
super(jdbcTemplate, name);
setFunction(false);
}
}
Use MyStoredProcedure to call database stored procedure:
//Pass jdbcTemlate and name of the stored Procedure.
MyStoredProcedure myStoredProcedure = new MyStoredProcedure(jdbcTemplate, "PROC_TEST");
//Sql parameter mapping
SqlParameter fNameParam = new SqlParameter("fName", Types.VARCHAR);
SqlParameter lNameParam = new SqlParameter("lName", Types.VARCHAR);
SqlOutParameter msgParam = new SqlOutParameter("msg", Types.VARCHAR);
SqlParameter[] paramArray = {fNameParam, lNameParam, msgParam};
myStoredProcedure.setParameters(paramArray);
myStoredProcedure.compile();
//Call stored procedure
Map storedProcResult = myStoredProcedure.execute("Aamir", " Roshan");
System.out.println(storedProcResult);
Conclusion
These are some of the ways by which we can call StoredProcedure using Spring. There might be some approaches which I have missed to list here. Out of these approaches I feel last two: Using SimpleJdbcCall and StoredProcedure are more powerful and easy. Suggestions are most welcome.