ResultSet loses first value

Question

Code seems to work fine, but I noticed whenever I queried a string with only one result, it returned nothing. Somehow I am skipping the first result I think but have no idea why.

    else{
   Conn con = null;
   try {
    con = new Conn();
   } catch (Exception e) {

    e.printStackTrace();
   }
   String sql = "SELECT productname, quantityperunit, unitprice FROM products pr, categories ca WHERE pr.categoryID = ca.categoryID AND ProductName LIKE '%" + searchTerm + "%'";
   System.out.println("last try");
   try {
    searchResults = con.query(sql);

    if (searchResults.next()){
     session.setAttribute("searchResults", searchResults);
    }


   } catch (Exception e) {

    e.printStackTrace();
   } 


  }

and this is the display code:

 java.sql.ResultSet resultSet = (java.sql.ResultSet) session.getAttribute("searchResults");
    if(resultSet == null){
     out.println("Nullified");
    }
 if(resultSet!=null){
  out.println("<table border='1'>");
     out.println("<tr><th>Product Name</th><th>Quantity per Item</th><th>Price</th><th>Quantity</th><th><Add to Cart</th></tr>");
     while(resultSet.next()){      
      out.println("<tr><td>"+resultSet.getString("ProductName")+"</td></tr>");
     }
     out.println("</table>");
 }

any help would be appreciated.

sorry about the formatting, I can't seem to get it to work right... — javArc, Jun 05 '10 at 00:15
To format code, indent it with 4 spaces. You can do it by selecting the piece and pressing `010101` button in editor toolbar or the `Ctrl+K` key. I've done it for you. — BalusC, Jun 05 '10 at 00:18
Khorkrak already nailed it down. I just want to add that this is **really not** the way to code it. JDBC code is leaking resources. Model, view and controller logic is mingled. Exception handling is bogus. Session scope is been abused. I've posted several answers with examples before how to do it the right way, have a look: [this](http://stackoverflow.com/questions/2219238#2219238), [this](http://stackoverflow.com/questions/1832524#1832524) and [this](http://stackoverflow.com/questions/2428468#2428468). — BalusC, Jun 05 '10 at 00:26
Thanks for the help BalusC, excuse the noobiness but his is the first I've heard of JSTL. — javArc, Jun 05 '10 at 00:32

Behrang · Answer 1 · 2019-05-07T04:49:26.293

According to the API docs for ResultSet next:

Moves the cursor forward one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

But in your code, before entering the while loop, you move the cursor to the first row:

searchResults = con.query(sql);
if (searchResults.next()){
    session.setAttribute("searchResults", searchResults);
}

One way to fix this problem is to change this:

while(resultSet.next()) {
   out.println(""+resultSet.getString("ProductName")+""); } out.println(""); 
}

to this:

do {
   out.println(""+resultSet.getString("ProductName")+""); } out.println("");
} while(resultSet.next());

Alternatively, if your JDBC driver supports it, you can make a call to beforeFirst() after you put the result set in the session:

searchResults = con.query(sql);
if (searchResults.next()){
    session.setAttribute("searchResults", searchResults);
    searchResults.beforeFirst();
}

Anyway, the first row is lost due to the call to searchResults.next() when searchResults is being put into the session:

searchResults = con.query(sql);
if (searchResults.next()){
    session.setAttribute("searchResults", searchResults);
}

just tested this and it works great, will fall back to it if my JSTL attempts fail. Thx man! — javArc, Jun 05 '10 at 00:35

score 2 · Answer 2 · answered May 06 '19 at 13:12

For those using Spring JDBC and seeing this behavior, note that the code for the ResultSetExtractor is as follows:

/**
 * Adapter to enable use of a RowCallbackHandler inside a ResultSetExtractor.
 * <p>Uses a regular ResultSet, so we have to be careful when using it:
 * We don't use it for navigating since this could lead to unpredictable consequences.
 */
private static class RowCallbackHandlerResultSetExtractor implements ResultSetExtractor<Object> {

    private final RowCallbackHandler rch;

    public RowCallbackHandlerResultSetExtractor(RowCallbackHandler rch) {
        this.rch = rch;
    }

    @Override
    public Object extractData(ResultSet rs) throws SQLException {
        while (rs.next()) {
            this.rch.processRow(rs);
        }
        return null;
    }
}

So when you're using the interface and providing the lambda to map the row, don't make the same mistake I did and include the while(resultSet.next()) loop again like so or you will also see that you are skipping the first row:

// THIS IS BAD AND WILL MAKE YOU SKIP THE FIRST RESULT
new JdbcTemplate(getDataSource()).query(sql, resultSet -> {
    while(resultSet.next()) {
        myMappingFunction();
    }
});

The proper way is just to write the code that maps the rows:

new JdbcTemplate(getDataSource()).query(sql, resultSet -> {
    myMappingFunction();
});

This was helpful to me. My Spring JDBC CallbackHandler inadvertently had the while loop in it (left over from a previous implementation code snippet that did not use callbacks.) The tricky think is that for results > 1, things may appear normal. The callback gets called, and then processes the whole results set ... meanwhile you may be unaware that the callback was just called once instead of repeatedly and that you are seeing 2 -> n results instead of 1 -> n. — RBH, May 19 '20 at 18:48

score 0 · Accepted Answer · answered Jun 05 '10 at 00:16

0

I'd imagine that resultSet.next() moves the cursor to the next result, thus it immediately would skip the first result on the first iteration of the while loop.

answered Jun 05 '10 at 00:16

Khorkrak

3,911
2
27
37

huh. is there another way to check if my resultset is empty? – javArc Jun 05 '10 at 00:19
Yes, map it to `List` and just test it by `List#isEmpty()` or `List#size()`. Also see [this answer](http://stackoverflow.com/questions/2591732/how-to-check-if-resultset-has-only-one-row-or-more/2592141#2592141). – BalusC Jun 05 '10 at 00:22
1

This answer is not correct. From the [docs](https://docs.oracle.com/en/java/javase/12/docs/api/java.sql/java/sql/ResultSet.html#next()): _"Moves the cursor froward one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on."_. – Behrang May 07 '19 at 01:04

ResultSet loses first value

3 Answers3