For stored procedure returns OUT parameters, it must be
- Registered via JDBC
CallableStatement.registerOutParameter(index,sqlType)
. - Get it back via
CallableStatement.getDataType(index)
.
See code snippets
//getDBUSERByUserId is a stored procedure
String getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}";
callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql);
callableStatement.setInt(1, 10);
callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(4, java.sql.Types.DATE);
// execute getDBUSERByUserId store procedure
callableStatement.executeUpdate();
String userName = callableStatement.getString(2);
String createdBy = callableStatement.getString(3);
Date createdDate = callableStatement.getDate(4);
JDBC CallableStatement Example
See a full JDBC CallableStatement
example for OUT parameter.
1. Stored Procedure
A stored procedure in Oracle database, with IN and OUT parameters. Later, calls it via JDBC.
CREATE OR REPLACE PROCEDURE getDBUSERByUserId(
p_userid IN DBUSER.USER_ID%TYPE,
o_username OUT DBUSER.USERNAME%TYPE,
o_createdby OUT DBUSER.CREATED_BY%TYPE,
o_date OUT DBUSER.CREATED_DATE%TYPE)
IS
BEGIN
SELECT USERNAME , CREATED_BY, CREATED_DATE
INTO o_username, o_createdby, o_date
FROM DBUSER WHERE USER_ID = p_userid;
END;
/
2. Calls Stored Procedure via CallableStatement
JDBC example to call a stored procedure via CallableStatement
.
File : JDBCCallableStatementOUTParameterExample.java
package com.mkyong.jdbc;
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
public class JDBCCallableStatementOUTParameterExample {
private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
private static final String DB_USER = "user";
private static final String DB_PASSWORD = "password";
public static void main(String[] argv) {
try {
callOracleStoredProcOUTParameter();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
private static void callOracleStoredProcOUTParameter() throws SQLException {
Connection dbConnection = null;
CallableStatement callableStatement = null;
String getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}";
try {
dbConnection = getDBConnection();
callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql);
callableStatement.setInt(1, 10);
callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(4, java.sql.Types.DATE);
// execute getDBUSERByUserId store procedure
callableStatement.executeUpdate();
String userName = callableStatement.getString(2);
String createdBy = callableStatement.getString(3);
Date createdDate = callableStatement.getDate(4);
System.out.println("UserName : " + userName);
System.out.println("CreatedBy : " + createdBy);
System.out.println("CreatedDate : " + createdDate);
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (callableStatement != null) {
callableStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(
DB_CONNECTION, DB_USER,DB_PASSWORD);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
}
Done.
Reference
- http://download.oracle.com/javase/6/docs/api/java/sql/CallableStatement.html
- http://docsrv.sco.com/JDK_guide/jdbc/getstart/callablestatement.doc.html
- http://onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html
转自:http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/