Custom iteration over a JDBC ResultSet

Question

I have a resultset that returns a list of (repeated) names with some sql conditions associated with that name. It is a 2 column resultset. It looks like this:

____________________________________
|id                |     data      |
|__________________________________|
|john              | sql1          |
|john              | sql2          |
|adam              | sql1          |
|jack              | sql3          |
|jack              | sql2          |
____________________________________

What I'd like is to build a SQL query that looks like this:

select *, 'john' as id from table_x where sql1 or sql2
union
select *, 'adam' as id table_x where sql1
union
select *, 'jack' as id from table_x where sql3 or sql2;

Is this possible simply by using the res.next() in a while loop and doing some magic there? I thought of using a 2-d array or a Map to first store the resultset and then iterate over that to get this done, but wanted to know if there are any better/easier ways of doing this.

Answer 1

It appears that impala has a group_concat function, so you could leverage that to illiminate a step.

(table_y being the table that your initial resultset came from

select id, group_concat(data) as predicates from table_y group by id;

Then simply loop through them:

  String sql;  
  while(rs.next()) {
      String rPredicates = rs.getString("predicates").split(",");
      sql = "SELECT *, " + rs.getString("id") + " FROM table_x WHERE "
      sql += rPredicates[0];
      for(int ndx=1; ndx< rPredicates.length; ndx++) {
        sql += " or " + rPredicates[ndx];
      }
      sql += " union ";
    }

This is untested, but you get the point. You could eliminate a few lines by using StringUtils.join or .collapse something simlar. There are many string utils classes out there.

If you tried hard enough, you might be able to produce this purely with SQL, but this is a bit better at least.

EDIT: I'll one up this, you can supply a seperator to group_concat, so you could possibly execute this SQL to start

select id, group_concat(date, ' or ') as predicates from table_y;

Then you won't have to assemble the predicates, simply append the whole thing.