rs.last() gives Invalid operation for forward only

问题:

I am trying to get the row count of a result set by:

rs.last();
int row_count = rs.getRow();

but im getting an Invalid operation for forward only resultset : last error. The result set is getting its data from an Oracle 10g database.

Here is how i set up my connection:

    Class.forName("oracle.jdbc.driver.OracleDriver");
    String connectionString = "jdbc:oracle:thin:@" + oracle_ip_address + ":" + oracle_db_port + ":" + oracle_db_sid;
    Connection conn = DriverManager.getConnection(connectionString, oracle_db_username, oracle_db_password);

回答1:

ResultSet.last() and other "absolutely-indexed" query operations are only available when the result set is scrollable; otherwise, you can only iterate one-by-one through the forward-only result set.

The following example (from the javadocs) demonstrates how to create a scrollable ResultSet.

Statement stmt = con.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY
);
ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");

Keep in mind that there are performance implications to using scrollable queries. If the goal of this particular ResultSet is only to grab its last value, please consider refining your query to return only that result.

回答2:

PreparedStatement ps = conn.prepareStatement ("SELECT * FROM
         EMPLOYEE_TABLE WHERE LASTNAME = ?" ,
         ResultSet.TYPE_SCROLL_INSENSITIVE , 
         ResultSet.CONCUR_UPDATABLE ,
         ResultSet.HOLD_CURSOR_OVER_COMMIT) ;

For prepared statements, you must specify, at a minimum, both the type and the concurrency mode for last() and isLast() to work.

回答3:

Thanks to cheeken (2 post above), but in Java 1.8, the createStatement() function need now 2 parameters

example:

stmt 
  = conx.createStatement
      (ResultSet.TYPE_SCROLL_INSENSITIVE
      ,ResultSet.CONCUR_READ_ONLY
      );