Build dynamic UPDATE query in Slick 3

Question

I am looking for a way to generate an UPDATE query over multiple columns that are only known at runtime.

For instance, given a List[(String, Int)], how would I go about generating a query in the form of UPDATE <table> SET k1=v1, k2=v2, kn=vn for all key/value pairs in the list?

I have found that, given a single key/value pair, a plain SQL query can be built as sqlu"UPDATE <table> SET #$key=$value (where the key is from a trusted source to avoid injection), but I've been unsuccessful in generalizing this to a list of updates without running a query for each.

Is this possible?

score 0 · Answer 1 · answered Sep 25 '15 at 07:09

This is one way to do it. I create a table definition T here with table and column names (TableDesc) as implicit arguments. I would have thought that it should be possible to set them explicitly, but I couldn't find it. For the example a create to table query instances, aTable and bTable. Then I insert and select some values and in the end I update a 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've got the sleep statement in the end to assert that the Future.onComplete gets time to finish before the application ends. Is there any other way?

Build dynamic UPDATE query in Slick 3

1 Answers1