Java: JDBC ResultSet not populated with all query results

Question

I'm working on getting our system's java beans to be able to use the SQL IN clause when running queries down through the database, but am running into a perplexing problem.

I am build the SQL query for the PreparedStatement in the following generic pattern:

select [column names] 
from [table name]
where [a column name] IN (?, ? , ?, ..., ?)

The ..., represents any number of ?'s depending on the number of values the user is deciding to build into the IN clause. I run a loop to get these into the query string.

From here, I use the PreparedStatement's setString( idx, String ) method, and iterate through the list of values and run from index 1 - # of values.

The PreparedStatement runs the query via the executeQuery() method and the returned ResultSet seems to be incorrect.

In a specific instance using 4 values, when I take the query in the PreparedStatement to SQL and replace each ? with the exact values in ' ', I get 3 results (as one of the values is purposely not in the DB).

The ResultSet, on the other hand only has 1 row in its set, and that row always corresponds to the first ? parameter in the IN clause.

I even tried faking the IN clause with ([column name] = ? OR [column name] = ? ... OR column name] = ?) but the same issue occurs here too.

Any ideas what is going on here? Connecting to an Oracle database, by the way.

Logs:

2010-02-10 11:16:28,505 DEBUG  basic.BasicCursor - Preparing statement SELECT MERCHANT_ID, M_NAME, M_AUTHEN, M_ADMIN_AUTHEN, M_CONTACT_ADDR, M_PAYMENT_ADDR, M_HAS_MPROXY, M_DISABLED, M_FREETEXT, TXN_ID, M_TAX_NAME, M_TAX_RATE, MERCHANT_PARENT_ID, MERCHANT_ROOT_ID, RESERVED_1, RESERVED_2, RESERVED_3, RESERVED_4, EMAIL, LOGICAL_TYPE, CHANNEL_MASK FROM MERCHANT0 WHERE MERCHANT_ID IN (?, ?, ?, ?)  ORDER BY MERCHANT_ID
2010-02-10 11:16:28,505 DEBUG  basic.BasicCursor - Adding string  to slot 1: 6172222222
2010-02-10 11:16:28,505 DEBUG  basic.BasicCursor - Adding string  to slot 2:  6177740603
2010-02-10 11:16:28,505 DEBUG  basic.BasicCursor - Adding string  to slot 3:  6177740602
2010-02-10 11:16:28,505 DEBUG  basic.BasicCursor - Adding string  to slot 4:  6172441111
2010-02-10 11:16:28,512 DEBUG  basic.BasicCursor - scanCursor() calling... checking for next row. Current row is : 0
2010-02-10 11:16:28,512 DEBUG  basic.BasicCursor - scanCursor() called, hit
2010-02-10 11:16:28,512 DEBUG  basic.BasicCursor - scanCursor() got object 6172222222
2010-02-10 11:16:28,512 DEBUG  basic.BasicCursor - scanCursor() calling... checking for next row. Current row is : 1
2010-02-10 11:16:28,512 DEBUG  basic.BasicCursor - scanCursor() called, not hit
2010-02-10 11:16:28,505 DEBUG  basic.BasicCursor - The size of variables list = 4

EDIT: Found the issues with the PreparedStatement. I'll leave it as an exercise to those curious to figure it out. It's visible in the log statements above. Unfortunately, now my problem has cascaded to some annoying proprietary code we have that limits the rows from the now expected ResultSet to displaying only 1 record anyway. sigh

Answer 1

• double-check the complete constructed query and compare that it is actually what you expect
• double-check that you actually call setString() with different values for the index and the String and that you're not using the same value over and over again
• double-check that you're not calling next() on your ResultSet more than once per loop iteration.
• edit: System.out.println() and check (and possibly post) the following:
  • The complete SQL query string
  • toString() of the newly created PreparedStatement
  • both parameters of each setString() call and toString() of the PreparedStatement each time you call setString()
  • the return value of next() each time you call it

Answer 2

So, you used this construct?

private static final String SQL = "SELECT * FROM MERCHANT0 WHERE MERCHANT_ID IN (%s)";

public List<Merchant> list(List<Long> ids) {
    StringBuilder placeHolders = new StringBuilder();
    for (int i = 0; i < ids.size(); i++) {
        placeHolders.append("?");
        if (i + 1 < ids.size()) {
            placeHolders.append(",");
        }
    }
    String sql = String.format(SQL, placeHolders.toString());

    // ...

    try {
        // ...

        preparedStatement = connection.prepareStatement(SQL);
        for (int i = 0; i < ids.size(); i++) {
            preparedStatement.setLong(i + 1, ids.get(i));
        }
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            Long id = resultSet.getLong("MERCHANT_ID");
            System.out.println(id); // Should print all of the `ids`.
        }

        // ...

Apart from the fact that Oracle has a limitation of about 1000 values inside the IN clause, this is supposed to work.