How to do batch insert use plain SQL in scala slick?

Question

This is my way to do batch insert in plain SQL in slick, which is kind of troublesome.

db withSession {
  (Q.u + "insert into customer (id, name, address) values " +
    users.map(toSql).mkString(",")).execute()
}

def toSql(user: User): String = "(%d, '%s', '%s')".format(user.id, user.name, user.address)

I'm wondering is there a better way to do batch insert/update in slick in plain SQL? I'm not a big fan of lifted or direct embedding, it seems Hibernate-ish to me.

I don't know your definition of Hibernate-ish, but in our opinion it is quite different. The lifted embedding provides you a type-safe, composeable(!), (domain-specific) functional programming language for writing queries. It needs a mental shift to fully embrace this, but after you do it feels like the way you should have been writing queries all along IMHO. Our Scala Days 2013 talk should help to convey the idea: http://www.parleys.com/play/51c2e20de4b0d38b54f46243/ — cvogt, Oct 10 '13 at 10:50
Chris, how's going? Don't know if you remember but we chatted at Scala Days 2013 and I was in your presentation, too. Lifted embedding has some cool ideas but enforce some constraints that I have to find ways hack around, for example, the case class fields and table columns are not 1 on 1 mapping, or to handle different database I have to extends two different Table (we use mysql in production and h2 for testing). Did I do anything wrong? As a slick contributor, what's your suggestions to the above problem? — Hung Lin, Oct 10 '13 at 15:00
[why comment has char limit??] To be honest, I still feel plain sql is more flexible and simple, it's string so not compiler checkable but I can use unit tests to check the sql statement. Does slick has (or plans to have) a way to do batch insert/update like Anorm? — Hung Lin, Oct 10 '13 at 15:04
Hung, on some levels I agree with the limitations of case classes, but mostly trying to get them to fit legacy systems where tables can be >22cols. However you can write your own functions to map to the projections in the table, so I'm not sure what you mean by "not 1 on 1 mapping". As for using two separate dbs, one of scala's BEST patterns is the cake pattern which you can use here. For an example of this, see: https://github.com/slick/slick-examples/blob/master/src/main/scala/com/typesafe/slick/examples/lifted/MultiDBCakeExample.scala — waffle paradox, Oct 10 '13 at 15:43
Hung, hi there, I didn't realize this was you. Of course I remember :). There are many people using h2 in testing and another in production. The referenced cake pattern is one way to do it. Similarly, you can put all tables in the same class/file instead to reduce boiler plate. Not sure what you mean by 1-on-1 mapping. Slick 2.0.0 will come with HLists, which lifts the 22 col limit. Mapping HLists >22 to case classes would require reflection I suppose :-\. — cvogt, Oct 11 '13 at 09:41
@waffle and Chris, thank you for the suggestions. I'll give them a try. — Hung Lin, Oct 13 '13 at 22:04
Please refer to this response for Slick 3.0: https://stackoverflow.com/questions/35001493/slick-3-0-bulk-insert-or-update-upsert — Leszek Gruchała, May 15 '18 at 12:07

score 3 · Answer 1 · answered Jul 03 '14 at 22:09

You can use SetParameter as Medina pointed out in the mailinglist (I asked a similar question):

import slick.jdbc.SetParameter
import slick.jdbc.PositionedParameters 

val params = Seq("foo", "bar", "baz")
implicit object SetSqlParamList extends SetParameter[Seq[String]] {
  override def apply(values: Seq[String], pp: PositionedParameters): Unit = values.foreach(v => pp.setString(v))
}

StaticQuery.query[List[String], User](
  s"""SELECT * FROM foo WHERE bar IN (${params.map(_ => "?").mkString(",")})"""
).list(params)

https://groups.google.com/d/msgid/scalaquery/cd028e33-42e8-4a26-85fc-e3b1b700e1f6%40googlegroups.com?utm_medium=email&utm_source=footer

How to do batch insert use plain SQL in scala slick?

1 Answers1