Spring JDBC Template for calling Stored Procedures
Solution 1
There are a number of ways to call stored procedures in Spring.
If you use CallableStatementCreator
to declare parameters, you will be using Java's standard interface of CallableStatement
, i.e register out parameters and set them separately. Using SqlParameter
abstraction will make your code cleaner.
I recommend you looking at SimpleJdbcCall
. It may be used like this:
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withSchemaName(schema)
.withCatalogName(package)
.withProcedureName(procedure)();
...
jdbcCall.addDeclaredParameter(new SqlParameter(paramName, OracleTypes.NUMBER));
...
jdbcCall.execute(callParams);
For simple procedures you may use jdbcTemplate
's update
method:
jdbcTemplate.update("call SOME_PROC (?, ?)", param1, param2);
Solution 2
Here are the ways to call the stored procedures from java
1. Using CallableStatement:
connection = jdbcTemplate.getDataSource().getConnection();
CallableStatement callableStatement = connection.prepareCall("{call STORED_PROCEDURE_NAME(?, ?, ?)}");
callableStatement.setString(1, "FirstName");
callableStatement.setString(2, " LastName");
callableStatement.registerOutParameter(3, Types.VARCHAR);
callableStatement.executeUpdate();
Here we externally manage the resource closing
2. Using CallableStatementCreator
List paramList = new ArrayList();
paramList.add(new SqlParameter(Types.VARCHAR));
paramList.add(new SqlParameter(Types.VARCHAR));
paramList.add(new SqlOutParameter("msg", Types.VARCHAR));
Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection connection)
throws SQLException {
CallableStatement callableStatement = connection.prepareCall("{call STORED_PROCEDURE_NAME(?, ?, ?)}");
callableStatement.setString(1, "FirstName");
callableStatement.setString(2, " LastName");
callableStatement.registerOutParameter(3, Types.VARCHAR);
return callableStatement;
}
}, paramList);
3. Use SimpleJdbcCall:
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withProcedureName("STORED_PROCEDURE_NAME");
Map<String, Object> inParamMap = new HashMap<String, Object>();
inParamMap.put("firstName", "FirstNameValue");
inParamMap.put("lastName", "LastNameValue");
SqlParameterSource in = new MapSqlParameterSource(inParamMap);
Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
System.out.println(simpleJdbcCallResult);
4. Use StoredProcedure class of org.springframework.jdbc.object
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("FirstNameValue", " LastNameValue");
Solution 3
I generally prefer to extend Spring based StoredProcedure
class to execute stored procedures.
-
You need to create your class constructor and need to call
StoredProcedure
class constructor in it. This super class constructor accepts DataSource and procedure name.Example code:
public class ProcedureExecutor extends StoredProcedure { public ProcedureExecutor(DataSource ds, String funcNameorSPName) { super(ds, funcNameorSPName); declareParameter(new SqlOutParameter("v_Return", Types.VARCHAR, null, new SqlReturnType() { public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName) throws SQLException { final String str = cs.getString(paramIndex); return str; } })); declareParameter(new SqlParameter("your parameter", Types.VARCHAR)); //set below param true if you want to call database function setFunction(true); compile(); }
-
Override execute method of stored procedure call as below
public Map<String, Object> execute(String someParams) { final Map<String, Object> inParams = new HashMap<String, Object>(8); inParams.put("my param", "some value"); Map outMap = execute(inParams); System.out.println("outMap:" + outMap); return outMap; }
Hope this helps you.
Solution 4
One more way to call stored procedure is:
sql="execute Procedure_Name ?";
Object search[]={Id};
List<ClientInvestigateDTO> client=jdbcTemplateObject.query(sql,search,new
ClientInvestigateMapper());
In this example 'ClientInvestigateDTO' is the POJO class and 'ClientInvestigateMapper' is the mapper class.'client' stores all the result you get on calling the stored procedure.
adarshr
Updated on May 03, 2020Comments
-
adarshr about 4 years
What is the correct way to invoke stored procedures using modern day (circa 2012) Spring JDBC Template?
Say, I have a stored procedure that declares both
IN
andOUT
parameters, something like this:mypkg.doSomething( id OUT int, name IN String, date IN Date )
I have come across
CallableStatementCreator
based approaches where we have to explicitly registerIN
andOUT
parameters. Consider the following method inJdbcTemplate
class:public Map<String, Object> call(CallableStatementCreator csc, List<SqlParameter> declaredParameters)
Of course, I do know that I can use it like so:
List<SqlParameter> declaredParameters = new ArrayList<SqlParameter>(); declaredParameters.add(new SqlOutParameter("id", Types.INTEGER)); declaredParameters.add(new SqlParameter("name", Types.VARCHAR)); declaredParameters.add(new SqlParameter("date", Types.DATE)); this.jdbcTemplate.call(new CallableStatementCreator() { @Override CallableStatement createCallableStatement(Connection con) throws SQLException { CallableStatement stmnt = con.createCall("{mypkg.doSomething(?, ?, ?)}"); stmnt.registerOutParameter("id", Types.INTEGER); stmnt.setString("name", "<name>"); stmnt.setDate("date", <date>); return stmnt; } }, declaredParameters);
What is the purpose of
declaredParameters
when I am already registering them in mycsc
implementation? In other words, why would I need to pass in acsc
when spring can simply docon.prepareCall(sql)
internally? Basically, can't I pass in either one of them instead of both of them?Or, is there a much better way to call stored procedures (using Spring JDBC Template) than what I have come across so far?
Note: You may find many questions that appear to have a similar title but they are not the same as this one.
-
adarshr about 12 years
SimpleJdbcCall
seems really cool. I will test this and let you know how it fared in my case. -
otgw over 9 yearsThe update method did not work for me, I received bad SQL grammar exception even though my statement executed just fine in the database. SimpleJdbcCall worked very well
-
Suleman khan about 9 yearsI am trying to use jdbcTemplate.update() the same way as you said but getting error saying
"cannot invoke update on null object"
. -
Alex Bitek almost 9 yearsThe
jdbcTemplate.update("call SOME_PROC (?, ?)", param1, param2);
approach fails for me too with errororg.postgresql.util.PSQLException: ERROR: syntax error at or near "call"
when invoked asgetNamedParameterJdbcTemplate().update("call API.UPSERT_TOKEN(:token, :email)", params)
-
EpicPandaForce over 8 years@MnemonicFlow use
?, ?
instead of:token, :email
-
Alex Bitek over 8 years@EpicPandaForce I had NamedParameterJdbcTemplate, not JdbcTemplate
-
inanutshellus about 8 yearsI don't know if it's Oracle-specific syntax, but the syntax for Oracle is:
jdbcTemplate.update("{ call my_schema.my_pkg.SOME_PROC (?, ?) }", param1, param2);
(note the curly braces). -
zygimantus about 8 yearsWhy it gives:
SQL update affected 0 rows
when rows were actually affected? -
SashikaXP over 7 yearsparam name should exactly match the param names that you have in your database. Also if you need any OUT params, define it as for example
new SqlInOutParameter(paramName,Types.VARCHAR)
so that they will be included in the returning Map -
Dmytro Boichenko over 7 years@adarshr Sometimes usage of
SimpleJdbcCall
has pitfalls. Code could stuck ifSimpleJdbcCall
is shared across several threads -
Kent Bull over 7 yearsWhat would this look like if I had a multi-row returning procedure? Would it be a List of Map<String, Object> objects?
-
0190198 over 7 years@Kent - You are correct. It would be List<Map<String, Object>> for multiple rows.
-
Steve almost 7 yearsHow would you get warnings (as in, Statement.getWarnings() in jdbc) from this?
-
theMind almost 6 yearshow dou you solve perfomance issue for SimpleJdbcCall? It takes 3000-4000ms for one call. But this time is 0.1-0.5 ms on basic jdbc call.
-
Ashish Shetkar over 5 yearshow can i handle it if my stored procedure is taking longer time , how do i manage the connection and time out issue
-
Marmite Bomber over 3 yearsThe plain JDBC (option 1.) seems to be the simplest option in this context -> +1