Calling Oracle stored procedure using JdbcDaoSupport.

[santhoshg77]

Hi,

Can anybody give me some sample code using JdbcDaoSupport to call an Oracle stored procedure.

thanks,
Santhosh.

[venosov]

Hi, you have to create a class that extends org.springframework.jdbc.object.StoredProcedure and your DAO class that extends org.springframework.jdbc.core.support.JdbcDaoSuppo rt should call that class, see these links.

Old but useful.
Current official documentation with an example.

[santhoshg77]

Thank you so much venosov.
I extended JdbcDaoSupport and used JdbcTemplate.call(CallableStatementCreator csc, List declaredParameters)

But i have few issues here.

First i was not sure how to get CallableStatementCreator reference.
After some R&D,i took the below solution,

//Procudure name
String query = "{my_procedure(?,?)}"

//Parameters
List<SqlParameter> declaredParameters = new ArrayList<SqlParameter>(1);
declareParameter.add(new SqlOutParameter("param_out",Types.INTEGER));
declareParameter.add(new SqlParameter("param_in1",Types.VARCHAR));

//Input data
Map paramMap = new HashMap();
paramMap.put("param_in1","ABC");

//Create factory
CallableStatementCreatorFactory cf = new CallableStatementCreatorFactory(query,declaredPara meters);

//Get CallableStatementCreator
CallableStatementCreator cr = cf.newCallableStatementCreator(paramMap);
result = getJdbcTemplate().call(cr, declaredParameters);

I got it working,but it looks urgly.. :-(
What is the correct approach to get CallableStatementCreator reference..?.
Also CallableStatementCreator reference should be injected,isn't..?.

[venosov]

OK, you can create a class that implements CallableStatementCreator, for example:

	private class ProcCallableStatementCreator implements
			CallableStatementCreator {
		private int a;
		private int b;

		public ProcCallableStatementCreator(int a, int b) {
			this.a = a;
			this.b = b;
		}

		public CallableStatement createCallableStatement(Connection conn)
				throws SQLException {
			CallableStatement cs = conn.prepareCall("call procTest(?, ?, ?)");
			cs.registerOutParameter(1, Types.NUMERIC);
			cs.setInt(2, a);
			cs.setInt(3, b);
			return cs;
		}
	}

and use it:

			Map results = template.call(
					new ProcCallableStatementCreator(10, 20), params);

[santhoshg77]

perfect..:-) thanks a lot..