The Play Framework, MySQL, currency, decimal fields, and BigDecimal types

I’m currently working on a Play Framework server-side application that handles money/currency. (The application UI uses Sencha ExtJS, but that doesn’t matter for this example.)

From past experience I know that this means I need to use a decimal field in my MySQL database table. (MySQL also lets you declare this field type as numeric.) I further know that the MySQL JDBC driver uses a java.math.BigDecimal field to insert and select from this field type.

On the Play Framework side I want to use a Scala BigDecimal, so there needs to be a little conversion work between Java and Scala BigDecimal types. I won’t discuss this much, but the following code shows how I work with this situation in my Scala Play Framework application.

Play Framework controller

My Play Framework controller class is named Transactions, and looks like this:

package controllers

import play.api._
import play.api.mvc._
import views._
import models._
import scala.collection.mutable.ArrayBuffer
import play.api.libs.json._
import play.api.libs.json.Json
import play.api.libs.json.Json._
import  // needed for `of[Double]` in mapping (which i initially used)
import java.util.Calendar

object Transactions extends Controller {
  val transactionForm: Form[Transaction] = Form(
    // the names you use in this mapping (such as 'symbol') must match the names that will be
    // POSTed to your methods in JSON.
    // note: skipping `id` field
    // note: skipping `datetime` field
      // verifying here creates a field-level error; if your test returns false, the error is shown
      "symbol" -> nonEmptyText,
      "ttype" -> nonEmptyText,
      "price" -> bigDecimal,
      "quantity" -> number,
      "notes" -> text
      // transactionForm -> Transaction
      ((symbol, ttype, price, quantity, notes) => Transaction(0, symbol, ttype, price, quantity, Calendar.getInstance.getTime, notes))
      // Transaction -> TransactionForm
      ((t: Transaction) => Some(t.symbol, t.ttype, t.price.toDouble, t.quantity, t.notes))
   * The Sencha client will send me id, symbol, and companyName in a POST request.
   * I need to return something like this on success:
   *     { "success" : true, "msg" : "", "id" : 100 }
  def add = Action { implicit request =>
      errors => {
        Ok(Json.toJson(Map("success" -> toJson(false), "msg" -> toJson("Boom!"), "id" -> toJson(0))))
      transaction => {
        val id = Transaction.insert(transaction)
        id match {
          case Some(autoIncrementId) =>
              val result = Map("success" -> toJson(true), "msg" -> toJson("Success!"), "id" -> toJson(autoIncrementId))
          case None =>
              // TODO inserts can fail; i need to handle this properly.
              val result = Map("success" -> toJson(true), "msg" -> toJson("Success!"), "id" -> toJson(-1))


The important part of that code for this discussion is that the mapping deals with the price field as a Scala BigDecimal type.

Play Framework model class

My Play Framework model code looks like this:

package models

case class Transaction (
    id: Long,
    symbol: String,
    ttype: String,
    price: BigDecimal,
    quantity: Int,
    datetime: java.util.Date,
    notes: String

object Transaction {

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

  // a parser that transforms a JDBC ResultSet row to a Transaction value.
  // names in the `get` expressions need to match the database table field names.
  // `price` field comes back from the jdbc driver as a `java.math.BigDecimal`, so convert it to a
  // scala BigDecimal as needed.
  val transaction = {
    get[Long]("id") ~
    get[String]("symbol") ~
    get[String]("ttype") ~
    get[java.math.BigDecimal]("price") ~
    get[Int]("quantity") ~
    get[java.util.Date]("date_time") ~
    get[String]("notes") map {
      case id~symbol~ttype~price~quantity~datetime~notes => Transaction(id, symbol, ttype, BigDecimal(price), quantity, datetime, notes)

   * This method returns the value of the auto_increment field when the transaction is inserted
   * into the database table.
   * Note: Inserting `transaction.price` does not work, throws nasty exception; need to insert a Java BigDecimal.
  def insert(transaction: Transaction): Option[Long] = {
    val id: Option[Long] = DB.withConnection { implicit c =>
      SQL("insert into transactions (symbol, ttype, price, quantity, notes) values ({symbol}, {ttype}, {price}, {quantity}, {notes})")
        .on("symbol" -> transaction.symbol.toUpperCase,
            "ttype" -> transaction.ttype,
            "price" -> transaction.price.bigDecimal,  //converts to java.math.BigDecimal
            "quantity" -> transaction.quantity,
            "notes" -> transaction.notes
   * JSON Serializer Code
   * --------------------
  import play.api.libs.json.Json
  import play.api.libs.json._

  implicit object TransactionFormat extends Format[Transaction] {

      // convert from Transaction object to JSON (serializing to JSON)
      def writes(transaction: Transaction): JsValue = {
          val transactionSeq = Seq(
              "id" -> JsNumber(,
              "symbol" -> JsString(transaction.symbol),
              "ttype" -> JsString(transaction.ttype),
              "price" -> JsNumber(transaction.price),
              "quantity" -> JsNumber(transaction.quantity),
              "datetime" -> JsNumber(transaction.datetime.getTime),  // TODO verify
              "notes" -> JsString(transaction.notes)

      // convert from a JSON string to a Transaction object (de-serializing from JSON)
      def reads(json: JsValue): JsResult[Transaction] = {
          val id = (json \ "id").as[Long]
          val symbol = (json \ "symbol").as[String]
          val ttype = (json \ "ttype").as[String]
          val price = (json \ "price").as[BigDecimal]
          val quantity = (json \ "quantity").as[Int]
          val datetime = (json \ "datetime").as[java.util.Date]
          val notes = (json \ "notes").as[String]
          JsSuccess(Transaction(id, symbol, ttype, price, quantity, datetime, notes))


If you search for “BigDecimal” in that code, you’ll see that I convert from a Scala BigDecimal type to a Java BigDecimal in the insert method, using the bigDecimal method.

Again, I’m not going to discuss this much; I’m just sharing this code here so I can remember how I solved this problem today. There may be other bugs in this code, I don’t know, but I do know that the BigDecimal code works as desired.

As a final note, it’s important to say that if you don’t convert from a Scala BigDecimal to a Java BigDecimal during this process, you’ll generate an exception when trying to insert the data into the MySQL database table.

The MySQL database table

That last sentence reminded me that I should also share the code for my MySQL database table:

create table transactions (
  id int auto_increment not null,
  symbol varchar(10) not null,
  ttype char(1) not null,
  quantity int not null,
  price decimal(10,2) not null,
  date_time timestamp not null default now(),
  notes text,
  primary key (id)
) engine = InnoDB;

As you can see, the price field is declared as a decimal field.