ResultSet滚动模式设置fetchSize无效

资料一：何意？ 经过测试发现，假如库中有1w条数据，设置fetchSize=100,返回的结果仍然是1w，起不到分页的作用呀？？？

使用ResultSet游标处理记录替代数据库层分页处理的方式,将游标的打开方式设置为FORWARD_READONLY模式, 使用ResultSet游标处理实际执行时间比数据库层分页处理方式快2倍。
代码如下(例)：
 

String sql=“select * from xxx”;
PreparedStatement pstmt=conn.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(100);
ResultSetrs = pstmt.executeQuery();

资料二： ResultSet不带参数，会一次性加载到内存当中，而带参数会一行行读取？？？

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.

资料三：prepareStatement带参数查询比不带参数查询慢，经过验证确实会慢。

            PreparedStatement ptmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); //预编译SQL，减少sql执行
//            PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

 资料四：java访问数据库实际上是在数据库中开启一个游标；getString是一次性加载到内存，getObject不是一次性读取到内存？？？

java代码在执行createStatement和prepareStatement的时候，实际上都是相当与数据库中打开了一个cursor，尤其是如果createStatement和prepareStatement在循环内部的话，一直使用不关闭非常容易出错。

使用getString函数的运行方式：程序会一次性把数据都放到内存里，然后通过调用ResultSet的next、getString等方法取数据。

getObject的运行模式：数据不一次性全部读入内存，而是存在与数据库中，每次调用getObject都是直接从数据库中取数据，因此可以正常运行

资料五：一个参数的prepareStatement会内存溢出，三个参数的prepareStatement不会内存溢出 ---我相信这是经过实际证明的！（虽然不知道原理）

大数据量查询,使用 ResultSet 出现 JVM 内存溢出

statement = connection.prepareStatement(sql);

修改为：

statement = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);