How to pass comma separated values in select statement

[PraveenAnekalmat]

Hi everyone,


String totalDbString = "ABC,DEF,GHI";
String queryString = "SELECT COUNT(DISTINCT(USER_ID)) FROM EDTM_USER_DETAILS WHERE USER_ID IN ( ? )";
PreparedStatement ps = oracon.prepareStatement( queryString );
ps.setString(1, totalDbString);
ResultSet rs = null;
rs = ps.executeQuery();
while (rs.next()) {
id = rs.getInt(1);
}

The count is coming 0 even it is greater than 0.
I need to perform this,
SELECT COUNT(DISTINCT(USER_ID)) FROM EDTM_USER_DETAILS WHERE USER_ID IN ( 'ABC','DEF','GHI')";
And above statement works fine with giving count greater than 0 .

How can i pass 'ABC','DEF','GHI' in ? as parameter. Please Help.

[manojdeoli]

I think when we use the setString() method, it'll put the quotes in the String you are passing in the query. e.g.
String totalDbString = "ABC,DEF,GHI";

It'll pass like 'ABC,DEF,GHI' , and will not give the expected result.

So we can use:
"SELECT COUNT(DISTINCT(USER_ID)) FROM EDTM_USER_DETAILS
WHERE USER_ID IN ( "+ totalDbString +")";

[nksol]

I think using string tokenizer, we can do easly.
devide the string with cama delimeter and add to sql statement