在测试 PG/PPAS 的时候,一个很大的查询,总是会很快就报OOM。把heap dump出来以后,发现一个一个ResultSet占用了大概600M;而同样的代码在oracle上面就没有问题。然后google了一下,发现是(默认)PG会一次把 query 执行完,并把结果返回。
可以用如下的方式设置 fetchsize
http://jdbc.postgresql.org/documentation/head/query.html#fetchsize-example
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();
同时,查询文档,发现 oracle jdbc(如果没有设置),默认是每次返回10条数据