over 2 years ago

使用以下版本練習
Scala 2.11.4
Slick 2.1.0
Mariadb 5.5.40 安裝方式參考在CentOS上安裝MariaDB

搞這個之前呢....
請先把你IntelliJ更新到最新,把scala跟sbt的plugin也更新到最新,不然你可能連編譯都不過
為了這個我卡了兩個禮拜了,結果全部重裝&更新後就可以編譯了(/‵Д′)/~ ╧╧

資料庫

--用戶錢包
CREATE TABLE wallet (
  userid varchar(50) NOT NULL,             --用戶ID
  amount int(11) NOT NULL,                  --錢包金額
  KEY userid (userid)
)
--消費與儲值記錄
CREATE TABLE record (
  serno bigint(20) NOT NULL AUTO_INCREMENT, --交易流水號(自增鍵)
  userid varchar(50) NOT NULL,              --用戶ID
  type int(11) NOT NULL,                    --交易類別0:儲值,1:消費
  amount int(11) DEFAULT NULL,              --交易金額
  timecreate datetime NOT NULL,               --交易時間
  PRIMARY KEY (serno),
  KEY record_ibfk_1 (userid),
  CONSTRAINT record_ibfk_1 FOREIGN KEY (userid) REFERENCES wallet (userid) ON DELETE NO ACTION ON UPDATE NO ACTION
)

編譯部分的設定

build.sbt
name := "SlickCodegen"

version := "1.0"

scalaVersion := "2.11.4"

libraryDependencies ++= Seq(
  "com.typesafe.slick" % "slick_2.11" % "2.1.0",
  "com.typesafe.slick" % "slick-codegen_2.11" % "2.1.0",
  "org.mariadb.jdbc" % "mariadb-java-client" % "1.1.7"
)

libraryDependencies += "org.scala-lang" % "scala-reflect" % scalaVersion.value

這邊是可以正常編譯了,但是執行時會出現NoclassNotFund xxx.scla....
What?這啥鬼?感覺上是IDE沒處理好....我試用了sbteclipse就沒事
所以加上了這句請IDE下在我指定版本Scala來用...XD
libraryDependencies += "org.scala-lang" % "scala-reflect" % scalaVersion.value

執行產生的主要程式

SlickCodegen.scala
package com.sam.codegen

import scala.slick.codegen.SourceCodeGenerator

object SlickCodegen {

  def main(args: Array[String]): Unit = {
    val slickDriver = "scala.slick.driver.MySQLDriver"
    val jdbcDriver = "org.mariadb.jdbc.Driver"
    val url = "jdbc:mysql://192.168.200.128:3306/mydb?characterEncoding=UTF-8&useUnicode=true"
    val outputFolder = "src/main/scala"
    val pkg = "com.example.models"
    var user = "root"
    var password = "!QAZ2wsx"

    SourceCodeGenerator.main(
      Array(
        slickDriver,
        jdbcDriver,
        url,
        outputFolder,
        pkg,
        user,
        password
      )
    )
  }
}

執行後會在你指定的package產生物件

Tables.scala
package com.example.models
// AUTO-GENERATED Slick data model
/** Stand-alone Slick data model for immediate use */
object Tables extends {
  val profile = scala.slick.driver.MySQLDriver
} with Tables

/** Slick data model trait for extension, choice of backend or usage in the cake pattern. (Make sure to initialize this late.) */
trait Tables {
  val profile: scala.slick.driver.JdbcProfile
  import profile.simple._
  import scala.slick.model.ForeignKeyAction
  // NOTE: GetResult mappers for plain SQL are only generated for tables where Slick knows how to map the types of all columns.
  import scala.slick.jdbc.{GetResult => GR}
  
  /** DDL for all tables. Call .create to execute. */
  lazy val ddl = Record.ddl ++ Wallet.ddl
  
  /** Entity class storing rows of table Record
   *  @param serno Database column serno DBType(BIGINT), AutoInc, PrimaryKey
   *  @param userid Database column userid DBType(VARCHAR), Length(50,true)
   *  @param `type` Database column type DBType(INT)
   *  @param amount Database column amount DBType(INT), Default(None)
   *  @param timecreate Database column timecreate DBType(DATETIME) */
  case class RecordRow(serno: Long, userid: String, `type`: Int, amount: Option[Int] = None, timecreate: java.sql.Timestamp)
  /** GetResult implicit for fetching RecordRow objects using plain SQL queries */
  implicit def GetResultRecordRow(implicit e0: GR[Long], e1: GR[String], e2: GR[Int], e3: GR[Option[Int]], e4: GR[java.sql.Timestamp]): GR[RecordRow] = GR{
    prs => import prs._
    RecordRow.tupled((<<[Long], <<[String], <<[Int], <<?[Int], <<[java.sql.Timestamp]))
  }
  /** Table description of table record. Objects of this class serve as prototypes for rows in queries.
   *  NOTE: The following names collided with Scala keywords and were escaped: type */
  class Record(_tableTag: Tag) extends Table[RecordRow](_tableTag, "record") {
    def * = (serno, userid, `type`, amount, timecreate) <> (RecordRow.tupled, RecordRow.unapply)
    /** Maps whole row to an option. Useful for outer joins. */
    def ? = (serno.?, userid.?, `type`.?, amount, timecreate.?).shaped.<>({r=>import r._; _1.map(_=> RecordRow.tupled((_1.get, _2.get, _3.get, _4, _5.get)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))
    
    /** Database column serno DBType(BIGINT), AutoInc, PrimaryKey */
    val serno: Column[Long] = column[Long]("serno", O.AutoInc, O.PrimaryKey)
    /** Database column userid DBType(VARCHAR), Length(50,true) */
    val userid: Column[String] = column[String]("userid", O.Length(50,varying=true))
    /** Database column type DBType(INT)
     *  NOTE: The name was escaped because it collided with a Scala keyword. */
    val `type`: Column[Int] = column[Int]("type")
    /** Database column amount DBType(INT), Default(None) */
    val amount: Column[Option[Int]] = column[Option[Int]]("amount", O.Default(None))
    /** Database column timecreate DBType(DATETIME) */
    val timecreate: Column[java.sql.Timestamp] = column[java.sql.Timestamp]("timecreate")
    
    /** Foreign key referencing Wallet (database name record_ibfk_1) */
    lazy val walletFk = foreignKey("record_ibfk_1", userid, Wallet)(r => r.userid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)
  }
  /** Collection-like TableQuery object for table Record */
  lazy val Record = new TableQuery(tag => new Record(tag))
  
  /** Entity class storing rows of table Wallet
   *  @param userid Database column userid DBType(VARCHAR), Length(50,true)
   *  @param amount Database column amount DBType(INT) */
  case class WalletRow(userid: String, amount: Int)
  /** GetResult implicit for fetching WalletRow objects using plain SQL queries */
  implicit def GetResultWalletRow(implicit e0: GR[String], e1: GR[Int]): GR[WalletRow] = GR{
    prs => import prs._
    WalletRow.tupled((<<[String], <<[Int]))
  }
  /** Table description of table wallet. Objects of this class serve as prototypes for rows in queries. */
  class Wallet(_tableTag: Tag) extends Table[WalletRow](_tableTag, "wallet") {
    def * = (userid, amount) <> (WalletRow.tupled, WalletRow.unapply)
    /** Maps whole row to an option. Useful for outer joins. */
    def ? = (userid.?, amount.?).shaped.<>({r=>import r._; _1.map(_=> WalletRow.tupled((_1.get, _2.get)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))
    
    /** Database column userid DBType(VARCHAR), Length(50,true) */
    val userid: Column[String] = column[String]("userid", O.Length(50,varying=true))
    /** Database column amount DBType(INT) */
    val amount: Column[Int] = column[Int]("amount")
    
    /** Index over (userid) (database name userid) */
    val index1 = index("userid", userid)
  }
  /** Collection-like TableQuery object for table Wallet */
  lazy val Wallet = new TableQuery(tag => new Wallet(tag))
}

專案執行結果

接下來使用Tables.scala來做一些常用的資料庫操作

HelloSlick.scala
package com.example.models

import java.text.SimpleDateFormat
import java.sql.Timestamp
import java.util.Date

import scala.slick.lifted.TableQuery
import com.example.models.Tables._
import scala.slick.driver.MySQLDriver.simple._

/**
 * Created by SAM on 2015/01/04.
 */
object HelloSlick {
  //val members: TableQuery[Member] = TableQuery[Member]
  val sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")

  def ts(str: String) = new Timestamp(sdf.parse(str).getTime)

  def main(args: Array[String]): Unit = {
    Database.forURL(
      url = "jdbc:mysql://192.168.200.128:3306/mydb?characterEncoding=UTF-8&useUnicode=true",
      user = "root",
      password = "!QAZ2wsx",
      driver = "org.mariadb.jdbc.Driver") withSession {
      implicit session =>
        //檢查是否有表格並清掉後重建
        try {
          Tables.ddl.drop
          Tables.ddl.create
        } catch {
          case _ => println("table not found")
        }

        //新增一筆錢包用戶
        Wallet += WalletRow("100", 0)

        //批次新增多筆用戶
        Wallet ++= Seq(
          WalletRow("101", 5000),
          WalletRow("102", 300)
        )

        //新增一筆消費紀錄並回傳自動增長的序號
        val serno1 = (Record returning Record.map(_.serno)) += RecordRow(0, "100", 0, Option(100), new Timestamp(System.currentTimeMillis()))
        println("serno1 => " + serno1)

        //新增一筆特定欄位,其餘欄位為預設值,但是這邊會回傳的值為執行成功與否
        Record.map(m => (m.userid, m.`type`, m.amount, m.timecreate)) +=("100", 0, Option(110), new Timestamp(System.currentTimeMillis()))

        //新增指定欄位並回傳自動增長的序號
        val serno2 = (Record.map(m => (m.userid, m.`type`, m.amount, m.timecreate)) returning Record.map(_.serno)) +=("100", 0, Option(120), new Timestamp(System.currentTimeMillis()))
        println("serno2 => " + serno2)

        //可以觀察組合出來的SQL
        val statement = Record.insertStatement
        println(statement)
        //insert into `record` (`userid`,`type`,`amount`,`timecreate`)  values (?,?,?,?)

        val invoker = Record.insertInvoker
        println(invoker)
        //scala.slick.driver.JdbcInsertInvokerComponent$CountingInsertInvoker@7e5afaa6

        //搜尋並讀出每一筆資料的email

        val q = Record
          .filter(_.userid inSet Set("100", "101", "102"))
          .filter(_.amount === 100)
          .sortBy(_.timecreate.desc.nullsFirst)
        //println("q: " + q.selectStatement);
        for (r <- q.list) {
          println("Value of Amount: " + r.amount);
        }

        //取出第一個
        val r1 = q.first
        println(r1)
        //RecordRow(1,100,0,Some(100),2015-01-05 00:21:09.0)

        //使用Join方式搜尋
        val useridlist = List("100", "101", "102")
        val q2 = for {
          a <- Wallet
          b <- Record
          if a.userid === b.userid
          if a.userid inSet useridlist
        } yield (a.userid, b.amount, b.timecreate)
        //println("q2: " + q2.selectStatement)
        //select x2.`userid`, x3.`amount`, x3.`timecreate` from `wallet` x2, `record` x3 where x2.`userid` = x3.`userid`
        for (r <- q.list) {
          println("ID:" + r.userid + ",Amount:" + r.amount + ",Time:" + r.timecreate);
        }

        //更新部分先找出對象再執行更新
        val q3 = for {
          r <- Record
          if r.userid === "100"
          if r.serno === (3).toLong
        } yield (r.serno, r.`type`, r.amount)
        //println("q3: " + q3.updateStatement)
        //update `record` set `serno` = ?, `type` = ?, `amount` = ? where (`record`.`userid` = '100') and (`record`.`serno` = 3)
        q3.update((3).toLong, 0, Option(500))

        //將查詢方法的結果刪除
        q.delete
    }
  }
}

如果要做到Transaction也很簡單
只要把withSession換成withTransaction
當發生Exception時會自動觸發rollback

session.withTransaction {
  // your queries go here
  if (/* some failure */ false){
    session.rollback // signals Slick to rollback later
  }
} // <- rollback happens here, if an exception was thrown or session.rollback was called

參考資料
Slick でテーブル定義のコードを自動生成する
http://slick.typesafe.com/
Defining database queries with Slick 1.0.1
Slick for Database Access in Scala

← Quartz排程透過Spring執行一般Bean方法 Spark teaching (1) -Hadoop2.6 installation →
 
comments powered by Disqus