rs.last() gives Invalid operation for forward only resultset : last

java oracle jdbc resultset
Mike picture Mike · Jan 25, 2012 · Viewed 49.4k times · Source

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

Answer

cheeken picture cheeken · Jan 25, 2012

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.

Related questions

JDBC returning empty result set

I'm using JDBC for very simple database connectivity. I have created my connection/statement and executed a query. I check the query object of the statement in the debugger to confirm that it is sending a proper query. I then …

Read More
Oracle doesn't remove cursors after closing result set

Note: we reuse single connection. ************************************************ public Connection connection() { try { if ((connection == null) || (connection.isClosed())) { if (connection!=null) log.severe("Connection was closed !"); connection = DriverManager.getConnection(jdbcURL, username, password); } } catch (SQLException e) { log.severe("can't connect: " + e.getMessage()); } return connection; } ************************************************** public …

Read More
JDBC ResultSet get columns with table alias

Imagine I have a query like SELECT * from table1 a, table2 b where (WHATEVER) Maybe both tables have the same column name. So I though it would be nice to access the data via resultSet.getString("a.columnName"); resultSet.getString("…

Read More