Play Framework 2.x includes Anorm, a useful data access library for Scala-based Play applications. To be able to use Anorm, ensure that Anorm and a suitable MySQL driver are declared as project dependencies in foo_scala/build.sbt.
For this recipe, we will be utilizing Anorm with database evolutions. Play Framework 2.x gives developers a way to manage database migrations. Database migrations are useful for tracking schema changes during the course of application development. Database evolutions are enabled by default but can be disabled in conf/application.conf using the following settings:
evolutionplugin=disabled
Evolution scripts are stored in the conf/evolutions/default/ directory. For more information regarding database evolutions, please refer to Play's online documentation at https://www.playframework.com/documentation/2.3.x/Evolutions.
You need to perform the following steps to utilize Anorm:
Add the Anorm dependency to
build.sbt:libraryDependencies ++= Seq( jdbc, anorm, "mysql" % "mysql-connector-java" % "5.1.28" )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/YOUR_DB" db.default.user=YOUR_USER db.default.password=YOUR_PASSWORDFor the next recipes, we need to create our products table in our MySQL database. Create our first database evolution file
1.sqlinconf/evolutions/defaultand add the following SQL statements:# --- !Ups CREATE TABLE Products ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, PRIMARY KEY (id) ); # --- !Downs DROP TABLE Products;
The following sections display various database-oriented operations using Anorm.
The following code snippet will create a new record:
DB.withConnection { implicit c =>
SQL("INSERT INTO Products(id, name) VALUES ({id}, {name});")
.on('id -> product.id, 'name -> product.name).executeInsert()
}The following code snippet will update a record:
DB.withConnection { implicit c =>
SQL("UPDATE Products SET name = {name} WHERE id = {id}")
.on('name -> product.name, 'id -> product.id).executeUpdate()
}The following code snippet will delete a record:
DB.withConnection { implicit c =>
SQL("DELETE FROM Products WHERE id={id};")
.on('id -> id).executeUpdate()
}Querying a recordThe following code snippet will query a record:
DB.withConnection { implicit c =>
SQL("SELECT * FROM Products WHERE id={id};")
.on('id -> id).executeQuery().singleOpt(defaultParser)
}The following code snippet will retrieve a record:
DB.withConnection { implicit c =>
SQL("SELECT * FROM Products;").executeQuery().list(defaultParser)
}Finally, we can combine all of these functions in a companion object called Product:
package models
import play.api.db.DB
import play.api.Play.current
import anorm._
import anorm.SqlParser.{str, int}
case class Product(id: Long, name: String)
object Product {
val defaultParser = int("id") ~ str("name") map {
case id ~ name => Product(id, name)
}
def save(product: Product) = {
DB.withConnection { implicit c =>
SQL("INSERT INTO Products(id, name) VALUES ({id}, {name});")
.on('id -> product.id, 'name -> product.name).executeInsert()
}
}
def update(product: Product) = {
DB.withConnection { implicit c =>
SQL("UPDATE Products SET name = {name} WHERE id = {id}")
.on('name -> product.name, 'id -> product.id).executeUpdate()
}
}
def delete(id: Long) = {
DB.withConnection { implicit c =>
SQL("DELETE FROM Products WHERE id={id};")
.on('id -> id).executeUpdate()
}
}
def get(id: Long) = {
DB.withConnection { implicit c =>
SQL("SELECT * FROM Products WHERE id={id};")
.on('id -> id).executeQuery().singleOpt(defaultParser)
}
}
def all = {
DB.withConnection { implicit c =>
SQL("SELECT * FROM Products;").executeQuery().list(defaultParser)
}
}
}