How to Retrieve The Primary Key When Saving a New Object in Anorm

How to Retrieve the Primary Key When Saving a New Object in Anorm

Use the executeInsert method instead of executeUpdate. Noted here, the foremer method returns Option[T] where T is the type of the primary key.

You can extract the value with a match statement:

    DB.withConnection { implicit connection =>
        SQL(...).executeInsert()
    } match {
        case Some(long) => long // The Primary Key
        case None       => ...
    }

Scala anorm retrieve inserted id

In the latest version you need a scalar:

   val newId = SQL(
      """
      insert into accom (places,lat,lon,permaname,country,defaultLanguage) 
      values ({places}, {lat}, {lon}, {permaname}, {country}, {defaultLanguage})
      """).on(
        'places -> places,
        'lat -> lat,
        'lon -> lon,
        'permaname -> permaname,
        'country -> country,
        'defaultLanguage -> defaultLanguage).executeInsert(scalar[Long].single)  

How do I cast Any returned from executeInsert in Anorm to Long

You get the Long id if you invoke map on the executeInsert result

DB.withConnection { implicit connection =>
   SQL("...").executeInsert().map(id => println(id))
}

Assigning new autoincrement id to returned SQL result in Anorm

Is Question a case class? You probably need to return this instead (as opposed to an instance of the same case class):

insertedId match {
  case Some(id) => question.copy(id = id)
  case _ => question 
}

Anorm Scala insert list of objects with nested list

I solved it with logic inside the db.withConnection block. Somehow I assumed that you had to have a single SQL statement inside db.withConnection, which turned out not to be true. So like this:

val idMap = scala.collection.mutable.Map[Long,Long]() // structure to hold map of old ids to new
db.withConnection { implicit conn =>
  // save all questions and gather map of the new ids to the old
  for (q <- questions) {
    val id: Long = SQL("INSERT INTO questions (titel) VALUES ({titel})")
      .on('titel -> q.titel)
      .executeInsert(scalar[Long].single)
    idMap(q.id) = id
  }

  // save answers with new question ids
  if (answers.nonEmpty) {
    for (a <- answers ) {
      SQL("INSERT INTO answers (question_id, text) VALUES ({qid}, {text});")
        .on('qid -> idMap(a.questionId), 'text -> a.text).execute()
    }
  }
}

What is purpose of anorm's Pk?

Pk allows you to specify a typed primary key.

Also, say you have a contrived model like this:

case class MyModel(id: Pk[Long], foo: String)

You have the benefit of constructing an instance of your model with:

MyModel(anorm.NotAssigned, "notKnownAtRuntime")

...if your database is responsible for generating your keys, or otherwise:

MyModel(anorm.Id(123L), "knownAtRuntime")

---

Related Topics