CODEWITHSUNDEEP
javasql-serverstored-procedures
Luke Gaskell
Luke Gaskell

Reputation: 175

Get ResultSet from NamedParameterJdbcTemplate executing a stored procedure

I am trying to get a ResultSet from the execution of a stored procedure using NamedParameterJdbcTemplate. I have set up my stored procedure to return the value with id as the column name. Unfortunately, the way I have found to execute the stored procedure seems to call the execute twice which then closes the result set immediately. I need to find a way to obtain the ResultSet.

MapSqlParameterSource params = params("intSiteId",job.getSite().getId())
            .addValue("dtmStart", job.getStartDate() == null ? null : dateTimeString(job.getStartDate()))
            .addValue("dtmEnd", job.getEndDate() == null ? null : dateTimeString(job.getEndDate()))
            .addValue("intManualFl",job.isManual());

ResultSet result = getTemplate().execute(getSql("create",params), params, new PreparedStatementCallback<ResultSet>(){
        @Override  
        public ResultSet doInPreparedStatement(PreparedStatement ps)  
                throws SQLException, DataAccessException {  
            return ps.executeQuery();
        }
        });

return result.getInt("id");

Upvotes: 2

Views: 8563

Answers (1)

Luke Gaskell
Luke Gaskell

Reputation: 175

I fixed the issue by returning a List of integers rather than a result set. This allowed me to get the data before the result set was closed and return it. I found this solution here: http://www.java2s.com/Code/Java/Spring/SelectStatementWithPreparedStatementCallback.htm

List<Integer> result = getTemplate().execute(getSql("create"), params, new PreparedStatementCallback<List<Integer>>(){
        @Override  
        public List<Integer> doInPreparedStatement(PreparedStatement ps)  
                throws SQLException, DataAccessException {  
            ResultSet rs = ps.executeQuery();
            List<Integer> id = new LinkedList<Integer>();

            while (rs.next())
                id.add(rs.getInt(1));

            rs.close();
            return id;
        }
        });
        return result.get(0);

Upvotes: 1

Related Questions