今天要介紹的是
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
用IntelliJ 開啟一個sbt project
假定聽眾的scala版本為2.11+ (非2.12)
build.sbt 新增下面內容
"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 檔案,並將設定寫在這裡。
}
實際範例
}
官網有特別註記,連結池的用法要特別注意
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 tableclass 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 columndef 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 parameterdef * = (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 joindef 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 瀏覽畫面,我們可以連上去觀察是不是真的有資料在此。
http://<docker host ip>:81
額外備註事項
在使用slick的物件時,比較內容的話,請改用 === 三個等號