使用jdbc访问PostgreSQL或者openGauss数据库里的cursor游标时，官方文档可查的资料较少，下面的示例供参考。
测试环境
- JDBC：postgresql-42.3.5.jar
- PG: 14.2
- openGauss: 3.0.0
测试背景
针对function和procedure返回cursor游标类型，通过jdbc如何调用。
测试function：curtest1，通过returns返回游标类型
create or replace function curtest1()
returns refcursor
language plpgsql
as $function$
declare
cur refcursor;
begin
open cur for select id,data from fiverows;
return cur;
end;
$function$;
测试procedure：curtest2，通过out参数返回游标类型
create or replace procedure curtest2(out cur refcursor)
language plpgsql
as $procedure$
begin
open cur for select id,data from fiverows;
end;
$procedure$;
测试procedure：curtest3，通过out参数返回多个游标类型
create or replace procedure curtest3(out cur1 refcursor,out cur2 refcursor)
language plpgsql
as $procedure$
begin
open cur1 for select id,data from fiverows where id between 1 and 3;
open cur2 for select id,data from fiverows where id between 4 and 5;
end;
$procedure$;
表结构及数据
create table fiverows(id serial primary key,data text);
insert into fiverows(data) values('one'),('two'),
('three'),('four'),('five');
测试一：function通过returns返回游标
function返回游标在PostgreSQL或者openGauss数据库里访问的代码参考如下：
public static void main(String[] args) throws Exception{
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.137.251:1402/postgres","postgres","admin");
CallableStatement stmt = null;
ResultSet resultSet = null;
try{
conn.setAutoCommit(false);
stmt = conn.prepareCall("{? = call public.curtest1()}");;
stmt.registerOutParameter(1, Types.REF_CURSOR);
stmt.setCursorName("mycur1");
stmt.execute();
resultSet = (ResultSet) stmt.getObject(1);
while(resultSet.next()){
Integer id = (Integer)resultSet.getInt(1);
String data = (String) resultSet.getString(2);
System.out.println("id = "+id+", data = "+data);
}
} catch (Exception e) {
e.printStackTrace();
}
conn.close();
}
注意下面这几点：
1.使用CallableStatement
2.使用Types.REF_CURSOR注册输出参数
3.Statement执行后，再通过Statement获取结果集
测试二：procedure通过out参数返回游标
procedure返回游标在PostgreSQL或者openGauss数据库里有一点差异：主要是escapeSyntaxCallMode参数。
PostgreSQL
procedure返回游标的jdbc代码跟上面function测试类似：
public static void main(String[] args) throws Exception{
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.137.251:1402/postgres?escapeSyntaxCallMode=call", "postgres","admin");
CallableStatement stmt = null;
ResultSet resultSet = null;
try{
conn.setAutoCommit(false);
stmt = conn.prepareCall("{call public.curtest2(?)}");;
stmt.registerOutParameter(1, Types.REF_CURSOR);
stmt.execute();
resultSet = (ResultSet) stmt.getObject(1);
while(resultSet.next()){
Integer id = (Integer)resultSet.getInt(1);
String data = (String) resultSet.getString(2);
System.out.println("id = "+id+", data = "+data);
}
} catch (Exception e) {
e.printStackTrace();
}
conn.close();
}
不过执行上面的代码客户端会报下面的错误：
错误信息提示并不是很明确，查找资料发现大概与escapeSyntaxCallMode参数有关，该参数的官方链接如下：https://jdbc.postgresql.org/documentation/head/connect.html
Specifies how the driver transforms JDBC escape call syntax into underlying SQL, for invoking procedures or functions. In escapeSyntaxCallMode=select mode (the default), the driver always uses a SELECT statement (allowing function invocation only). In escapeSyntaxCallMode=callIfNoReturn mode, the driver uses a CALL statement (allowing procedure invocation) if there is no return parameter specified, otherwise the driver uses a SELECT statement. In escapeSyntaxCallMode=call mode, the driver always uses a CALL statement (allowing procedure invocation only).
自PostgreSQL v11开始支持procedure，PostgreSQL JDBC驱动也配套引入了escapeSyntaxCallMode参数，该参数有三种值，默认是第一种func，系统理解为function来调用；第二种是call，系统理解为procedure来调用；第三种是系统根据定义自动猜测。
上面的connection连接串加上escapeSyntaxCallMode参数，修改成：
jdbc:postgresql://192.168.137.251:1402/postgres?escapeSyntaxCallMode=call
则可正常执行
openGauss
procedure返回游标的jdbc代码与function测试类似，并且也不需要设置escapeSyntaxCallMode参数。
procedure创建语法有差异，代码如下：
create or replace procedure curtest2(out cur refcursor)
as
begin
open cur for select id,data from fiverows;
end;
测试三：procedure通过out参数返回多个游标
返回多个游标与测试二类似，也是同样支持，在PostgreSQL也是需要设置escapeSyntaxCallMode参数，openGauss不需要设置。
PostgreSQL
jdbc代码如下：
public static void main(String[] args) throws Exception{
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.137.251:1402/postgres?escapeSyntaxCallMode=call","postgres","admin");
CallableStatement stmt = null;
ResultSet resultSet = null;
try{
conn.setAutoCommit(false);
stmt = conn.prepareCall("{call public.curtest3(?,?)}");;
stmt.registerOutParameter(1, Types.REF_CURSOR);
stmt.registerOutParameter(2, Types.REF_CURSOR);
stmt.execute();
resultSet = (ResultSet) stmt.getObject(1);
System.out.println("cursor1 data:");
while(resultSet.next()){
Integer id = (Integer)resultSet.getInt(1);
String data = (String) resultSet.getString(2);
System.out.println("id = "+id+", data = "+data);
}
resultSet = (ResultSet) stmt.getObject(2);
System.out.println("cursor2 data:");
while(resultSet.next()){
Integer id = (Integer)resultSet.getInt(1);
String data = (String) resultSet.getString(2);
System.out.println("id = "+id+", data = "+data);
}
} catch (Exception e) {
e.printStackTrace();
}
conn.close();
}
测试结果如下：
openGauss
与测试二类似，不需要设置escapeSyntaxCallMode参数。
procedure创建语法有差异，代码如下：
create or replace procedure curtest3(out cur1 refcursor,out cur2 refcursor)
as
begin
open cur1 for select id,data from fiverows where id between 1 and 3;
open cur2 for select id,data from fiverows where id between 4 and 5;
end;
结论
通过jdbc访问function里的returns cursor或者procedure里的out cursor类型都支持。
区别在于procedure的out cursor类型，在PostgreSQL需要设置escapeSyntaxCallMode=call，openGauss数据库不需要。
