Oracleの取扱い

基本情報
データベース接続設定
データ型マッピング
- 自動マッピング
- 未サポートのデータ型
主キーでの自動採番
ページング検索の条件
- ConditionBeanのPaging
- OutsideSqlのManualPaging
更新ロックの取得
プロシージャ
データベース依存機能
- "for update nowait (wait N)" を利用
- ConditionBeanで全文検索
DBMS独自の利用方法
DBMS独自の注意点
Exampleのススメ
DBFlute内部での取扱い
Oracle補足資料

基本情報

対応バージョン: Oracle9iR2(9.2.0.1) 以上
JDBCドライバの同梱: なし
(推奨)JDBCドライバ: ojdbc6.jar (Exampleで利用)

データベース接続設定

データベース接続設定(databaseInfoMap.dfprop)について。

接続設定の仕様 @databaseInfoMap.dfprop
map:{
    ; driver   = oracle.jdbc.OracleDriver
    ; url      = jdbc:oracle:thin:@[host]:[port]:[SID]
    ; schema   = [SCHEMA]
    ; user     = [dbuser]
    ; password = [dbpassword]
}

catalog はそもそも対応する概念がないため設定不要
schema は大文字で指定
[xxx]の[]は単なる表現上(ドキュメント上)の囲み

以下、実際のテストプロジェクトでの設定例です。

e.g. dbflute-test-dbms-oracle {host=localhost,port=1521,SID=XE} @databaseInfoMap.dfprop
map:{
    ; driver   = oracle.jdbc.OracleDriver
    ; url      = jdbc:oracle:thin:@localhost:1521:XE
    ; schema   = MAIHAMADB
    ; user     = maihamadb
    ; password = maihamadb
}

データ型マッピング

データベース上のデータ型とプログラム型との(デフォルトの)マッピングについて。

java.lang.String: CHAR, VARCHAR2, N...CHAR, ...CLOB
java.lang.Integer: {NUMBER(1-9, 0)}
java.lang.Long: {NUMBER(10-18, 0)}
java.math.BigDecimal: INTEGER, NUMBER(n, m)
java.util.Date: DATE
java.sql.Time: なし
java.sql.Timestamp: TIMESTAMP, (TIMESTAMP WITH TIME ZONE)
byte[]: BLOB, RAW

自動マッピング

NUMBER に関しては、自動マッピング機能が利用可能です。

現場フィット - NUMERICの自動マッピング

未サポートのデータ型

BINARY_FLOAT: 数値表現の文字列として利用可能 (ex. "123.45")
BINARY_DOUBLE: 数値表現の文字列として利用可能 (ex. "123.45")
INTERVAL YEAR TO MONTH: 年月表現の文字列として利用可能 (ex. "32-10")
INTERVAL DAY TO SECOND: 日秒表現の文字列として利用可能 (ex. "1 0:0:1.0")
ROWID: 利用不可 (今後のサポートは未定)
ARRAY: 利用不可 (今後のサポートは未定) プロシージャでは限定的に利用可
OBJECT: 利用不可 (今後のサポートは未定) プロシージャでは限定的に利用可
LONG: 利用不可 (サポート予定なし)

DBMSごとの取扱い - 未サポートのデータ型

主キーでの自動採番

自動採番(連番)の仕組みとして シーケンス を利用します。

現場フィット - AutoIncrement

ReplaceSchema - データ登録 - シーケンス値の調整

ページング検索の条件

rownum を利用します。

ConditionBeanのPaging

e.g. ConditionBeanでページング検索 {81-100} @DisplaySql
select *
  from (
select plain.*, rownum as rn
  from (
select ...
  from ...
 where ...
 order by ...
       ) plain
       ) ext
 where ext.rn > 80
   and ext.rn <= 100

ConditionBean - Paging

OutsideSqlのManualPaging

e.g. OutsideSqlのManualPagingでページング検索 {81-100} @OutsideSql
/*IF pmb.isPaging()*/
select *
  from (
select plain.*, rownum as rn
  from (
select ...
-- ELSE select count(*)
/*END*/
  from ...
 where ...
 /*IF pmb.isPaging()*/
 order by ...
       ) plain
       ) ext
 where ext.rn > /*pmb.pageStartIndex*/80
   and ext.rn <= /*pmb.pageEndIndex*/100
 /*END*/

TypedParameterBean における ManualPaging の自動判別ロジックは、"rownum" という文字列が含まれることです。(大文字小文字は区別せず)

OutsideSql - manualPaging().selectPage()

TypedParameterBean

更新ロックの取得

ConditionBean の lockForUpdate() では、for update of [pk-column] を利用します。

this - "for update nowait (wait N)" を利用

e.g. ConditionBeanで更新ロックの取得 (cb.lockForUpdate()) @DisplaySql
select ...
  from MEMBER dfloc
 where ...
   and ...
 for update of dfloc.MEMBER_ID

基点テーブルのカラム名を指定することで、ロックを基点テーブルのみ掛けるようにしています。
PKのないテーブルでは、何かしらのカラムが指定されます。

プロシージャ

ストアドプロシージャ および ストアドファンクション を(DBFluteの機能としての)プロシージャとしてサポートしています。

現場フィット - プロシージャコール

INパラメータ: サポート
OUTパラメータ: サポート
INOUTパラメータ: サポート
プロシージャリターン: サポート ※ストアドファンクションにおいて
ResultSetパラメータ: サポート
ResultSetリターン: DBMSにて未サポート
NotParamResult: DBMSにて未サポート
パッケージプロシージャ: サポート ※一部例外あり
プロシージャシノニム: サポート ※一部例外あり

プロシージャパラメータの型

テーブルのデータ型に加えて、TABLE型(PL/SQL表)、VARRAY型などが限定的に利用できます。

Oracleの配列・構造体パラメータ

ProcedurePmb - サポートされる型

パッケージプロシージャ

パッケージプロシージャのParameterBeanのクラス名にはパッケージ名が付与されます。

パッケージプロシージャ

パッケージプロシージャで利用できるオーバーロードプロシージャに対して、厳密なサポートはされていません。オーバーロードは全て一つの ProcedurePmb として自動生成され、最小公倍数のプロパティ項目が定義されます。そのままでは実行できないため、独自にサブクラスを定義してプロパティ項目を絞ることで実行が可能です。

プロシージャシノニム

プロシージャシノニムの自動生成はオプションです。

this - シノニムの利用

プロシージャシノニム

別スキーマのプロシージャ

別スキーマのプロシージャもオプションで自動生成できます。

this - 別スキーマの利用

追加スキーマ(AdditionalSchema)

別スキーマ(OtherSchema)

自動生成対象プロパティの有効項目

Catalog: サポート (パッケージプロシージャのパッケージ名として)
Schema: サポート
Name: サポート

dfprop - プロシージャの自動生成対象プロパティ

Catalog(カタログ)

データベース依存機能

データベース依存機能を有効にした場合の利用可能な機能について。

現場フィット - データベース依存機能(メソッド)の利用

"for update nowait (wait N)" を利用

Oracle独自の "for update of [pk] nowait" および "for update of [pk] wait N" を利用できます。

e.g. selectした行を待機時間なし(NOWAIT)でロック @Java
cb.lockForUpdateNoWait();
Member member = memberBhv.selectEntityWithDeletedCheck(cb);
// select ... from MEMBER ... for update of MEMBER_ID nowait

e.g. selectした行を待機時間 3 秒(WAIT 3)でロック @Java
cb.lockForUpdateWait(3);
Member member = memberBhv.selectEntityWithDeletedCheck(cb);
// select ... from MEMBER ... for update of MEMBER_ID wait 3

ConditionBeanで全文検索

"Oracle Text" の利用を想定した全文検索の条件を指定できます。

Oracle Text

e.g. 会員名称を "foo" で全文検索 @Java
MemberDbm dbm = MemberDbm.getInstance();
cb.query().match(dbm.columnMemberName(), "foo");
// where (contains(dfloc.MEMBER_NAME, '{foo}') > 0)

e.g. 会員名称と会員アカウントを "f{o}o" で全文検索 @Java
MemberDbm dbm = MemberDbm.getInstance();
List<ColumnInfo> textColumnList = new ArrayList<ColumnInfo>();
textColumnList.add(dbm.columnMemberName());
textColumnList.add(dbm.columnMemberAccount());
cb.query().match(textColumnList, "f{o}o");
// where (contains(dfloc.MEMBER_NAME, '{f{o}}o}') > 0
//    or contains(dfloc.MEMBER_ACCOUNT, '{f{o}}o}') > 0
//       )

条件値が null もしくは空文字の場合は、ConditionBeanの基本仕様と同じく条件は無効になります。条件値は必ずエスケープされます。("{}" で囲われる)

ConditionBeanの使い方 - 基本仕様

DBMS独自の利用方法

マテリアライズドビューの利用

マテリアライズドビューは、通常のビューと同様に自動生成して CB などで検索できます。

ReplaceSchemaで作成する場合は、データを登録してから作成する方が効率が良いので、take-finally(.sql) にて create 文を記述すると良いでしょう。

ReplaceSchema - 最終チェック

e.g. ReplaceSchemaのデータ登録の後にマテリアライズドビューを作成 @take-finally.sql
create materialized view SUMMARY_MEMBER_PURCHASE
refresh complete
on commit
as
select ... from ...

シノニムの利用

以下の設定を行うことで自動生成対象にすることができます。

接続プロパティにシノニムを含める設定を有効に: databaseInfoMap.dfprop の propertiesMap に includeSynonyms=true を設定
自動生成対象オブジェクトにシノニムを追加: databaseInfoMap.dfprop の variousMap の objectTypeTargetList に SYNONYM を追加

dfprop - propertiesMap

databaseInfoMap - variousMap

テーブル参照のシノニム自動生成の特徴

通常のテーブルと全く同じようにプログラム上で扱うことができる
シノニムのメタデータ(PK、FK、UQなど)を完全サポート (参照先テーブルから取得)
シノニムの参照先テーブル自体は(直接利用しないなら)自動生成対象から除外してもよい
FK先テーブルにシノニムがある場合はそのシノニムの方に対して(のみ)関連が作成される
データベースリンクのシノニムもサポート。但し、一部メタデータ(FKとIndex情報)は未サポート

データベースリンク(DB Link)

プロシージャ参照のシノニム

プロシージャ参照のシノニム(プロシージャシノニム)に対する ProcedurePmb を自動生成するには、テーブル参照のシノニムの設定に追加して、outsideSqlDefinitionMap.dfprop の procedureSynonymHandlingType を設定することで自動生成できるようになります。

dfprop - procedureSynonymHandlingType

データベースリンク先のプロシージャシノニムの場合は、データベースリンク先のプロシージャと同じく、一部の機能が利用できない可能性があります。

データベースリンク先のプロシージャ

パッケージプロシージャのパッケージ部分を参照したシノニム(パッケージシノニム)に関しては、未サポートです。パッケージプロシージャ自体はシノニム経由ではない直接呼び出しでサポートされています。 DBリンク先のパッケージプロシージャは、DBFluteプロパティの直接定義においてサポートされています。シノニムの利用が必須でないならば、これらで代替できます。ただ、どうしてもシノニムの利用が必須の場合は、自動生成だけは直接パッケージプロシージャを参照して、実行時は ProcedurePmb の Exクラスにて、プロシージャ名をオーバーライドしてシノニムに修正すると良いでしょう。

パッケージプロシージャ

データベースリンク先のプロシージャ

その他オブジェクト参照のシノニム

テーブル(として扱えるオブジェクト)とプロシージャ以外を参照するシノニムは特に自動生成という形式でのサポートはありません。例えば、シーケンスのシノニムであれば、シノニムの名前を sequenceDefinitionMap.dfprop にシーケンス名としてそのまま指定して利用できます(但し、シーケンスキャッシュは利用できません)。

sequenceDefinitionMap.dfprop

別スキーマの利用

(同じDBインスタンス内の)別スキーマのテーブルを自動生成の対象にすることができます。

現場フィット - AdditionalSchema

追加スキーマ(AdditionalSchema)

別スキーマ(OtherSchema)

但し、シノニムが利用できる環境であれば、別スキーマのテーブルを参照するシノニムを自動生成対象に含めることで代替することができます。シノニムであれば、外だしSQL(OutsideSql)でもSQL上のテーブルのスキーマ修飾を意識する必要がありません。

this - シノニムの利用

データベースリンク先のプロシージャ

outsideSqlDefinitionMap.dfprop の targetProcedureNameList にて、"プロシージャ名@データベースリンク名" という形式で指定すると、そのプロシージャの ProcedurePmb が自動生成されます。ただし、一部の機能が利用できない可能性があります。

dfprop - targetProcedureNameList

この場合、本来のこのプロパティの自動生成対象の絞り込みには影響しません。データベースリンク先のどのプロシージャを自動生成対象にするかを、このプロパティを間借りして指定しているだけです。

パッケージシノニムはサポートされていないので、データベースリンク先のパッケージプロシージャを呼び出す場合は、この機能を使って代替します。

パッケージプロシージャ

Recyclebin の一掃

OracleでReplaceSchemaを繰り返し実行すると、Oracle内部の Recyclebin の古いテーブル情報がどんどん積み重なっていきます。それで特に問題になることはありませんが、 JDBCタスクでの除外作業がちょっとずつ長くなる可能性がありますので、take-finally(.sql) にて、"purge recyclebin" を実行すると良いでしょう。

ReplaceSchema - 最終チェック

e.g. ReplaceSchemaの最後に Recyclebin を一掃 @take-finally.sql
purge recyclebin;

日付リテラル

表示用SQLの日付フォーマットとして、日付リテラルを利用しています。表示用SQLをツールから実行するような場合に、 (Oracleの仕様として)リテラルなしの日付フォーマットだとエラーになることがあるためです。

日付: date '2009-12-24'
日時: timestamp '2009-12-24 12:34:56'

e.g. Oracleの日付リテラルを利用した表示用SQL @DisplaySql
 where BIRTHDATE >= timestamp '2009-12-24 12:34:56'
   and FORMALIZED_DATETIME < timestamp '2009-12-24 12:34:56.123'

これは、2Way-SQL を実現する上でも重要な要素です。バインド変数コメントのテスト値でこの日付リテラルを利用して、2Way-SQL の要件を満たすようにします。

e.g. Oracleの日付リテラルを利用した2Way-SQL @OutsideSql
member.FORMALIZED_DATETIME >= /*pmb.fromFormalizedDate*/date '1964-12-27'

この場合に限り、テスト値の中に空白(date や timestamp の後ろ)が含まれていても正常に認識される

Oracleの日付型の最適化

Oracle のDATE型(時分秒あり)に対する検索条件では、java.sql.Timestamp でバインドするよりも、oracle.sql.DATE でバインドする方がインデックス利用効率が良いことが報告されています。 (Timestamp でもインデックスが利用されないわけではなく、oracle.sql.DATE の方がより効率のよいインデックス利用をする)

デフォルトでは前者ですが、オプションにて後者を利用するように最適化することができます。littleAdjustmentMap.dfprop の isAvailableDatabaseNativeJDBC を有効にすると、上記の最適化を利用することができます。具体的には、java.util.Date 型のプロパティに対するバインドの型が Timestamp ではなく oracle.sql.DATE に差し替えられます。@since 0.9.7.5

dfprop - isAvailableDatabaseNativeJDBC

但し、これはJavaやJDBCドライバ、Oracle自体のバージョンに結果が依存するかもしれません。プロジェクトの環境に最適だと考えられる場合に限り利用するようにして下さい。

dbflute-oracle-example(Java6, ojdbc6.jar, Oracle10g XE)においては、30万件のテーブルのDATE型に対する検索条件で、わずかながらパフォーマンスが向上されたことが確認されています。テスト環境のPC上において(MacOSX上のVMWareによるWindowsXP)、日付型カラムに GreaterEqual の条件を付与して selectCount() を実行し、平均 50ms の検索処理が平均 30ms に。そもそもインデックスが付いていない場合だと、どちらにしても平均 150ms は超える。

DBMS独自の注意点

導出的one-to-oneのインラインビュー化

Oracle では、join の on 句に相関サブクエリを記述することができないため(Oracle10gで確認)、導出的one-to-oneを利用する場合は、固定条件(fixedCondition)をインラインビューにする必要があります。

現場フィット - 業務的one-to-one - 導出的one-to-one

プロシージャのエスケープ

char型のパラメータが存在して、かつ、内部でDBアクセス処理を行っている場合、エスケープ処理を施していると逆にエラーになってしまうことがあります(JDBCドライバのバージョンに依存する可能性あり)。その場合は、ProcedurePmb にて、そのプロシージャだけエスケープなしで実行されるように調整します。

Sql2Entity - ProcedurePmb - プロシージャのエスケープ

CustomizeEntityの対応テーブル

CustomizeEntityのそれぞれのカラムが、元は何のテーブルの何のカラムから派生したものか、通常はJDBCのメタデータから取得できるため、 Sql2Entity 内で自動解決し、JavaDocコメント上などで表示されます。但し、Oracleに関してはこのメタデータが取得できません。これによって、機能の利用の仕方が少しだけ変わるものがあります。外だしSQLでの LoadReferrer では、このことによりPKマークで "元は何のテーブルのカラムだったのか" を明示的に指定する必要があります。

Behavior - 外だしSQLでもLoadReferrer

Exampleのススメ

Oracle を使ったテストプロジェクト dbflute-test-dbms-oracle があります。

DBFlute Example - データベース

DBFlute内部での取扱い

DBFlute内部でどのようにOracleと付き合っているか、特殊なパターンを挙げます。将来的に同じ状況・同じ方法かどうかは保証されませんので、ここに書かれることに依存した利用はしないようにして下さい。 (DBFluteを深く理解するためのドキュメントと思って下さい)

CLOBのValueType: OracleのCLOBは、VARCHARの時とは違うValueTypeを使う必要がありますが、その指定をEntityの定数アノテーションにて行っています。自動生成時にCLOBか否かを判断し、該当カラムに付与します。一方で、プラグインValueTypeとしてCLOB専用のValueTypeを、初期化時に登録しています。
in句の1000制限対応: Oracleの in句 (InScope) は、指定できる要素が 1000 個までと制限されています。頻度は少ないにしろ、業務で 1000 個以上指定したいこともあり、通常その場合は条件を分割して or 句などでつなげる必要があります。DBFlute の ConditionBean では、1000 以上の要素が InScope に指定されたときに、内部で自動的に分割して同じ条件を実現しています。
バッチ更新の排他制御: OracleのJDBCドライバは、PreparedStatement.executeBatch() の戻り値からとして更新件数を取得することができません。代わりに Statement.getUpdateCount() の値を使って排他制御を実現しています。
DATEのマッピング: OracleのDATEは、JDBC上では ojdbc5.jar と ojdbc6.jar で違う型として扱われています。前者は java.sql.Types.DATE、後者は java.sql.Types.TIMESTAMP。DBFluteではこの違いを意識しないために、OracleのDATEを java.util.Date で扱うことを固定の仕様とし、メタデータ取得時に固定で前者の型にマッピングしています。
表示用SQLの日付フォーマット: 日付リテラルを利用するようにしていますが、これはデフォルトのフォーマットと違うため、初期化時(DBFluteConfigの初期化)に専用のフォーマットを設定して実現してます。また、バインド変数コメントのテスト値で利用された場合に、リテラル表現の後の空白をテスト値の終わりと判断しないようにしています。
シノニムのメタデータ: JDBC経由だとシノニムのメタデータ(PKやFKなど)はほとんど取得できません。データディクショナリを直接参照し、参照先テーブルのメタデータを取得して利用しています。
プロシージャのカタログ名: メタデータ上のプロシージャのカタログ名が、パッケージプロシージャのパッケージ名として扱われているため、特別な処理を施しています。 (他のDBMSでは、基本的にはカタログ名はそのままカタログ名(データベース名)として扱われます)
全角の "％" や "＿" もワイルドカード: Oracleは、全角の "％" や "＿" もワイルドカードとして扱われるため、曖昧検索のエスケープ処理にて、これらも対象としています。但し、DBMSごとの分岐がとてもしづらい部分での処理のため、DBMSに関わらずエスケープしています。 (全角をワイルドカードにしない他のDBMSでは単にエスケープ文字が無視されるだけで、動作に問題ないことはテストで確認されている); ただ、Oracleのバージョンによって、全角の "％" や "＿" がワイルドカードにならないケースがあるようです。
外部サイト - Oracle11gR2でLike検索でエラーになるケース

その他、挙げるとキリがないくらいあります。

Oracle補足資料

マテリアライズドビューを作成

e.g. マテリアライズドビューを作成 @SQL
create materialized view SUMMARY_MEMBER_PURCHASE
refresh complete
on commit
as
select ... from ...

シノニムを作成

e.g. シノニムを作成 @SQL
create synonym VD_SYNONYM_MEMBER for MEMBER;

シーケンスを作成

e.g. シーケンスを作成 {SEQ_MEMBER} @SQL
create sequence SEQ_MEMBER start with 1 increment by 1;

シーケンスを取得

e.g. シーケンスを取得 {SEQ_MEMBER} @SQL
select SEQ_MEMBER.nextval from dual;

Oracleでストアドプロシージャを作成

e.g. パラメータなしのストアドプロシージャを作成 @SQL
create or replace procedure SP_NO_PARAMETER
as
begin
  dbms_output.put_line('aaa');
end SP_NO_PARAMETER;

e.g. パラメータありのストアドプロシージャを作成 @SQL
create or replace procedure SP_IN_OUT_PARAMETER(
  v_in_varchar in varchar2
  , v_out_varchar out varchar2
  , v_inout_varchar in out varchar2) as
begin
  dbms_output.put_line(v_in_varchar);
  dbms_output.put_line(v_out_varchar);
  dbms_output.put_line(v_inout_varchar);
  v_out_varchar := 'ddd';
  v_inout_varchar := 'eee';
end SP_IN_OUT_PARAMETER;

ResultSetパラメータ

e.g. ResultSetパラメータのストアドプロシージャを作成 @SQL
create or replace procedure SP_RESULT_SET_PARAMETER(cur out sys_refcursor)
as
begin
  open cur for select * from MEMBER;
end SP_RESULT_SET_PARAMETER;

パッケージプロシージャ

e.g. パッケージプロシージャを作成 (definition) @SQL
create or replace package MAIN_PKG as
  procedure SP_PKG_NO_PARAMETER;
  procedure SP_PKG_IN_OUT_PARAMETER(v_in_varchar in varchar2
                                  , v_out_varchar out varchar2
                                  , v_inout_varchar in out varchar2);
end;

e.g. パッケージプロシージャを作成 (body) @SQL
create or replace package body MAIN_PKG as
procedure SP_PKG_NO_PARAMETER is
begin
  dbms_output.put_line('aaa');
end SP_PKG_NO_PARAMETER;
procedure SP_PKG_IN_OUT_PARAMETER(v_in_varchar in varchar2
                                , v_out_varchar out varchar2
                                , v_inout_varchar in out varchar2) is
begin
  dbms_output.put_line(v_in_varchar);
  dbms_output.put_line(v_out_varchar);
  dbms_output.put_line(v_inout_varchar);
  v_out_varchar := 'ddd';
  v_inout_varchar := 'eee';
end SP_PKG_IN_OUT_PARAMETER;
end;

ストアドファンクションを作成

e.g. パラメータなしのストアドファンクションを作成 (body) @SQL
create or replace function FN_NO_PARAMETER
return varchar2 as
begin
  return 'FN_NO_PARAMETER';
end FN_NO_PARAMETER;

e.g. パラメータなしのストアドファンクションを作成 (body) @SQL
create or replace function FN_IN_OUT_PARAMETER(
  v_in_varchar in varchar2
  , v_out_varchar out varchar2
  , v_inout_varchar in out varchar2)
return varchar2 as
begin
  dbms_output.put_line(v_in_varchar);
  dbms_output.put_line(v_out_varchar);
  dbms_output.put_line(v_inout_varchar);
  v_out_varchar := 'ddd';
  v_inout_varchar := 'eee';
  return 'FN_IN_OUT_PARAMETER';
end FN_IN_OUT_PARAMETER;