看完Slick官方网站上关于Slick3.1.1技术文档后决定开始动手建一个项目来尝试一下Slick功能的具体使用方法。我把这个过程中的一些了解和想法记录下来和大家一起分享。首先我用IntelliJ-Idea创建了一个scala项目。下一步就是如何选择数据库了。Slick是集成jdbc的更高层的Query编程语言,可以通过jdbc的url、DataSource等来指定目标数据库类型及相关的参数。对应Slick中的具体函数有:
<span style="font-size:14px;">val db = Database.forConfig("mydb")
val db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", driver="org.h2.Driver")
val db = Database.forDataSource(dataSource: slick.jdbc.DatabaseUrlDataSource)</span>
在Slick的Database配置方面forConfig("confItem")是比较灵活、方便实用的。confItem是resources/application.conf文件里的一个配置项目。Slick是通过typesafe-config来解析配置文件的。forConfig函数用typesafe-config库里的函数载入application.conf文件解析confItem并获取项目里的数据库配置参数,下面是项目中resources/application.conf文件内容:
h2mem {
url = "jdbc:h2:mem:slickdemo"
driver = "org.h2.Driver"
connectionPool = disabled
keepAliveConnection = true
}
h2 = {
url = "jdbc:h2:~/slickdemo;mv_store=false;MODE=MSSQLServer;DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE"
driver = org.h2.Driver
connectionPool = disabled
keepAliveConnection = true
}
mysql {
driver = "slick.driver.MySQLDriver$"
db {
url = "jdbc:mysql://localhost/slickdemo"
driver = com.mysql.jdbc.Driver
keepAliveConnection = true
user="root"
password="123"
numThreads=10
maxConnections = 12
minConnections = 4
}
}
mysqldb = {
dataSourceClass = "com.mysql.jdbc.jdbc2.optional.MysqlDataSource"
properties {
user = "root"
password = "123"
databaseName = "slickdemo"
serverName = "localhost"
}
numThreads = 10
maxConnections = 12
minConnections = 4
}
postgres {
driver = "slick.driver.PostgresDriver$"
db {
url = "jdbc:postgresql://127.0.0.1/slickdemo"
driver = "org.postgresql.Driver"
connectionPool = HikariCP
user = "slick"
password = "123"
numThreads = 10
maxConnections = 12
minConnections = 4
}
}
postgressdb = {
dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"
properties = {
databaseName = "slickdemo"
user = "slick"
password = "123"
}
connectionPool = HikariCP
numThreads = 10
maxConnections = 12
minConnections = 4
}
mssql {
driver = "com.typesafe.slick.driver.ms.SQLServerDriver$"
db {
url = "jdbc:sqlserver://host:port"
driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
connectionTimeout = 30 second
connectionPool = HikariCP
user = "slick"
password = "123"
numThreads = 10
maxConnections = 12
minConnections = 4
keepAliveConnection = true
}
}
tsql {
driver = "slick.driver.H2Driver$"
db = ${h2mem}
}
好了,选择了数据库,下面我们就来试试使用它。基本流程是这样的:首先在数据库里创建表,跟着写入一些数据,然后再读出显示。整个过程会涉及:表结构schema定义,数据插写Insert,数据读取Query及简单的Query运算方法和数据显示方法。
现在我们先设计表结构schema:
<span style="font-size:14px;">package com.datatech.learn.slick101
import slick.driver.H2Driver.api._
object slick101 {
/* ----- schema */
//表字段对应模版
case class AlbumModel (id: Long
,title: String
,year: Option[Int]
,artist: String
)
//表结构: 定义字段类型, * 代表结果集字段
class AlbumTable(tag: Tag) extends Table[AlbumModel](tag, "ALBUMS") {
def id = column[Long]("ID",O.AutoInc,O.PrimaryKey)
def title = column[String]("TITLE")
def year = column[Option[Int]]("YEAR")
def artist = column[String]("ARTIST",O.Default("Unknown"))
def * = (id,title,year,artist) <> (AlbumModel.tupled, AlbumModel.unapply)
}
//库表实例
val albums = TableQuery[AlbumTable]</span>
现在需要从库表实例albums产生它的schema,然后转换成一个DBIOAction:
<span style="font-size:14px;"> //创建表动作
val createTableAction = albums.schema.create
</span>
这个createTableAction就是个DBIOAction:一个效果描述。我们必须用具体的实现方式Database.run来运算产生实际效果:
<span style="font-size:14px;"> //数据库实例化
val db = Database.forConfig("h2")
def main(args: Array[String]): Unit = {
val res = db.run(createTableAction).andThen {
case Success(_) => println("table ALBUMS created.")
case Failure(e) => println(e.getMessage)
}
Await.result(res, 10 seconds)
}
</span>
<span style="font-size:14px;">table ALBUMS created.
Process finished with exit code 0
</span>
<span style="font-size:14px;">Exception in thread "main" org.h2.jdbc.JdbcSQLException: Table "ALBUMS" already exists; SQL statement:
Table "ALBUMS" already exists; SQL statement: ...
</span>
下面是一个插入数据的动作:
<span style="font-size:14px;">//插入数据动作
val insertAlbumsAction =
albums ++= Seq(
AlbumModel(0, "Keyboard Cat", Some(2003), "Keyboard Cat's Greatest Hits"),
AlbumModel(0, "Spice Girls", Some(2010), "Spice"),
AlbumModel(0, "Rick Astley", Some(1998), "Whenever You Need Somebody"),
AlbumModel(0, "Manowar", None,"The Triumph of Steel"),
AlbumModel(0, "Justin Bieber", Some(2011),"Believe"))
</span>
<span style="font-size:14px;"> val res2 = db.run(insertAlbumsAction).andThen {
case Success(_) => println("albums inserted.")
case Failure(e) => println(e.getMessage)
}
Await.result(res2, 10 seconds)
---
table ALBUMS created.
albums inserted.
Process finished with exit code 0
</span>
下面是抽取动作和数据显示函数。我们把新插入的数据再读出来验证插入情况:
//数据抽取动作
val selectAlbumsAction =
albums.result
<span style="font-size:14px;"> def printResults[T](fut: Future[Iterable[T]]): Unit =
Await.result(fut, Duration.Inf).foreach(println)
</span>
<span style="font-size:14px;"> val res3 = db.run(selectAlbumsAction)
printResults(res3)
</span>
<span style="font-size:14px;">AlbumModel(1,Keyboard Cat,Some(2003),Keyboard Cat's Greatest Hits)
AlbumModel(2,Spice Girls,Some(2010),Spice)
AlbumModel(3,Rick Astley,Some(1998),Whenever You Need Somebody)
AlbumModel(4,Manowar,None,The Triumph of Steel)
AlbumModel(5,Justin Bieber,Some(2011),Believe)
Process finished with exit code 0
</span>
下面是完整的示范代码:
<span style="font-size:14px;">package com.datatech.learn.slick101
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.duration._
import scala.concurrent.{Await, Future}
import scala.util.{Success,Failure}
import slick.driver.H2Driver.api._
object slick101 {
/* ----- schema */
//表字段对应模版
case class AlbumModel(id: Long
, artist: String
, year: Option[Int]
, title: String
)
//表结构: 定义字段类型, * 代表结果集字段
class AlbumTable(tag: Tag) extends Table[AlbumModel](tag, "ALBUMS") {
def id = column[Long]("ID", O.AutoInc, O.PrimaryKey)
def title = column[String]("TITLE")
def year = column[Option[Int]]("YEAR")
def artist = column[String]("ARTIST", O.Default("Unknown"))
def * = (id, artist, year, title) <> (AlbumModel.tupled, AlbumModel.unapply)
}
//库表实例
val albums = TableQuery[AlbumTable]
//创建表动作
val createTableAction = albums.schema.create
//数据库实例化
val db = Database.forConfig("h2")
//插入数据动作
val insertAlbumsAction =
albums ++= Seq(
AlbumModel(0, "Keyboard Cat", Some(2003), "Keyboard Cat's Greatest Hits"),
AlbumModel(0, "Spice Girls", Some(2010), "Spice"),
AlbumModel(0, "Rick Astley", Some(1998), "Whenever You Need Somebody"),
AlbumModel(0, "Manowar", None,"The Triumph of Steel"),
AlbumModel(0, "Justin Bieber", Some(2011),"Believe"))
//数据抽取动作
val selectAlbumsAction =
albums.result
def printResults[T](fut: Future[Iterable[T]]): Unit =
Await.result(fut, Duration.Inf).foreach(println)
def main(args: Array[String]): Unit = {
val res = db.run(createTableAction).andThen {
case Success(_) => println("table ALBUMS created.")
case Failure(e) => println(e.getMessage)
}
Await.result(res, 10 seconds)
val res2 = db.run(insertAlbumsAction).andThen {
case Success(_) => println("albums inserted.")
case Failure(e) => println(e.getMessage)
}
Await.result(res2, 10 seconds)
val res3 = db.run(selectAlbumsAction)
printResults(res3)
}
}</span>