mysql sql语句 gt lt_如何传递ArrayList<>作为MySQL中SQL查询的IN子句

I am using mySQL JDBC driver in my java program. I want to pass a ArrayList in the IN clause in my SQL query.

i did use a prepared statement like this, but this throws an

"java.sql.SQLFeatureNotSupportedException"exception

since mysql doesn't support this.

ArrayList list = new ArrayList();

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.

解决方案

Build the SQL statement with the correct number of markers, and set all the values.

Beware: Databases have a limit to the number of parameters allowed, though it's very high for 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

}

}

}