I want to build a simple utility application to track my travel expenses. All I need is a web application where I can feed details of my expenses and store them in a database. I can then generate a report and mail it to the Accounts department. I also want comparison charts, to analyze my expenses over a period of time and take actions accordingly. I have decided to build this using Play framework with Scala, moving away from the servlet-based web development. In this article, I will discuss the database access mechanism.
Initial database design
We’ll use MySQL database. Let’s take baby steps. We’ll create a schema, say expenses. To begin with we may need a table to store all the events. Each event will have a set of expense items. For example, Scala conference in Bangalore from July 1 to July 4 is an event. The expenses associated with this event will be fed into the database. We’ll worry about storing the expenses later. Let’s create a table called event that will store all the events. The event table will have id, name, description, start_date and end_date columns as shown in Figure 1.0.
Figure 1.0: event table
We’ll insert a sample record in the event table using the following query.
INSERT INTO expenses.event(name,description,start_date,end_date) VALUES(“GIDS”,”GIDS 2015″,”2015-04-22″,”2015-04-25″);
Alright, we have the basic design ready. Let’s move on to the Play area.
Getting started
Let’s create a Play application, ExpensesApp using activator new from the command prompt. The skeleton is generated with folders like controllers and views and some sample scala files. In the conf folder we have the application.conf file where we will configure the database details as shown below.
# Database configuration
# ~~~~~
# You can declare as many datasources as you want.
# By convention, the default datasource is named `default`
#
db.default.driver=com.mysql.jdbc.Driver
db.default.url=”jdbc:mysql://localhost/expenses”
db.default.user=root
db.default.password=”*******”
Before we start writing database access code, let’s find out the options to do that.
Database access mechanisms
There are a couple of ways by which you can access the database in Scala; Anorm and squeryl. Of course, there is our JDBC too.
- Anorm: Anorm stands for Anorm is not an ORM. We write SQL queries and map them explictly to the models ie., the Scala classes. The queries are still the normal SQL queries that we need to write and map the results to the models.
- Squeryl: Squeryl is the ORM for Scala. You define the classes and map them with the tables. Squeryl provides its own DSL syntax for writing queries.
We may have to configure the build.sbt file to include the libraries based on the access mechanism we choose. In this article we’ll deal with anorm. So, here’s how the build.sbt file will look like.
name := “””ExpensesApp”””
version := “1.0-SNAPSHOT”
lazy val root = (project in file(“.”)).enablePlugins(PlayScala)
scalaVersion := “2.11.6”
libraryDependencies ++= Seq(
jdbc,
anorm,
cache,
ws,
“mysql” % “mysql-connector-java” % “5.1.27”
)
Access using Anorm
We have the event table and we would like to play with its data. Let’s create a model class called Event and write the Anorm code. In the app folder along with controllers and views, let’s create a models folder. The models folder will haveEvent.scala file as shown in Figure 2.0.
Figure 2.0: app folder
Let’s define a case class Event as shown below.
package models
import java.util.{Date}
case class Event(
name:String,
description:String,
start:Date,
end:Date )
The Event class contains the fields that correspond to the columns in the table. This class will be used for all the CRUD operations that we will perform against the event table. Let’s define an Event object and here is where we’ll use Anorm API. To begin with let’s retrieve all the records from the events table. The Event.scala file with the Event object and case class is shown below.
package models
import java.util.{Date}
import java.text.SimpleDateFormat
import anorm.SQL
import anorm.SqlQuery
import play.api.Play.current
import play.api.db.DB
case class Event(
name:String,
description:String,
start:Date,
end:Date )
object Event{
val selectQuery : SqlQuery = SQL("select * from event")
def listAll = {
DB.withConnection {
implicit connection =>
selectQuery().map {row =>
Event(row[String]("name"),
row[String]("description"),
new SimpleDateFormat("yyyy-MM-dd").parse(row[String]("start_date")),
new SimpleDateFormat("yyyy-MM-dd").parse(row[String]("end_date")))
}.toList
}
}
}
The Event object has a selectQuery variable that defines the SQL select query. The listAll method gets the connection from the DB and executes the selectQuery. The result is mapped to the Event case class instance and finally we get a list of Event instances. Pretty concise, isn’t it?
Let’s write an addEvent method with an insert operation as shown below.
val insertQuery : SqlQuery = SQL("""insert into
event(name,description,start_date,end_date)
values({name},{description},{start},{end})
""")
def addEvent(event:Event) = {
DB.withConnection {
implicit connection =>
insertQuery.on(
"name" -> event.name,
"description" -> event.description,
"start" -> event.start,
"end" -> event.end ).executeUpdate
}
}
We use the executeUpdate method on the query instance. Similarly we can also define the delete and update queries.
Now, it’s time to create a Controller and wire up the database code.
Controller
Let’s define a new controller class EventsController. The controller class will have action methods to add and list all events. As we already have a record in the event table, we’ll define a listAllEvents method as shown below.
package controllers
import play.api._
import play.api.mvc._
import models.Event
import java.util.Date
import java.text.SimpleDateFormat
object EventsController extends Controller{
def listAllEvents = Action{
def events = Event.listAll
Ok(views.html.events.list(events))
}
}
The listAllEvents method invokes the listAll method of Event object and executes the select query. The returned list ofEvent instances are passed to a view page for display. The view page views/events/list.scala.html has a simple loop like this.
@(events:List[Event])
@main("List of Events"){
<h3>List of events</h3>
<ul>
@for(event <- events){
<li>@event.name, @event.description,
@event.start, @event.end
</li>
}
</ul>
}
And finally we have the routes configuration. We’ll add the URL mapping for the listAllEvents method in the routes.conf.
GET /events controllers.EventsController.listAllEvents
Time to start our server and run the URL, http://localhost:9000/events/. We’ll get the output as shown in Figure 3.0.
Figure 3.0:List of all events
Let’s define addEvent method in the EventsController class as shown below.
def addEvent(name:String,
description:String,
start:String,
end:String) = Action{
val startDate = new SimpleDateFormat("yyyy-MM-dd").parse(start)
val endDate = new SimpleDateFormat("yyyy-MM-dd").parse(end)
val eventInstance = new Event(name,description,startDate,endDate)
Event.addEvent(eventInstance)
Ok("Event added successfully")
}
We can bring forms and binding of form values in the controller, but I’m going to write about it as a separate article. We’ll add the URL mapping for the addEvent method in the routes.conf file.
POST /events/add controllers.EventsController.addEvent(name:String,description:String,start:String,end:String)
As we don’t have a form, let’s use the Postman client to send a POST request to addEvent method as shown in Figure 4.0.
Figure 4.0:Postman client for adding event
Note:
Postman is a REST client available as a chrome extension. To quote their description, ” Postman helps you be more efficient while working with APIs. Postman is a scratch-your-own-itch project. The need for it arose while one of the developers was creating an API for his project. After looking around for a number of tools, nothing felt just right. The primary features added initially were a history of sent requests and collections. …”*
Running the list of events URL will give you the result as shown in Figure 5.0.
Figure 5.0:List of all events