Question 100543 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Storing 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
Tags:

2

Answers


  1. 0

    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 problem 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.

    Login or Signup to reply.
  2. 0

    To fill the blanks in your snippet with a minimalistic approach:

        final var rowLength =
            result_set
                .getMetaData()
                .getColumnCount();

    final var resultCache = Stream.<Object[]>builder();

    while ( result_set.next() )
    {
        final var row = new Object[ rowLength ];
        for ( int index=1; index<=rowLength; index++ )
        {
            row[index] = result_set.getObject( index );
        }
        
        resultCache.accept( row );
    }

    record MyData( Integer value, String text )
    {
        // ... transformation could be placed in here with alternative constructor
    }
        
    final var results =
            resultCache.build()
                    .map( row -> new MyData( (Integer) row[0],
                                             (String)  row[1] ) )
                    .toList();

    This assumes you know the ordering in the returned data and the types to do the ‘parsing’ / etc later on.
    Thinking about the computationtime needed for the ‘parsing’ and the memoryfootprint and so on is a good way to become familiar with the behavior of the framework. I recommend trying out diffent sizes of resultsets (in number and rows) and the complexity of the parsing/ transformation. Maybe a look at a jpa-implementation helps to understand the possible solutions.

    In this post more details regarding result-extraction can be found: How to fetch entire row as array of objects with JDBC

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search