Postgres查询结果集的获取方法及其优缺点

PG jdbc的查询结果集获取方式

1 简单全部结果集查询

默认情况下，pgjdbc是一次获取全部查询结果的，并且结果集的类型和并发性分别为ResultSet.TYPE_FORWARD_ONLY和ResultSet.CONCUR_READ_ONLY。也就是pgjdbc默认会一次获取全部查询结果，只能从前往后取，是只读的。

     stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);     rs = stmt.executeQuery(sql);     while(rs.next())     {             ...     }

通过修改结果集的类型和并发性，还分别可以获得(不)可滑动的，对变化（不）敏感的，（不）可更新的结果集。

ResultSet类型API:http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#TYPE_SCROLL_SENSITIVE

ResultSet.TYPE_FORWARD_ONLY ResultSet.CONCUR_READ_ONLY

ResultSet.TYPE_SCROLL_INSENSITIVE ResultSet.CONCUR_UPDATABLE

ResultSet.TYPE_SCROLL_SENSITIVE

该方法一次获取全部结果：对于数据量小的查询来说，速度更快，结果集有多种灵活的获取方式；但对于数据量很大的查询，容易造成很大的内存消耗，甚至内存溢出。

2 基于游标的批量结果集查询

通过设置setFetchSize(int size)获取基于游标cursor的结果集，size>0表示开启游标，一次只获取size行结果集，该size取完后在获取下一size的结果集；size=0表示关闭游标，结果集一次取出全部行。

基于游标的结果集的使用是有条件的，如果不满足条件setFechSize(int size)将不起作用，pgjdbc还是会一次获取全部结果集。基于游标的结果集使用条件如下：

1.连到数据库服务的连接必须是基于V3协议的，V3协议是7.4及更新版本PG才能支持的，并且是他们的默认协议；

2.Connection必须是非自动提交模式.后端会在事务的结束的时候关闭游标，所以，在自动提交模式里，还没从游标里获取任何东西的时候，后端就已经把游标关闭了。

3.Statement必须以ResultSet.TYPE_FORWARD_ONLY的类型来创建，该结果集类型是默认的，所以可以直接使用stmt = conn.createStatement()来创建（或者stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY)）.因此基于游标的结果集是只能向前获取，不能向后或者跳跃获取的。

4. 查询sql语句必须是一个单一的语句，不能是由分好分隔的多个语句。

// make sure autocommit is off
conn.setAutoCommit(false);
Statement st = conn.createStatement();
// Turn use of the cursor on.
st.setFetchSize(50);ResultSet
rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next())
{
    System.out.print("a row was returned.");
}
rs.close();

// Turn the cursor off.
st.setFetchSize(0);
rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next())
{
    System.out.print("many rows were returned.");
}
rs.close();

该方法分批获取结果集，一次仅获取一个批量的数据，只有一个小批量的行集缓存在连接的客户端，当该批量用尽后才会通过重定位游标来获取下一批量的行集：对于数据量小的查询，由于分批获取，会导致查询速度降低，结果集取得的灵活性也不可用了；但是对于数据量大的查询，分批获取，首次获得结果的速度加快，降低了内存的消耗的阀值，可以避免内存溢出的问题。




桥哥的博客：http://blog.chinaunix.net/uid-20726500-id-4844406.html


JAVA API:http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#TYPE_SCROLL_SENSITIVE

PG JDBC Last Doc: https://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor

原文如下：

Chapter 5. Issuing a Query and Processing the Result

Table of Contents

Getting results based on a cursor

Using the Statement or PreparedStatement Interface

Using the ResultSet Interface

Performing Updates

Creating and Modifying Database Objects

Any time you want to issue SQL statements to the database, you require a Statement or PreparedStatement instance. Once you have a Statement or PreparedStatement, you can use issue a query. This will return a ResultSet instance, which contains the entire result (see the section called “Getting results based on a cursor” here for how to alter this behaviour). Example 5.1, “Processing a Simple Query in JDBC” illustrates this process.

Example 5.1. Processing a Simple Query in JDBC

This example will issue a simple query and print out the first column of each row using a Statement.

Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM mytable WHERE columnfoo = 500");
while (rs.next())
{
   System.out.print("Column 1 returned ");
   System.out.println(rs.getString(1));
} rs.close();
st.close();

This example issues the same query as before but uses a PreparedStatement and a bind value in the query.

int foovalue = 500;
PreparedStatement st = conn.prepareStatement("SELECT * FROM mytable WHERE columnfoo = ?");
st.setInt(1, foovalue);
ResultSet rs = st.executeQuery();
while (rs.next())
{
   System.out.print("Column 1 returned ");
   System.out.println(rs.getString(1));
}
rs.close();
st.close();

Getting results based on a cursor

By default the driver collects all the results for the query at once. This can be inconvenient for large data sets so the JDBC driver provides a means of basing a ResultSet on a database cursor and only fetching a small number of rows.

A small number of rows are cached on the client side of the connection and when exhausted the next block of rows is retrieved by repositioning the cursor.

Note

Cursor based ResultSets cannot be used in all situations. There a number of restrictions which will make the driver silently fall back to fetching the whole ResultSet at once.

The connection to the server must be using the V3 protocol. This is the default for (and is only supported by) server versions 7.4 and later.

The Connection must not be in autocommit mode. The backend closes cursors at the end of transactions, so in autocommit mode the backend will have closed the cursor before anything can be fetched from it. *The Statement must be created with a ResultSet type of ResultSet.TYPE_FORWARD_ONLY. This is the default, so no code will need to be rewritten to take advantage of this, but it also means that you cannot scroll backwards or otherwise jump around in the ResultSet.

The query given must be a single statement, not multiple statements strung together with semicolons.

Example 5.2. Setting fetch size to turn cursors on and off.

Changing code to cursor mode is as simple as setting the fetch size of the Statement to the appropriate size. Setting the fetch size back to 0 will cause all rows to be cached (the default behaviour).

// make sure autocommit is off
conn.setAutoCommit(false);
Statement st = conn.createStatement();
// Turn use of the cursor on.
st.setFetchSize(50);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next())
{
   System.out.print("a row was returned.");
}
rs.close();

// Turn the cursor off.
st.setFetchSize(0);
rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next())
{
   System.out.print("many rows were returned.");
}
rs.close();
// Close the statement.
st.close();