PostgreSQLの取扱い
- 基本情報
- データベース接続設定
- データ型マッピング
- 主キーでの自動採番
- ページング検索の条件
- 更新ロックの取得
- プロシージャ
- データベース依存機能
- DBMS独自の利用方法
- DBMS独自の注意点
- Exampleのススメ
- DBFlute内部での取扱い
- PostgreSQL補足資料
基本情報
- 対応バージョン
- PostgreSQL 8.1 以上
- JDBCドライバの同梱
- あり
- (同梱)JDBCドライバ
- postgresql-9.4-1212.jre6.jar
もし認証エラーが発生した場合
自動生成時に、"認証型 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
}
データ型マッピング
データベース上のデータ型とプログラム型との(デフォルトの)マッピングについて。
- 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) ※未検証
主キーでの自動採番
自動採番(連番)の仕組みとして シーケンス を利用します。
serial 型と bigserial 型は設定レス
通常シーケンスは明示的にテーブルとの関連付けをDBFluteプロパティに設定する必要がありますが、serial 型および bigserial 型を利用する場合は、DBFluteが その関連付けを自動判別 しているので設定は不要です。
ページング検索の条件
limit、offset を利用します。
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にて未サポート ※そもそもシノニムの概念なし
本当のストアドプロシージャは?
PostgreSQL-11 からサポートされたストアドプロシージャは、DBFlute-1.2.5の時点で未対応です。 (いずれ procedure と function を両方ともしっかりサポートしたいと考えております)
別スキーマのプロシージャ
別スキーマのプロシージャもオプションで自動生成できます。
自動生成対象プロパティの有効項目
- 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
}
# - - - - - - - - - -/
マテリアライズドビューを自動生成するには?
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内部での取扱い
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';