Scala DAO(1)Prequel

Scala DAO(1)Prequel

Since there are not many codes in the project, I would like to learn it from the source codes. Maybe this way will be good for my scala skill.

>git clone https://github.com/30ss/prequel.git
>cd prequel
>sbt clean update compile eclipse gen-idea

>sbt publish-local
/Users/carl/.ivy2/local/net.noerd/prequel_2.10/0.3.9.1/jars/prequel_2.10.jar 

I placed this project in a spark project to learn it. Because my rest server project is using slick. 

1. Add Dependencies in build.sbt
    "net.noerd"           %%  "prequel"                   % "0.3.9.1",
    "org.scalatest"       %   "scalatest_2.10"            % "1.9.1"   % "test",
    "org.specs2"          %%  "specs2"                    % "1.13"    % "test",
    "mysql"               %   "mysql-connector-java"      % "5.1.24"

>sbt update gen-idea

2. Some Basic Configuration Class
PrequelDAO
package com.sillycat.easysparkserver.dao

import com.typesafe.config._
import net.noerd.prequel.{IsolationLevels, SQLFormatter, DatabaseConfig}
import com.sillycat.easysparkserver.model.ProductsPrequel

object PrequelDAO extends ProductsPrequel{

  val config = ConfigFactory.load()

  val testDatabase: DatabaseConfig = DatabaseConfig(
    driver = config.getString("database.driver"),
    jdbcURL = config.getString("database.url"),
    username = config.getString("database.username"),
    password = config.getString("database.password"),
    sqlFormatter = SQLFormatter.HSQLDBSQLFormatter,
    isolationLevel = IsolationLevels.RepeatableRead
    //There are some other configuration like driver, jdbcURL, username, password, isolationLevel,
    //sqlFormatter, poolConfig
    //to know the detail, just read the source codes in net.noerd.prequel.DatabaseConfig
  )

  def create: Unit = {
    testDatabase transaction { implicit tx =>
      Products.create
    }
  }

  def drop: Unit = {
    testDatabase transaction { implicit tx =>
      Products.drop
    }
  }

}

This class will manage all the table and business class.

Where the SQL hide, here is the mapping and SQL and business class Product, ProductsPrequel
package com.sillycat.easysparkserver.model

import net.noerd.prequel.SQLFormatterImplicits._
import spark.Logging
import org.joda.time.DateTime
import net.noerd.prequel.Transaction


case class Product(id: Option[Long], brand: String, productName: String, createDate: DateTime)

trait ProductsPrequel extends Logging{

   object Products {
     def create(implicit tx: Transaction){
         tx.execute(
           """create table if not exists PRODUCTS (
             |ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
             |BRAND VARCHAR(20),
             |PRODUCT_NAME VARCHAR(20),
             |CREATE_DATE TIMESTAMP(8)
             |)
           """.stripMargin)
         tx.commit()
     }

     def drop(implicit tx: Transaction){
          tx.execute("""drop table if exists PRODUCTS""")
     }

     def insertProduct(item: Product)(implicit tx: Transaction): Long = {
          tx.execute(
            "insert into PRODUCTS( BRAND, PRODUCT_NAME, CREATE_DATE) values ( ?, ?, ? )",
            item.brand, item.productName, item.createDate
          )
          tx.selectLong(
            """
              |SELECT LAST_INSERT_ID()
            """.stripMargin)
     }

     def loadProducts()(implicit tx: Transaction): Seq[Product] = {
         tx.select(
           """select
             |ID,
             |BRAND,
             |PRODUCT_NAME,
             |CREATE_DATE
             |from
             |PRODUCTS
           """.stripMargin){ r =>
           Product(r.nextLong,r.nextString.getOrElse(""),r.nextString.getOrElse(""),new DateTime(r.nextDate.get.getTime))
         }
     }

     def getProduct(id: Long)(implicit tx: Transaction): Option[Product] = {
          tx.selectHeadOption(
            """select
              |ID,
              |BRAND,
              |PRODUCT_NAME,
              |CREATE_DATE
              |from
              |PRODUCTS
              |where
              |ID = ?
            """.stripMargin, id){ r =>
            Product(r.nextLong,r.nextString.getOrElse(""),r.nextString.getOrElse(""),new DateTime(r.nextDate.get.getTime))
          }
     }

     def deleteProduct(id: Long)(implicit tx: Transaction): Unit = {
        tx.execute(
          """
            |delete from PRODUCTS
            |where ID = ?
          """.stripMargin, id)
     }

     def batchInsertProducts(products: Seq[Product])(implicit tx: Transaction): Unit = {
       tx.executeBatch("insert into PRODUCTS( BRAND, PRODUCT_NAME, CREATE_DATE) values ( ?, ?, ? )") { statement =>
           products.foreach { item =>
             statement.executeWith(item.brand,item.productName,item.createDate)
           }
       }
     }
   }
}

The test case class and How we use the DAO layer in ProductPrequelSpec
package com.sillycat.easysparkserver.model

import org.scalatest.FunSuite
import org.specs2.matcher.ShouldMatchers
import org.scalatest.BeforeAndAfterAll
import com.sillycat.easysparkserver.dao.PrequelDAO
import org.joda.time.DateTime

class ProductPrequelSpec extends FunSuite with ShouldMatchers with BeforeAndAfterAll {

  val dao = PrequelDAO

  override def beforeAll() {
    dao.create
  }

  override def afterAll() {
    dao.drop
  }

  test("Database tables are created and dropped") {
    assert("x" === "x")
  }

  test("Verify Products Insert Operation"){
    val item = Product(None,"CK","good things",DateTime.now)
    dao.testDatabase transaction { implicit tx =>
      val id = dao.Products.insertProduct(item)
      assert(id === 1)
    }
  }

  test("Verify Query Products Operation"){
    dao.testDatabase transaction { implicit tx =>
      val items = dao.Products.loadProducts
      assert(items != Nil)
      assert(items.size === 1)
    }
  }

  test("Verify Get Product Operation"){
    dao.testDatabase transaction { implicit tx =>
      val item = dao.Products.getProduct(1)
      assert(item != None)
      assert(item.get.id.get === 1)
      assert(item.get.brand === "CK")
    }
  }

  test("Verify delete Product Operation"){
    dao.testDatabase transaction { implicit tx =>
      dao.Products.deleteProduct(1)
      val item = dao.Products.getProduct(1)
      assert(item === None)
    }
  }

  test("Verify batch Insert Operation"){
    dao.testDatabase transaction { implicit tx =>
      val items = Seq(Product(None,"CK1","good things1",DateTime.now), Product(None,"CK2","good things2",DateTime.now))
      dao.Products.batchInsertProducts(items)
      val return_items = dao.Products.loadProducts()
      assert(return_items.size === 2)
    }
  }

}

Tips
Error Message:
java.lang.NoSuchMethodError: org.hsqldb.DatabaseURL.parseURL 

Solution:
    "org.hsqldb"          %   "hsqldb"                    % "2.2.4" 
I will not spend time on that, I will change my database to use mysql.

Error Message:
I do not know why the executeBatch is not working. I can not run the statement.executeWith 

Solution:
Find the class package net.noerd.prequel.ReusableStatement and change this class from private to null private
Build the source codes myself and publish-local again.


References:
https://github.com/30ss/prequel

Creating table
http://troels.arvin.dk/db/rdbms/#mix-identity