Reading Time: 3 minutes

 In this blog, we are going to explore the Anorm. Play includes a simple data access layer called Anorms that uses plain SQL to interact with the database and provides an API to parse and transform the resulting datasets. In this blog, we will be using MySQL as our database. 

You need to perform the following steps to utilize Anorm:

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

libraryDependencies +=("org.playframework.anorm" %% "anorm" % "2.6.4")

2)Ensure that Anorm and MySQL are configured properly in conf/application.conf:

db.default.driver= com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://localhost/bookStore"
db.default.user=testuser
db.default.password=test123

3)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 book (
id INT PRIMARY KEY,
bookname VARCHAR(255) NOT NULL
author VARCHAR(255) NOT NULL
);
# --- !Downs schema destruction statements
DROP TABLE IF EXISTS book;

4)Connect to the database.

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

There are one of the several ways to connect to the databases. if you need to write a method to run a single query, define your method with db.withConnection but If you need to create a method that executes several queries as part of a transaction, use db.withTransaction.

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

For example,

// bookStore is the name of the database
DB.withConnection("bookStore")
DB.withTransaction("bookStore")

In general, there are three things you need to do to run a SQL query with Anorm:

• Connect to the database. You can do this with or without a transaction.
• Create your SQL query using Anorm’s SQL string interpolator.
• Call a method like executeInsert or executeUpdate to run the query.

The following sections display various database-oriented operations using Anorm.

Creating a new record

The following code snippet will create a new record:

DB.withConnection { implicit c =>
 	SQL("INSERT INTO book(id, bookname,author) VALUES ({id}, {bookname},{author});")
    	.on('id -> book.id, 'bookname -> book.name,'author -> book.author).executeInsert()
   }

Updating a record

The following code snippet will update a record:

DB.withConnection { implicit c =>
  	SQL("UPDATE  book SET bookname = {bookname} WHERE id = {id}")
   	.on('bookname -> book.bookname, 'id -> book.id).executeUpdate()
   }

Deleting a record

The following code snippet will delete a record:

DB.withConnection { implicit c =>
  	SQL("DELETE FROM book WHERE id={id};")
   	.on('id -> id).executeUpdate()
   }Querying a record

SQL SELECT queries

For SQL SELECT queries you’ll also need to create a RowParser when your query returns a custom data type, such as a User or Book. Before getting into the examples, there are a few things to know about SELECT queries with Anorm:

• The Anorm single method returns a single value, but it throws an exception if your query returns no rows.
• The singleOpt method returns a single value as an Option.
• You can select simple values like Int and String, as well as your own custom data types.
• If your query will return a custom data type like User or Book, you need to use a RowParser.

The following code snippet will retrieve a record:

DB.withConnection { implicit c =>
  	SQL("SELECT * FROM book WHERE id={id};")
   	.on('id -> id).executeQuery().singleOpt(defaultParser)
	}

The Above code snippet will retrieve a record of books that matches the given condition.

The following code snippet will retrieve a record:

DB.withConnection { implicit c =>
 	SQL("SELECT * FROM book;").executeQuery().list(defaultParser)
   }

The above code snippet will retrieve a record of all books which are present in the book table.

References:

1. https://www.playframework.com/documentation/2.7.x/Anorm

2. https://playframework.github.io/anorm/#executing-sql-queries

3. https://blog.knoldus.com/using-anorm/