SqlServer PrepareStatement set parameters for SQL 'in(?)' clause

Question

I have a sql which looks like this:

SELECT * FROM T_TABLE WHERE ID IN(?)

I wanna to set parameters for IN(?) via PrepareStatement. I think the desired method would looked like this:

prepareStatement.setList(1,Arrays.asList("1","2","3"));

But I don't know which method should I use to achieve this.

I have tried setArray method with the help from here and I got these error messages.

java.sql.SQLFeatureNotSupportedException:
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.createArrayOf(SQLServerConnection.java:2763)
    at com.newegg.ec.review.summary.dao.mssql.MSSQLReviewAccess.nextPage(MSSQLReviewAccess.java:165)

Does it mean that SqlService is not support createArrayOf method? And now, how can I achieve this? I don't like the way to join sql like this:

String sql = "SELECT * FROM T_TABLE WHERE ID IN("+in+")";

My code,

ps.setArray(1, conn.createArrayOf("VARCHAR",items.subList(1,9);

Any help would be appreciate.

Regards.

Answer 1

Basically what you are wanting to do is not directlypossible using a PreparedStatement.

Have a look at this wonderful article which shows other alternatives http://www.javaranch.com/journal/200510/Journal200510.jsp#a2

Alternatively you can use a createQuery statement which will accept an IN clause as per JPQL IN clause: Java-Arrays (or Lists, Sets...)?