SQLException: Before start of result set

背景

用惯了mybatis，今天用原生的jdbc获取链接，查询数据时，取数据报错。问题代码片段

Connection conn = getConnection();
PreparedStatement preparedStatement = conn.prepareStatement("SELECT max(version) as cur_version FROM test_version ");
ResultSet resultSet = preparedStatement.executeQuery();
return resultSet.getInt("cur_version");

报错信息如下：

java.sql.SQLException: Before start of result set
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.result.ResultSetImpl.checkRowPos(ResultSetImpl.java:484) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1283) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.result.ResultSetImpl.getInt(ResultSetImpl.java:786) ~[mysql-connector-java-8.0.17.jar:8.0.17]
	at com.mysql.cj.jdbc.result.ResultSetImpl.getInt(ResultSetImpl.java:807) ~[mysql-connector-java-8.0.17.jar:8.0.17]

问题

通过报错定位，可知报错位置为 resultSet.getInt(“cur_version”) 获取数据异常。

解决

查询资料得知，获取数据前需要调用 resultSet.next() 方法，然后才能取数据。代码调整为如下即可：

Connection conn = getConnection();
PreparedStatement preparedStatement = conn.prepareStatement("SELECT max(version) as cur_version FROM test_version ");
ResultSet resultSet = preparedStatement.executeQuery();
return resultSet.next() ? resultSet.getInt("cur_version") : -1;

总结

查看 java.sql.ResultSet#next 方法可知，ResultSet 指针的初始位置位于第一行之前；第一次调用next()方法将会把第一行设置为当前行；
调用 next() 方法后才能开始获取数据。这种做法的好处在于可以方便地配合while进行遍历，避免取不到第一行数据。

源码解释如下：

    /**
     * Moves the cursor forward one row from its current position.
     * A <code>ResultSet</code> cursor is initially positioned
     * before the first row; the first call to the method
     * <code>next</code> makes the first row the current row; the
     * second call makes the second row the current row, and so on.
     * <p>
     * When a call to the <code>next</code> method returns <code>false</code>,
     * the cursor is positioned after the last row. Any
     * invocation of a <code>ResultSet</code> method which requires a
     * current row will result in a <code>SQLException</code> being thrown.
     *  If the result set type is <code>TYPE_FORWARD_ONLY</code>, it is vendor specified
     * whether their JDBC driver implementation will return <code>false</code> or
     *  throw an <code>SQLException</code> on a
     * subsequent call to <code>next</code>.
     *
     * <P>If an input stream is open for the current row, a call
     * to the method <code>next</code> will
     * implicitly close it. A <code>ResultSet</code> object's
     * warning chain is cleared when a new row is read.
     *
     * @return <code>true</code> if the new current row is valid;
     * <code>false</code> if there are no more rows
     * @exception SQLException if a database access error occurs or this method is
     *            called on a closed result set
     */
    boolean next() throws SQLException;