CODEWITHSUNDEEP
jdbc
omerkudat
omerkudat

Reputation: 10101

JDBC batch select returns multiple ResultSets

A minor issue but I was wondering whether someone knows why I get the following behavior. I have a large select statement with a UNION directive. I need to call this statement for a list of inputs. Thus:

Connection connection = createConnection();
PreparedStatement statement = connection.prepareStatement(dbLoadOneQuery, TYPE_FORWARD_ONLY, CONCUR_READ_ONLY);

for (Integer param : params) {
    statement.setInt(1, param);
    statement.setInt(2, param);
    statement.addBatch();
}
statement.execute();

do {
    ResultSet resultSet = statement.getResultSet();
    // snip
} while (statement.getMoreResults());

Now, I'm aware I could have done this a number of ways. Whether there is a better way is not the question. Rather, I would like to know why my results are coming in bunches? I need to read 3-4 separate ResultSets from my statement. They don't correspond to the unions, they are not of equal size, and the data is not group in any particular form.

My guess it's a driver specific thing, but I couldn't find the JDBC specification that allowed for this behavior. The driver is com.sybase.jdbc4.jdbc.SybDriver from library jconn4.

jConnect (TM) for JDBC(TM)/7.07 GA(Build 26666)/P/EBF19485/JDK 1.6.0/jdbcmain/Wed Aug 31 03:14:04 PDT 2011

Upvotes: 0

Views: 2585

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109157

This is not executing a UNION query, it is batching n separate queries (that is dbLoadOneQuery with n different combinations of parameters), and executing them in one go, then retrieving the n individual ResultSet (one for each query).

If you want UNIONs, then actually execute a UNION query.

Based on your query that would be something like:

StringBuilder query = new StringBuilder();
query.append(dbLoadOneQuery);
int queryCount = params.size();
// If we have more than one parameter add a UNION clause:
while (queryCount-- > 1) {
   query.append(" UNION ").append(dbLoadOneQuery);
}
PreparedStatement statement = connection.prepareStatement(query.toString());
int paramIdx = 1;
for (Integer param : params) {
   statement.setInt(paramIdx++, param);
   statement.setInt(paramIdx++, param);
}
ResultSet rs = statement.executeQuery();

Upvotes: 1

Related Questions