Java ResultSet 是将所有数据加载到内存中还是仅在请求时加载？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/858836/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does a ResultSet load all data into memory or only when requested?
提问by tth
I have a .jsp page where I have a GUI table that displays records from an Oracle database. This table allows typical pagination behaviour, such as "FIRST", "NEXT", "PREVIOUS" and "LAST". The records are obtained from a Java ResultSet object that is returned from executing a SQL statement.
我有一个 .jsp 页面，其中有一个 GUI 表，用于显示 Oracle 数据库中的记录。此表允许典型的分页行为，例如“FIRST”、“NEXT”、“PREVIOUS”和“LAST”。这些记录是从执行 SQL 语句返回的 Java ResultSet 对象中获取的。
This ResultSet might be very big, so my question is:
这个 ResultSet 可能很大，所以我的问题是：
If I have a ResultSet containing one million records but my table only displays the data from the first ten records in the ResultSet, is the data only fetched when I start requesting record data or does all of the data get loaded into memory entirely once the ResultSet is returned from executing a SQL statement?
如果我有一个包含 100 万条记录的 ResultSet，但我的表只显示 ResultSet 中前十个记录的数据，是仅在我开始请求记录数据时才获取数据还是所有数据都在 ResultSet 后完全加载到内存中是从执行 SQL 语句返回？
采纳答案by Chris Dail
The Java ResultSet is a pointer (or cursor) to the results in the database. The ResultSet loads records in blocks from the database. So to answer your question, the data is only fetched when you request it but in blocks.
Java ResultSet 是指向数据库中结果的指针（或游标）。ResultSet 从数据库中加载块中的记录。因此，为了回答您的问题，仅在您请求数据时才会获取数据，但会以块为单位。
If you need to control how many rows are fetched at once by the driver, you can use the setFetchSize(int rows)method on the ResultSet. This will allow you to control how big the blocks it retrieves at once.
如果需要控制驱动程序一次获取多少行，可以使用ResultSet 上的setFetchSize(int rows)方法。这将允许您控制它一次检索的块有多大。
回答by Edward Q. Bridges
While the JDBC spec does not specify whether or not the all data in the result set would get fetched, any well-written driver won't do that.
虽然 JDBC 规范没有指定是否会获取结果集中的所有数据，但任何编写良好的驱动程序都不会这样做。
That said, a scrollable result set might be more what you have in mind: (link redacted, it pointed to a spyware page)
也就是说，可滚动的结果集可能更符合您的想法：（链接已编辑，它指向一个间谍软件页面）
You may also consider a disconnected row set, that's stored in the session (depending on how scalable your site needs to be): http://java.sun.com/j2se/1.4.2/docs/api/javax/sql/RowSet.html
您还可以考虑存储在会话中的断开连接的行集（取决于您的站点需要的可扩展性）：http: //java.sun.com/j2se/1.4.2/docs/api/javax/sql/行集.html
回答by Joshua
The JDBC spec does not specify whether the data is streamed or if it is loaded into memory. Oracle streams by default. MySQL does not. To get MySQL to stream the resultset, you need to set the following on the Statement:
JDBC 规范没有指定数据是流式传输还是加载到内存中。默认情况下 Oracle 流。MySQL 没有。要让 MySQL 流式传输结果集，您需要在 Statement 上设置以下内容：
pstmt = conn.prepareStatement(
sql,
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(Integer.MIN_VALUE);
回答by Tony
The best idea is make a sub query and display 100 or 1000 rows at a time/in single page. And managing the connection by connection pooling.
最好的想法是进行子查询并一次/在单个页面中显示 100 或 1000 行。并通过连接池管理连接。
To make a sub query you can use Row count in oracle and Limit in MY SQL.
要进行子查询，您可以使用 oracle 中的 Row count 和 MY SQL 中的 Limit。
回答by QuakeCore
lets say we have a table that contains 500 records in it
假设我们有一个包含 500 条记录的表
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
