java - mysql query in clause at playframework -

java - mysql query in clause at playframework -

in below 2 sql query sql1 not selecting row, , sql2 selecting 1 111@k2.com

var ids="'111@k2.com','222@k2.com','333@k2.com','444@k2.com','555@k2.com','666@k2.com'" val sql1 = sql("select id,point,privacy `pointtable` state=1 , id in ({users})").on("users" -> ids) sql1().map { row => val point = if (row[boolean]("privacy")) { row[double]("point").tostring } else { "0" } println(write(map("id" -> row[string]("id"), "point" -> point))) } val sql2 = sql("select id,point,privacy `pointtable` state=1 , id in (" + ids + ")") sql2().map { row => val point = if (row[boolean]("privacy")) { row[double]("point").tostring } else { "0" } println(write(map("id" -> row[string]("id"), "point" -> point))) }

in phpmyadmin when run query manualy returns 6 rows why not working here. using play framework 2.2 scala 2.1

that's not going work. passing users though on going escape entire string, it's going appear one value instead of list. anorm in play 2.3 allows pass lists parameters, here you'll have work around that.

val ids: list[string] = list("111@k2.com", "222@k2.com", "333@k2.com") val indexedids: list[(string, int)] = ids.zipwithindex // create bunch of parameter tokens in clause.. {id_0}, {id_1}, .. val tokens: string = indexedids.map{ case (id, index) => s"{id_${index}}" }.mkstring(", ") // create parameter bindings tokens val parameters = indexedids.map{ case (id, index) => (s"id_${index}" -> toparametervalue(id)) } val sql1 = sql(s"select id,point,privacy `pointtable` state=1 , id in (${tokens})") .on(parameters: _ *)

java mysql sql scala playframework