java preparestatement oracle,Java中针对Oracle的准备语句问题

看到这份文件我有点吃惊。确实,不能像下面这样设置数组/集合(这与使用的数据库/jdbc驱动程序无关):

String sql = "SELECT col FROM tbl WHERE id IN (?)";

statement = connection.prepareStatement(sql);

statement.setArray(1, arrayOfValues); // Fail.

但文档中提到的查询应该可以工作。我可以从Oracle 10g Xe和

ojdbc14.jar

. 我怀疑要么是文档的作者把事情弄糊涂了,要么它实际上涉及到了另一个(老的?)DB和/或JDBC驱动程序的版本。

无论使用的JDBC驱动程序是什么(尽管您依赖于所使用的数据库in子句可以包含多少项,但Oracle(是的,再一次)的限制是1000项左右),都应该可以执行以下操作:

private static final String SQL_FIND = "SELECT id, name, value FROM data WHERE id IN (%s)";

public List find(Set ids) throws SQLException {

Connection connection = null;

PreparedStatement statement = null;

ResultSet resultSet = null;

List list = new ArrayList();

String sql = String.format(SQL_FIND, preparePlaceHolders(ids.size()));

try{

connection = database.getConnection();

statement = connection.prepareStatement(sql);

setValues(statement, ids.toArray());

resultSet = statement.executeQuery();

while (resultSet.next()) {

Data data = new Data();

data.setId(resultSet.getLong("id"));

data.setName(resultSet.getString("name"));

data.setValue(resultSet.getInt("value"));

list.add(data);

}

} finally {

close(connection, statement, resultSet);

}

return list;

}

public static String preparePlaceHolders(int length) {

StringBuilder builder = new StringBuilder();

for (int i = 0; i < length;) {

builder.append("?");

if (++i < length) {

builder.append(",");

}

}

return builder.toString();

}

public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {

for (int i = 0; i < values.length; i++) {

preparedStatement.setObject(i + 1, values[i]);

}

}