Merging Multiple ResultSet into Single ResultSet i

Question

Let say I have multiple ResultSet(each resultSet would refer to 1 row in database) (they are of same table.) . Now I want to create consolidated ResultSet which would intern have all other resultSet. So my primary goal is to create a combined ResultSet which would point to all rows which where previously pointed by individual resultSet.

I am using Java. Do any one know how pragmatically we can achieve this?

Edit : we are using java.sql.ResultSet.

Edit : To make it more clear :

Let say I have

List<ResultSet> someResults ; // each resultSet Would point to a single row in database.

I want to create a consolidated ResultSet finalResults;

psudo code :

List<ResultSet> resultSets = // poppulated from code
ResultSet rs  = convert(resultSets) // psude conver method

Answer 1

If you are talking about a java.sql.ResultSet, it's not possible as far as I know. I suggest you change your query instead.

Or you can retrieve the results into an java object and then combine all the objects into a Collection.

Answer 2

If the results are gonna be from the same table, why not use UNION/UNION ALL(depending on your needs) in your query itself.

Something like this:

select A, B
from C where Q = R
union
select A, B
from C where P = S

Or else, there is the hard way of iterating through each result set and populating POJOs and adding them to a List/Set(Based on your needs). This seems to be an overkill if there are a lot of result sets.

Answer 3

public class ResultSets {
        private java.util.List<java.sql.ResultSet> resultSets;

        private java.sql.ResultSet current;

        @lombok.SneakyThrows
        public ResultSets(java.util.List<java.sql.ResultSet> resultSets) {
            this.resultSets = new java.util.ArrayList<>(resultSets);
            current = resultSets.remove(0);
        }
        @lombok.SneakyThrows
        public boolean next() {
            if (current.next()) {
                return true;
            }else if (!resultSets.isEmpty()) {
                current = resultSets.remove(0);
                return next();
            }
            return false;
        }

        @lombok.SneakyThrows
        public int getInt(int pos){
            return current.getInt(pos);
        }

        @lombok.SneakyThrows
        public String getString(String field){
            return current.getString(field);
        }
    }

Usage :-

  @lombok.SneakyThrows
  public static void main(String ... args) {

        Connection conn =  pe.getConnection("backup");
        String sql1 = "SELECT count(distinct(User_Key)) FROM user_table";
        String sql2 = "SELECT count(distinct(Username)) FROM user_table";

        Statement stmt1 = conn.createStatement();
        Statement stmt2 = conn.createStatement();

        List<ResultSet> resultSets = new ArrayList<>();
        resultSets.add(stmt1.executeQuery(sql1));
        resultSets.add(stmt2.executeQuery(sql2));

        ResultSets rs = new ResultSets(resultSets);

        while(rs.next()){
           System.out.println(rs.getInt(1));

        }
  } 

Answer 4

I would do it this way

class GatheringResultSet implements ResultSet {
    List<E> resultSets;
    ResultSet current;

    GatheringResultSet(List resultSets) {
        this.resultSets = new ArrayList(resultSets);
        current = resultSets.remove(0);
    }

    @Override
    public boolean next() throws SQLException {
        if (current.next()) {
            return true;
        }
        if (resultSets.isEmpty()) {
            return false;
        }
        current = resultSets.remove(0);
        return true;
    }

...

the rest of the methods just delegate call to current ResultSet