QA@IT
この質問・回答は、@ITの旧掲示板からインポートされたものです。

PreparedStatementにリストを埋め込む方法について

はじめまして

PreparedStatementのプレースホルダに、","で区切ったリストを埋め込みたいのですが、
思い通りにゆかず困っております。

以下のようなソースがあって、
------------------------------------------------------------
import java.sql.*;

public class pptest{
public static void main(String ags[]){
PreparedStatement ps;
Connection con;
ResultSet rs;

    String str_name;
    String str_address;

    try{
        Class.forName("org.postgresql.Driver");
        con = DriverManager.getConnection("jdbc:postgresql:mydb",
                                          "user",
                                          ""
                                          );

        ps = con.prepareStatement("select name, address from addrt where id in (?)");
        ps.setString(1, ags[0]);
        rs = ps.executeQuery();

        while(rs.next()){
            str_name = rs.getString("name");
            str_address = rs.getString("address");

            System.out.println("name: "+str_name+" address: "+str_address);
        }

        rs.close();
        ps.close();
        con.close();
    }catch(Exception e){
        e.printStackTrace();
    }
}

}
------------------------------------------------------------

19行目のprepareStatement()のSQL中の"?"に、複数の値を入れてSELECTをかけたくて

$ java pptest "1,2" #<- "select name, address from addrt where id in (1,2)"としたい

とやってみたのですが、Exceptionが発生してしまいます

---------------------------------------------
org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: "1,2"

    at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
    at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:156)
    at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:101)
    at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
    at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
    at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:233)
    at pptest.main(pptest.java:21)

------------------------------------------------------------

引数を1つだけ("java pptest 1"など)としてやるとうまくゆくので、
このプレースホルダには整数型が1個しか入らないということは何となくわかるのですが、
こういうものなのでしょうか?
回避策等ご存じの方がいらっしゃいましたら、ご教授いただけると幸いです
(あるいはPostgres特有の問題なのか。。)

環境は、以下の通りです。
OS : Vine Linux 2.6
JDK : 1.4.2_04
DB : Postgres 7.4.2

よろしくお願いいたします。

## もともとはJakartaプロジェクトのdbutilを使ったアプリで上記のような
IN演算子に可変個のリストを入れたSQL文を発行したいというのが発端です。
こんな時一般的にはどうするものなのでしょうか。

----------------------------------------------
List rlist;
org.apache.commons.dbutils.QueryRunner runner;
String sql="select name, address from addrt where id in (?)";
String id_sel="1,2";
(中略)
rlist = (List)runner.query(sql,
id_sel,
new org.apache.commons.dbutils.handlers.BeanListHandle(XXXbean.class));

質問者:gotaro

回答

こんにちは。

大分前の話でアレですが、いつもお知恵ばかり拝借しているので
恩返しが出来れば、と思い回答してみることにしました。
( いらない?汗 )

質問の主旨は
PreparedStatement を用いた SQL で in 句を使う場合は?
と理解しています。

回答としては、
in 句で使用する検索文字列分、? ( プリペアードステートメント ) を記述する
となります。

多分、ソースを見ていただいた方が早いので、以下を参照下さい。



public void makeSql( ArrayList id ){

    StringBuffer questions = new StringBuffer();

    for ( int count = 0; count <  id..size(); count++){

        questions.append(",?");
    }

    sql = "select * from user where id in (" + questions.toString().replaceFirst(",","") + ")";

    setPreparedStatement(sql);

    for ( int count = 0; count < id..size(); count++){

        getPreparedStatement().setString(count + 1,  id.get(count).toString());
    }
}

最初の for 分で、検索対象分 ( ここでいうと ArrayList の id 分 )「?」を生成しています。
で、 sql String に他の SQL 分と結合して設定、PreparedStatement にセットしています。
次の for 分で PreparedStatement のパラメータに登録しています。

個人的には where 句の in は ? が一つで、PreparedStatement のパラメータに設定する際に
1,2,3・・・
とカンマ区切りで渡せば OK かと思っていましたので、ちょっとはまってしまいました。

宜しくお願い致します。

[ メッセージ編集済み 編集者: 小僧 編集日時 2007-08-30 11:15 ]

投稿者:小僧

編集 履歴 (0)

自分もリストの要素があれば、
要素数分プレースホルダを生成してから
ステートメントを作成しています。
そして、要素数分バインドします。

小僧さんと同じ方式ですね。

投稿者:かつのり

編集 履歴 (0)

一般的かどうかは分かりませんが。

gotaroさんの書き込み (2004-05-06 02:46) より:

String sql="select name, address from addrt where id in (?)";

String id_sel="1,2";

プレースホルダを使わないとか。



String id_sel="1,2"; 
String sql="select name, address from addrt where id in ("+id_sel+")";

投稿者:でゅうく

編集 履歴 (0)

gotaroさんの書き込み (2004-05-06 02:46) より:

19行目のprepareStatement()のSQL中の"?"に、複数の値を入れてSELECTをかけたくて

$ java pptest "1,2" #<- "select name, address from addrt where id in (1,2)"としたい

とやってみたのですが、Exceptionが発生してしまいます

 IN句の中身がそれほど数が多くないなら、
下記のような感じでよいのではないでしょうか?



//SQL
ps = con.prepareStatement("select name, address from addrt where id in (?,?)");

//1だけの場合
ps.setString(1, "1");
ps.setString(2, "1");

//2だけの場合
ps.setString(1, "2");
ps.setString(2, "2");

//1と2の場合
ps.setString(1, "1");
ps.setString(2, "2");

投稿者:taku

編集 履歴 (0)

ご指摘ありがとうございます。

プレースホルダは、SQL文中に文字列埋め込みをする、C言語の"%s"みたいなものだと思っていたのですが、プリコンパイル済みのSQLにSQL表現のvarcharなりfloatなりをセットするものらしく、プレースホルダに対して"1,2"のような形で埋め込むのは無理そう、というのがわかってきました。

プレースホルダを使わないとか。

ごもっともです。
ただ、そうするとソース中でSQLを組み立てることになりますよね。
できれば、SQLはpropertiesファイルに書くなどして外出しにして、
ソースではpropertiesを読んでデータを「埋め込むだけ」にしたいのです。
もっとも、"1,2,..."という文字列はソース中でループで組み立てることになるので、
変なこだわりなのかもしれませんが、、

 IN句の中身がそれほど数が多くないなら、

下記のような感じでよいのではないでしょうか?

あいにく、一覧で(かなり大量に)出した選択肢から、いくつでも複数選択可、
という処理で選択されたリストがここに入ってくるので、"?"の数を事前に決めることはできないのです。

結局、プレースホルダの代わりに、Cのsprintf()のjava版みたいなのを使おうかと思っています。
(ここで見つけました: http://java.sun.com/developer/technicalArticles/Programming/sprintf/))

プレースホルダの代わりに"%s"を置いて、そこに"1,2,...."という文字列を埋め込むという寸法です。
ただこれだと、SQLのワイルドカード"%"を"%%"としてエスケープしてやらねばならず、
あまりスマートな方法ではないようにも思うのですが。。

以上、お礼&報告でした。

投稿者:gotaro

編集 履歴 (0)

概念的に異なるような気が・・・。
?に対し渡すのは,で連結したSQL的な考え方ではなくて
objectの引渡しだと思うのですが。

インタフェース PreparedStatementで定義される

setArray(int i, Array x)

指定されたパラメータを指定された Array オブジェクトに設定します。

を使うべきなんじゃないでしょうか?

テストしたわけでもないので間違ってるかもしれませんが。

投稿者:極東

編集 履歴 (0)

ちょっと調べてみたらPreparedStatementのsetArray(int i, Array x)はJDBC Feature Limitationsでした。
InterfaceのAPIだけしか見てなかったので・・・。

お騒がせしました。

投稿者:極東

編集 履歴 (0)
ウォッチ

この質問への回答やコメントをメールでお知らせします。