JDBC CallableStatement – ストアドプロシージャCURSORの例
OracleストアドプロシージャがCURSORパラメータを返す場合、
-
JDBC経由で登録
`CallableStatement.registerOutParameter（index、OracleTypes.CURSOR）`です。
-
`callableStatement.getObject（index）`でそれを取得します.
コードスニペットを見る
….//getDBUSERCursor is a stored procedure
String getDBUSERCursorSql = “{call getDBUSERCursor(?,?)}”;
callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);
callableStatement.setString(1, “mkyong”);
callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
callableStatement.executeUpdate();
rs = (ResultSet) callableStatement.getObject(2);
while (rs.next()) {
String userid = rs.getString(“USER__ID”);
String userName = rs.getString(“USERNAME”);
}
=== JDBC CallableStatement CURSORの例 OUT ** CURSOR ** パラメータの完全なJDBCの `CallableStatement`の例を参照してください。 === 1.ストアドプロシージャ 1つのINパラメータと1つのOUT CURSORパラメータを持つOracleストアド・プロシージャ。 その後、JDBC経由で呼び出します。
CREATE OR REPLACE PROCEDURE getDBUSERCursor(
p
username IN DBUSER.USERNAME%TYPE,
c
dbuser OUT SYS__REFCURSOR)
IS
BEGIN
OPEN c__dbuser FOR SELECT ** FROM DBUSER WHERE USERNAME LIKE p__username || '%';
END;/….
2. CallableStatement経由でストアドプロシージャを呼び出します.
JDBCの例では、上記のストアドプロシージャを呼び出し、返された
CURSOR
を
ResultSet
にキャストし、レコードを順番にループします。
File:JDBCCallableStatementCURSORExample.java
package com.mkyong.jdbc;
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
public class JDBCCallableStatementCURSORExample {
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 {
callOracleStoredProcCURSORParameter();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
private static void callOracleStoredProcCURSORParameter()
throws SQLException {
Connection dbConnection = null;
CallableStatement callableStatement = null;
ResultSet rs = null;
String getDBUSERCursorSql = "{call getDBUSERCursor(?,?)}";
try {
dbConnection = getDBConnection();
callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);
callableStatement.setString(1, "mkyong");
callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
//execute getDBUSERCursor store procedure
callableStatement.executeUpdate();
//get cursor and cast it to ResultSet
rs = (ResultSet) callableStatement.getObject(2);
while (rs.next()) {
String userid = rs.getString("USER__ID");
String userName = rs.getString("USERNAME");
String createdBy = rs.getString("CREATED__BY");
String createdDate = rs.getString("CREATED__DATE");
System.out.println("UserName : " + userid);
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 (rs != null) {
rs.close();
}
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;
}
}
完了しました。
