DBFluteにおけるSQLのIn句のバインドにおける型のエラーついて（postgres利用時）

s.dot.s...@gmail.com

unread,

Nov 10, 2016, 8:35:57 PM11/10/16

to DBFluteユーザの集い

以前、

「データベースをOracleからPostgresに変更するにあたっての対応方法」

について、質問させていただ、杉本です。

長文になります、すみません。

利用しているDataBase：Postgres 9.2

利用しているDBFlute：0.9.6.9

Postgresのテーブルのカラムの型がInteger（int4）のカラムに対し

自動生成された In句指定を行うと、In句に渡すパラメータの型はCollection<Integer>に

なっていますが、SQLのプリペアステートメント？では

colum1 In ('1','2')

のように、文字列型となって実行され、Integer型に対し文字列を指定しているためcastエラーが発生します。

DBFlute Runtimeで、0.9.6、1.0.5Nどちらで試しても同じ挙動でした。

どちらのバージョンにおいても、jdbc.bat、generate.bat、sql2entity.batを実行し自動生成し直してます。

エラー内容は、後述参照。

実行するSQLの条件は、SQLファイルに記述せず、ConditionBeanで組み立ています。

DB管理ツールのPgAdminにおいてSQLバインドせずに、colum1 In ('1','2')のようにしても

SQLエラーにならず正常通りSQLが実行されるため、プリペアステートメントだと発生するのでは？と思います。

また、PostgresではVer8.2と8.3の間で型を厳密に扱うよう暗黙の型変換が廃止されています。

http://lets.postgresql.jp/documents/tutorial/cast/

http://qiita.com/6in/items/f23ead1314b9e6d2f2b7

を参考に、

-- 数値 <-> 文字列変換

CREATE CAST (int4 AS text) WITH INOUT AS IMPLICIT;

CREATE CAST (text as numeric) WITH INOUT AS IMPLICIT;

を、postgresのDBインスタンス（databaseでなくインスタンス単位）に実行すると

数値型のカラム対し、文字列型のIn句がバインド指定されてもSQLエラーは発生せず

正常に実行できるようになります。

DBFlute側で、シングルクォート''を補完している動きだと思うのですが、

その挙動を数値型ならば、シングルクォートで囲まないといった動きにする設定は

ありますでしょうか？もしくは、私の利用方法が間違っていますでしょうか？

なお、Oralce10gでは、暗黙の型変換され問題のSQLは正常に実行されます。

■In句に指定しているバインド変数用にセットする際のメソッド

public void setXxxId_InScope(Collection<Integer> xxxList) {

doSetXxxId_InScope(xxxList);

}

■DBのテーブル定義

[r_テーブル].[カラム1] :integer の定義です

■エラーログ、一部テーブル名やクラス名は加工しています

Throwable:org.seasar.dbflute.exception.SQLFailureException: Look! Read the message below.

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

The SQL failed to execute!

[Advice]

Please confirm the SQLException message.

[SQLState]

42883

[ErrorCode]

0

[SQLException]

org.seasar.framework.exception.SSQLException

[ESSR0072]SQLで例外(SQL=[select count(*)

from m_テーブル dflocal

left outer join m_スタッフテーブル dfrelation_0 on dflocal.xxxx = dfrelation_0.xxxx

where exists (select dfsublocal_0.xxxx

from r_テーブル dfsublocal_0

where dfsublocal_0.xxx = dflocal.xxx

and dfsublocal_0.カラム1 in (?)

)

and exists (select dfsublocal_0.xxx

from r_テーブル dfsublocal_0

where dfsublocal_0.xxx = dflocal.xxx

and dfsublocal_0.カラム1 in (?)

)

and dflocal.delete_date is null

and dflocal.status = ?], Message=[0], ErrorCode=42883, SQLState={3})が発生しました

[NextException]

org.postgresql.util.PSQLException

ERROR: operator does not exist: integer = character varying

ヒント: No operator matches the given name and argument type(s). You might need to add explicit type casts.

ポジション: 390

[Behavior]

XxxBhv.selectPage():count()...

[ConditionBean]

MXxxCB

[Statement]

org.seasar.extension.jdbc.impl.PreparedStatementWrapper

[Display SQL]

select count(*)

from m_テーブル dflocal

left outer join m_スタッフテーブル dfrelation_0 on dflocal.create_user = dfrelation_0.STAFF_ID

where exists (select dfsublocal_0.favorite_comment_id

from r_テーブル dfsublocal_0

where dfsublocal_0.xxx = dflocal.xxx

and dfsublocal_0.カラム1 in ('1')

)

and exists (select dfsublocal_0.favorite_comment_id

from r_テーブル dfsublocal_0

where dfsublocal_0.xxx = dflocal.xxx

and dfsublocal_0.カラム1 in ('15')

)

and dflocal.delete_date is null

and dflocal.status = 1

kubo

unread,

Nov 10, 2016, 9:58:51 PM11/10/16

to DBFluteユーザの集い

jfluteです

杉本さん、こんにちは

> colum1 In ('1','2')

取り急ぎ、SQLのログで表示されている値は、
あくまで表示用SQLとしての形式で、
http://dbflute.seasar.org/ja/manual/function/genbafit/implfit/displaysql/index.html
実際には、PreparedStatement のバインド変数として設定されます。

なので、クォートされていること自体はあまり関係ないはずですが、
Integer の InScope であれば、表示用SQLも (1, 2) と表示されるはずで、
PreparedStatementも setInt() を呼ぶはずなので、
そこがちょっと矛盾してますね。
(なぜか、内部的に文字列と判定されちゃっている!?!?)

ConditionBeanのコードも見せて頂けないでしょうか？

kubo

unread,

Nov 10, 2016, 10:13:02 PM11/10/16

to DBFluteユーザの集い

jfluteです

こちらのExampleで、
https://github.com/seasarorg/dbflute-example-database/tree/master/dbflute-postgresql-example

Integer や int4 のカラムに対して InScope やっても、
「XXX in (1 ,2)」となって正常に実行できるので、
何かしらそちらの環境で文字列として扱われてしまう設定が
入っちゃってるのかもしれません。
(こちらのPostgreSQLは 9.2 です)

MemberCB memberCB = new MemberCB();
memberCB.query().existsMemberSecurityAsOne(new SubQuery<MemberSecurityCB>() {
public void query(MemberSecurityCB subCB) {
subCB.query().setReminderUseCount_InScope(Arrays.asList(1, 2));
}
});
memberBhv.selectList(memberCB);
VendorCheckCB cb = new VendorCheckCB();
cb.query().setTypeOfInt4_InScope(Arrays.asList(1, 2));
vendorCheckBhv.selectList(cb);

とりあえず考えられるのは、
わざわざバインド変数をやめてquoteをtrueにしていれば、
in ('1', '2') という風に囲わせることはできますが、
この機能を使っているとはちょっと考えにくいので...
memberCB.embedCondition(newHashSet(MemberSecurityDbm.getInstance().columnReminderUseCount()),
true);

s.dot.s...@gmail.com

unread,

Nov 10, 2016, 11:36:01 PM11/10/16

to DBFluteユーザの集い

ご回答ありがとうございます。

先のConditionBeanのコードは別途レスします。

もっとも単純な１テーブルの１カラムに対しIn句を指定した場合で試してみました。

正常でした。

@Binding

private XxxBhv xxxBhv;

XxxCB cb = xxxBhv.newMyConditionBean();

cb.query().setCommentTypeId_InScope((Arrays.asList(1, 2)));

xxxBhv.selectList(cb);

上記を実行したところ

下記のコンソールログのSQLで、シングルクォートは存在しませんでした。

SQLエラーにもならず（cast関数は、postgresのインスタンスから削除済）

select （略）

from テーブル dflocal

where dflocal.comment_type_id in (1, 2)

こちらの環境の、特定の場合に発生していそうです。

2016年11月11日金曜日 12時13分02秒 UTC+9 jflute:

s.dot.s...@gmail.com

unread,

Nov 11, 2016, 12:43:10 AM11/11/16

to DBFluteユーザの集い

文字列になる要因が判明しました。

javaのオブジェクトのfinalと無名内部クラスでのoverrideが関係していそうです。

問題の発生しているときのロジック

XxxCB cb = xBhv.newMyConditionBean();

cb.query().existsXxxList(

new SubQuery<XxxCB>() {

@Override

public void query(XxxCB rcb) {

rcb.query().setCommentTypeId_InScope(xxxDto.course);

}

});

xxxDto.java

public List<Integer> course = new ArrayList<Integer>();

InScopeに指定するList<Integer>の型である、「xxxDto.course」を指定しているところが問題のようで

無名内部クラス？に渡す場合は、finalでないといけないのですが、Dto内のオブジェクトだと

finalでなくてもコンパイルエラーにはならない（？）。

finalでないと、↓のようなメッセージ(eclispeの場合）

Cannot refer to the non-final local variable 変数 defined in an enclosing scope

下記の例１，例２のように一時変数などにして、その変数をfinal属性をつけて

InScopeの引数に設定するとバインドされる値が数値のままとなります。

例１）

final List<Integer> list = new ArrayList<Integer>();

list.add(1);

list.add(2);

cb.query().existsXxxList(

new SubQuery<XxxCB>() {

@Override

public void query(XxxCB rcb) {

rcb.query().setCommentTypeId_InScope(list);

}

});

例２）

final List<Integer> course = xxxDto.course;

cb.query().existsXxxList(

new SubQuery<XxxCB>() {

@Override

public void query(XxxCB rcb) {

rcb.query().setCommentTypeId_InScope(course);

}

});

ロジックを追ったところ、以下が呼ばれているのですが、

AbstractConditionQuery#cTL

AbstractConditionQuery#convertToList

AbstractConditionQuery#filterRemoveNullOrEmptyValueFromList

このメソッド内での

final PROPERTY_TYPE element = ite.next();

if (element == null) {

continue;

}

if (element instanceof String) {

if (((String) element).length() == 0) {

continue;

}

newList.add(element);

elementがStringとして認識されていました。

なので、どこかの段階でStringに変わって？しまっていそうでした。

回避としては、一時変数で final List<Integer> course = xxxDto.course; として、そのfinal変数をInScopeに渡す対応かと思います。

2016年11月11日金曜日 11時58分51秒 UTC+9 jflute:

s.dot.s...@gmail.com

unread,

Nov 11, 2016, 1:01:16 AM11/11/16

to DBFluteユーザの集い

こちらでの環境は、Windows版で以下のバージョンです。

>java.exe -version

java version "1.6.0_45"

Java(TM) SE Runtime Environment (build 1.6.0_45-b06)

Java HotSpot(TM) 64-Bit Server VM (build 20.45-b01, mixed mode)

>AP tomcat

Starting Servlet Engine: Apache Tomcat/6.0.45

2016年11月11日金曜日 14時43分10秒 UTC+9 s.dot.s...@gmail.com:

文字列になる要因が判明しました。
javaのオブジェクトのfinalと無名内部クラスでのoverrideが関係していそうです。

ロジックを追ったところ、以下が呼ばれているのですが、

AbstractConditionQuery#cTL
AbstractConditionQuery#convertToList
AbstractConditionQuery#filterRemoveNullOrEmptyValueFromList
このメソッド内での
final PROPERTY_TYPE element = ite.next();
if (element == null) {
continue;
}
if (element instanceof String) {
if (((String) element).length() == 0) {
continue;
}
}
newList.add(element);

elementがStringとして認識されていました。
なので、どこかの段階でStringに変わって？しまっていそうでした。

デバッグしてみると、String としてではなく、Object型のリストでした。

ただ、

if (element instanceof String) {

は、trueになっています。

InScope(Arrays.asList(1,2) ) の場合だと　Integer[] となっています。

kubo

unread,

Nov 11, 2016, 5:59:57 AM11/11/16

to DBFluteユーザの集い

jfluteです

> InScopeに指定するList<Integer>の型である、
> 「xxxDto.course」を指定しているところが問題のようで

おおぉ、なかなか想像しづらい状況ですが、
実際にそうなるんですね。。。

> 無名内部クラス？に渡す場合は、finalでないといけないのですが、Dto内のオブジェクトだと
> finalでなくてもコンパイルエラーにはならない（？）。

Java的には、ローカル変数は final じゃないとコールバック内に変数を
渡せませんが、インスタンス変数であれば渡せます。
course というよりも xxxDto 自体がインスタンス変数なのでは
ないでしょうか？ (であれば、Javaとしては特に問題ない挙動です)

> なので、どこかの段階でStringに変わって？しまっていそうでした。

final のローカル変数に置いた場合だと、
前者existsメソッドを呼ぶ前の xxxDto.course インスタンスが、
InScopeに指定されているのに対して、
直接 xxxDto.course の場合だと、existsメソッドが呼ばれたときの
xxxDto.course インスタンスが指定されるとう違いがあります。

万が一、existsを呼び出す前に、
誰かが xxxDto.course を書き換えていたりすると、
違う結果になる可能性はありますが、
existsが呼び出されるのは Behavior の select メソッド内なので、
このあとすぐですからちょっと考えにくいですね。

一方で、Listの中身が文字列と認識されてしまっている事実があるので、
xxxDto.course の List の実体が List<String> なのではないか？
という疑問があります。
型宣言が List<Integer> だとしても、
JavaのGenericは実行時には消えますから、
リフレクションで突っ込めば List<Integer> の変数に List<String> を
入れることは可能です。Dtoのプロパティってリフレクションで
値を突っ込まれやすいものなので、時々そういうのを見かけます。

A. xxxDto.course のインスタンスはどこからどうやって設定されるか？
B. xxxDto.course の中身は List<String> になっているか？
for (Object obj : xxxDto.course) {
System.out.println(obj + " :: " + obj.getClass()); // これで何が出て来るか？
}

ただ、

final List<Integer> course = xxxDto.course;

とするだけで、中身のインスタンスの型が変わるとは思えないので、
ちょっと謎ですね...

kubo

unread,

Nov 11, 2016, 6:07:36 AM11/11/16

to DBFluteユーザの集い

jfluteです

とりあえず、こちらでの検証、
以下のコードで seaDto.course も localCourse も、
文字列の InScope になってしまいました。
仮に course の中身が List<String> だったとしても、
ローカル変数に置くことで回避ができるというのが謎で...

public class DemoTest extends UnitContainerTestCase {

@Resource
private MemberBhv memberBhv;

private SeaDto seaDto;

public void test_demo() {
seaDto = new SeaDto();
Field field =
DfReflectionUtil.getAccessibleField(SeaDto.class, "course");
DfReflectionUtil.setValue(field, seaDto, Arrays.asList("1", "2"));
log(seaDto.course);
for (Object obj : seaDto.course) {
log(obj + " :: " + obj.getClass());
}
final List<Integer> localCourse = seaDto.course;
for (Object obj : localCourse) {
log(obj + " :: " + obj.getClass());
}
MemberCB cb = new MemberCB();
cb.query().existsPurchaseList(new SubQuery<PurchaseCB>() {
public void query(PurchaseCB subCB) {
subCB.query().setPurchaseCount_InScope(seaDto.course);
subCB.query().setPurchasePrice_InScope(localCourse);
}
});
memberBhv.selectList(cb);
}

public static class SeaDto {

public List<Integer> course;
}
}

2016-11-11 20:03:48,475 [main]-DEBUG (PlainTestCase#log():616) - [1, 2]
2016-11-11 20:03:48,475 [main]-DEBUG (PlainTestCase#log():616) - 1 ::
class java.lang.String
2016-11-11 20:03:48,475 [main]-DEBUG (PlainTestCase#log():616) - 2 ::
class java.lang.String
2016-11-11 20:03:48,475 [main]-DEBUG (PlainTestCase#log():616) - 1 ::
class java.lang.String
2016-11-11 20:03:48,476 [main]-DEBUG (PlainTestCase#log():616) - 2 ::
class java.lang.String
2016-11-11 20:03:48,711 [main]-DEBUG (XLog#log():43) - ...Initializing
sqlExecution for the key 'MEMBER:selectList(MemberCB):Member'
2016-11-11 20:03:48,877 [main]-DEBUG (XLog#log():43) - SqlExecution
Initialization Cost: [00m00s165ms]
2016-11-11 20:03:48,886 [main]-DEBUG (XLog#log():43) -
/===========================================================================
2016-11-11 20:03:48,887 [main]-DEBUG (XLog#log():43) -
MemberBhv.selectList()
2016-11-11 20:03:48,888 [main]-DEBUG (XLog#log():43) -
=====================/
2016-11-11 20:03:48,888 [main]-DEBUG (XLog#log():43) -
DemoTest.test_demo():46 -> ...
2016-11-11 20:03:49,158 [main]-DEBUG (QLog#log():43) -
select dfloc.MEMBER_ID as MEMBER_ID, dfloc.MEMBER_NAME as MEMBER_NAME,
dfloc.MEMBER_ACCOUNT as MEMBER_ACCOUNT, dfloc.MEMBER_STATUS_CODE as
MEMBER_STATUS_CODE, dfloc.FORMALIZED_DATETIME as FORMALIZED_DATETIME,
dfloc.BIRTHDATE as BIRTHDATE, dfloc.REGISTER_DATETIME as
REGISTER_DATETIME, dfloc.REGISTER_USER as REGISTER_USER,
dfloc.UPDATE_DATETIME as UPDATE_DATETIME, dfloc.UPDATE_USER as
UPDATE_USER, dfloc.VERSION_NO as VERSION_NO
from MEMBER dfloc
where exists (select sub1loc.MEMBER_ID
from PURCHASE sub1loc
where sub1loc.MEMBER_ID = dfloc.MEMBER_ID
and sub1loc.PURCHASE_COUNT in ('1', '2')
and sub1loc.PURCHASE_PRICE in ('1', '2')
)
2016-11-11 20:03:49,187 [main]-DEBUG (XLog#log():43) - ===========/
[00m00s297ms (0)]

s.dot.s...@gmail.com

unread,

Nov 13, 2016, 9:44:04 PM11/13/16

to DBFluteユーザの集い

杉本です。

申し訳ありません。

先のローカル変数宣言すると、問題ないというのは誤りでした。

final List<Integer> course = xxxDto.course;

においても、同様のSQLエラーとなりました。

setCommentTypeId_InScope(Arrays.asList(1, 2))にしたときか

Listが空っぽの場合と混同していたかもしれません。

cb.query().setCommentTypeId_InScope( ~ ) でセットする前に

型を確認したところ

for (Object obj : xxxDto.course) {

System.out.println("dot list<Integer>の宣言 " + obj + " :: " + obj.getClass());

}

dto list<Integer>の宣言 1 :: class java.lang.String

dto list<Integer>の宣言 2 :: class java.lang.String

となり、String型でした。

また、ローカル変数finalに代入した場合のときも

final List<Integer> course = xxxDto.course;

for (Object obj : course) {

System.out.println("ローカル変数final list<Integer>の宣言 " + obj + " :: " + obj.getClass());

}

ローカル変数final list<Integer>の宣言 1 :: class java.lang.String

ローカル変数final list<Integer>の宣言 2 :: class java.lang.String

となり、String型でした。

>型宣言が List<Integer> だとしても、

>JavaのGenericは実行時には消えますから、

>リフレクションで突っ込めば List<Integer> の変数に List<String> を

>入れることは可能です。Dtoのプロパティってリフレクションで

>値を突っ込まれやすいものなので、時々そういうのを見かけます。

まさしく、この通りだと思います。

型宣言ばかり見ていて、その型が維持されていると思ってました。

>A. xxxDto.course のインスタンスはどこからどうやって設定されるか？

SAStrutsを使っており、

ActionFormをInstanceType.SESSIONとして、formのプロパティとしてxxxDtoを保持しており

以下のようなソースの構造なのですが、Actionクラスのメソッド呼び出し後に、既にListの中身は

String型になっていました。

XxxForm.java{

public XxxDto xxxDto

public void resetCheckBox() {

xxxDto.reset();

}

XxDto.java{

public List<Integer> course = new ArrayList<Integer>();

public void reset() {

course = new ArrayList<Integer>();

}

XxxAction.java {

@Execute(validator = false, reset = "resetCheckBox")

public String search() {

for (Object obj : commentForm.commentSearchConditionDto.nutritionFullCourse) {

System.out.println("search メソッド dto list<Integer>の宣言 " + obj + " :: " + obj.getClass());

★ここで既にString型でした

}

この後に、ロジックでDBFluteでクエリーを実行

return "list.jsp";

}

httpリクエストパラメータは以下です（courseはStringの配列です）

[param]SAStruts.method=search

[param]xxxDto.course=1, 2　　　★（dtoにセットされる値）

DBFluteの問題ではなく、SAStrustとdtoやら、別の問題のため

以下で対応していこうと思います。

・どこかでListを詰め替える

・Dtoの渡し方法を変えて別のDtoに自前で詰め直し

・postrgres側でcastを定義する

よろしくお願いします。

Reply all

Reply to author

Forward