如何使用 JDBC 调用 PostgreSQL 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26110185/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to call PostgreSQL stored procedures with JDBC
提问by machinery
I'm using postgresql and I have created some stored procedures. Now I want to access the stored procedures via jdbc and process the results. The results of the stored procedures are either integer or a TABLE.
我正在使用 postgresql 并创建了一些存储过程。现在我想通过 jdbc 访问存储过程并处理结果。存储过程的结果是整数或表。
I found the following:
我发现了以下内容：
CallableStatement upperProc = conn.prepareCall("{ ? = call upper( ? ) }");
upperProc.registerOutParameter(1, Types.VARCHAR);
upperProc.setString(2, "lowercase to uppercase");
upperProc.execute();
String upperCased = upperProc.getString(1);
upperProc.close();
With this I think I can process the single integer return but how can I process the TABLE returns?
有了这个，我想我可以处理单个整数返回，但如何处理 TABLE 返回？
回答by blo0p3r
What you need to do is register all the return variables you desire using. In the code provided, you are only registering the first out parameter.
您需要做的是注册您想要使用的所有返回变量。在提供的代码中，您只注册了第一个输出参数。
Something like this registers the first 3 :
像这样注册前 3 个：
String callableSQL = "{call upper(?)}";
try {
dbConnection = getDBConnection();
callableStatement = dbConnection.prepareCall(callableSQL);
callableStatement.setString(1, "lowercase to uppercase");
//register multiple output parameters to match all return values
callableStatement.registerOutParameter(1, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(3, java.sql.Types.XYZ); //any data type here
callableStatement.execute();
//do something with your return values
String xyz = callableStatement.getString(1);
//... for other items you have registered.
} catch (SQLException up) {
throw up; //haha!
} finally {
//Silently close off
if (callableStatement != null) {
callableStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
See also
也可以看看
回答by Craig Ringer
To handle a result set from a CallableStatement, treat it just like a Statementthat returns one or more ResultSets:
要处理来自 a 的结果集CallableStatement，请将其视为Statement返回一个或多个ResultSets 的 a：
ResultSet rs = tableProc.getResultSet();
// ... and loop over the result set just like normal
(A PostgreSQL function can return multiple resultsets if it returns SETOF REFCURSOR).
（如果返回 ，PostgreSQL 函数可以返回多个结果集SETOF REFCURSOR）。
