検索時にIN句の要素数が可変の場合の設定方法を知りたい

Question

###やりたいこと
・Java,Spring,Postgresqlを利用したWebシステムで、画面の検索フォームから入力した項目について検索を行いたい。
・フォーム上の入力項目について、Select部品の選択及びテキスト入力のあった項目のみを検索条件に設定したい。
・特定項目について、IN句で複数の項目値を検索条件に設定したい。

発生している問題・エラーメッセージ

IN句で設定している項目について、項目値を渡すMap内容が１件の場合は検索が動作するが、Map内に２件項目値が入るとエラーメッセージが出る。

2021-07-19 15:21:04.283 ERROR 9996 --- [nio-8080-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from XXXtable where 1=1 and(?='' or webacceptno like'%'||?||'%') and...(?, ?='0' or requestkbn IN(CAST(?, ? AS INTEGER))) order by ?]; nested exception is org.postgresql.util.PSQLException: ERROR: ","またはその近辺で構文エラー
位置: 311] with root cause

org.postgresql.util.PSQLException: ERROR: ","またはその近辺で構文エラー

sql="select * from XXXtable where 1=1"
+ " and(:acceptno='' or acceptno like'%'||:acceptno||'%')"
...
+ " and(:requestkbn='0' or requestkbn IN(CAST(:requestkbn AS INTEGER)))"
+ " order by :sort";

Set<Integer> rkKbnSet = new HashSet<>();
（画面からの区分をMapにセット...区分が"1"または"2"の場合は1または2(Integer)をセット。
　区分が"3"の場合は1,2をMapにセットする）

parameters = new MapSqlParameterSource("acceptno",AcceptNo)
...
.addValue("requestkbn", rkKbnSet).addValue("sort",sort);

List<Map<String, Object>> ret = npjdbcTemplate.queryForList(sql,parameters);

試したこと

対策を考える過程で、データ型指定を外した場合異なるエラーメッセージが出る。

2021-07-19 15:41:30.459 ERROR 9996 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [
select * from shinseiitirantable where 1=1 and(?='' or acceptno like'%'||?||'%') ... and(? is null or requestkbn IN(?)) order by ?
]; nested exception is org.postgresql.util.PSQLException: ERROR: パラメータ$***のデータ型が決定できません] with root cause

補足情報（FW/ツールのバージョンなど）

Java1.8
Spring Tool Suite 4 Version: 4.6.1.RELEASE
PostgreSQL 11.9

Answer 1

パラメータrequestkbnをカンマ区切り文字列に編集して、パラメータの値が1件の場合と複数件の場合の両方に対応しました。
（型キャストの問題は、カラムのデータ型の修正が効く段階だったのでデータ型をを文字列型に変更して対応しました。）

sql="select * from XXXtable where 1=1"

• " and(:acceptno='' or acceptno like'%'||:acceptno||'%')"

...

• " and(:requestkbn is null or requestkbn = ANY(string_to_array(:requestkbn,',')))"

Answer 2

rkKbnSet の要素数がふたつあるなら、 requestkbn IN(CAST(:requestkbn AS INTEGER))) の部分を requestkbn IN(CAST(:aa AS INTEGER), CAST(:bb AS INTEGER))) となるようなSQL文を組み立てて、値はの設定を .addValue("aa", rkKbnSet.get(0)).addValue("bb", rkKbnSet.get(1)) となるようにすればいいのでは。

上記は取り消し。内容を訂正します。

requestkbn IN(CAST(:requestkbn AS INTEGER)))の部分は、requestkbnに対応する値がひとつのときはrequestkbn IN(CAST(? AS INTEGER)))となり、requestkbnに対応する値がふたつあるとrequestkbn IN(CAST(?, ? AS INTEGER)))となる。これは、ログ出力の結果から。

エラーメッセージにもそのように出力されている... requestkbn IN(CAST(?, ? AS INTEGER))) order by ?]; nested exception is org.postgresql.util.PSQLException

Set<Integer> rkKbnSet = new HashSet<>();ということなので、設定する値はInteger型。であれば、キャストは不要なのでは?

だからrequestkbn IN(:requestkbn)とすればいいのではないかと。