如何使用JDBC调用PostgreSQL存储过程 [英] How to call PostgreSQL stored procedures with JDBC

问题描述

我正在使用postgresql，并且已经创建了一些存储过程.现在，我想通过jdbc访问存储过程并处理结果.存储过程的结果是整数或表.

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.

我发现了以下内容:

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?

推荐答案

您需要做的是注册您希望使用的所有返回变量.在提供的代码中，您仅注册了先进先出参数.

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.

类似这样的东西注册了前3个:

Something like this registers the first 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();
    }
}

另请参见

• JDBC CallableStatement –存储过程OUT参数示例
• 带有jdbc和存储过程(函数)的PostgreSQL:结果集
• 使用JDBC CallableStatements执行存储过程
• 在Java DB和MySQL中调用存储过程

See also

• JDBC CallableStatement – Stored Procedure OUT Parameter Example
• postgresql with jdbc and stored procedures (functions): ResultSet
• Using JDBC CallableStatements to Execute Stored Procedures
• Calling Stored Procedures in Java DB and MySQL

这篇关于如何使用JDBC调用PostgreSQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！