Does ResultSet execute all data in memory or only on request?

Question

I have a .jsp page where I have a GUI table that displays entries from an Oracle database. This table allows typical pagination behavior, such as FIRST, NEXT, PREVIOUS, and LAST. Records are obtained from the Java ResultSet object, which is returned from the execution of the SQL statement.

This ResultSet can be very large, so my question is:

If I have a ResultSet containing a million records, but only the data from the first ten records in the ResultSet is displayed in my table, is the data taken only at the beginning of the query for the record data or are all the data completely loaded into memory as soon as the ResultSet is returned from the SQL statement?

Answer 1

Java ResultSet is a pointer (or cursor) to the results in the database. ResultSet loads records into blocks from the database. Therefore, to answer your question, data is retrieved only upon request, but in blocks.

If you need to control how many lines are retrieved immediately by the driver, you can use the setFetchSize () method in the ResultSet. This will allow you to control how large blocks it receives immediately.

Answer 2

The JDBC specification does not determine whether data is being transferred or loaded into memory. Oracle threads by default. MySQL does not. To force MySQL to stream a result set, you need to set the following in Statement:

pstmt = conn.prepareStatement( sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); pstmt.setFetchSize(Integer.MIN_VALUE); 

Answer 3

As long as the JDBC specification does not indicate whether all data in the result set will be retrieved, any well-written driver will not.

However, the scrollable result set may be larger than what you have in mind: (link edited, it pointed to the spy page)

You can also consider the set of disconnected lines that is stored in the session (depending on how much your site scales): http://java.sun.com/j2se/1.4.2/docs/api/javax/sql/RowSet. html

Answer 4

The best idea is to make a request and display 100 or 1000 rows at a time / on one page. And connection management by pooling.

To make an additional query, you can use Row count in oracle and Limit in MY SQL.

Answer 5

lets say that we have a table containing 500 entries in it

 PreparedStatement stm=con.prepareStatement("select * from table"); stm.setFetchSize(100);// now each 100 records are loaded together from the database into the memory, // and since we have 500 5 server round trips will occur. ResultSet rs = stm.executeQuery(); rs.setFetchSize (50);//overrides the fetch size provided in the statements, //and the next trip to the database will fetch the records based on the new fetch size