开发者

SQL : how to use IN keyword with prepared statement for range replacement using Java

String sql = "select * from file_repo_index where id in (?)";

PreparedStatement ps = conn.prepareStatement(sql);

ps.setString (1,开发者_运维问答 toCommaSeparatedList(repoIdList));     

ResultSet result = ps.executeQuery();

public static String toCommaSeparatedList(Collection col);

I have to use the query as

select * from file_repo_index where id in ( 1,2,3,4 )

But it gives following error in executeQuery() statement

java.sql.SQLException: Conversion failed when converting the nvarchar value '213304,213305,213307' to data type int.

I can use it like

String sql = "select * from file_repo_index where id in ("+toCommaSeparatedList(repoIdList)+")";

Statement ps = conn.createStatement();

ResultSet result = ps.executeQuery(sql);

But I want to use the PrepareStatment method. How can I do it. ??


You should create your prepare statement placeholders based on your values, for instance:

String sql = "select * from file_repo_index where id in (";
//append ?, in above sql in a loop
//Then prepare statement.

This will involve a bit of extra coding, but i think this is the only way to force using PreparedStatement.


If You want to use IN keyword then inside the brackets you have to put like

('213304','213305','213307')

as seperate variables and dont combine in a single "single quotes the entire values" and also in the select statement

 String sql = "select * from file_repo_index where id in ("+toCommaSeparatedList(repoIdList)+")";

Change the double quotes to single and try

('+ toCommaSeparatedList(repoIdList) +')";
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜