How to pass ArrayList<> as IN clause in SQL query in MySQL

Question

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<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.

score 6 · Accepted Answer · edited May 23 '17 at 11:52

6

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
        }
    }
}

edited May 23 '17 at 11:52

Community

1
1

answered Jan 31 '16 at 07:48

Andreas

154,647
11
152
247

1

That is so incredibly clunky and hacky...Not your solution, just the fact that that's pretty much the only way to do it in MySQL – Fueled By Coffee Jan 13 '18 at 17:39
1

@FueledByCoffee as in most other databases too. Dinosaur technology… – Holger Nov 09 '21 at 09:44

How to pass ArrayList<> as IN clause in SQL query in MySQL

1 Answers1

Linked

Related