ResultSet behavior with MySQL database, does it store all rows in memory?

When returning results from a select query, does the ResultSet store all of the rows in memory? Or does it only fetch a limited amount of rows? Does it differ database to database? What's the behavior for MYSQL?开发者_JAVA百科

By default, the MySQL JDBC driver attempts to fetch everything in Java's memory. So if you're dealing with extremely a lot of rows and/or little memory, then you need to tell it to not do so. This is in detail described in its JDBC driver documentation. Here's an extract of relevance:

ResultSet

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and can not allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, you need to create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
             java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this any result sets created with the statement will be retrieved row-by-row.

There are some caveats with this approach. You will have to read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown.

For other databases (read: other JDBC drivers) you have to consult its documentation.

ResultSet does not store all the rows, it keeps a cursor open on the database.

Exact amount of data fetched at once depends on the driver (consequently it does differ from database to database).

Resultset might store all the data if the size is not very big. It depends on the fetch size. ResultSet setFetchSize is just a hint, but its up to the underlying JDBC driver to respect the hint. For all I know oracle jdbc driver does respect the hint.

In general, this is not specified, and thus may depend on the database (and the JDBC driver).

A JDBC implementation which holds always all lines in memory would have problems with really big results, while implementations which fetch each line individually would have poor performance (at least, if the database is somewhere remote).

So most implementations fetch the data in blocks of some implementation-dependent size. I have no idea what any MySQL JDBC drivers would do.