JdbcTemplate multiple result sets

Question

I am trying to find an easy way to deal with Stored Procedures / SQL returning multiple result sets. I have been using the SimpleJdbcOperations#queryForList() method however this will only return the first result set as a List<Map<String, Object>>. I need to be able to get multiple result sets, ideally as a Collection of List<Map<String, Object>> or something. The program I am writing is a middleware component so I don't know what the SQL will be, or the form of the result set.

I think I have to use the JdbcOperations class which gives me access to more methods, including execute(CallableStatementCreator csc, CallableStatementCallback<T> action) but now I am stuck.

    CallableStatementCallback<T> callback = new CallableStatementCallback<T>() {
       @Override
       public T doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException
       {
           boolean results = cs.execute(request);
           while(results)
           {
               ResultSet result = cs.getResultSet();
               results = cs.getMoreResults();
           }
           return null;
        }
};

I am not really sure how to use the method though, or what to do with the ResultSet to get my generic List<Map<String, Object>>s.

Ardor

A ResultSet contains rows as you would find them after executing SQL directly on a database, it won't ever return a List<Map<X,Y>>. You have to generate it yourself with the fields in the ResultSet, which you can access with getters. – Sarge

I should be able to iterate the ResultSet using hasNext() and getObject()? – Ardor

You would use a while loop with next() and get different row fields with the various getters. – Sarge

Think I can only use getObject since I don't know what the ResultSet will be. – Ardor

Aren't you the one writing the SQL? I would think you're going to have to cast it as some point. – Sarge

Answer 1

I managed to get a Set<ResultSet> using this code,

private Set<ResultSet> executeProcedure(final String sql)
{
    return jdbc.execute(new CallableStatementCreator() {
        @Override
        public CallableStatement createCallableStatement(Connection con) throws SQLException
        {
            return con.prepareCall(sql);
        }
    }, new CallableStatementCallback<Set<ResultSet>>() {
        @Override
        public Set<ResultSet> doInCallableStatement(CallableStatement cs) throws SQLException
        {
            Set<ResultSet> results = new HashSet<>();

            boolean resultsAvailable = cs.execute();

            while (resultsAvailable)
            {
                results.add(cs.getResultSet());
                resultsAvailable = cs.getMoreResults();
            }
            return results;
        }
    });
}

Just going to look at translating a ResultSet into List<Map<String, Object>>.

Ardor

Hi. This might be an alternative for you: https://mcmap.net/q/1624320/-stored-procedure-returning-multiple-tables-to-spring-jdbc-template – Shaner

Answer 2

This code might be easier to use in most cases:

Map<String,Object> resultSets = new JdbcTemplate(dataSource)
            .call(con -> con.prepareCall(query), new ArrayList<>());

Aldenalder

Thanks for the answer. Little addition - statement parameters can be set in first argument (CallableStatementCreator) – Heilungkiang

Answer 3

You can use the resultSet.getMetaData() method to work out what columns are in the data:

ResultSetMetaData meta = resultSet.getMetaData();
int colcount = meta.getColumnCount();
for (int i = 1; i <= colcount; i++) 
{
    String name = meta.getColumnLabel(i); // This is the name of the column
    int type = meta.getColumnType(i);     // from java.sql.Types
   // Maybe add to a Map,List, etc...
}

You can then do as the other commentors have mentioned do a loop through the ResultSet pulling out the data you need:

while (resultSet.hasNext())
{
     resultSet.next();
     // Find the columns you want to extract (via the above method maybe) and add to your row.
}

Rocaille

Oh cool thanks :) I was looking for a way to get column count. – Ardor

Answer 4

I have used below method to get List of ResultSet in form of List<Map<String, Object>>

public List<List<Map<String, Object>>> executeProcedure(final String sql) {
        return jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                return con.prepareCall(sql);
            }
        }, new CallableStatementCallback<List<List<Map<String, Object>>>>() {
            @Override
            public List<List<Map<String, Object>>> doInCallableStatement(CallableStatement cs) throws SQLException {
                boolean resultsAvailable = cs.execute();
                List<List<Map<String, Object>>> list = new ArrayList<List<Map<String, Object>>>();
                while (resultsAvailable) {
                    ResultSet resultSet = cs.getResultSet();
                    List<Map<String, Object>> subList = new ArrayList<Map<String, Object>>();
                    while (resultSet.next()) {
                        ResultSetMetaData meta = resultSet.getMetaData();
                        int colcount = meta.getColumnCount();
                        Map<String, Object> map = new HashMap<String, Object>();
                        for (int i = 1; i <= colcount; i++) {
                            String name = meta.getColumnLabel(i);
                            map.put(name, resultSet.getString(i));
                        }
                        subList.add(map);
                    }
                    list.add(subList);
                    resultsAvailable = cs.getMoreResults();
                }
                return list;
            }
        });
    }

Darlleen