Announcement Announcement Module
Collapse
No announcement yet.
Using JDBCTemplate to handle stored proc result sets Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using JDBCTemplate to handle stored proc result sets

    Hi,

    I've been through the examples here but still can't work out what to do.

    I use Oracle and initially I used sql directly in my java. However, they all need to be replaced with stored proc calls.

    Here's my current method I want to replace with a call to a stored proc instead:

    Code:
    public List scheduledForDeletion() {
            jt = new JdbcTemplate(dataSource);
            
            List rows = jt.queryForList(
                    "select ID,RESULT_AS_LOC from T_PBTVQUERY WHERE (SYSDATE-INSERT_DATE) > 5 and STATUS_ID IN (30,40)");
    
            return rows;
        }
    My oracle proc will not take any parameters and will return a ref cursor.

    Any help appreciated.

    Thanks

    Rakesh

  • #2
    Here is an example using the StoredProcedure class for calling a procedure that returns a ref-cursor and a timestamp.
    Code:
    package org.springframework.prospring.ticket.db;
    
    import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.jdbc.object.StoredProcedure;
    import org.springframework.prospring.ticket.domain.Genre;
    
    import javax.sql.DataSource;
    import java.util.Map;
    import java.util.HashMap;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class GetGenresCall extends StoredProcedure {
        private static final String GET_GENRES_SQL = "get_genres";
    
        public GetGenresCall(DataSource dataSource) {
            super(dataSource, GET_GENRES_SQL);
            declareParameter(new SqlOutParameter("genre",
                    oracle.jdbc.OracleTypes.CURSOR, new MapGenre()));
            declareParameter(new SqlOutParameter("rundate", java.sql.Types.TIMESTAMP));
            compile();
        }
    
        Map executeGetGenre() {
            Map out = execute(new HashMap());
            return out;
        }
    
    }
    
    class MapGenre implements RowMapper {
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Genre genre = new Genre();
            genre.setId(rs.getInt("id"));
            genre.setName(rs.getString("name"));
            return genre;
        }
    }
    The way you would call this class is as follows:
    Code:
            GetGenresCall proc = new GetGenresCall(ds);
            ...
            Map res = proc.executeGetGenre();
            List genre = (List)res.get("genre");
            for &#40;int i = 0; i < genre.size&#40;&#41;; i++&#41; &#123;
                Genre g = &#40;Genre&#41;genre.get&#40;i&#41;;
                ...
            &#125;

    Comment


    • #3
      Hi,

      thanks for replying Thomas. I already know about the Stored Procedure class and have used them extensively. In fact, I was using them even when I could have used the JDBCTemplate class directly with a CalleableStatement.

      I guess what I was asking if there was a way I could use the JDBC template directly with a CalleableStatement to get the result back as a list.

      I guess not?

      Thanks

      Rakesh

      Comment


      • #4
        You could use the "Object execute(String callString, CallableStatementCallback action) " method on JdbcTemplate. You would have to implement the CallableStatementCallback functionality retreiving the resultset and iterating over the rows using regular JDBC code.

        Comment


        • #5
          Hi,

          I've coded this but it errors out when i try and access a value in the resultset:

          Code:
          JdbcTemplate jt = new JdbcTemplate&#40;ds&#41;;
                  Object o = jt.execute&#40;"&#123;?= call testpkg.testcase4&#40;?,?&#41;&#125;",new CallableStatementCallback&#40;&#41;&#123;
          
                      public Object doInCallableStatement&#40;CallableStatement arg0&#41; throws SQLException, DataAccessException &#123;
                          arg0.registerOutParameter&#40;1,OracleTypes.CURSOR&#41;;
                          arg0.setString&#40;2,"Hello"&#41;;
                          arg0.setInt&#40;3,5&#41;;
                          
                          ResultSet rs = arg0.executeQuery&#40;&#41;;
                          
                          while&#40;rs.next&#40;&#41;&#41;&#123;
                              System.out.println&#40;"ROWID&#58; " + rs.getString&#40;"ROWID"&#41;&#41;;
                              System.out.println&#40;"Ename&#58; " + rs.getString&#40;"ename"&#41;&#41;;
                          &#125;
                          
                          return null;
                      &#125;
                      
                  &#125;&#41;;
          and here's the corresponding Oracle function:

          Code:
          FUNCTION testcase4&#40;s IN VARCHAR2,
          			   i IN PLS_INTEGER&#41; RETURN SYS_REFCURSOR IS
          		RESULT SYS_REFCURSOR;
          	BEGIN
          		OPEN RESULT FOR
          			SELECT ROWID,
          			       ename
          			FROM emp;
          	
          		RETURN&#40;RESULT&#41;;
          	END testcase4;
          here's the error:



          Code:
          org.springframework.jdbc.BadSqlGrammarException&#58; CallableStatementCallback; bad SQL grammar &#91;&#123;?= call testpkg.testcase4&#40;?,?&#41;&#125;&#93;; nested exception is java.sql.SQLException&#58; ORA-00900&#58; invalid SQL statement
          
          java.sql.SQLException&#58; ORA-00900&#58; invalid SQL statement
          
          	at oracle.jdbc.dbaccess.DBError.throwSqlException&#40;DBError.java&#58;134&#41;
          	at oracle.jdbc.ttc7.TTIoer.processError&#40;TTIoer.java&#58;289&#41;
          	at oracle.jdbc.ttc7.v8Odscrarr.receive&#40;v8Odscrarr.java&#58;205&#41;
          	at oracle.jdbc.ttc7.TTC7Protocol.describe&#40;TTC7Protocol.java&#58;800&#41;
          	at oracle.jdbc.driver.OracleStatement.describe&#40;OracleStatement.java&#58;6442&#41;
          	at oracle.jdbc.driver.OracleStatement.get_column_index&#40;OracleStatement.java&#58;6206&#41;
          	at oracle.jdbc.driver.OracleResultSetImpl.findColumn&#40;OracleResultSetImpl.java&#58;1557&#41;
          	at oracle.jdbc.driver.OracleResultSet.getString&#40;OracleResultSet.java&#58;1543&#41;
          	at com.amex.ifst.fet.dao.SampleDao$1.doInCallableStatement&#40;SampleDao.java&#58;113&#41;
          	at org.springframework.jdbc.core.JdbcTemplate.execute&#40;JdbcTemplate.java&#58;808&#41;
          	at org.springframework.jdbc.core.JdbcTemplate.execute&#40;JdbcTemplate.java&#58;837&#41;
          	at com.amex.ifst.fet.dao.SampleDao.getListOFNamesFromDb&#40;SampleDao.java&#58;103&#41;
          	at com.amex.ifst.fet.dao.TestSampleDao.testFunctionReturningRefCursor&#40;TestSampleDao.java&#58;51&#41;
          	at sun.reflect.NativeMethodAccessorImpl.invoke0&#40;Native Method&#41;
          	at sun.reflect.NativeMethodAccessorImpl.invoke&#40;Unknown Source&#41;
          	at sun.reflect.DelegatingMethodAccessorImpl.invoke&#40;Unknown Source&#41;
          	at java.lang.reflect.Method.invoke&#40;Unknown Source&#41;
          	at junit.framework.TestCase.runTest&#40;TestCase.java&#58;154&#41;
          	at junit.framework.TestCase.runBare&#40;TestCase.java&#58;127&#41;
          	at junit.framework.TestResult$1.protect&#40;TestResult.java&#58;106&#41;
          	at junit.framework.TestResult.runProtected&#40;TestResult.java&#58;124&#41;
          	at junit.framework.TestResult.run&#40;TestResult.java&#58;109&#41;
          	at junit.framework.TestCase.run&#40;TestCase.java&#58;118&#41;
          	at junit.framework.TestSuite.runTest&#40;TestSuite.java&#58;208&#41;
          	at junit.framework.TestSuite.run&#40;TestSuite.java&#58;203&#41;
          	at junit.extensions.TestDecorator.basicRun&#40;TestDecorator.java&#58;22&#41;
          	at junit.extensions.TestSetup$1.protect&#40;TestSetup.java&#58;19&#41;
          	at junit.framework.TestResult.runProtected&#40;TestResult.java&#58;124&#41;
          	at junit.extensions.TestSetup.run&#40;TestSetup.java&#58;23&#41;
          	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests&#40;RemoteTestRunner.java&#58;478&#41;
          	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run&#40;RemoteTestRunner.java&#58;344&#41;
          	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main&#40;RemoteTestRunner.java&#58;196&#41;
          Thanks

          Rakesh

          Comment


          • #6
            Rakesh,

            Missing one more parameter in the call !

            your code :
            JdbcTemplate jt = new JdbcTemplate(ds);
            Object o = jt.execute("{?= call testpkg.testcase4(?,?)}",new CallableStatementCallback(){


            Corrected one :

            JdbcTemplate jt = new JdbcTemplate(ds);
            Object o = jt.execute("{?= call testpkg.testcase4(?,?,?)}",new CallableStatementCallback(){

            Or

            JdbcTemplate jt = new JdbcTemplate(ds);
            Object o = jt.execute("call testpkg.testcase4(?,?,?)",new CallableStatementCallback(){

            Hope it should work.

            Thanks
            Sudhakar

            Comment

            Working...
            X