JDBC CallableStatement – ストアドプロシージャのOUTパラメータの例
ストアドプロシージャはOUTパラメータを返します。
-
JDBC経由で登録
`CallableStatement.registerOutParameter（index、sqlType）`です。
-
`CallableStatement.getDataType（index）`で返す.
コードスニペットを見る
….//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の例 OUTパラメータの完全なJDBCの `CallableStatement`の例を参照してください。 === 1.ストアドプロシージャ INパラメータとOUTパラメータを持つOracleデータベースのストアド・プロシージャ。 その後、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. CallableStatement経由でストアドプロシージャを呼び出します.
JDBCの例では、 `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;
}
}
完了しました。
