PL/SQLおよびJDBCドライバ(21c、23c)を使用するOracle Database 23cの新しいBOOLEANデータ型 (2023/09/25)

https://medium.com/oracledevs/the-new-boolean-data-type-in-oracle-database-23c-with-pl-sql-and-the-jdbc-drivers-21c-23c-c83957e7e21e

投稿者：Juarez Junior

by Juarez Junior

はじめに

以前のブログ投稿では、Oracle Database 23c Free - Developer ReleaseでサポートされるISO SQL標準準拠のBOOLEANデータ型について記述しています。

Java開発者が列定義のBOOLEANデータ型を操作する場合、およびOracle JDBCドライバ(23c、21c)とともに使用する場合に注意を払う必要がある詳細に関するいくつかの具体的なシナリオを確認しました。

また、NUMBERおよびVARCHARデータ型を使用する表列と、論理ブール値を表す可能性のある値で異なるJDBC問合せを使用する場合の動作についても分析しました。

このブログ投稿は、これらのシナリオを補完しますが、現在では、PL/SQLストアド・プロシージャ、JDBCおよびCallableStatementsとの組合せを考慮しています。

JDBCおよびCallableStatementsの概要が必要な場合は、前のブログ投稿を確認してください- JDBC CallableStatementsを使用したストアド・プロシージャの起動。

それでは、それ以上のアドオンがなければ、始めましょう!

前提条件

JDK — Java Development Kit 17+
Your preferred Java IDE — Eclipse, IntelliJ, VS Code
Apache Maven
Oracle Database 23c Free — Developer Release running on Docker

Oracle Database 23c Free - Dockerでの開発者リリース

Oracle Database 23c Free - Developer ReleaseインスタンスをローカルDocker環境で実行

Dockerイメージ(Oracle Database 23c Free - Developer Release for Java Developers with Docker on Windows)の使用の詳細は、前のブログ投稿を確認してください。

新しいBOOLEANデータ型、SQLおよびJDBC CallableStataments

シナリオを説明するには、まず、次のようにBOOLEAN列を含むHQ_EMPLOYEEという単純なデータベース表を作成します。

シナリオを説明するには、まず、必要に応じてデータベース・ユーザーおよび関連する権限を作成します。

CREATE USER JDBCSP_USER IDENTIFIED BY <JDBCSP_USER_PASSWORD>;
GRANT DB_DEVELOPER_ROLE TO JDBCSP_USER;
GRANT CREATE SESSION TO JDBCSP_USER;
GRANT UNLIMITED TABLESPACE TO JDBCSP_USER;

次に、次のようにBOOLEAN列を含むHQ_EMPLOYEEという単純なデータベース表を作成します。

CREATE TABLE HQ_EMPLOYEE
(
  "EMP_ID"   NUMBER NOT NULL ENABLE,
  "NAME"    VARCHAR2(20 BYTE) DEFAULT NULL,
  "ROLE"    VARCHAR2(20 BYTE) DEFAULT NULL,
  "ACTIVE" BOOLEAN DEFAULT NULL,
  PRIMARY KEY ("EMP_ID")
);
COMMIT;

表の詳細を調べて、ACTIVE列のデータ型がBOOLEANデータ型であることを確認します。

DESCRIBE HQ_EMPLOYEE;

ACTIVE column as BOOLEAN

ここでは、以前のブログ投稿のように様々なシナリオを分析し、現在はPL/SQLストアド・プロシージャでCallableStatementを使用するストアド・プロシージャを作成できます。

現行の分析では、次のPL/SQLストアド・プロシージャをチェックする際に注意するアプローチが多少異なります。これらのシナリオでは、SQL SELECTのかわりにSQL INSERT文を使用するためです。そのため、前者は、PL/SQLと組み合せたJDBC CallableStatementがBOOLEANデータ型列の使用をどのようにサポートするかを理解することに重点を置いています。後者は、このシリーズの最初のブログ投稿の範囲ですでに検討されています。

CREATE OR REPLACE PROCEDURE INSERT_HQ_EMPLOYEE_PRC
(
 in_emp_id IN HQ_EMPLOYEE.EMP_ID%TYPE,
 in_name IN HQ_EMPLOYEE.NAME%TYPE,
 in_role IN HQ_EMPLOYEE.ROLE%TYPE,
 in_active IN HQ_EMPLOYEE.ACTIVE%TYPE, 
 out_result OUT VARCHAR2)
AS
BEGIN
  INSERT INTO HQ_EMPLOYEE (EMP_ID, NAME, ROLE, ACTIVE) 
  VALUES (in_emp_id, in_name, in_role, in_active);
  COMMIT;
  
  out_result := 'TRUE';
 
EXCEPTION
  WHEN OTHERS THEN 
  out_result := 'FALSE';
  ROLLBACK;
END;

GRANT EXECUTE ON INSERT_HQ_EMPLOYEE_PRC TO JDBCSP_USER;

最後に、CallableStatementを使用してJavaコード・サンプルを実装し、プロシージャをコールしてすべてをテストできるようになりました。

その後、テスト・ワークベンチを使用して、提案されたシナリオの分析を支援します。

This software is dual-licensed to you under the Universal Permissive License

(UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl or Apache License

2.0 as shown at http://www.apache.org/licenses/LICENSE-2.0. You may choose

either license.

Licensed under the Apache License, Version 2.0 (the "License");

you may not use this file except in compliance with the License.

You may obtain a copy of the License at

https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software

distributed under the License is distributed on an "AS IS" BASIS,

WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

See the License for the specific language governing permissions and

limitations under the License.

package com.oracle.dev.jdbc;

import java.sql.CallableStatement;

import java.sql.SQLException;

import java.util.Properties;

import java.util.concurrent.ThreadLocalRandom;

import oracle.jdbc.OracleConnection;

import oracle.jdbc.pool.OracleDataSource;

public class JDBCStoredProcHqEmployee {

	private final static String DB_URL = "jdbc:oracle:thin:@<DB_HOST>:<DB_PORT>/<DB_NAME>";

private final static String DB_USER = "<DB_USER>";

private final static String DB_PASSWORD = "<DB_PASSWORD>";

private static OracleConnection con;

private static CallableStatement stmt;

public static void main(String[] args) {

System.out.println("--------------------");

System.out.println("Input parameters");

System.out.println("--------------------");

int id = ThreadLocalRandom.current().nextInt();

System.out.println("ID: " + id);

String name = "Duke";

System.out.println("Name: " + name);

String role = "Mascott";

System.out.println("Role: " + role);

// boolean active = true;

// System.out.println("Active: " + active);

try {

Properties info = new Properties();

info.put(OracleConnection.CONNECTION_PROPERTY_USER_NAME, DB_USER);

info.put(OracleConnection.CONNECTION_PROPERTY_PASSWORD, DB_PASSWORD);

info.put(OracleConnection.CONNECTION_PROPERTY_FAN_ENABLED, false);

// JDBC datasource

OracleDataSource ods = new OracleDataSource();

ods.setURL(DB_URL);

ods.setConnectionProperties(info);

// JDBC connection

con = (OracleConnection) ods.getConnection();

// CallableStatement

			// https://docs.oracle.com/en/java/javase/19/docs/api/java.sql/java/sql/CallableStatement.html

stmt = con.prepareCall("{call INSERT_HQ_EMPLOYEE_PRC(?,?,?,?,?)}");

// set IN parameters

stmt.setInt(1, id);

stmt.setString(2, name);

stmt.setString(3, role);

stmt.setBoolean(4, true);

// register OUT parameter

stmt.registerOutParameter(5, java.sql.Types.VARCHAR);

stmt.executeUpdate();

// get OUT parameter

String result = stmt.getString(5);

System.out.println("--------------------\n");

System.out.println("Output parameter");

System.out.println("--------------------");

System.out.println("Procedured executed : " + result);

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

stmt.close();

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

シナリオ1 - Oracle JDBCドライバ23c (23.2.0.0)、Oracle Database 23c Free、setBoolean()を含むCallableStatementおよびBOOLEANデータ型列

シナリオ1

次のシナリオをサポートするには、Oracle JDBCドライバ23cをプロジェクトへの依存性として追加する必要があります。このサンプル・コードではMavenが使用されるため、次のような依存関係が表示されます。

<dependency>
   <groupId>com.oracle.database.jdbc</groupId>
   <artifactId>ojdbc11-production</artifactId>
   <version>23.2.0.0</version>
   <type>pom</type>
  </dependency>

ここでは、最初のシナリオについて、PL/SQLストアド・プロシージャをコールして、前述のシナリオ仕様に従って、このような組合せでその動作を確認できます。

要約すると、ストアド・プロシージャは、CallableStatementに設定されたパラメータ・タイプ(java.lang.Boolean (またはプリミティブ pool) - stmt.setBoolean(4、 true); で実行されます。

これによって、新しいBOOLEANデータ型で定義された列を持つデータがターゲット表に挿入されるかどうかを確認します。

そのため、この最初のシナリオに関して、ストアド・プロシージャは期待どおりに正常に実行されます。

PL/SQLストアド・プロシージャが正常に実行されました

二重チェックとして、データベースを問い合せて、前述のIDの新規レコードが挿入されたことを確認することもできます。

レコードが正常に挿入されました

シナリオ2 - Oracle JDBCドライバ23c (23.2.0.0)、Oracle Database 23c Free、setInt()を含むCallableStatementおよびBOOLEANデータ型列

シナリオ2

2番目のシナリオでは、ストアド・プロシージャは、CallableStatementに設定されたパラメータ・タイプ(java.lang.Integer (またはプリミティブint) - stmt.setInt(4、 1))を使用して実行されます。

これによって、新しいBOOLEANデータ型で定義された列を持つデータがターゲット表に挿入されるかどうかが再度確認されます。

ただし、CallableStatementsをストアド・プロシージャとともに使用すると、次に示すように完全に失敗します。

PL/SQLストアド・プロシージャが失敗しました

このような結果から、このブログ・シリーズの第1部で検証したBOOLEANデータ型列に対する問合せの実行時に発生するバリエーションおよび柔軟性に対する広範なサポートは、CallableStatementsおよびPL/SQLストアド・プロシージャの使用時に同じではないことがわかります。

シナリオ3 - Oracle JDBCドライバ23c (23.2.0.0)、Oracle Database 23c Free、setString()を含むCallableStatementおよびBOOLEANデータ型列

このシナリオに関して、ストアド・プロシージャは、CallableStatementに設定されたパラメータ・タイプ(java.lang.String - stmt.setString(4、 "1"); を使用して実行されます。

予想どおり、このシナリオは機能せず、失敗も引き起こします。

PL/SQLストアド・プロシージャが失敗しました

これを完了するために、JDBCドライバ・バージョン21c (21.9.0.0)を使用する上記のような追加のシナリオもテストされており、結果はほぼ同じです。

<dependency>
   <groupId>com.oracle.database.jdbc</groupId>
   <artifactId>ojdbc11-production</artifactId>
   <version>21.9.0.0</version>
   <type>pom</type>
  </dependency>

要約すると、キー・テイクアウェイは、CallableStatementで機能する唯一のシナリオは前述のシナリオ1です。ここで、BOOLEANデータ型列を含む表にデータを挿入するターゲットPL/SQLストアド・プロシージャをコールする前に、java.lang.BOOLEAN型(またはJavaプリミティブ・ブール型)のパラメータを設定します。

まとめ

これで終わりです。このブログでは、Oracle 23c Free - Developer Releaseで新しいBOOLEANデータ型をOracle JDBC Drivers 23c (23.2.0.0)および21c (21.9.0.0)と組み合せて使用する場合、様々なシナリオについて調査しました。

特に、このシリーズの第1部で検討されたシナリオを超えて、BOOLEANデータ型列を持つデータベース表にデータを挿入するためのjava.SQL.CallableStatementおよびPL/SQLプロシージャの使用を検討しました。

入力パラメータの様々な組合せが、CallableStatementのインタフェース(setBoolean()、setInt()およびsetString())で使用可能な各メソッドで設定されており、その結果の動作が分析および文書化されています。

前述の様々なシナリオおよび組合せに関する予想される動作の理解に役立つことを願っています。

参照

Oracle Database 23c Free — Developer Release — Boolean Data Type

Oracle Database JDBC Java API Reference, Release 23c

Product — Oracle Database 23c Free — Developer Release

Technical Blog — Oracle Database 23c Free — Developer Release

Documentation — Oracle Database 23c Free — Developer Release

Forum — Oracle Database 23c Free — Developer Release

Database PL/SQL Language Reference 23

PL/SQL Developer Guides