Playframework(9)Scala Project and Working with JSON

Playframework(9)Scala Project and Working with JSON
5. Working with JSON
5.1 The Play JSON library

We recommend to deal with JSON based the type class library play.api.libs.json.

This library is built on top of https://github.com/codahale/jerkson/, which is a Scala wrapper around the super-fast java based JSON library, Jackson.

There are some JSON data types:
JsObject
JsNull
JsUndefined
JsBoolean
JsNumber
JsArray
JsString

All of them inherit from the generic JSON value, JsValue.

Parsing a Json String
val json: JsValue = Json.parse(jsonString)

Navigating into a Json tree
As soon as we have a JsValue we can navigate into the tree.

val json = Json.parse(jsonString)

val maybeName = (json \ "user" \ name).asOpt[String]
val emails = (json \ "user" \\ "emails").map(_.as[String])

asOpt[T] that will return None if the value is missing.
Otherwise we can use as[T] that we fail with an exception if the value was missing.

Converting a Scala value to Json

val jsonNumber = Json.toJson(4)

val jsonArray = Json.toJson(Seq(1, 2, 3, 4))

It is complicated if the Seq contains some other values than Int, for example:

Seq(1, "Carl", 3, 4)

val jsonArray = Json.toJson(Seq(
     toJson(1), toJson("Carl"), toJson(3), toJson(4)
))

Serializing Json
val jsonString: String = Json.stringify(jsValue)

Other Options
…snip...

5.2 Handling and serving JSON requests
def sayHi = Action { reqeust =>
     request.body.asJson.map { json =>
          (json \ "name").asOpt[String].map { name =>
               Ok("Hello " + name)
          }.getOrElse{
               BadRequest("Missing parameter [name] ")
          }
     }.getOrElse{
          BadRequest("Expecting Json data")
     }
}

Better and Simpler to specify our own BodyParser

def sayHi = Action(parse.json) { request =>
     (request.body \ "name").asOpt[String].map { name =>
          Ok("Hello " + name)
     }.getOrElse{
          …snip...
     }
}

We can use command line cURL
>curl    
     --header "Content-type: application/json"
     --request POST
     --data '{"name": "Carl"}'
     http://localhost:9000/sayHi

Serving a JSON response
def sayHi = Action(parse.json) { request =>
     (request.body \ "name").asOpt[String].map { name =>
          Ok(toJson(
               Map("status" -> "OK", "message" -> ("Hello " + name))
          ))
     }.getOrElse{
          BadRequest(toJson(
               Map("status" -> "KO", "message" -> "Missing parameter [name]")
          ))
     }
}

6. Working with XML
Handling an XML request

def sayHi = Action { request =>
     request.body.asXml.map { xml =>
          (xml \\ "name" headOption).map(_.text).map { name=>
               Ok("Hello " + name)
          }.getOrElse{
               BadRequest("Missing parameter [name]")
          }
     }.getOrElse{
          BadRequest("Expecting XML data")
     }
}

def sayHi = Action(parse.xml) { request =>
     (request.body \\ "name" headOption).map(_.text).map { name=>
          Ok("Hello " + name)
     }.getOrElse{
          BadRequet("Missing parameter [name])
     }
}

Serving an XML response
def sayHi = Action(parse.xml) { request =>
     (request.body \\ "name" headOption).map(_.text).map { name =>
          Ok(<message status="OK"> Hello {name}</message>)
     }.getOrElse{
          BadRequest(<message status="KO">Missing parameter [name]</message>)
     }
}

7 Handling file upload
Uploading files in a form using multipart/form-data
@form(action = routes.Application.upload, 'enctype -> "multipart/form-data")
     <input type="file" name="picture">
     <p><input type="submit"></p>
}

def upload = Action(parse.multipartFormData) { request =>
     request.body.file("picture").map { picture =>
          import java.io.File
          val filename = picture.filename
          val contentType = picture.contentType
          picture.ref.moveTo(new File("/tmp/picture"))
          Ok("File uploaded")
     }.getOrElse{
          Redirect(routes.Application.index).flashing(
               "error" -> "Missing file"
          )
     }
}

The ref attribute gives me a reference to a TemporaryFile.

Direct file upload
AJAX upload the file asynchronously in a form.

Writing Our Own Body Parser

8. Accessing an SQL database
8.1 Configuring and Using JDBC
SQLite database engine connection properties
db.default.driver=org.sqlite.JDBC
db.default.url="jdbc:sqlite:/path/to/db-file"

Accessing the JDBC datasource
The play.api.db package provides access to the configured data sources:
val ds = DB.getDataSource()

Obtaining a JDBC connection
val connection = DB.getConnection()

We need to call close after we use the connection. We can do that in another way:

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

//access "orders" database instead of "default"
DB.withConnection("orders") { conn =>
          //
}

8.2 Anorm, simple SQL data access
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.

mysql configuration
db.default.driver=com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://localhost/world"
db.default.user=root
db.default.password=111111

Overview
It can feel strange to return to plain old SQL to access an SQL database these days. Java Developers accustomed to using a high-level Object Relational Mapper like Hibernate.

But we think these tools are not needed at all when you have the power of a high-level programming language like Scala. On the contrary, they will quickly become counter-productive.

Using JDBC is a pain, but we provide a better API
You don't need another DSL to access relational databases
SQL is already the best DSL for accessing relational databases. We don't need to invent something new.
A type safe DSL to generate SQL is a mistake
Take Control of your SQL code

Executing SQL queries

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

The execute() method returns a Boolean value indicating whether the execution was successful.

To execute an update, you can use executeUpdate(), which returns the number of rows updated.

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

If we plan to insert data that has an auto-generated Long primary key, you can call executeInsert().

val id: Int = SQL("insert into City(name, country) values ({name}, {country}")
                  .on("Cambridge", "New Zealand").executeInsert();

Since Scala supports multi-line strings, feel free to use them for complex SQL statement:
val sqlQuery = SQL(
     """
          select * from Country c
          join CountryLanguage l on l.CountryCode = c.Code
          where c.code = 'FRA';
     """
)

If our SQL query needs dynamic parameters, we can declare placeholders like {name} in the query string, and later assign a value to them:
SQL(
     """
          select * from Country c
          join CountryLanguage l on l.CountryCode = c.Code
          where c.code = {countryCode};
     """
).on("countryCode" -> "FRA")

Retrieving data using the Stream API
The first way to access the results of a select query is to use the Stream API.

When you call apply() on any SQL statement, you will receive a lazy Stream of Row instances, where each row can be seen as a dictionary:

//Create an SQL query
val selectCountries = SQL("Select * from Country")

//Transform the resulting Stream[Row] to a List[(String, String)]
val countries = selectCountries().map(row =>
     row[String]("code") -> row[String]("name")
).toList

Count the number of Country entries in the database, so the result set will be a single row with a single column:
val firstRow = SQL("Select count(*) as c from Country").apply().head

//Next get the content of the 'c' column as Long
val countryCount = firstRow[Long]("c")

Using Pattern Matching
…snip…

Special data types
Clobs

SQL("Select name, summary from Country")().map{
     case Row(name: String, summary: java.sql.Clob) => name -> summary
}

Binary
SQL("Select name, image from Country")().map{
     case Row(name: String, image: Array[Byte]) => name ->image
}

Dealing with Nullable columns
If a column can contain Null values in the database schema, you need to manipulate it as an Option type.

SQL("Select name, indepYear from Country")().collect {
     case Row(name:String, Some(year:int)) => name -> year
}

If we try to retrieve the column content as Int directly from the dictionary
SQL("Select name, indepYear from Country")().map{ row =>
     row[String]("name") -> row[Int]("indepYear")
}
This will produce an UnexpectedNullableFound(COUNTRY.INDEPYEAR) exception if it encounters a null value.

We need to write like this to an Option[Int]
SQL("Select name, indepYear from Country")().map { row =>
     row[String]("name") -> row[Option[Int]]("indepYear")
}


References:
http://www.playframework.org/documentation/2.0.4/ScalaHome
http://www.playframework.org/documentation/2.0.4/ScalaXmlRequests