Unparsing With ANORM in Play Framework 2

I recently wrote about how to approach ANORM when parsing SQL results into domain model objects, in most cases you also want to write data to your relational database. The following is one way to do the reversed mapping in a general way and avoiding repeating yourself over and over again.

A basic ANORM inserts looks like this:

SQL("INSERT INTO my_table (col1, col2) VALUES ({id1}, {id2})")
  .on("id1" -> value1, "id2" -> value2)
  .execute() // or .executeInsert

In most simple cases we are not really interested in giving the prepared statement parameters ("id1", "id2") names, so lets use the column names for those:

SQL("INSERT INTO my_table (col1, col2) VALUES ({col1}, {col2})")
  .on("col1" -> value1, "col2" -> value2)
  .execute()

Hey, wait a minute, now we have a column name to value mapping as parameters to .on(...),lets extract that to a val:

// import anorm._
// provides an implicit conversion
// from (Any, Any) to (Any, ParameterValue[_])
val params: Seq[(Any, ParameterValue[_])] =
  Seq("col1" -> value1, "col2" -> value2)
SQL("INSERT INTO my_table (col1, col2) VALUES ({col1}, {col2})")
  .on(params: _*)
  .execute()

With the params as a key-value sec we can generate the (...) VALUES (...) part of the query like this:

def paramsToInsert(params: Seq[(Any, ParameterValue[_])]): Stri
ng =
  "(" + params.map(_._1).mkString(",") + ") VALUES " +
  "(" + params.map("{" + _._1 + "}").mkString(",") + ")"

Now we can create a generic insert function and a re-usable mapping of each domain model class:

/** generic insert */
def insert(table: String, ps: Seq[(Any, ParameterValue[_])]) =
DB.withConnection { implicit c =>
  SQL("INSERT INTO " + table + paramsToInsert(ps)).on(ps).execute
}
/** re-usable mapping */
def toParams(c: Customer): Seq[(Any, ParameterValue[_])] = Seq(
  "name" -> c.name,
  "company" -> c.company)
def insert(c: Customer) { insert("customer", toParams(c)) }

Of course we can abstract this further into some kind of generic abstract data access baseclass with all common ops and let the individual subclasses provide only the table name and the parameter mappings.

abstract class BaseDAO[A](tableName: String) {
  def toParams(a: A): Seq[(Any, ParameterValue[_])]
  /** really generic insert */
  def insert(a: A) { insert(tableName, toParams(a) }
  ...
}