Search code examples
javamysqljdbc

Caching data from database using JDBC

When using JDBC to retrieve query results I use ResultSet like: ResultSet result_set = statement.executeQuery(query); In documentation it says: "A ResultSet object maintains a cursor pointing to its current row of data." meaning that if I do result_set.close() before I parse the query results I loose them. If I parse data in ResultSet right away I'am maintaining connection to the database during parsing process. I want to minimize time, I want to parse query result without being connected to the DB.

Is there a way to store query data in such way that I can close connection to the database before I parse the results?

for example something like this:

Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
ResultSet result_set = statement.executeQuery(query);
// store data retrieved from query
result_set.close();
statement.close();
connection.close();
// do something with stored data
Solution

  • RowSet

    The javax.sql.rowset package extends ResultSet, adding various features.

    In particular, CachedRowSet copies the rows’ data into memory. To quote the documentation:

    A CachedRowSet object is a container for rows of data that caches its rows in memory, which makes it possible to operate without always being connected to its data source.

    …

    A CachedRowSet object is a disconnected rowset, which means that it makes use of a connection to its data source only briefly.

    The trick is finding a capable concrete implementation of the interface. Oracle provides an open-source reference implementation from the days of Sun. But, as I recall (and I may be wrong), that implementation had some limitations or flaws that went unaddressed. Be sure to test thoroughly.