To illustrate the usage of Anorm, we are going to make a small Play example that connects to the existing CustomerDB database from the NetBeans distribution that we have used in the previous section and introduced in Chapter 2, Code Integration.

The most straightforward way to start is to create a default Play Scala project from a terminal window by entering the following command:

> play new anormsample

Once created and imported into Eclipse (after creating Eclipse-related files once again using the > play eclipse command; refer to Chapter 5, Getting Started with the Play Framework, if you need more details) we can see that the dependency to Anorm is already part of the built.sbt file. However, we need to add the dependency to the derby-client database driver to this file to be able to communicate with the database through jdbc. The dependency can be added as follows:

libraryDependencies ++= Seq(
  jdbc,
  anorm,
  cache,
  "org.apache.derby" % "derbyclient" % "10.8.1.2"
)  

We can now define a Customer case class that will represent the CUSTOMER table from the database and implement some behaviors in the form of methods defined in its companion object, as follows:

package models

import play.api.db._
import play.api.Play.current
import anorm._
import anorm.SqlParser._
import scala.language.postfixOps

case class Customer(id: Pk[Int] = NotAssigned, name: String)

object Customer {
  /**
   * Retrieve a Customer from an id.
   */
  def findById(id: Int): Option[Customer] = {
    DB.withConnection { implicit connection =>
      println("Connection: "+connection)
      val query = SQL("SELECT * from app.customer WHERE customer_id = {custId}").on('custId -> id)
      query.as(Customer.simple.singleOpt)
    }
  }

  /**
   * Parse a Customer from a ResultSet
   */
  val simple = {
    get[Pk[Int]]("customer.customer_id") ~
    get[String]("customer.name") map {
      case id~name => Customer(id, name)
    }
  }
}

The Anorm SQL query conforms to a string-based SQL statement where variables are bound to values. Here we bind the customer_id column to the id input parameter. Since we want to return an Option[Customer] to handle the case where the SQL query did not return any result, we first need to parse the ResultSet object to create a Customer instance and invoke the singleOpt method that will make sure we wrap the result into an Option (which can return None instead of a potential error).

The Application controller is given as follows:

package controllers

import play.api._
import play.api.mvc._
import play.api.db._
import play.api.Play.current
import models._

object Application extends Controller {
  def index = Action {
    val inputId = 2  //  Hardcoded input id for the example
    val result = 
      DB.withConnection { implicit c =>
        Customer.findById(inputId) match {
          case Some(customer) => s"Found the customer: ${customer.name}"
          case None => "No customer was found."
      }
    }
    Ok(views.html.index(result))
  }
}

It simply surrounds the database query with a database connection and does some pattern matching on the Option[Customer] entity to display different messages whether the queried customer id is found or not.

You may have noticed the keyword, implicit, sometimes while reading the Scala code in general (such as the implicit c parameter given in the previous code example). As clearly explained in the Scala documentation:

In our previous case, we could have omitted this implicit parameter since we are not using the database connection c variable further in the body of our method.

Running the application with inputId=2 can be replaced by inputId=3000; for example, to demonstrate the case where no customer is found. To avoid changing anything in the view, we have reused the welcome message location of the default index.html page; therefore, you will see the result in the browser in the green header at the top of the HTML page.

This sample only shows a basic usage of Anorm; it is derived from the much more complete computer-database example that is part of the samples of the Play Framework distribution. You can refer to it if you need a deeper knowledge of the Anorm framework.