ResultSet is not null yet resultset.next() returns false

Question

I'm trying to search tables based on a certain criteria. The SQL query returns the correct result(1 row) when executed in SQL Developer. In JDBC, resultset is not null yet resultset.next() returns false.

Code:

public static ArrayList<SearchRecord> searchRecords(Connection conn, int orderID) throws SQLException {

    System.out.println("Order_id: " + orderID); //The correct orderID is passed
    ArrayList<SearchRecord> searchResult = null;
    SearchRecord searchRecord = null;
    PreparedStatement search_pstm = null;
    ResultSet search_rs = null;

    if (conn != null) {
        // This query is successfully executed in SQL Developer and outputs a row
        String search_sql = "select d.PPS_NUM, a.BRC_ORD_ID, PROJECT_NUM, LICENSE_NUM, EXPIRY_DATE, FIRST_NAME, LAST_NAME, ADDRESS1, ADDRESS2, CITY, PROVINCE, POSTAL_CODE from VD_SHIP_AOR a, VD_BRC_ORD b, VD_CONSOL_VALID c, VD_SHIP d where a.brc_ord_id = b.brc_ord_id and b.CONSOL_VALID_ID= c.consol_valid_id and a.SHIP_ID = d.SHIP_ID and d.ORD_ID = b.ORD_ID and d.PPS_NUM = ?";
        search_pstm = conn.prepareStatement(search_sql);
        search_pstm.setInt(1, orderID);
        search_rs = search_pstm.executeQuery();
        searchResult = new ArrayList<SearchRecord>();

        if (search_rs != null) {
            System.out.println("Not null"); //Prints this
            System.out.println(search_rs.isClosed()); //Prints false
            System.out.println(search_rs.getRow()); //Prints 0

            while (search_rs.next()) { //Does not enter the while loop, why?
                System.out.println("Inside while rs.next");
                //store resultset's data into arraylist
            }
        }
    }
    return searchResult;
}

What I considered referring:

Similar Java Forums question

But the existing solutions are not working for me, please help me with this.

Is it possible that you are already on the only result in the set, and there is no next record? — Chris Meurer, Feb 08 '18 at 22:30
No it's not possible, I'm opening the resultset in the while for the first time! — babybear, Feb 08 '18 at 22:59
Try after calling getRow() to execute search_rs.beforeFirst() — access_granted, Feb 09 '18 at 04:05

Mark Rotteveel · Accepted Answer · 2018-02-09T15:59:08.500

You seem to assume that this method returns null if there are no rows, that is not the case. The method executeQuery() will never return null^*. The API documentation specifically says:

Returns:
a ResultSet object that contains the data produced by the query; never null

So, your null-check is entirely unnecessary. It will either produce a result set (even if the query produced no rows), or it will throw an SQLException for example for syntax errors, or if the executed statement does not produce a result set (eg an update or delete statement).

A result set after execution is initially open, and positioned before the first row (if any). A call to ResultSet.next() returns false if there are no more rows. In this case it is plausible that your select statement did not produce any rows, so the result set is empty and the first call to next() will return false.

I think your assumptions about the API are incorrect. I suggest you read the JDBC API documentation carefully, and maybe read some tutorials or introductory texts on JDBC.

If SQL Developer shows a row and your code doesn't, carefully check if 1) you are really using the same database, 2) the id used is really the same, and 3) if the data you are viewing in SQL Developer is actually committed. And consider simplifying your query first, eg select only from one table, remove the specific condition on this order number so you select all rows, use the same literal value you use in SQL Developer (hardcoded in the query), etc. Also, are you are sure you're using the right condition, PPS_NUM doesn't look like an Order_id, while for example ORD_ID looks more like one.

^{* : ignoring the possibility of bugs in a specific driver implementation}

I was assuming the method returned null if no rows, and hence was caught up in the confusion. Thanks Mark for all the clarification. Also, I found out that the cause of my problem was not committing the data in SQL Developer. Hence, it was producing results in Developer but not in JDBC. It works fine now! — babybear, Feb 09 '18 at 15:55
@Mark In my case, the query properly retrieved the value from SQL Server Database and it has records. But through JDBC result set it gives false for ResultSet.next() — Ashok kumar Ganesan, Jul 24 '20 at 08:55
@AshokkumarGanesan If JDBC returns false for the first call to `next()` then it didn't retrieve any rows, and all the troubleshooting steps I mentioned still apply. — Mark Rotteveel, Jul 24 '20 at 08:58
Alright I will troubleshoot it, Where as if I use a simple query that works properly. Do I need to construct the query? like JOOQ? — Ashok kumar Ganesan, Jul 24 '20 at 09:03
Verified the cases and all are proper. In my case, I have a simple query with Inner join and group by with Limit 1. — Ashok kumar Ganesan, Jul 24 '20 at 09:06

ResultSet is not null yet resultset.next() returns false

1 Answers1