java 如何在 MySQL 中的 SQL 查询中将 ArrayList<> 作为 IN 子句传递
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35111475/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pass ArrayList<> as IN clause in SQL query in MySQL
提问by Maverick
I am using mySQL JDBC driver in my java program. I want to pass a ArrayList in the IN clause in my SQL query.
我在我的 java 程序中使用 mySQL JDBC 驱动程序。我想在我的 SQL 查询的 IN 子句中传递一个 ArrayList。
i did use a prepared statement like this, but this throws an
我确实使用了这样的准备好的语句，但这会引发
"java.sql.SQLFeatureNotSupportedException"exception
“java.sql.SQLFeatureNotSupportedException”异常
since mysql doesn't support this.
因为mysql不支持这个。
ArrayList<String> list = new ArrayList<String>();
PreparedStatement pstmt =
conn.prepareStatement("select * from employee where id in (?)");
Array array = conn.createArrayOf("VARCHAR", list.toArray());
pstmt.setArray(1, array);
ResultSet rs = pstmt.executeQuery();
Is there any other way to do this ? Maybe with Statement stmt.
有没有其他方法可以做到这一点？也许用声明 stmt。
回答by Andreas
Build the SQL statement with the correct number of markers, and set all the values.
使用正确数量的标记构建 SQL 语句，并设置所有值。
Beware: Databases have a limit to the number of parameters allowed, though it's very high for MySQL (65535).
注意：数据库对允许的参数数量有限制，但对于 MySQL ( 65535)来说非常高。
char[] markers = new char[list.size() * 2 - 1];
for (int i = 0; i < markers.length; i++)
markers[i] = (i & 1 == 0 ? '?' : ',');
String sql = "select * from employee where id in (" + markers + ")";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
int idx = 1;
for (String value : list)
stmt.setString(idx++, value);
try (ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
// code here
}
}
}
