PostgreSQLの取扱い

基本情報

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

もし認証エラーが発生した場合

自動生成時に、"認証型 10 はサポートされません" というエラーが発生した場合、PostgreSQL本体に対して、JDBCドライバーが古いことが原因である可能性があります。

一方で、最新のJDBCドライバーにすると、PostgreSQLのJDBCドライバーの挙動変更により、ストアドファンクションに対応する ParameterBean の自動生成がされなくなります。

ゆえに、両方の問題を回避できるギリギリのJDBCのバージョン 42.2.10 にすると良いです。

mydbfluteの下のDBFluteエンジンのlib配下のPostgreSQLのJDBCドライバーを差し替えてください。 (DBFlute-1.2.6 より、そのバージョンが組み込まれます)

データベース接続設定

データベース接続設定(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
}
databaseInfoMap.dfprop

データ型マッピング

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

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 時に削除することはありません。

未サポートのデータ型

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

主キーでの自動採番

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

serial 型と bigserial 型は設定レス

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

現場フィット - AutoIncrement
ReplaceSchema - データ登録 - シーケンス値の調整

ページング検索の条件

limit、offset を利用します。

ConditionBeanのPaging

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

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" という文字列が含まれることです。(大文字小文字は区別せず)

OutsideSql - manualPaging().selectPage()
TypedParameterBean

更新ロックの取得

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にて未サポート ※そもそもシノニムの概念なし
プロシージャリターン
ResultSetパラメータ
ResultSetリターン
NotParamResult(Set)

本当のストアドプロシージャは？

PostgreSQL-11 からサポートされたストアドプロシージャは、DBFlute-1.2.5の時点で未対応です。 (いずれ procedure と function を両方ともしっかりサポートしたいと考えております)

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

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

this - 別スキーマの利用
別スキーマ(AdditionalSchema)

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

Catalog
利用不可 (メタデータとしてカタログ名が取得できないため)
Schema
サポート
Name
サポート
dfprop - プロシージャの自動生成対象プロパティ
Catalog(カタログ)

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

dfprop - additionalSchemaMap

データベース依存機能

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

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

"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" の利用を想定した "%%" 構文を使った全文検索の条件を指定できます。

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の基本仕様と同じく条件は無効になります。

ConditionBeanの使い方 - 基本仕様

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

DBFluteConfig - fullTextSearchOperand

DBMS独自の利用方法

別スキーマの利用

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

現場フィット - AdditionalSchema
追加スキーマ(AdditionalSchema)
別スキーマ(OtherSchema)
別カタログ(OtherCatalog)
別データベース(OtherDatabase)

大文字小文字を吸収する曖昧検索 (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)をインラインビューにする必要があります。

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

CustomizeEntityの対応テーブル

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

Behavior - 外だしSQLでもLoadReferrer

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 
}
# - - - - - - - - - -/
dfprop - initializeFirstSqlList

マテリアライズドビューを自動生成するには？

PostgreSQL のマテリアライズドビュー (Materialized View) 対応のクラスを自動生成する場合は、databaseInfoMap.dfprop の objectTypeTargetList にて、MATERIALIZED VIEW と定義をすると自動生成対象になります。 (他のDBMSだと、VIEWだけでマテビューも含まれたりしますが、PostgreSQLは区別されているようです) 

e.g. スキーマ初期化前に extension を drop @replaceSchemaDefinitionMap.dfprop
...
    ; variousMap = map:{
        # o objectTypeTargetList: (NotRequired - Default list:{TABLE;VIEW})
        #  If you want to include other object types in generating target,
        #  you should specify the list of included object types as adding.
        #    e.g. Synonym of Oracle --> list:{TABLE ; VIEW ; SYNONYM}
        #  This is only for the main schema. Additional schemas are unconcerned.
        #  However ReplaceSchema and Sql2Entity task also uses this.
        #  But you can set ReplaceSchema-original setting in its own dfprop.
        #
        ; objectTypeTargetList = list:{TABLE ; MATERIALIZED VIEW}
...

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';