PostgreSQLの取扱い

基本情報

対応バージョン
PostgreSQL 8.1 以上
JDBCドライバの同梱
あり
(同梱)JDBCドライバ
postgresql-8.4-701.jdbc3.jar

データベース接続設定

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

接続設定の仕様 @databaseInfoMap.dfprop
map:{
    ; driver   = org.postgresql.Driver
    ; url      = jdbc:postgresql://[host]:[port]/[dbname]
    ; schema   = [schema]
    ; user     = [dbuser]
    ; password = [dbpassword]
}
  • catalog はURLから自動判別されるため設定不要
  • schema は独自のスキーマを利用しない限りは public と指定
  • [xxx]の[]は単なる表現上(ドキュメント上)の囲み

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

e.g. dbflute-postgresql-exampleの場合 {host=localhost,port=5432,dbname=exampledb} @databaseInfoMap.dfprop
map:{
    ; driver   = org.postgresql.Driver
    ; url      = jdbc:postgresql://localhost:5432/exampledb
    ; schema   = public
    ; user     = exampledb
    ; password = exampledb
}

データ型マッピング

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

java.lang.String
char, varchar, text
java.lang.Integer
serial, integer, {numeric(1-9, 0)}
java.lang.Long
bigserial, bigint, {numeric(10-18, 0)}
java.math.BigDecimal
real, float8, decimal, numeric(n, m)
java.util.Date
date
java.sql.Time
time, timetz
java.sql.Timestamp
timestamp
java.lang.Boolean
boolean
byte[]
bytea, oid(*1)

*1: DBFluteの処理として、OIDに関連付くDB上のリソースを update や delete 時に削除することはありません。

自動マッピング

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

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

未サポートのデータ型

money
BigDecimalにマッピングされるが利用不可 (PSQLException)
bit
Booleanにマッピングされるが利用不可 (PSQLException)
Interval
PGIntervalを使わないと利用できない!? (PSQLException) ※未検証
幾何データ型全般
(point, line, lseg, ...) ※未検証
ネットワークアドレス型
(cidr, inet, macaddr) ※未検証

主キーでの自動採番

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

serial 型と bigserial 型は設定レス

通常シーケンスは明示的にテーブルとの関連付けをDBFluteプロパティに設定する必要がありますが、serial 型および bigserial 型を利用する場合は、DBFluteが その関連付けを自動判別 しているので設定は不要です。

ページング検索の条件

limitoffset を利用します。

ConditionBeanのPaging

e.g. ConditionBeanでページング検索 {81-100} @DisplaySql
select ...
  from MEMBER dfloc  
 order by dfloc.MEMBER_NAME asc 
 offset 80 limit 20

OutsideSqlのManualPaging

e.g. OutsideSqlのManualPagingでページング検索 {81-100} @OutsideSql
/*IF pmb.isPaging()*/
select ...
-- ELSE select count(*)
/*END*/
  from ...
 where ...
 /*IF pmb.isPaging()*/
 order by ...
 offset /*pmb.pageStartIndex*/80 limit /*pmb.fetchSize*/20
 /*END*/

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

更新ロックの取得

ConditionBean の lockForUpdate() では、for update を利用します。

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

プロシージャ

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

INパラメータ
サポート
OUTパラメータ
サポート
INOUTパラメータ
サポート
プロシージャリターン
サポート
ResultSetパラメータ
サポート
ResultSetリターン
サポート
NotParamResult
DBMSにて未サポート
パッケージプロシージャ
DBMSにて未サポート ※そもそもパッケージの概念なし
プロシージャシノニム
DBMSにて未サポート ※そもそもシノニムの概念なし

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

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

別スキーマ(AdditionalSchema)

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

Catalog
利用不可 (メタデータとしてカタログ名が取得できないため)
Schema
サポート
Name
サポート

カタログ(データベース)名で自動生成対象を調整したい場合は、databaseInfoMap.dfprop の AdditionalSchema の設定で(うまく)調整することで代替できます。

データベース依存機能

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

"for update nowait" を利用する

PostgreSQL独自の "for update nowait" を利用できます。

e.g. selectした行をロック @Java
cb.lockForUpdateNoWait();
Member member = memberBhv.selectEntityWithDeletedCheck(cb);
// select ... from MEMBER ... for update nowait

ConditionBeanで全文検索

"Ludia + Senna" の利用を想定した "%%" 構文を使った全文検索の条件を指定できます。

e.g. 会員名称を "foo" で全文検索 @Java
cb.query().match(MemberDbm.getInstance().columnMemberName(), "foo");
// where dfloc.MEMBER_NAME %% 'foo'
e.g. 会員名称を "foo" で全文検索 @Java
List<String> columnList = new ArrayList<String>();
columnList.add(MemberDbm.getInstance().columnMemberName());
columnList.add(MemberDbm.getInstance().columnMemberAccount());
cb.query().match(columnList, "foo");
// where dfloc.MEMBER_NAME %% 'foo' or dfloc.ACCOUNT %% 'foo'

条件値が null もしくは空文字の場合は、ConditionBeanの基本仕様と同じく条件は無効になります。

DBFluteConfigの設定で、古いタイプの演算子 "@@" 構文を利用することもできます。

DBMS独自の利用方法

別スキーマの利用

別スキーマのテーブルを自動生成できます。(別カタログ(別データベース)のスキーマも可)

大文字小文字を吸収する曖昧検索 (ilike)

PostgreSQL独自の大文字小文字を吸収する曖昧検索 "ilike" 構文を利用することができます。

e.g. 会員名称が大文字小文字区別なしで "s" で始まる会員を検索 (ilike構文) @Java
cb.query().setMemberName_LikeSearch("s",  new LikeSearchOption() {
    @Override
    public ExtensionOperand getExtensionOperand() {
        return WayOfPostgreSQL.OperandOfLikeSearch.CASE_INSENSITIVE;
    }
}.likePrefix());

プロジェクトで複数箇所で統一的に利用する場合は、LikeSearchOptionを拡張したプロジェクト独自クラスを定義して、それを横展開すると良いでしょう。

DBMS独自の注意点

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

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

CustomizeEntityの対応テーブル

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

Extension利用時の微調整

pg_trgm などの extension を利用する場合に、スキーマ初期化前に extension を事前に drop する必要があります。スキーマ初期化の際に、pg_trgm 独自のプロシージャを drop してしまい、例外になるためです(PostgreSQL-9.1にて確認)。

replaceSchemaDefinitionMap.dfprop の initializeFirstSqlList にて、drop文を設定しておくと、ReplaceSchema のスキーマ初期化前にそのSQLが実行されます。@since 0.9.9.4B

e.g. スキーマ初期化前に extension を drop @replaceSchemaDefinitionMap.dfprop
# /- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# o initializeFirstSqlList: (NotRequired - Default list:{})
#  You can execute the SQL statements before initializing schema.
#
; initializeFirstSqlList = list:{
    ; drop extension if exists pg_trgm cascade 
}
# - - - - - - - - - -/

Exampleのススメ

PostgreSQL を使ったExample実装 dbflute-postgresql-example があります。

DBFlute Example - データベース

DBFlute内部での取扱い

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

OIDのValueType
PostgreSQLのOIDは、通常のバイナリ型の時とは違うValueTypeを使う必要がありますが、その指定をEntityの定数アノテーションにて行っています。 自動生成時にOIDか否かを判断し、該当カラムに付与します。 一方で、プラグインValueTypeとしてOID専用のValueTypeを、初期化時(TnValueTypeFactoryImplの初期化)に登録しています。
ResultSetパラメータのメタデータ
ResultSetパラメータを一つだけ利用したプロシージャのメタデータに、(なぜか)存在しないResultSet戻り値の情報が含まれます。 二つ以上利用する場合は問題なく、引数が一つのときのみ発生します。 PostgreSQLの仕様として、ResultSetパラメータとResultSet戻り値を同時に定義することができないため、 そのようなメタデータが取得された場合は、ResultSet戻り値をメタデータから除去するようにして、正常に利用できるようにしています。
テーブルの継承 (inherit)
ReplaceSchemaでテーブルをdropするときに、継承関係にある子テーブルを先にdropします(@since 1.0.5K)

PostgreSQL補足資料

シーケンスと連動する連番型

serial 型および bigserial 型は正確にはデータ型ではありません。内部で integer 型とシーケンスとの割当を行う表記法です。

以下の二つの例は、同じことを示します。

e.g. create文で serial 型を利用 @SQL
create table tablename (
    colname serial
) ;
e.g. create文で serial 型と同じ設定 @SQL
create sequence tablename_colname_seq;
create table tablename (
    colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
) ;

シーケンスを作成

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

シーケンスを取得

e.g. シーケンスを取得 {SEQ_MEMBER} @SQL
select nextval ('SEQ_MEMBER')

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

e.g. パラメータなしのファンクションを作成 @SQL
create or replace function SP_NO_PARAMETER()
returns integer as
$BODY$
begin
  return 1;
end;
$BODY$ LANGUAGE 'plpgsql';
e.g. パラメータありのファンクションを作成 @SQL
create or replace function SP_IN_OUT_PARAMETER(
  v_in_varchar in varchar
  , v_out_varchar out varchar
  , v_inout_varchar out varchar)
as
$BODY$
begin
  v_out_varchar := 'ddd';
  v_inout_varchar := 'eee';
end;
$BODY$ LANGUAGE 'plpgsql';
e.g. ResultSetパラメータ(一つ)のファンクションを作成 @SQL
create or replace function SP_RESULT_SET_PARAMETER(cur out refcursor)
as
$BODY$
begin
  open cur for select MEMBER_ID, MEMBER_NAME, UPDATE_DATETIME from MEMBER;
end;
$BODY$ LANGUAGE 'plpgsql';
e.g. ResultSet戻り値のファンクションを作成 @SQL
create or replace function SP_RETURN_RESULT_SET()
returns refcursor as
$BODY$
declare
  cur refcursor;
begin
  open cur for select MEMBER_ID, MEMBER_NAME, UPDATE_DATETIME from MEMBER;
  return cur;
end;
$BODY$ LANGUAGE 'plpgsql';