2016年11月23日 星期三

Slick第一天─基礎介紹與簡單實作


今天要介紹的是

Slick,一個Scala用來跟database溝通的工具,能使用類似scala 操作collection的方式來操作database。

也意味開發專案時,不需要自己去寫一個抽象工廠來為各家db作sql的實作,只要專注在Slick的操作,然後若對方專案db有不同選項時,僅需更換設定檔與換成該db的jdbc即可。


實作準備素材

Docker Container 準備

假想聽眾已經對於docker,網路連線有基本常識,
稍後會從docker hub上拉一個h2的docker image下來,並運行Container,將1521 與 81 port 映射在host上。


docker pull zeilush/h2

docker run -d -p 1521:1521 -p 81:81 -v /tmp/h2-data:/opt/h2-data --name=myH2 zeilush/h2


用IntelliJ 開啟一個sbt project 

假定聽眾的scala版本為2.11+ (非2.12)



build.sbt 新增下面內容

libraryDependencies ++= Seq(
"com.typesafe.slick" %% "slick" % "3.1.1",
"org.slf4j" % "slf4j-nop" % "1.6.4",
"com.h2database" % "h2" % "1.3.176"
)


com.typesafe.slick 這個套件,是我們要使用slick時,必須添加slf4j-nop 套件是 因為slick 用slf4j紀錄log,這個套件會disable logging,若在生產環境下必須要置換掉此套件,原文如下:
Slick uses SLF4J for its own debug logging so you also need to add an SLF4J implementation. Here we are using slf4j-nop to disable logging. You have to replace this with a real logging framework like Logback if you want to see log output.

h2database 這個套件是因為我們使用的是h2 database,我們並不會直接碰觸到這個套件,而是透過設定檔告知slick,讓slick去調用這個套件


撰寫database設定檔

在 專案的src/main/resource這個資料夾下,創建一個application.conf 檔案,並將設定寫在這裡。

h2mem1 = {
  url = "jdbc:h2:tcp://<database Ip>:1521/tmp/test1"
  driver = org.h2.Driver
  connectionPool = disabled
  keepAliveConnection = true
}

實際範例
h2mem1 = {
  url = "jdbc:h2:tcp://192.168.114.113:1521/tmp/test1"
  driver = org.h2.Driver
  connectionPool = disabled
  keepAliveConnection = true
}

官網有特別註記,連結池的用法要特別注意


src/main/scala-2.11/WeTrySlick.scala 

我們在src/main/scala-2.11/ 資料夾下,新增一個 WeTrySlick.scala,等等要用來放 撰寫檔案的方式,稍後的這個範例只是為了讓大家體驗Slick的資料操作方式。

在正式情況下並不會使用這樣的方式進行開發,而是會透過 code generator 的方式生成一個scala Trait與Object,透過引用該Object,來對db進行操作。

我們需要先創建一個object,並import下列套件

import slick.driver.H2Driver.api._

import scala.concurrent.ExecutionContext.Implicits.globalimport scala.concurrent.Await
import scala.concurrent.duration._

object SlickGettingStart {

  def main(args: Array[String]): Unit = {
 
  }
}


稍後預計在資料庫創建兩張表,故先在此先寫class,並繼承Table[T],T為泛型,暫時先想成預設使用tuple格式宣告資料庫的欄位型態,官網已經突破了tuple只能22個的限制,建立class之後,協定一個變數 給 TableQuery[class_Name],稍後資料庫操作 對該變數進行操作即可。

import slick.driver.H2Driver.api._

import scala.concurrent.ExecutionContext.Implicits.globalimport scala.concurrent.Await
import scala.concurrent.duration._

object SlickGettingStart {

  def main(args: Array[String]): Unit = {
 
  }
    // Definition of the SUPPLIERS table
  class Suppliers(tag: Tag) extends Table[(Int, String, String, String, String, String)](tag, "SUPPLIERS") {
    def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column    
    def name = column[String]("SUP_NAME")
    def street = column[String]("STREET")
    def city = column[String]("CITY")
    def state = column[String]("STATE")
    def zip = column[String]("ZIP")
    // Every table needs a * projection with the same type as the table's type parameter    
    def * = (id, name, street, city, state, zip)
  }
  val suppliers = TableQuery[Suppliers]


// Definition of the COFFEES table  class Coffees(tag: Tag) extends Table[(String, Int, Double, Int, Int)](tag, "COFFEES") {
    def name = column[String]("COF_NAME", O.PrimaryKey)
    def supID = column[Int]("SUP_ID")
    def price = column[Double]("PRICE")
    def sales = column[Int]("SALES")
    def total = column[Int]("TOTAL")
    def * = (name, supID, price, sales, total)
    // A reified foreign key relation that can be navigated to create a join    
    def supplier = foreignKey("SUP_FK", supID, suppliers)(_.id)
  }
  val coffees = TableQuery[Coffees]
}

main區塊放業務流程執行語句

第一次使用→創建表格→插入資料
db.run(放要背拿對資料庫執行操作的語句),回傳型態為Future
Await.result(Future, duration)  監看指定future,及最長監看時間

q1, q2 則是對database進行資料操作,是不是很像在操作scala collection呢?

def main(args: Array[String]): Unit = {
  val db = Database.forConfig("h2mem1")

  try{
    val setup = DBIO.seq(
      // Create the tables, including primary and foreign keys      (suppliers.schema ++ coffees.schema).create,
      // Insert some suppliers      suppliers += (101, "Acme, Inc.",      "99 Market Street", "Groundsville", "CA", "95199"),      suppliers += ( 49, "Superior Coffee", "1 Party Place",    "Mendocino",    "CA", "95460"),      suppliers += (150, "The High Ground", "100 Coffee Lane",  "Meadows",      "CA", "93966"),      // Equivalent SQL code:      // insert into SUPPLIERS(SUP_ID, SUP_NAME, STREET, CITY, STATE, ZIP) values (?,?,?,?,?,?)
      // Insert some coffees (using JDBC's batch insert feature, if supported by the DB)      coffees ++= Seq(
        ("Colombian",         101, 7.99, 0, 0),        ("French_Roast",       49, 8.99, 0, 0),        ("Espresso",          150, 9.99, 0, 0),        ("Colombian_Decaf",   101, 8.99, 0, 0),        ("French_Roast_Decaf", 49, 9.99, 0, 0)
      )
      // Equivalent SQL code:      // insert into COFFEES(COF_NAME, SUP_ID, PRICE, SALES, TOTAL) values (?,?,?,?,?)    )
    val setupFuture = db.run(setup)

    Await.result(setupFuture, 3 seconds)

    println("Coffees:")
    val ttt = db.run(coffees.result).map(_.foreach {
      case (name, supID, price, sales, total) =>
        println("  " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" + total)
    })

    Await.result(ttt, 3 seconds)

    val q2 = for {
      c <- coffees if c.price < 9.0      s <- suppliers if s.id === c.supID
    } yield (c.name, s.name)

    db.stream(q2.result).foreach(println)


  }finally db.close



}


完整程式碼
/**  * Created by BingHongLi on 2016/11/19.  */

// Use H2Driver to connect to an H2 databaseimport slick.driver.H2Driver.api._

import scala.concurrent.ExecutionContext.Implicits.globalimport scala.concurrent.Await
import scala.concurrent.duration._
object SlickGettingStart {

  def main(args: Array[String]): Unit = {
//  告知使用此設定值,創立一物件 db
    val db = Database.forConfig("h2mem1")

    try{
//  第一次使用,先對資料庫進行建表,及新增資料
      val setup = DBIO.seq(
        // Create the tables, including primary and foreign keys        (suppliers.schema ++ coffees.schema).create,
        // Insert some suppliers        suppliers += (101, "Acme, Inc.",      "99 Market Street", "Groundsville", "CA", "95199"),        suppliers += ( 49, "Superior Coffee", "1 Party Place",    "Mendocino",    "CA", "95460"),        suppliers += (150, "The High Ground", "100 Coffee Lane",  "Meadows",      "CA", "93966"),        // Equivalent SQL code:        // insert into SUPPLIERS(SUP_ID, SUP_NAME, STREET, CITY, STATE, ZIP) values (?,?,?,?,?,?)
        // Insert some coffees (using JDBC's batch insert feature, if supported by the DB)        coffees ++= Seq(
          ("Colombian",         101, 7.99, 0, 0),          ("French_Roast",       49, 8.99, 0, 0),          ("Espresso",          150, 9.99, 0, 0),          ("Colombian_Decaf",   101, 8.99, 0, 0),          ("French_Roast_Decaf", 49, 9.99, 0, 0)
        )
        // Equivalent SQL code:        // insert into COFFEES(COF_NAME, SUP_ID, PRICE, SALES, TOTAL) values (?,?,?,?,?)      )
//  要求db執行此操作語句
      val setupFuture = db.run(setup)
//  由於操作是Future的,等待他完成
      Await.result(setupFuture, 3 seconds)

      println("Coffees:")
//  對資料庫進行操作
      val ttt = db.run(coffees.result).map(_.foreach {
        case (name, supID, price, sales, total) =>
          println("  " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" + total)
      })

      Await.result(ttt, 3 seconds)

      val q2 = for {
        c <- coffees if c.price < 9.0        s <- suppliers if s.id === c.supID
      } yield (c.name, s.name)

      db.stream(q2.result).foreach(println)


    }finally db.close



  }

  // Definition of the SUPPLIERS table  class Suppliers(tag: Tag) extends Table[(Int, String, String, String, String, String)](tag, "SUPPLIERS") {
    def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column    
    def name = column[String]("SUP_NAME")
    def street = column[String]("STREET")
    def city = column[String]("CITY")
    def state = column[String]("STATE")
    def zip = column[String]("ZIP")
    // Every table needs a * projection with the same type as the table's type parameter   
    def * = (id, name, street, city, state, zip)
  }
  val suppliers = TableQuery[Suppliers]


  // Definition of the COFFEES table  class Coffees(tag: Tag) extends Table[(String, Int, Double, Int, Int)](tag, "COFFEES") {
    def name = column[String]("COF_NAME", O.PrimaryKey)
    def supID = column[Int]("SUP_ID")
    def price = column[Double]("PRICE")
    def sales = column[Int]("SALES")
    def total = column[Int]("TOTAL")
    def * = (name, supID, price, sales, total)
    // A reified foreign key relation that can be navigated to create a join    
    def supplier = foreignKey("SUP_FK", supID, suppliers)(_.id)
  }
  val coffees = TableQuery[Coffees]


}



驗證

H2 Server有提供web 瀏覽畫面,我們可以連上去觀察是不是真的有資料在此。

然後用瀏覽器連回Server觀察
http://<docker host ip>:81

瀏覽器ui填寫位置
user與password均不填

額外備註事項

在使用slick的物件時,比較內容的話,請改用 === 三個等號
若要比較值 請多加一個 = , == 變成 ===, 依此類推

原因在於Any 基本型態已經有 == 不能被置換。