preparedstatement with list of parameters for sql IN operator

Question

I have an sql statement in an external properties file so it cannot be modified dynamically as some have suggested:

query = select distinct BSG_HANDLE FROM MYUSER.MYTABLE WHERE RATE_CODE IN (?) AND CORP = ? 

A SOAP request provides the array of strings that will be used to populate the IN values.

public BsgHandleResponse getBsgHandleByRateCode(
@QueryParam("rateCodes") String rateCodes,
@QueryParam("corp") String corp)

Then I put them into the prepared statement like this:

ps = startTimedPreparedStatement(conn,SQL_FROM_PROPERTIES_FILE);
ps.setString(1, rateCodes);
ps.setString(2, corp);
rs = ps.executeQuery();

It works fine if rateCodes is a single string value like this:

25

but fails for multiple values like this:

25, 1P

I assume I have to do something like this instead

    public BsgHandleResponse getBsgHandleByRateCode(
    @QueryParam("rateCodes") final List<String> rateCodes,
    @QueryParam("corp") String corp)

    ps = startTimedPreparedStatement(conn,SQL_FROM_PROPERTIES_FILE);
    ps.setArray(1, rateCodes);
    ps.setString(2, corp);
    rs = ps.executeQuery();

But I can't get it to work. Any ideas?

Answer 1

PreparedStatement question in Java against Oracle

Solution mention by @DJ works great but you have to take care of one edge case that there is limit on number of bind values you can have use with IN clause. For oracle i think is close to 1K or 4K.