1

I'm using a JdbcRowSet to retrieve a data set that could be very large (1M+ rows returned).

Because the result set could be so large I do not wish to hold it in memory. I wish to use the JdbcRowSet as a connected RowSet and fetch rows from the database as needed, rather than using the JdbcRowSet as a disconnected RowSet which would hold all rows in memory.

Unfortunately when using the below code to initialize my JdbcRowSet, the entire RowSet is pulled into the heap, which causes OutOfMemoryErrors and takes the application down.

How can I prevent the JDBC driver or JdbcRowSet from forcibly loading the entire result set into memory, but rather only fetching (per the example below) 100 rows into memory at a time?

I am using Spring to wire the application generally, and Spring JDBC templates throughout my data layer. Database is Oracle 11g.

JdbcRowSet jdbcRowSet = new com.sun.rowset.JdbcRowSetImpl(jdbcTemplate.getDataSource().getConnection());
jdbcRowSet.setConcurrency(ResultSet.CONCUR_READ_ONLY);
jdbcRowSet.setReadOnly(true);
jdbcRowSet.setCommand(sql);
jdbcRowSet.setString(1, value);
jdbcRowSet.execute();
jdbcRowSet.setFetchDirection(ResultSet.FETCH_FORWARD);
jdbcRowSet.setFetchSize(100);

(please don't encourage me to change my query so that fewer rows are returned; I assure you that this is a business requirement, is non-optional, etc)

Jim Kiley
  • 3,632
  • 3
  • 26
  • 43
  • 1
    If you need to process a query of that size, you should use a plain `ResultSet`, not a `RowSet`. – Andreas Jul 08 '16 at 12:59
  • @Andreas I am beginning to think that you're right, but I think the question is still valid: How do you coerce this construct to only pull a limited number of rows from the query, rather than pulling everything in and blowing the heap up? – Jim Kiley Jul 08 '16 at 13:10
  • 1
    you can use jdbcTemplate.query() variant with RowCallbackHandler or ResultSetExtractor. It will not load all resultset into memory. – user1516873 Jul 08 '16 at 13:14
  • http://stackoverflow.com/questions/1341254/spring-jdbc-support-and-large-dataset?rq=1 is actually pretty helpful here. The solution I ended up with was as @Andreas suggested, not using `RowSet` at all, but rather a vanilla `ResultSet`. – Jim Kiley Jul 08 '16 at 15:14

0 Answers0