Create a dynamic UPDATE query in Slick 3
I'm looking for a way to generate an UPDATE query on multiple columns that are only known at runtime.
For example, if given List[(String, Int)]
, how can I start generating a query in the form UPDATE <table> SET k1=v1, k2=v2, kn=vn
for all key / value pairs in the list?
I found that when using a single key / value pair, a simple SQL query can be generated as sqlu"UPDATE <table> SET #$key=$value
(where the key is from a trusted source to avoid injection), but I have not been successful in generalizing this to a list of updates without running the query for each.
Is it possible?
source to share
This is one way to do it. I am creating a table definition T here with table and column names (TableDesc) as implicit arguments. I would have thought it should be possible to set them explicitly, but I couldn't find it. For example, create instances of the query table, aTable and bTable. Then I insert and select some values, and at the end I update the value in the bTable.
import slick.driver.H2Driver.api._
import scala.concurrent.Await
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.duration.Duration
import scala.util.{Failure, Success}
val db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", "sa", "", null, "org.h2.Driver")
case class TableDesc(tableName: String, intColumnName: String, stringColumnName: String)
class T(tag: Tag)(implicit tableDesc: TableDesc) extends Table[(String, Int)](tag, tableDesc.tableName) {
def stringColumn = column[String](tableDesc.intColumnName)
def intColumn = column[Int](tableDesc.stringColumnName)
def * = (stringColumn, intColumn)
}
val aTable = {
implicit val tableDesc = TableDesc("TABLE_A", "sa", "ia")
TableQuery[T]
}
val bTable = {
implicit val tableDesc = TableDesc("TABLE_B", "sb", "ib")
TableQuery[T]
}
val future = for {
_ <- db.run(aTable.schema.create)
_ <- db.run(aTable += ("Hi", 1))
resultA <- db.run(aTable.result)
_ <- db.run(bTable.schema.create)
_ <- db.run(bTable ++= Seq(("Test1", 1), ("Test2", 2)))
_ <- db.run(bTable.filter(_.stringColumn === "Test1").map(_.intColumn).update(3))
resultB <- db.run(bTable.result)
} yield (resultA, resultB)
Await.result(future, Duration.Inf)
future.onComplete {
case Success(a) => println(s"OK $a")
case Failure(f) => println(s"DOH $f")
}
Thread.sleep(500)
I have a sleep statement at the end to assert that Future.onComplete gets the time before the application ends. Is there another way?
source to share