A Play Framework Anorm insert method that returns the database auto-insert ID

By Alvin Alexander. Last updated: April 22, 2018

As a quick example, if you need to see some code for a Play Framework Anorm method that inserts an object into a relational database table and returns the auto-into (MySQL auto_increment) ID field on success, here you go:

/**
 * This method returns the value of the auto_increment field from the MySQL `stocks` table
 * when the stock is inserted into the table.
 */
def insert(stock: Stock): Option[Long] = {
    val id: Option[Long] = DB.withConnection { implicit c =>
        SQL("insert into stocks (symbol, company) values ({symbol}, {companyName})")
            .on("symbol" -> stock.symbol.toUpperCase,
                "companyName" -> stock.companyName)
            .executeInsert()
        }
    id
}

I just got this code working with Scala 2.10.x, Play 2.2.x, and MySQL 5.something, so I know it works with those versions.

Necessary import statements

I don’t remember which Scala import statements are necessary for this code to work, but in my Stock model object I have these import statements:

import anorm._
import anorm.SqlParser._
import play.api.db._
import play.api.Play.current

The complete Play Framework model class

Okay, what the heck, here’s the complete Stock model class and object from my Play Framework project:

package models

case class Stock (val id: Long, 
                  var symbol: String, 
                  var companyName: String)

object Stock {
  
  import anorm._
  import anorm.SqlParser._
  import play.api.db._
  import play.api.Play.current

  // a parser that will transform a JDBC ResultSet row to a Stock value
  // uses the Parser API
  // http://www.playframework.org/documentation/2.0/ScalaAnorm
  // these names need to match the field names in the 'stocks' database table
  val stock = {
    get[Long]("id") ~ 
    get[String]("symbol") ~ 
    get[String]("company") map {
      case id~symbol~company => Stock(id, symbol, company)
    }
  }
  
  def selectAll(): List[Stock] = DB.withConnection { implicit c =>
    SQL("select * from stocks order by symbol asc").as(stock *)
  }
  
  /**
   * This method returns the value of the auto_increment field when the stock is inserted
   * into the database table.
   */
  def insert(stock: Stock): Option[Long] = {
    val id: Option[Long] = DB.withConnection { implicit c =>
      SQL("insert into stocks (symbol, company) values ({symbol}, {companyName})")
        .on("symbol" -> stock.symbol.toUpperCase,
            "companyName" -> stock.companyName)
        .executeInsert()
      }
    id
  }
  
  def delete(id: Long): Int = {
    DB.withConnection { implicit c =>
      val nRowsDeleted = SQL("DELETE FROM stocks WHERE id = {id}")
        .on('id -> id)
        .executeUpdate()
      nRowsDeleted
    }
  }

  def delete(symbol: String): Int = {
    DB.withConnection { implicit c =>
      val nRowsDeleted = SQL("DELETE FROM stocks WHERE symbol = {symbol}")
        .on('symbol -> symbol)
        .executeUpdate()
      nRowsDeleted
    }
  }
  

  /**
   * JSON Serializer Code
   * --------------------
   */
  import play.api.libs.json.Json
  import play.api.libs.json._

  implicit object StockFormat extends Format[Stock] {

      // convert from Stock object to JSON (serializing to JSON)
      def writes(stock: Stock): JsValue = {
          val stockSeq = Seq(
              "id" -> JsNumber(stock.id),
              "symbol" -> JsString(stock.symbol),
              "companyName" -> JsString(stock.companyName))
          JsObject(stockSeq)
      }

      // convert from a JSON string to a Stock object (de-serializing from JSON)
      def reads(json: JsValue): JsResult[Stock] = {
          val id = (json \ "id").as[Long]
          val symbol = (json \ "symbol").as[String]
          val companyName = (json \ "companyName").as[String]
          JsSuccess(Stock(id, symbol, companyName))
      }

  }

}

Summary

The primary purpose for this article was to share that insert method, so I won’t guarantee that the other methods work, but I’m pretty sure they do, especially the selectAll method, and the JSON serializing/de-serializing code.

If you needed to see a Play Framework Anorm “insert” method, I hope this has been helpful.

scala

auto_increment