38

I have written this code which works perfectly

class Items(tag: Tag) extends Table[Item](tag, "ITEMS") {
  def id = column[Long]("ITEMS_ID", O.PrimaryKey, O.AutoInc)
  def name = column[String]("ITEMS_NAME")
  def price = column[Double]("ITEMS_PRICE")
  def * = (id, name, price) <> ((Item.apply _).tupled, Item.unapply _)
}

object Shop extends Shop{
  val items = TableQuery[Items]
  val db = Database.forConfig("h2mem1")

  def create(name: String, price: Double) : Int = {
    val action = items ++= Seq(Item(0, name, price))
    val future1 = db.run(action)
    val future2 = future1 map {result => 
      result map {x => x}
    }
    Await.result(future2, Duration.Inf).getOrElse(0)
  }
}

This code works but the return value is number of records inserted. But I want to return the value of the AutoInc after the insert has been done.

i did google and found few articles

Slick 3.0.0 AutoIncrement Composite Key

Returning the auto incrementing value after an insert using slick

But somehow these do not answer the question cleanly.

Community
  • 1
  • 1
Knows Not Much
  • 30,395
  • 60
  • 197
  • 373

2 Answers2

68

Here's the relevant documentation page, according to which, you should construct a query like this:

val insertQuery = items returning items.map(_.id) into ((item, id) => item.copy(id = id))

def create(name: String, price: Double) : Future[Item] = {
  val action = insertQuery += Item(0, name, price)   
  db.run(action)
}
Andrii Abramov
  • 10,019
  • 9
  • 74
  • 96
dcastro
  • 66,540
  • 21
  • 145
  • 155
  • 4
    +1. I found that [Play-Slick-Silhouette](https://github.com/sbrunk/play-silhouette-slick-seed) Typesafe Activator template is a pretty good introduction how to use Slick 3.0 in a 'real-life' setting, for example [here](https://github.com/sbrunk/play-silhouette-slick-seed/blob/master/app/models/daos/PasswordInfoDAO.scala) and [here](https://github.com/sbrunk/play-silhouette-slick-seed/blob/master/app/models/daos/UserDAOImpl.scala), including the 'return assigned identity' problem. – Patryk Ćwiek Jul 16 '15 at 08:32
  • If I could give you an upvote of 100+ I would. This is probably the most concise answer to this question I have seen. – Vincil Bishop Sep 29 '15 at 18:38
  • 2
    this may be obvious, but I'm new in slick/scala, in what variable the autoincrement column is stored? how to get it? – ps0604 Jan 08 '16 at 11:19
  • 2
    @ps0604 The query above constructs an instance of `Item` with the (auto-incremented) id set to 0. When a row is inserted into the database, it creates a *copy* of this instance of `Item`, where the new copy has its id set to, for example, 8 instead of 0 - `(item, id) => item.copy(id = id)`. So, to answer your question, you'll find the autoincremented value in `item.id`. – dcastro Jan 08 '16 at 11:23
  • where exactly is `item.id`? I don't see any `item` object accessible after you run the action – ps0604 Jan 08 '16 at 12:26
  • 1
    @ps0604 `db.run` will return a `Future[Item]` (note the method's signature). After awaiting the future, you'll get an `Item`. – dcastro Jan 08 '16 at 13:30
  • 1
    It will only work if your table has less than 22 columns, because otherwise HLists will replace case classes and there will be no `copy` method. – JulienD Nov 09 '16 at 16:53
  • having the db.run(action) right there will break transactionality if there is a greater chain of db work going on: https://stackoverflow.com/questions/41219200/how-to-use-transaction-in-slick?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Andrew Norman Apr 25 '18 at 00:50
9

Try this one instead:

def create(name: String, price: Double): Future[Int] = db.run {
    (items returning items.map(_.id)) += Item(0, name, price)
}
aleck
  • 305
  • 4
  • 10