Using Anorm

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:

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`

2. Connect to the database.

Import the DB object of play api.

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,

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.

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

We can create the sql queries using the SQL string interpolator

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

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

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:

eg.

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!

 

 

Written by 

Rachel Jones is a Solutions Lead at Knoldus Inc. having more than 22 years of experience. Rachel likes to delve deeper into the field of AI(Artificial Intelligence) and deep learning. She loves challenges and motivating people, also loves to read novels by Dan Brown. Rachel has problem solving, management and leadership skills moreover, she is familiar with programming languages such as Java, Scala, C++ & Html.

Leave a Reply

%d bloggers like this: