SqlServer PrepareStatement set parameters for SQL

问题:

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.

回答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...)?

回答2:

See PreparedStatement.setInt, which takes the parameter index and value:

private String sqlParams(int numParams) {
    StringBuilder sb = new StringBuilder();
    if(numParams <= 0) return sb.toString();
    for(int ctr = 0; ctr < numParams - 1; ++ctr) {
        sb.append("?,");
    }
    sb.append("?");
    return sb.toString();
}

/** In the method that accesses the DB... */
/** paramArray is an array of parameters for the IN clause. */
PreparedStatement preparedStatement = sqlObj.prepareStatement(
    "SELECT * FROM T_TABLE WHERE ID IN(" + sqlParams(paramArray.length) + ")");
for(int idx = 0; idx < paramArray.length; ++idx) {
    preparedStatement.setInt(idx, paramArray[idx]);
}
ResultSet rs = preparedStatement.executeQuery();