Is there any way to do multiple inserts/updates in Slick?

Question

In sql we can do something like this:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Is there any way to do multiple/bulk/batch inserts or updates in Slick?

Can we do something similar, at least using SQL plain queries ?

Answer 1

For inserts, as Andrew answered, you use insertALL.

  def insertAll(items:Seq[MyCaseClass])(implicit session:Session) = {
    (items.size) match {
      case s if s > 0 =>
        try {
          // basequery is the tablequery object
          baseQuery.insertAll(tempItems :_*)
        } catch {
          case e:Exception => e.printStackTrace()
        }
      Some(tempItems(0))
        case _ => None
    }
  }

For updates, you're SOL. Check out Scala slick 2.0 updateAll equivalent to insertALL? for what I ended up doing. To paraphrase, here's the code:

  private def batchUpdateQuery = "update table set value = ? where id = ?"

  /**
   * Dropping to jdbc b/c slick doesnt support this batched update
   */
  def batchUpate(batch:List[MyCaseClass])(implicit session:Session) = {
    val pstmt = session.conn.prepareStatement(batchUpdateQuery)

    batch map { myCaseClass =>
      pstmt.setString(1, myCaseClass.value)
      pstmt.setString(2, myCaseClass.id)
      pstmt.addBatch()
    }

    session.withTransaction {
      pstmt.executeBatch()
    }
  }

Answer 2

In Slick, you are able to use the insertAll method for a Table. An example of insertAll is given in the Getting Started page on Slick's website.

http://slick.typesafe.com/doc/0.11.1/gettingstarted.html