Hi all,

As you know, Play is an open source web application framework, written in Scala and Java, which follows the model–view–controller (MVC) architectural pattern. It aims to optimize developer productivity by using convention over configuration, hot code reloading and display of errors in the browser.

Today I will introduce a basic concept of this framework, it is how to access an SQL database.

Firstly, all things you need is:

• IntelliJ IDEA (http://www.jetbrains.com/idea/download/)
• Scala plugin (enable through IntelliJ IDEA)
• Play Framework (enable through IntelliJ IDEA)
• Mysql

After these materials above has been installed and run successfully, now we will focus on major part of this blog, how to connect and interactive to dabase using Play Framework based on Scala language.

Configuring JDBC connection pools

Play provides a plug-in for managing JDBC connection pools. You can configure as many databases as you need.
You must configure a connection pool in the conf/application.conf file. By convention, the default JDBC data source must be called default and the corresponding configuration properties are db.default.driver and db.default.url.

MySQL database engine connection properties

Play can support H2, SQLite, PostGreSQL and MySQL engine but in my case I use MySQL database, these lines below is configuration for MySQL database engine

# Default database configuration using MySQL database engine
# Connect to playdb as playdbuser
db.default.driver=com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://localhost/playdb"
db.default.user=playdbuser
db.default.password="your password"

Obtaining a JDBC connection

There are several ways to retrieve a JDBC connection. The simplest way is:

val connection = DB.getConnection()

But after some SQL query execution you need to call close() at some point on the opened connection to return it to the connection pool. Another way is to let Play manage closing the connection for you:

// access "default" database
DB.withConnection { conn =>
   // do whatever you need with the connection
}

The connection will be automatically closed at the end of the block.

Using Anorm to access your database

Play includes a simple data access layer called Anorm that uses plain SQL to interact with the database and provides an API to parse and transform the resulting datasets.

Anorm is Not an Object Relational Mapper

Add Anorm to your project

You will need to add Anorm and jdbc plugin to your dependencies :

libraryDependencies ++= Seq(
jdbc,
anorm
)

Executing SQL queries with Anorm

You need to import anorm._ to use SQL object to create queries and play.api.db.DB to retrieve results of the query

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

DB.withConnection { implicit c =>
val result: Boolean = SQL("Select 1").execute()
}

The execute method indicate that whether the execution was succeful.

To execute an update, you can use executeUpdate()

val result: Int = SQL("delete from City where id = 99").executeUpdate()

The query above will delete the record which have id equal 99 and return result is number of affected rows

If you are inserting data that has an auto-generated Long primary key, you can call executeInsert()

val id: Option[Long] =
SQL("insert into City(name, country) values ({name}, {country})")
.on('name -> "Cambridge", 'country -> "New Zealand").executeInsert()

That is only some simple examples about how to use Anorm to interactive with DB in Play framework. We will continue to investigate and execute some complex concepts of Anorm in next blog

================================
Ref: Accessing an SQL database