Combining resultsets?

M

mistral

Hello,

I've been looking in doc's of jdbc ..but is there a way add the results
of 2 different queries to a resultset?

Something like this :

String SQL1 = "SELECT * FROM administrators;"
String SQL2 = "SELECT * FROM users;"

ResultSet result = statement.executeQuery(SQL1);
result += statement.executeQuery(SQL2);


Tom
 
G

GaryM

String SQL1 = "SELECT * FROM administrators;"
String SQL2 = "SELECT * FROM users;"

ResultSet result = statement.executeQuery(SQL1);
result += statement.executeQuery(SQL2);

Changing the query would be your easiest option. You could use a 3rd
party class called Beanutils that will allow you to convert a ResultSet
to a what they call a RowSetDynaClass, which in turn can produce a
List. You could then combine Lists. However the Lists would comprise of
DynaBean objects, which are a little more cumbersome to deal with,
because you must cast the columns to their type.

HTH,

Gary
 
B

Bjorn Abelli

...
I've been looking in doc's of jdbc ..but is there
a way add the results
of 2 different queries to a resultset?

Something like this :

String SQL1 = "SELECT * FROM administrators;"
String SQL2 = "SELECT * FROM users;"

ResultSet result = statement.executeQuery(SQL1);
result += statement.executeQuery(SQL2);

Assuming that the database is conforming to at least SQL-92, you could use a
single statement instead:

String SQL1 = "SELECT * FROM administrators UNION " +
"SELECT * FROM users";

ResultSet result = statement.executeQuery(SQL1);

Another option is to combine the results *after* you're through with the
handling of the actual resultsets.

You don't mention how these results are to be used or presented further down
the code, but there could possibly be another solution waiting for you down
there.

// Bjorn A
 
M

mistral

Bjorn said:
...


Assuming that the database is conforming to at least SQL-92, you could use
a single statement instead:

String SQL1 = "SELECT * FROM administrators UNION " +
"SELECT * FROM users";

ResultSet result = statement.executeQuery(SQL1);

Another option is to combine the results *after* you're through with the
handling of the actual resultsets.

You don't mention how these results are to be used or presented further
down the code, but there could possibly be another solution waiting for
you down there.

// Bjorn A

Ok the Union is an option but then the fieldnames + types have to match.
 
B

Bjorn Abelli

Bjorn Abelli wrote:
Ok the Union is an option but then the
fieldnames + types have to match.

Your original question implied just that, as it wouldn't be possible to
combine them into one resultset anyway otherwise... :)

However, if there's a mismatch between types and fieldnames between the two
tables you could probably construct each part of the query with projection
and aliases etc to make them union-compatible.


// Bjorn A
 
M

mistral

Bjorn said:
...


Your original question implied just that, as it wouldn't be possible to
combine them into one resultset anyway otherwise... :)

You are right it's the same problem with resultsets :) ..But it could be
possible that once te resultset of the second query is reached with the
next() method everything is dynamicaly changed :) But yeah that's just a
dream I think :)...
However, if there's a mismatch between types and fieldnames between the
two tables you could probably construct each part of the query with
projection and aliases etc to make them union-compatible.

The problem is I have dynamical tables... users can add tables , change them
etc.. But I just going create my own container and put everything in
there...
// Bjorn A

Thank you for the replies !
 
D

Dimitri Maziuk

mistral sez:
You are right it's the same problem with resultsets :) ..But it could be
possible that once te resultset of the second query is reached with the
next() method everything is dynamicaly changed :)

If all you want is to iterate through combined resultset,

for( i = 0; i < rslist.size(); i++ )
while( ((ResultSet) rslist.get( i )).next() )
...

will work just as well, I think (where rslist is a list of
resultsets, obviously).

Dima
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top