概要
JDBC（Java）から検索結果のメタ情報を取得する場合、ResultSetMetaDataを使用します。
検索結果のカラムの桁精度は、ResultSetMetaData#getScale()で取得することができます（サンプルコード）。
しかし、以下の両方の条件に当てはまる場合に取得できる桁精度の値が、データ型によって固定された値になってしまいます（例：timestamp型なら6、numeric型なら0 など）。
- データベースがPostgreSQLである。
- SELECT句で取得する値がFUNCTIONの戻り値である。
この記事では、FUNCTIONの戻り値の桁精度をJDBCで取得するための方法を紹介します。
経緯
弊社製品1は、データベースに長らくOracleを使用しています。
また、2020年にリリースされたバージョンから、Oracleに加えてPostgreSQLも使用できるように改善しました。
弊社製品は、SQL実行などのDBアクセスに関する処理を、自社開発しているフレームワークで実装しています。
一般的なORM（Object Relational Mapping）などのフレームワークは利用していません。
フレームワークでは、業務ロジックで利用する変数の型とDB上のカラムの型を対応させて、変数の値を制御しています。
そのため、SELECT文を実行した場合は、SELECT句のカラムの型から、値を格納するJavaの変数の型を適切に判断する必要があります。
また、OracleとPostgreSQLの仕様差異は、Oracleの動作に合わせるという開発方針を採用しました2。
このため、後述する日時や時刻を扱うデータ型については、必ずしも最適とは言えないデータ型を採用せざるを得ないという背景があります。
日付や時刻を扱うデータ型について
弊社製品では、で日付や時刻を格納するデータ型として、以下の2種類の型を使用しています。
| システムで扱うデータ型 | Oracle | PostgreSQL |
|---|---|---|
| 年月日時分秒 | DATE | timestamp(0) |
| 年月日時分秒（ミリ秒まで） | timestamp(3) | timestamp(3) |
PostgreSQLには、「年月日時分秒」に完全に一致するデータ型はありません（「DATE」型は「年月日」であり、時分秒の値を保持しません）。
そのため、PostgreSQL環境では「年月日時分秒」と「年月日時分秒（ミリ秒まで）」のどちらもtimestamp型を使用する方針にしました。
データベースにアクセスするフレームワークでは、SQLの実行結果の型を判断し、値を格納するJavaの変数の型を決定する必要があります。
実テーブルをSELECTする場合など、ほとんどの場合は上記のデータ型の方針で問題はおきません。
SELECTしたカラムの型がtimestampであった場合、桁精度を判定することで、「年月日時分秒」と「年月日時分秒（ミリ秒まで）」を区別することができます。
しかし、SELECT句に「FUNCTIONの実行」が含まれている場合に問題が発生します。
概要に記載したとおり、PostgreSQLを使用した場合、JDBCでFUNCTIONの戻り値の桁精度を正確に取得することができません。
そのため、戻り値の型がtimestampであった場合、timestamp(0)なのかtimestamp(3)なのか判断することができません。
PostgreSQLにおけるFUNCTIONの検証
例として、以下のようなFUNCTIONを作成します。
CREATE OR REPLACE FUNCTION test_func01()
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS
$BODY$
DECLARE
BEGIN
RETURN CURRENT_TIMESTAMP::TIMESTAMP(0) WITHOUT TIME ZONE;
END;
$BODY$
LANGUAGE plpgsql
;
このFUNCTION定義をpsqlで確認すると以下のようになります。
test01db=> \df test_func01
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-------------+-----------------------------+---------------------+------
test01 | test_func01 | timestamp without time zone | | func
(1 row)
FUNCTIONからはTIMESTMP(0)にキャストした値をリターンしています。
しかし、Result data typeをみると、FUNCTIONのRETURNに指定した精度情報は無視され、精度なしの型情報のみが定義されています。
FUNCTIONの定義を以下のように変更し、リターンに精度情報を指定しても結果は変わりません。
FUNCTIONの定義
CREATE OR REPLACE FUNCTION test_func02()
RETURNS TIMESTAMP(0) WITHOUT TIME ZONE
AS
$BODY$
DECLARE
BEGIN
RETURN CURRENT_TIMESTAMP::TIMESTAMP(0) WITHOUT TIME ZONE;
END;
$BODY$
LANGUAGE plpgsql
;
定義情報の確認
test01db=> \df test_func02
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-------------+-----------------------------+---------------------+------
test01 | test_func02 | timestamp without time zone | | func
(1 row)
解決方法
解決方法は大きく２とおりの方法があります。
- SELECT文でキャストする
- VIEWを経由してFUNCTIONの値を取得する
「SELECT句でキャストする」方法
SELECT句で取得するカラムをキャストすると、FUNCTIONの実行結果を取得する場合でも桁精度を取得することができます。
例えば以下のようなSQLにすることで、func01_valの桁精度を取得することができるようになります。
SELECT CAST(test_func01() AS timestamp(0)) as func01_val;
この方法のメリットは、後述するVIEWを利用する方法と比較して、課題解決のためにDBオブジェクトを作成する必要がないという点です。
対応法もシンプルでわかりやすいのも良い点です。
デメリットは修正箇所が多くなる点です。
FUNCTIONを使用しているSELECT句を漏れなく洗い出して修正する方法は、コード量の観点から実現が難しいかもしれません。
「VIEWを経由してFUNCTIONの値を取得する」方法
FUNCTIONを実行するVIEWを定義して、VIEWをSELECTするSQLにすると桁精度を取得することができます。
例えば以下のようなVIEWを定義します。
CREATE OR REPLACE VIEW func_view(
func01_column
)
AS
SELECT
test_func01()::timestamp(0) without time zone
;
VIEWをSELECTするSQLを実行することで、VIEWを経由してtest_func_01の桁精度を取得することができます。
SELECT func01_column FROM func_view;
この方法は、上述したキャストする方法と比較して、VIEWを作成する必要がある点がデメリットです。
また、FUNCTIONとVIEWではSQLの書き方が変わってしまう点も注意が必要です。
実行するSQLの文字列は、キャストする必要がない分、簡潔なSQLになる点が良い点です。
例えばログに出力されたSQL文を確認する場合を想定すると、SELECT句にtimestamp型のカラムが多いほど、SQL文の可読性に差が出てきます。
まとめ
この記事では、FUNCTIONの戻り値の桁精度をJDBCで取得するための方法として２つの方法を紹介しました。
- SELECT文でキャストする
- VIEWを経由してFUNCTIONの値を取得する
弊社では、VIEWとFUNCTIONではSQL文の記述方法が異なるため、ORACLE版と合わせることが難しいといった理由から「1」の方法を採用しました。
DBアクセス用のフレームワークを自社開発している環境では、JDBCという共通の仕様が定義されていても実際の動作は実行時のDB製品によるといった点を検討する必要があります。
同じような状況下で開発する際に、この記事の内容が参考の一つとなれば幸いです。
【補足】JDBCで型精度情報を取得するサンプル
JDBCで型精度情報は以下のようなコードで取得することができます。
// 変数connectionは、DBに接続済みのjava.sql.Connectionのインスタンスです。
String sql = "SELECT CAST(test_func01() AS timestamp(0)) as func01_val";
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
rs.next();
ResultSetMetaData meta = rs.getMetaData();
String scaleStr = String.format(
"column scale [%d]",
meta.getScale(1));
System.out.println(scaleStr); // column scale [0]