Announcement Announcement Module
Collapse
No announcement yet.
Calling Oracle stored procedure using JdbcDaoSupport. Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calling Oracle stored procedure using JdbcDaoSupport.

    Hi,

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

    thanks,
    Santhosh.

  • #2
    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.

    Comment


    • #3
      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..?.

      Comment


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

        Code:
        	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:

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

        Comment


        • #5
          perfect..:-) thanks a lot..

          Comment

          Working...
          X