Posted Tuesday, June 12, 2012 @ 07:03:38  

We are migrating a swing application using quicktable from Oracle to Postgresql (9.1) and have encountered one great obstacle: We are not able to use cursor based result sets (fetch on demand). This means that the whole table is loaded into memory on dbTable.refresh().

In Postgresql, as far as I know, the only way to use cursor based result sets with jdbc is to set autocommit to false (so that the select can be in a transaction) and set fetchsize for the statement to > 0 (fetchsize 0 which is the default, means that all rows are loaded). E.g:

// make sure autocommit is off
conn.setAutoCommit(false);
Statement st = conn.createStatement();

// Turn use of the cursor on.
st.setFetchSize(50);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
if (rs.next()) {
System.out.print("a row was returned.");
}
rs.close();

Setting autocommit to false for dbtable is easy. My problem is that I can find no way to set fetchsize for the select statement that Quicktable executes, so that when we load a big table, e.g. 250 000 rows, all rows are loaded resulting in very high memory usage. Our users normally opens many such result sets in a session.

Does anyone know a solution to this?