Product Catalog Application using Play and Scala Part 5 – Defining Object Models

Thu, Jul 31, 2014

Play Programming #Play #programming #scala

We will be using Anorm as the persistence layer in this application. As we can see from previous post, we have defined 2 tables in this application, Product and Category. So we will be creating 2 classes for both of them. If you coming from a Java background, you might be familiar with JPA or Hibernate. Unlike these 2 libraries which require us to write a HQL(Hibernate Query Language) or JPQL(Java Persistence Query Language), Anorm allows us to write a conventional SQL and map the result to a case class before return them to the caller. The closest I can relate to is JdbcTemplate provided by Spring framework.

First we will create a new file under the app/models/ folder(create the folder if it’s not there), name Product.scala.

package models

case class Product (
    id: Long,
    name: String,
    categoryId: Long,
    categoryName: String = ""
)

If you compare to the table we created, this case class has one additional column, which will be used to store the category name when querying from the database. And here is the companion object for Product.

object Product {
  val productParser: RowParser[Product] = 
  { 
      long("Products.id") ~ str("Products.name") ~ long("Products.category_id") ~ str("Categories.name") map {
      case id ~ ean ~ name ~ description => Product(id, ean, name, description)
    }
  }

  def insert(product: Product) : Option[Long] = {
    DB.withConnection { implicit connection =>
      SQL(""" 
            INSERT INTO Products(name, category_id) 
            VALUES({name}, {categoryId})
      """).on(
        'name -> product.name,
        'categoryId -> product.categoryId
      ).executeInsert()
    }
  }

  def update(product: Product) {
    DB.withConnection { implicit c =>
      SQL("""
        update Products set 
        name = {name},
        category_id = {categoryId}
        where id={id}
        """
      )
      .on(
        'name -> product.name,
        'categoryId -> product.categoryId,
        'id -> product.id
      ).executeUpdate()
    }
  }

  def list(): List[Product] = {
    DB.withConnection { implicit connection =>
      SQL("SELECT product.*,category.name FROM Products as product left join Categories as category on product.category_id = category.id ORDER BY product.name ASC").as(productParser *)
    }
  }

  def findById(id: Long): Option[Product] = {
    DB.withConnection { implicit c =>
      SQL("SELECT product.*,category.name FROM Products as product left join Categories as category on product.category_id = category.id WHERE product.id = {id}")
        .on('id -> id)
        .as(productParser.singleOpt)
    }
  }
  def delete(product: Product) {
    DB.withConnection { implicit connection =>
      SQL("delete from products where id = {id}").on("id" -> product.id).executeUpdate()
    }
  }
}

We written few methods in Product object in order to perform CRUD operation on product records. The only things that I want to mention here is the  productParser variable. This variable is used to map the result set retrieved from database into a Product type. As we can see from the “list” and “findById” method we apply the parser in the “as()” method and the result will be converted into a Product type respectively.

Here are the complete codes for Product.scala

package models

import play.api.db._
import play.api.Play.current
import anorm.SQL
import anorm.SqlQuery
import anorm.~ 
import anorm.RowParser
import anorm.SqlParser._ 
import scala.language.postfixOps    

case class Product (
    id: Long,
    name: String,
    categoryId: Long,
    categoryName: String = ""
)

object Product {
  val productParser: RowParser[Product] = 
  { 
      
      long("Products.id") ~ str("Products.name") ~ long("Products.category_id") ~ str("Categories.name") map {
      case id ~ ean ~ name ~ description => Product(id, ean, name, description)
    }
  }

  def insert(product: Product) : Option[Long] = {
    DB.withConnection { implicit connection =>
      SQL(""" 
            INSERT INTO Products(name, category_id) 
            VALUES({name}, {categoryId})
      """).on(
        'name -> product.name,
        'categoryId -> product.categoryId
      ).executeInsert()
    }
  }

  def update(product: Product) {
    DB.withConnection { implicit c =>
      SQL("""
        update Products set 
        name = {name},
        category_id = {categoryId}
        where id={id}
        """
      )
      .on(
        'name -> product.name,
        'categoryId -> product.categoryId,
        'id -> product.id
      ).executeUpdate()
    }
  }

  def list(): List[Product] = {
    DB.withConnection { implicit connection =>
      SQL("SELECT product.*,category.name FROM Products as product left join Categories as category on product.category_id = category.id ORDER BY product.name ASC").as(productParser *)
    }
  }

  def findById(id: Long): Option[Product] = {
    DB.withConnection { implicit c =>
      SQL("SELECT product.*,category.name FROM Products as product left join Categories as category on product.category_id = category.id WHERE product.id = {id}")
        .on('id -> id)
        .as(productParser.singleOpt)
    }
  }
}

As for the Category, we will create another file app/models/Category.scala . The code is similar with Product.scala.

package models

import play.api.db._
import play.api.Play.current
import anorm.SQL
import anorm.SqlQuery
import anorm.~ 
import anorm.RowParser
import anorm.SqlParser._ 
import scala.language.postfixOps    

case class Category (
id: Long,
name: String
)

object Category {
  val categoryParser: RowParser[Category] = 
  {     
      long("Categories.id") ~ str("Categories.name") map {
      case id ~ name => Category(id, name)
    }
  }

  def insert(category:Category) : Option[Long] = {
    val id: Option[Long] = DB.withConnection { implicit connection =>
      SQL(""" 
            INSERT INTO Categories(name) 
            VALUES({name})
      """).on(
        'name -> category.name
      ).executeInsert()
    }

    id
  }

  def update(category:Category) {
    DB.withConnection { implicit connection =>
      SQL("""
        update Categories set 
        name = {name}
        where id={id}
        """
      )
      .on(
        'name -> category.name,
        'id -> category.id
      ).executeUpdate()
    }
  }

  def list(): List[Category] = {
    DB.withConnection { implicit connection =>
      SQL("SELECT * FROM categories ORDER BY name ASC").apply().as(categoryParser *)
    }
  }

  def findById(id: Long): Category = {
    DB.withConnection { implicit c =>
      val row = SQL("SELECT * FROM Categories WHERE id = {id}")
        .on('id -> id)
        .as(categoryParser.singleOpt)
    }
  }
}

To wrap things up, we can see that Anorm is more flexible when you have an existing database and want to define entity relationship on code level. Also note that we don’t have to specify which table the class is mapped on, unlike some of the ORM frameworks like Hibernate or JPA.