Practical Guide: Anorm Using MySQL With Scala

In this article, 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. 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:

xxxxxxxxxx

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

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

xxxxxxxxxx

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:

xxxxxxxxxx

# --- 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.

xxxxxxxxxx

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. If you need to create a method that executes several queries as part of a transaction, use db.withTransaction.

You may also like:  The Right Way to Use Spark and JDBC

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:

xxxxxxxxxx

// 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:

xxxxxxxxxx

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:

xxxxxxxxxx

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:

xxxxxxxxxx

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:

xxxxxxxxxx

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:

xxxxxxxxxx

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

Further Reading

MySQL Tutorial: A Beginners Guide to Learn MySQL

Top 5 Courses to Learn MySQL in 2019