Announcement Announcement Module
Collapse
No announcement yet.
JdbcTemplate call with ResultSet Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JdbcTemplate call with ResultSet

    I'm trying to get the results of a JdbcTemplate call on a stored procedure. I can't figure out from the documentation how to extract the results.

    Can anyone provide me with an example of how this should be used?

    Thanks,

    Kim

    MS SQL Stored proc:

    Code:
    CREATE PROCEDURE
    SP_GET_NEXT_SEQU (@MYTABLE char(25))
    AS
    BEGIN TRANSACTION
      BEGIN
      UPDATE F_SEQU WITH (UPDLOCK)  Set SQ_COUNT=SQ_COUNT+1 WHERE SQ_TABLE=@MYTABLE
      SELECT SQ_COUNT  FROM F_SEQU WHERE SQ_TABLE=@MYTABLE
      END
    COMMIT TRANSACTION
    
    
    GO
    And the method in my DAO that extends JdbcDaoSupport:



    Code:
        public int getNextSequ(final String tableName) {
            
            int sequ = 0;
            final List sequs = new ArrayList();
            
            RowCallbackHandler rch = new RowCallbackHandler() {
                public void processRow(ResultSet rs) throws SQLException {
                    sequs.add(new Integer(rs.getInt(1)));
                }
            };
            
            List params = new ArrayList();
            SqlReturnResultSet rsr = new SqlReturnResultSet("sequ", rch);        
            params.add(rsr);
            
            Map results = getJdbcTemplate().call(new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con) throws SQLException {
                    CallableStatement cs = con.prepareCall("{call SP_GET_NEXT_SEQU (?)}");              
                    cs.setString(1, tableName);
                    return cs;
                }
            }, params);
            
            if (!sequs.isEmpty())
                sequ = ((Integer)sequs.iterator().next()).intValue();        
               
            return sequ;
            
        }

  • #2
    I think you got it. Your Spring code should work as is. What problems are you encountering?

    I don't know if it is a good idea to call a stored procedure that does it's own transaction management - you should verify that it does not interfere with any ongoing transaction that you might have already when you call this stored procedure.

    Comment


    • #3
      The call does not return a result set. The stored procedure itself does return a single row single column result when executed in query analyser.

      I am confused as to why the SqlReturnResultSet is included in params but not the input parameter.

      The documentation doesn't provide any examples on how to get a result set using the template.

      Comment


      • #4
        There are two types of parameters in and out. Only refernces for the out parameters need to be supplied since you are directly setting the in parameters in the createCallableStatement callback. You could also supply the in parameters but they would not be used in your case. Some other framework classes like the StoredProcedure class do use them.

        The SqlReturnResultSet is a special type of out parameter that returns a resultset. The resultset will be handled by the RowCallbackHandler that you provide to the out parameter. The callback method processRow gets called onece for each row in the resultset and you can map the data to any datastructure that you prefer. The actual resultset is passed in as a parameter to this callback method. In you example the List sequs will contain the data from the returned resultset.

        You might find the StoredProcedure class easier to work with. Here is a sample of what this would look like.


        Code:
        	public void callProc(DataSource dataSource) {	
        		SeqProcedure sp = new SeqProcedure(dataSource);
        		int seq = sp.execute("xxx");
        		System.out.println(seq);
        	}
        
        	public class SeqProcedure extends StoredProcedure {
        		SeqProcedure(DataSource dataSource) {
        			super(dataSource, "SP_GET_NEXT_SEQU");
        			declareParameter(new SqlReturnResultSet("sequ", new RowMapper() {
        				public Object mapRow(ResultSet rs, int rowNum)
        						throws SQLException {
        					Integer seq = new Integer(rs.getInt(1));
        					return seq;
        				}
        			}));
        			declareParameter(new SqlParameter("table", Types.VARCHAR));
        			compile();
        		}
        		
        		public int execute(String tableName) {
        			Map input = new HashMap(1);
        			input.put("table", tableName);
        			Map out = execute(input);
        			List l = (List)out.get("sequ");
        			int seq = ((Number) l.get(0)).intValue();
        			return seq;
        		}
        	}
        The constructor of the SeqProcedure defines the in and out parameters and in this example I used the RowMapper for mapping the rows returned in the resultset. The execute method is here used to mimic the signature of the stored procedure and it will create the actual map that gets passed in and extract the data returned.

        Comment


        • #5
          Thanks. I'll give it a try.

          Comment

          Working...
          X