about 4 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
)
編譯部分的設定
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
執行產生的主要程式
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產生物件
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來做一些常用的資料庫操作
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
