AWS, Scala, 後端工程師的部落格

今天要介紹的是

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 基本型態已經有 == 不能被置換。

AWS, Scala, 後端工程師的部落格

2016年11月23日星期三

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

實作準備素材

撰寫database設定檔

src/main/scala-2.11/WeTrySlick.scala

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

驗證

額外備註事項

標籤

網誌存檔

2016年11月23日 星期三

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

實作準備素材

撰寫database設定檔

src/main/scala-2.11/WeTrySlick.scala

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

驗證

額外備註事項

2016年11月23日星期三