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; } }
完了しました。