Spring JDBC template for calling stored procedures

Question

What is the correct way to call stored procedures using the modern day (circa 2012) Spring JDBC Template?

Say I have a stored procedure that declares an IN and OUT parameter, something like this:

 mypkg.doSomething( id OUT int, name IN String, date IN Date ) 

I came across CallableStatementCreator based CallableStatementCreator where we must explicitly register the IN and OUT parameters. Consider the following method in the JdbcTemplate class:

 public Map<String, Object> call(CallableStatementCreator csc, List<SqlParameter> declaredParameters) 

Of course, I know that I can use it like this:

 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 already register them in my csc implementation? In other words, why do I need to switch to csc when Spring can just do con.prepareCall(sql) internally? Basically, I can’t get through in either of them, or in both of them?

Or is there a much better way to invoke stored procedures (using the Spring JDBC Template) than what I have come across so far?

Note. You may find many questions that appear to have a similar heading, but they do not match this.

Answer 1

There are several ways to call stored procedures in Spring.

If you use CallableStatementCreator to declare parameters, you will use the standard Java CallableStatement interface, then you can register the parameters and set them separately. Using the SqlParameter abstraction will make your code cleaner.

I recommend you look at SimpleJdbcCall . It can be used as follows:

 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 can use the jdbcTemplate update method:

 jdbcTemplate.update("call SOME_PROC (?, ?)", param1, param2); 

Answer 2

I usually prefer to extend the StoredProcedure Spring class to execute stored procedures.

• You need to create your class constructor and call the StoredProcedure class constructor in it. This superclass constructor accepts the name of the data source and procedure.

  Code example:

   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 the stored procedure call method 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.

Answer 3

Listed below are ways to call 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 closure of resources

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 the StoredProcedure class for 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"); 

Link

Answer 4

Another way to call a stored procedure:

 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. The "client" stores the entire result that you get when you call the stored procedure.