JDBCからストアドプロシージャを呼び出す(Oracle、SQL Server、MySQL)
RDBMSの差異を吸収してくれるのがJDBC…、のはずが、なかなかそううまくはいかない。。
ストアドプロシージャの呼び出しも、RDMBSによって細かな違いがあるようだ。
MySQLのストアドプロシージャを呼び出す
まず、テスト用のストアドプロシージャを準備する。
DELIMITER // CREATE PROCEDURE TEST1( IN n1 INT, INOUT n2 INT, OUT n3 INT) BEGIN SET n3 := n1 * n2; SET n2 := n2 + 1; END; // DELIMITER ;
ストアドプロシージャの呼び出しには、CallableStatementを利用する。
Connection#prepareCallにより得ることができる。
INの設定にはsetXXX、OUTの取得にはgetXXXを使う。
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/TEST", "testuser", "testpass")) { try (CallableStatement statement = connection.prepareCall("CALL TEST1(?, ?, ?)")) { // メタ情報を取得するテスト for (int i = 1; i <= statement.getParameterMetaData().getParameterCount(); i++) { System.out.println(String.format("Parameter #%d : mode = %d", i, statement.getParameterMetaData().getParameterMode(i))); } //statement.registerOutParameter(1, Types.INTEGER); statement.setInt(1, 5); statement.setInt(2, 7); statement.execute(); System.out.println(String.format("n2 = %d", statement.getInt(2))); System.out.println(String.format("n3 = %d", statement.getInt(3))); } }
registerOutParameterを呼んでおかなくても、OUTの値は取れた。
Oracleのストアドプロシージャを呼び出す
同じように、テスト用のストアドプロシージャを準備する。
CREATE OR REPLACE PROCEDURE TEST1( n1 IN INT, n2 IN OUT INT, n3 OUT INT) AS BEGIN n3 := n1 * n2; n2 := n2 + 1; END; /
やっぱりMySQLとは文法が違う。
「IN」とか「OUT」を指定する順番が違うのが、ちょっと混乱する。
ストアドプロシージャの呼び出しは、こんな感じ。
try (Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:TESTDB", "TEST", "testpass")) { try (CallableStatement statement = connection.prepareCall("CALL TEST1(?, ?, ?)")) { // メタ情報を取得するテスト for (int i = 1; i <= statement.getParameterMetaData().getParameterCount(); i++) { // サポートされていない // System.out.println(String.format("Parameter #%d : mode = %d", i, statement.getParameterMetaData().getParameterMode(i))); } // 呼ばないとエラー statement.registerOutParameter(2, Types.INTEGER); statement.registerOutParameter(3, Types.INTEGER); statement.setInt(1, 5); statement.setInt(2, 7); statement.execute(); System.out.println(String.format("n2 = %d", statement.getInt(2))); System.out.println(String.format("n3 = %d", statement.getInt(3))); } }
残念ながら、ParameterMetaData#getParameterModeはサポートしていなかった。
汎用的なツールを作るときとか、ちょっと不便だなあ…。
それから、registerOutParameterを呼んでおかないと、エラーになってしまう。
(全ての引数について、setXXXかregisterOutParameterのどちらかを呼ばないといけないようだ。なので、INOUTの引数については呼ばなくても動いた。)
SQL Serverのストアドプロシージャを呼び出す
こちらも同様に、テスト用のストアドプロシージャを準備する。
CREATE PROCEDURE TEST1( @n1 INT, @n2 INT, @n3 INT OUT) AS BEGIN SET @n3 = @n1 * @n2; END;
SQL Serverには、INOUTの引数は無いのかな?
ドキュメントを見たけど、見つからなかった。
ストアドプロシージャの呼び出しは、こんな感じ。
try (Connection connection = DriverManager.getConnection("jdbc:sqlserver://localhost\\SQLEXPRESS:1433;databasename=TEST", "testuser", "testpass")) { try (CallableStatement statement = connection.prepareCall("EXEC TEST1 ?, ?, ?")) { // メタ情報を取得するテスト for (int i = 1; i <= statement.getParameterMetaData().getParameterCount(); i++) { System.out.println(String.format("Parameter #%d : mode = %d", i, statement.getParameterMetaData().getParameterMode(i))); } // 呼ばないとエラー statement.registerOutParameter(3, Types.INTEGER); statement.setInt(1, 5); statement.setInt(2, 7); statement.execute(); //System.out.println(String.format("n2 = %d", statement.getInt(2))); System.out.println(String.format("n3 = %d", statement.getInt(3))); } }
SQL Serverの場合は、「CALL」ではなく「EXECUTE」または「EXEC」で呼び出す。また、括弧を付けるとエラーになる。
Oracleと同様、registerOutParameterを呼んでおかないとエラーになる。
JDBCについて調べると、いつもこんな感じだな。。
OS | Windos 7 |
Java | 8u31 |
Oracle | 12c JDBC Driver 12.1.0.2 JDBC Driver 11.2.0.4 |
SQL Server | 2012 JDBC Driver 4.1 |
MySQL | 5.6 JDBC Driver 5.1.34 |