Apache DbUtils : Handling multiple result sets returned from Stored Procedure

Question

I have a problem with using DbUtils to retrieve the results from a Stored Procedure in SQL Server.

The stored procedure when executing in SQL Server Management Studio is returning two separate result sets when executed for a specific input value, but for other values it's returning only one result set. The following images illustrate the issue:

One result set returned:

Two result sets returned:

The problem I am facing here is I am using a DbUtils BeanListHandler to convert the results into a list of UserInfo beans.

List<UserInfo> userList = (List<UserInfo>) run.query(STORED_PROC, new BeanListHandler(UserInfo.class), refId);

When the stored procedure returns just one result set it's working fine. However, in the case where two result sets are returned, it is only giving the list for the first result set.

I think that by using JDBC we can get using multiple ResultSet but I am not sure how to handle this DbUtils.

Can somebody provide an insight? If any other info is required, please update me I will provide.

Answer 1

To be honest, if that stored procedure is returning 2 result sets in a single execution, you've got bigger problems. Ideally, you want the 2 results to be returned as a single table result from the SP and then you should be ok.

1) Try to contact the person who has access to the SP and bring your case to their attention. Have them create a temp table to store all the records from the 2 results being returned and then just return all the contents in that temp table.

2) If you don't have that option, you can try the process outlined in this article retrieve-data-from-stored-procedure-which-has-multiple-result-sets to get the results if you unable to get any movement from 1)

HTH

Dave

Answer 2

It would be simple enough to subclass the QueryRunner object and then tweak the appropriate query method(s) to handle multiple result sets. With the following code I was able to retrieve the full list of UserInfo objects using

ResultSetHandler<List<UserInfo>> h = new BeanListHandler<UserInfo>(UserInfo.class);
MyQueryRunner run = new MyQueryRunner(ds);
String sql = 
        "EXEC dbo.Gain_Web_GetCompanyRepByIndRefID @RefID=?";
List<UserInfo> result = run.query(sql, h, 2);

where MyQueryRunner is

package com.example.so36623732;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

public class MyQueryRunner extends QueryRunner {

    public MyQueryRunner(DataSource ds) {
        super(ds);
    }

    /**
     * Executes the given SELECT or EXEC SQL query and returns a result object.
     * The <code>Connection</code> is retrieved from the
     * <code>DataSource</code> set in the constructor.
     * @param <T> The type of object that the handler returns
     * @param sql The SQL statement to execute.
     * @param rsh The handler used to create the result object from
     * the <code>ResultSet</code>.
     * @param params Initialize the PreparedStatement's IN parameters with
     * this array.
     * @return An object generated by the handler.
     * @throws SQLException if a database access error occurs
     */
    public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.<T>query(conn, true, sql, rsh, params);
    }

    /**
     * Calls query after checking the parameters to ensure nothing is null.
     * @param conn The connection to use for the query call.
     * @param closeConn True if the connection should be closed, false otherwise.
     * @param sql The SQL statement to execute.
     * @param params An array of query replacement parameters.  Each row in
     * this array is one set of batch replacement values.
     * @return The results of the query.
     * @throws SQLException If there are database or parameter errors.
     */
    @SuppressWarnings("unchecked")
    private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
            throws SQLException {
        if (conn == null) {
            throw new SQLException("Null connection");
        }

        if (sql == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null SQL statement");
        }

        if (rsh == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null ResultSetHandler");
        }

        PreparedStatement stmt = null;
        ResultSet rs = null;
        T result = null;
        List<T> allResults = null;

        try {
            stmt = this.prepareStatement(conn, sql);
            this.fillStatement(stmt, params);
            rs = this.wrap(stmt.executeQuery());
            allResults = (List<T>)rsh.handle(rs);
            while (stmt.getMoreResults()) {
                rs = stmt.getResultSet();
                result = rsh.handle(rs);
                allResults.addAll((List<T>)result);
            }

        } catch (SQLException e) {
            this.rethrow(e, sql, params);

        } finally {
            try {
                close(rs);
            } finally {
                close(stmt);
                if (closeConn) {
                    close(conn);
                }
            }
        }

        return (T) allResults;
    }

}