CODEWITHSUNDEEP
sqlprepared-statement
nattu
nattu

Reputation: 149

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. ??

Upvotes: 0

Views: 2296

Answers (2)

Usman Saleem
Usman Saleem

Reputation: 1655

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.

Upvotes: 2

Sai Kalyan Kumar Akshinthala
Sai Kalyan Kumar Akshinthala

Reputation: 11844

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) +')";

Upvotes: 0

Related Questions