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

Question

When returning the results of a select query, does the ResultSet store all the rows in memory? Or does he only get a limited number of rows? Does a database distinguish between a database? What is the behavior for MYSQL?

Answer 1

By default, the MySQL JDBC driver attempts to retrieve everything in Java memory. Therefore, if you are dealing with a very large number of lines and / or small memory, then you need to say that this should not be done. This is described in detail in his JDBC driver documentation . Here's an excerpt of relevance:

ResultSet

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

To enable this functionality, you need to instantiate the Statement as follows:

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

The combination of a read-only, read-only result set with a sample size of Integer.MIN_VALUE serves as a signal to the sequential result of a set of rows to convert to a stream. After that, any result sets created using the instruction will be retrieved line by line.

There are some caveats with this approach. You will need to read all the lines in the result set (or close it) before you can issue any other requests in the connection, or an exception is thrown.

For other databases (read: other JDBC drivers), you should read your documentation.

Answer 2

ResultSet does not save all rows; it keeps the cursor open in the database.

The exact amount of data received right away depends on the driver (therefore, it differs from database to database).

Answer 3

A result set can store all data if the size is not very large. It depends on the size of the sample. ResultSet setFetchSize is just a hint, but its approach to the main JDBC driver is suitable for a hint. As far as I know, the oracle jdbc driver respects the hint.

Answer 4

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

A JDBC implementation that always contains all the rows in memory will have problems with really large results, while implementations that retrieve each row individually will have poor performance (at least if the database is located somewhere remotely).

Thus, most implementations retrieve data in blocks of some size, depending on the implementation. I have no idea what any MySQL JDBC drivers could do.