How to write sql with "in" clause ?

[Han-yanq]

for example , sql like this "select from table where id in (4,5,6,8) ". sql"""select from table where id in ($array) """ the array parameter is val array = conn.createArray.....
or val array = List[Int].mkString(",") but they all failed

[AugustNagro]

This is supported with the postgres module: https://github.com/AugustNagro/magnum#postgres-module

It's not something that can be supported generally. Many JDBC drivers don't support arrays in the IN clause.

An alternative (that Spring Data uses) is to dynamically generate '?' for each element of the array. So for

val array = List(1, 2, 3)
sql"""select * from table where id in ($array) """

They would generate

select * from table where id in (?, ?, ?)

There's two major problems with this approach:

1. DBs have limits on the number of params in an IN clause. For example, Oracle supports 2k parameters. I've personally had to fix a bug in production where the query worked fine in INT tests with a small parameter count, but then started failing when the production table grew larger then 2k rows.
2. Bad performance if the array size is dynamic because it prevents prepared statement caching (each query is different)

So, the solution is to

1. Use Postgres module if you can
2. Otherwise, interpolate each array element individually
3. Or finally, implement a DbCodec that does what Spring Data does.

[Han-yanq]

Unfortunately,I have to support two database at the same time. I finally find a simple "solution" to this problem. just use val arrays = SqlLiteral( listids.map(_.id).mkString(",") ) and the "In sql" work well in both database 💯 . I know there maybe have a bit of security risk, It seem that SqlLiteral did nothing with the parameter. so should we add a little security checking like replacing char [ ' ; ] etc in SqlLiteral class ?

[AugustNagro]

Unfortunately,I have to support two database at the same time.

Which two databases? It's possible that they both support arrays and we just need to implement a module like magnum-pg. I haven't checked for every DB.

so should we add a little security checking

SqlLiteral is not designed for untrusted input and I don't think we should try to do so.

[AugustNagro]

Hi @Han-yanq feel free to re-open if unresolved.