Using Anorm

Table of contents

Reading Time: 3 minutes

Anorm is basically a data access layer to talk to the database we are using. In this blog, we will be using mysql as our database. Anorm is the default persistence provider in play framewok. Anorm uses plain sql to interact with the database and provides an API to parse and transform the resulting datasets.

To start with anorm, you need to include the following external dependncy of anorm in your build.sbt file :

libraryDependencies += “com.typesafe.play” %% “anorm” % “2.4.0”

Anorm requires a SQL schema creation / destruction script since it doesn’t do auto schema creation. Create a new file named “conf/evolutions/default/1.sql” containing:

# --- First database schema
# --- !Ups schema creation statements
CREATE TABLE table1 (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
# --- !Downs schema destruction statements
DROP TABLE IF EXISTS table1;

The steps we need to follow to be able to start using anorm are :

1. Define the data sources in the application. conf.

We can connect to the any databases, we have defined in the application.conf.
You can declare as many data sources as you want. By convention, the evolutions.default datasource is named `evolutions.default`

db.default.driver=com.mysql.jdbc.Driver
db.default.url=&quot;jdbc:mysql://localhost/test1?characterEncoding=UTF-8"

db.testdb.driver=com.mysql.jdbc.Driver
db.testdb.url=&quot;jdbc:mysql://localhost/test2?characterEncoding=UTF-8&quot;

2. Connect to the database.

Import the DB object of play api.

import play.api.db._
DB.withConnection { implcit connection
//Here comes the queries to be excecuted
// Note : all sql statement needs database connection as an
// implicit paramter to be exectued
}

There are one of the several ways to connect to the databases.

We can connect to the default database, with transaction using DB.withTransaction

And If we need to connect to the specific database other than default, just pass the database name as the string parameter to the withConnection method.

For example,


// test1 is the name of the database
DB.withConnection(&quot;test1&quot;)
DB.withTransaction(&quot;test1&quot;)

3. Within the connection, we write the sql statement to be executed. Anorm gives us a way to write raw SQL and create a Scala object of type SqlQuery. SqlQuery has mehods to execute the query and return various types of result.

import play.api.db._
import anorm._

case class Test(id: Long, name: String)
val simple: RowParser[Test] = {
get[Long](&quot;id&quot;) ~
get[String](&quot;name&quot;)
map { case id ~ name ⇒ Test(id, name) }
}

// executes the select query and returns result mapped to Test case class
def getData(name : String) : Test = {
DB.withConnection { implicit connection ⇒
SQL( &quot;&quot;&quot;SELECT * from test1 where name = {name}&quot;&quot;&quot;)
.on('name -&gt; &quot;Test data 1&quot;)
.as(simple.single)
}

// executes the insert statement and returns the number of rows effected
def insertData(name : String) : Int= {
DB.withConnection { implicit connection ⇒
SQL( &quot;&quot;&quot;INSERT into test1 values(1,'xyz')&quot;&quot;&quot;)
.executeInsert
}

// executes the insert statement and returns the number of rows effected
def updateData(name : String) : Int = {
DB.withConnection { implicit connection ⇒
SQL(&quot;&quot;&quot;UPDATE test1 set name = {name} where name = 'xyz'""")
.on('name -&gt; &quot;Test data 1&quot;).executeUpdate
}
}

Let’s have a brief explaination of the aforesaid method,

We can create the sql queries using the SQL string interpolator

SQL( &quot;&quot;&quot;SELECT * from test1&quot;&quot;&quot;)

If your SQL query needs dynamic parameters, you can declare placeholders like{name} in the query string, and later assign a value to them:

SQL( &quot;&quot;&quot;SELECT * from test1 where name = {name}&quot;&quot;&quot;).on('name -&gt; &quot;Test data 1&quot;)

We can get result from SQL query using RowParser which can be mapped to the single column or as a case class.

SQL( &quot;&quot;&quot;SELECT name from test1 where name = {name}&quot;&quot;&quot;).on('name -&gt; &quot;Test data 1&quot;).as(get[String](&quot;name&quot;) single)
SQL( &quot;&quot;&quot;SELECT name from test1 where name = {name}""").on('name -> "Test data 1").as(get[Option[String]]("name") singleOpt)
SQL( &quot;&quot;&quot;SELECT name from test1 where name = {name}&quot;&quot;&quot;).on('name -&gt; &quot;Test data 1&quot;).as(get[String](&quot;name&quot;) *)

We can expect a single row to be retrieved as single, option of single or list of values.

For eg, we can define a parser get[String](“name”) to transform a single column result set row, to a a string, which in turn transformed into a ResultSetParser for a single row using single.

We can also map the row of the result to a single case class, and get the results in the same manner as above but expecting the rows to be fetched as single value object, optional value object or list of values objects.

In the simplest case, Anorm allows you to map the results of a query to a Scala case class like the following:

case class Test(id: Long, name: String)
val simple: RowParser[Test] = {
get[Long](&quot;id&quot;) ~ // &quot;id&quot; is the int column name defined in the database
get[String](&quot;name&quot;) // &quot;name&quot; is the string column name defined in the database
map { case id ~ name ⇒ Test(id, name) }
}

eg.

SQL( &quot;&quot;&quot;SELECT * from test1 where name = {name}&quot;&quot;&quot;).on('name -&gt; &quot;Test data 1&quot;).as(simple single)
SQL( &quot;&quot;&quot;SELECT * from test1 where name = {name}""").on('name -> "Test data 1").as(simple singleOpt)
SQL( &quot;&quot;&quot;SELECT * from test1 where name = {name}&quot;&quot;&quot;).on('name -&gt; &quot;Test data 1&quot;).as(simple *)

That is all we need to know to start implement anorm.
We will have some details about handling the complex join results in the next part of the blog.
Thanks!