- PR -

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

1
投稿者投稿内容
gotaro
会議室デビュー日: 2004/05/06
投稿数: 2
投稿日時: 2004-05-06 02:46
はじめまして

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が発生してしまいます

<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)
------------------------------------------------------------

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

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

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



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

<DButilアプリ主要部>----------------------------------------------
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));
でゅうく
大ベテラン
会議室デビュー日: 2003/11/30
投稿数: 129
投稿日時: 2004-05-06 07:09
一般的かどうかは分かりませんが。
引用:

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+")";
taku
ぬし
会議室デビュー日: 2002/11/12
投稿数: 918
お住まい・勤務地: 墨田区→中野区
投稿日時: 2004-05-06 09:24
引用:

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が発生してしまいます

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

コード:
//ＳＱＬ
ps = con.prepareStatement("select name, address from addrt where id in (?,?)");

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

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

//１と２の場合
ps.setString(1, "1");
ps.setString(2, "2");
gotaro
会議室デビュー日: 2004/05/06
投稿数: 2
投稿日時: 2004-05-10 01:43
ご指摘ありがとうございます。

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

引用:

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


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

引用:

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


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

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

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

以上、お礼&報告でした。
極東
会議室デビュー日: 2003/08/11
投稿数: 9
投稿日時: 2004-05-10 18:11
概念的に異なるような気が・・・。
?に対し渡すのは,で連結したSQL的な考え方ではなくて
objectの引渡しだと思うのですが。
インタフェース PreparedStatementで定義される
================================================================
setArray(int i, Array x)
指定されたパラメータを指定された Array オブジェクトに設定します。
================================================================
を使うべきなんじゃないでしょうか？

テストしたわけでもないので間違ってるかもしれませんが。
極東
会議室デビュー日: 2003/08/11
投稿数: 9
投稿日時: 2004-05-10 18:52
ちょっと調べてみたらPreparedStatementのsetArray(int i, Array x)はJDBC Feature Limitationsでした。
InterfaceのAPIだけしか見てなかったので・・・。

お騒がせしました。
小僧
大ベテラン
会議室デビュー日: 2005/06/24
投稿数: 122
投稿日時: 2007-08-30 11:14
こんにちは。

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

質問の主旨は
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 ]
かつのり
ぬし
会議室デビュー日: 2004/03/18
投稿数: 2015
お住まい・勤務地: 札幌
投稿日時: 2007-08-30 12:18
自分もリストの要素があれば、
要素数分プレースホルダを生成してから
ステートメントを作成しています。
そして、要素数分バインドします。

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

スキルアップ／キャリアアップ（JOB@IT）