ResultSet loses first value

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.

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);
}

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();
});

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.