scala - Is it possible to use IN clause in plain sql Slick for integers? -

there similar question here doesn't answer question.

is possible use in clause in plain sql slick?

note part of larger , more complex query, need use plain sql instead of slick's lifted embedding. following good:

val ids = list(2,4,9) sql"select * coffee id in ($ids)" 

the sql prefix unlocks stringcontext can set sql parameters. there no sql parameter list, can end opening sql injection here if you're not careful. there (and dangerous) suggestions dealing problem sqlserver on this question. have few options:

your best bet use #$ operator mkstring interpolate dynamic sql:

val sql = sql"""select * coffee id in (#${ids.mkstring(",")})""" 

this doesn't use parameters , therefore might open sql-injection , other problems.

another option use regular string interpolation , mkstring build statement:

val query = s"""select * coffee id in (${ids.mkstring(",")})""" staticquery.queryna[coffee](query) 

this same approach using #$, might more flexible in general case.

if sql-injection vulnerability major concern (e.g. if elements of ids user provided), can build query parameter each element of ids. you'll need provide custom setparameter instance slick can turn list parameters:

implicit val setstringlistparameter = new setparameter[list[string]]{     def apply(v1: list[string], v2: positionedparameters): unit = {         v1.foreach(v2.setstring)     } }  val idsinclause = list.fill(ids.length)("?").mkstring("(", ",", ")") val query = s"""select * coffee id in ($idsinclause)""" q.query[list[string], string](query).apply(ids).list(s) 

since ids ints, less of concern, if prefer method, need change setstringlistparameter use int instead of string: