pgsql的存储过程调用mysql,如何使用JDBC调用PostgreSQL存储过程

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?

解决方案

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