Anorm is a module in Play that supports interactions with the database using a plain SQL.

Anorm exposes methods to query the SQL database and parse the result as Scala objects, built in as well as custom.

The objective behind Anorm as stated on the Play website (https://www.playframework.com/documentation/2.3.x/ScalaAnorm) is:

val appDependencies = Seq(
      jdbc,
      anorm
)

Let's picture our user model in MySQL. The table can be defined as follows:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login_id` varchar(45) NOT NULL,
  `password` varchar(50) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `dob` bigint(20) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `login_id_UNIQUE` (`login_id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB

Now let's look at the different queries we will make in this table. The queries will be as follows:

Assume that when a user requests to delete his account from our application, we do not delete the user from the database, but instead mark the user's status as inactive. Therefore, we will not use any delete queries.

Using Anorm, we could have the userId autogenerated as follows:

DB.withConnection {
  implicit connection =>
  val userId  = SQL"""INSERT INTO user(login_id,password,name,
  dob) VALUES($loginId,$password,$name,$dob)""".executeInsert()
userId
}

Here, loginId, password, name, and dob are variables that are replaced in the query at runtime. Anorm builds only java.sql.PreparedStatements, which prevents SQL injection.

The SQL method returns an object of the SimpleSql type and is defined as follows:

implicit class SqlStringInterpolation(val sc: StringContext) extends AnyVal {
  def SQL(args: ParameterValue*) = prepare(args)

  private def prepare(params: Seq[ParameterValue]) = {
    // Generates the string query with "%s" for each parameter placeholder
    val sql = sc.parts.mkString("%s")

    val (ns, ps): (List[String], Map[String, ParameterValue]) =
        namedParams(params)

      SimpleSql(SqlQuery(sql, ns), ps,
        defaultParser = RowParser(row => Success(row)))
    }
  }

SimpleSql is used to represent a query in an intermediate format. Its constructor is as follows:

case class SimpleSql[T](sql: SqlQuery, params: Map[String, ParameterValue], defaultParser: RowParser[T]) extends Sql { … }

The executeInsert method fetches PreparedStatement from the SimpleSql object using its getFilledStatement method. Then the getGeneratedKeys() method of PreparedStatement is executed.

The getGeneratedKeys method results in an autogenerated key, created as a result of executing the statement in which it is called. If no key is created, it returns an empty object.

Now let's use Anorm to update a user's password:

DB.withConnection {
    implicit connection =>
SQL"""UPDATE user SET password=$password WHERE id = $userId""".executeUpdate()
}

The executeUpdate method works similar to executeInsert. The difference is that it calls the executeUpdate method of the PreparedStatement, instead of getGeneratedKeys.

The executeUpdate method returns a count of affected rows for the Data Manipulation Language (DML) statements. If the SQL statement is of the other types, such as Data Definition Language (DDL), it returns 0.

Now let's try to fetch the details of all registered users. If we want the resulting rows to be parsed as user objects, we should define a parser. The parser for a user will be as follows:

def userRow:RowParser[User] = {
    get[Long]("id") ~
      get[String]("login_id") ~
      get[Option[String]]("name") map {
      case id ~ login_id ~ name  =>  User(id, login_id, name)
    }
  }

In most queries, we will not need the password and date of birth, so we can exclude them from the user RowParser default.

A query using this parser can be shown in this way:

DB.withConnection {
  implicit connection =>
  val query = "SELECT id,login_id,name FROM user"
  SQL(query).as(userRow.*)
}

The .* symbol indicates that the result should have one or more rows similar to its common interpretation in regular expressions. Similarly, the .+ symbol can be used when we expect the result to consist of zero or more rows.

DB.withConnection {
  implicit connection =>
val insertQuery  = """INSERT INTO user(login_id,password,name,
  |dob) VALUES({loginId},{password},{name},{dob})""".stripMargin
val userId = SQL(insertQuery).on(
  'loginId -> loginId,
  'password -> password,
  'name -> name,
  'dob -> dob).executeInsert()
userId
}

 def on(args: NamedParameter*): SimpleSql[T] =
    copy(params = this.params ++ args.map(_.tupled))

Please refer to the Play documentation (http://www.playframework.com/documentation/2.3.x/ScalaAnorm) and the Anorm API documentation (http://www.playframework.com/documentation/2.3.x/api/scala/index.html#anorm.package) for more use casess and details.