How to call PostgreSQL stored procedures with JDBC

Question

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.

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?

Answer 1

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 :

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

• 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