Why doesn't this JDBC select return any rows?-CodePudding

I have the following SQL statement which I am trying to execute via JDBC PreparedStatement:

SELECT TOP(1) * FROM offsets sto WHERE sto.done = 0 AND sto.instance_index = 1

In my database this happily returns 1 row as it should.

But once I execute it in a JdbcTemplate:

private final String selectSql = "SELECT TOP(1) * "  
            "FROM offsets sto "  
            "WHERE sto.done = 0 "  
            "AND sto.instance_index = ? ";

template.query(selectSql, new Object[]{1}, new int[]{Types.INTEGER} ,rs -> {

            Offset offset = new Offset();
            offset.setId(rs.getLong("id"));
            offset.setInstance_index(rs.getInt("instance_index"));
            offset.setDone(rs.getBoolean("done"));

            return Optional.of(offset);

        });

I get :

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The result set has no current row.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:237) ~[mssql-jdbc-9.4.0.jre11.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.verifyResultSetHasCurrentRow(SQLServerResultSet.java:563) ~[mssql-jdbc-9.4.0.jre11.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getterGetColumn(SQLServerResultSet.java:2046) ~[mssql-jdbc-9.4.0.jre11.jar:na]

What is going on ?

What am I doing wrong ?

CodePudding user response：

The resultset starts before the first returned row. Call

 rs.next();
 Offset offset = new Offset();
 offset.setId(rs.getLong("id")); 
 . . .

boolean next()

throws SQLException

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. When a call to the next method returns false, the cursor is positioned after the last row.

Resultset