带有jdbc和存储过程(函数)的PostgreSQL:ResultSet [英] postgresql with jdbc and stored procedures (functions): ResultSet
问题描述
我只是试图从服务器(getStat)调用存储的函数,如下所示:
I just tried to call a stored function from the server (getStat), that is looking like this:
create type stat as (type text, location text, number int);
create function getStat() returns setof stat as 'select distinct table1.type, table1.location, table1.number from table1, table2 where table2.finding=10 order by number desc;' language 'sql';
现在这是jdbc代码:
Now here is the jdbc code:
CallableStatement callable = null;
String storedProc = "{call getStat(?, ?, ?)}";
try {
callable = connection.prepareCall(storedProc);
callable.registerOutParameter(1, java.sql.Types.VARCHAR);
callable.registerOutParameter(2, java.sql.Types.VARCHAR);
callable.registerOutParameter(3, java.sql.Types.INTEGER);
boolean results = callable.execute();
System.out.println(callable.getString(1));
System.out.println(callable.getString(2));
System.out.println(callable.getInt(3));
while(results){
ResultSet rs = callable.getResultSet();
while(rs.next()){
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getInt(3));
}
//rs.close();
results = callable.getMoreResults();
}
好的,现在是问题所在: 当我调用它时,它只是打印出第一行,应该全部打印出来.是的,很明显,因为我执行以下代码:
Okay, and now the problem: When I am calling it, it just prints out the first line, of the whole bulk, that should be printend. Yeah, that is clear, because I execute the following code:
System.out.println(callable.getString(1));
System.out.println(callable.getString(2));
System.out.println(callable.getInt(3));
但是我在while循环中做同样的事情……并且什么也没有显示.
But I do the same in the while loop...and nothing more is displayed.
也许问题很明显,但我很想念:(
Maybe the problem is something obvious, but I am missing that :(
谢谢!
推荐答案
无需使用具有{call ...}
语法的CallableStatement.
No need to use a CallableStatement with the {call ...}
syntax.
只需使用select和常规Statement
:
Just use a select and a regular Statement
:
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select * from getStat()");
while (rs.next())
{
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getInt(3));
}
这篇关于带有jdbc和存储过程(函数)的PostgreSQL:ResultSet的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!