前面几篇介绍里尝试了一些Slick的功能和使用方式，看来基本可以满足用scala语言进行数据库操作编程的要求，而且有些代码可以通过函数式编程模式来实现。我想，如果把Slick当作数据库操作编程主要方式的话，可能需要先制定一套比较规范的模式来应付日常开发（也要考虑团队开发）、测试和维护。首先从项目结构来说，我发现由Intellij-Idea IDE界面直接产生的SBT项目结构已经比较理想了。在src/main/resources是scala项目获取配置文件的默认目录、我们可以按照需要在src/main/scala下增加代码子目录（package）及在src/main/test下摆放测试代码。配置文件application.conf、logback.xml是放在src/main/resources下的。application.conf是Slick的配置文件，logback.xml是跟踪器logback（log4j）的配置文件。Slick把jdbc api集成到scala编程语言里，能够支持多种数据库。也就是说Slick提供了多种数据库的驱动api。Slick支持在配置文件application.conf里配置数据库功能模式，这样我们就可以在正式部署软件时才通过修订application.conf里的配置来决定具体的数据库种类和参数。当然前提是我们的程序代码不能依赖任何特别的数据库api。我们从表结构设定开始，先看看上篇Slick101里的例子：
1 package com.datatech.learn.slick101
2 import slick.driver.H2Driver.api._
3 object slick101 {
4
5 /* ----- schema */
6 //表字段对应模版
7 case class AlbumModel (id: Long
8 ,title: String
9 ,year: Option[Int]
10 ,artist: String
11 )
12 //表结构: 定义字段类型, * 代表结果集字段
13 class AlbumTable(tag: Tag) extends Table[AlbumModel](tag, "ALBUMS") {
14 def id = column[Long]("ID",O.AutoInc,O.PrimaryKey)
15 def title = column[String]("TITLE")
16 def year = column[Option[Int]]("YEAR")
17 def artist = column[String]("ARTIST",O.Default("Unknown"))
18 def * = (id,title,year,artist) <> (AlbumModel.tupled, AlbumModel.unapply)
19 }
20 //库表实例
21 val albums = TableQuery[AlbumTable]
我们可以看到这段代码依赖了slick.driver.H2Driver.api，是专门针对H2 Database的了。我们可以用依赖注入（dependency injection, IOC）来解决这个依赖问题。先试试用最传统的依赖注入方式：传入参数来注入这个数据库驱动依赖，把代码放在src/main/scala/model/TableDefs.scala里：
1 package com.bayakala.learn.slick301.model
2 import slick.driver.JdbcProfile
3 class TableDefs(val dbDriver: JdbcProfile) {
4 import dbDriver.api._
5 case class Supplier(id: Long
6 , name: String
7 , contact: Option[String]
8 , website: Option[String])
9 final class Suppliers(tag: Tag) extends Table[Supplier](tag,"SUPPLERS") {
10 def id = column[Long]("ID",O.AutoInc,O.PrimaryKey)
11 def name = column[String]("NAME")
12 def contact = column[Option[String]]("CONTACT")
13 def website = column[Option[String]]("WEBSITE")
14 def * = (id, name, contact, website) <> (Supplier.tupled,Supplier.unapply)
15 def nidx = index("NM_IDX",name,unique = true)
16 }
17 val suppliers = TableQuery[Suppliers]
18
19 case class Coffee(id: Long
20 ,name: String
21 ,supid: Long
22 ,price: Double
23 ,sales: Int)
24 final class Coffees(tag: Tag) extends Table[Coffee](tag, "COFFEES") {
25 def id = column[Long]("ID",O.AutoInc,O.PrimaryKey)
26 def name = column[String]("NAME")
27 def supid = column[Long]("SUPID")
28 def price = column[Double]("PRICE",O.Default(0.0))
29 def sales = column[Int]("SALES",O.Default(0))
30 def * = (id,name,supid,price,sales) <> (Coffee.tupled, Coffee.unapply)
31 def fk_sup = foreignKey("FK_SUP",supid,suppliers)(_.id,onDelete = ForeignKeyAction.Restrict,onUpdate = ForeignKeyAction.Cascade)
32 def supidx = index("SUP_IDX",supid,unique = false)
33 def nidx = index("NM_IDX",name,unique = true)
34 }
35 val coffees = TableQuery[Coffees]
36
37 }
注意我们是把JdbcProfile作为参数注入了class TableDefs里。如果TableDefs经常需要作为其它类的父类继承的话，设计成trait能更加灵活的进行类型混合（type mixing）。这样的需求可以用cake pattern方式进行依赖注入。我们在需要src/main/scala/config/AppConfig.scala里定义依赖界面trait DBConfig：
1 package com.bayakala.learn.slick301.config
2 import slick.driver.JdbcProfile
3 trait DBConfig {
4 val jdbcDriver: JdbcProfile
5 import jdbcDriver.api._
6 val db: Database
7 }
后面我们可以通过实现多种DBConfig实例方式来构建开发、测试、部署等数据库环境。为了方便示范，我们设计几个基本的Query Action，放在src/main/scala/access/DAOs.scala里，用cake pattern注入依赖DBConfig：
1 package com.bayakala.learn.slick301.access
2 import com.bayakala.learn.slick301.config
3 import com.bayakala.learn.slick301.config.DBConfig
4 import com.bayakala.learn.slick301.model.TableDefs
5 trait DAOs { dbconf: DBConfig =>
6 import jdbcDriver.api._
7 //注入依赖
8 val tables = new TableDefs(dbconf.jdbcDriver)
9 import tables._
10 //suppliers queries
11 val createSupplierTable = suppliers.schema.create
12 val allSuppliers = suppliers.result
13 def insertSupplier(id:Long,name:String,address:Option[String],website:Option[String])
14 = suppliers += Supplier(id,name,address,website)
15 def insertSupbyName(n: String) = suppliers.map(_.name) += n
16 //coffees queries
17 val createCoffeeTable = coffees.schema.create
18 val allCoffees = coffees.result
19 def insertCoffee(c: (Long,String,Long,Double,Int)) =
20 coffees += Coffee(id=c._1, name=c._2,supid=c._3,price=c._4,sales=c._5)
21
22 }
dbconf: DBConfig => 的意思是在进行DAOs的实例化时必须混入（mixing）DBConfig类。
以上两个代码文件TableDefs.scala和DAOs.scala在注入依赖后都能够顺利通过编译了。
我们在src/main/scala/main/Main.scala里测试运算DAOs里的query action：
1 package com.bayakala.learn.slick301.main
2 import com.bayakala.learn.slick301.config.DBConfig
3 import com.bayakala.learn.slick301.access.DAOs
4
5 import scala.concurrent.{Await, Future}
6 import scala.util.{Failure, Success}
7 import scala.concurrent.duration._
8 import scala.concurrent.ExecutionContext.Implicits.global
9 import slick.backend.DatabaseConfig
10 import slick.driver.{H2Driver, JdbcProfile}
11 object Main {
12
13 object Actions extends DAOs with DBConfig {
14 override lazy val jdbcDriver: JdbcProfile = H2Driver
15 val dbConf: DatabaseConfig[H2Driver] = DatabaseConfig.forConfig("h2")
16 override val db = dbConf.db
17 }
18 import Actions._
19
20 def main(args: Array[String]) = {
21 val res = db.run(createSupplierTable).andThen {
22 case Success(_) => println("supplier table created")
23 case Failure(_) => println("unable to create supplier table")
24 }
25 Await.ready(res, 3 seconds)
26
27 val res2 = db.run(insertSupbyName("Acme Coffee Co."))
28 Await.ready(res2, 3 seconds)
29
30 Await.ready(db.run(allSuppliers), 10 seconds).foreach(println)
31
32 val res10 = db.run(createCoffeeTable).andThen {
33 case Success(_) => println("coffee table created")
34 case Failure(_) => println("unable to create coffee table")
35 }
36 Await.ready(res10, 3 seconds)
37
38 val res11 = db.run(insertCoffee((101,"Columbia",1,158.0,0)))
39 Await.ready(res11, 3 seconds)
40
41 Await.ready(db.run(allCoffees), 10 seconds).foreach(println)
42
43 }
44
45 }
Actions是DAOs的实例。我们看到必须把DBConfig混入（mixin）。但是我们构建的数据库又变成了专门针对H2的api了，这样的话每次变动数据库对象我们就必须重新编译Main.scala，不符合上面我们提到的要求。我们可以把目标数据库放到application.conf里，然后在Main.scala里用typesafe-config实时根据application.conf里的设置确定数据库参数。src/main/resources/application.conf内容如下：
1 app = {
2 dbconfig = h2
3 }
4
5 h2 {
6 driver = "slick.driver.H2Driver$"
7 db {
8 url = "jdbc:h2:~/slickdemo;mv_store=false"
9 driver = "org.h2.Driver"
10 connectionPool = HikariCP
11 numThreads = 10
12 maxConnections = 12
13 minConnections = 4
14 keepAliveConnection = true
15 }
16 }
17
18 h2mem = {
19 url = "jdbc:h2:mem:slickdemo"
20 driver = org.h2.Driver
21 connectionPool = disabled
22 keepAliveConnection = true
23 }
24
25 mysql {
26 driver = "slick.driver.MySQLDriver$"
27 db {
28 url = "jdbc:mysql://localhost/slickdemo"
29 driver = com.mysql.jdbc.Driver
30 keepAliveConnection = true
31 user="root"
32 password="123"
33 numThreads=10
34 maxConnections = 12
35 minConnections = 4
36 }
37 }
38
39 mysqldb = {
40 dataSourceClass = "com.mysql.jdbc.jdbc2.optional.MysqlDataSource"
41 properties {
42 user = "root"
43 password = "123"
44 databaseName = "slickdemo"
45 serverName = "localhost"
46 }
47 numThreads = 10
48 maxConnections = 12
49 minConnections = 4
50 }
51
52 postgres {
53 driver = "slick.driver.PostgresDriver$"
54 db {
55 url = "jdbc:postgresql://127.0.0.1/slickdemo"
56 driver = "org.postgresql.Driver"
57 connectionPool = HikariCP
58 user = "slick"
59 password = "123"
60 numThreads = 10
61 maxConnections = 12
62 minConnections = 4
63 }
64 }
65
66 postgressdb = {
67 dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"
68 properties = {
69 databaseName = "slickdemo"
70 user = "slick"
71 password = "123"
72 }
73 connectionPool = HikariCP
74 numThreads = 10
75 maxConnections = 12
76 minConnections = 4
77 }
78
79 mssql {
80 driver = "com.typesafe.slick.driver.ms.SQLServerDriver$"
81 db {
82 url = "jdbc:sqlserver://host:port"
83 driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
84 connectionTimeout = 30 second
85 connectionPool = HikariCP
86 user = "slick"
87 password = "123"
88 numThreads = 10
89 maxConnections = 12
90 minConnections = 4
91 keepAliveConnection = true
92 }
93 }
94
95 tsql {
96 driver = "slick.driver.H2Driver$"
97 db = ${h2mem}
98 }
现在application.conf里除了数据库配置外又加了个app配置。我们在Main.scala里实例化DAOs时可以用typesafe-config读取app.dbconfig值后设定jdbcDriver和db：
1 object Actions extends DAOs with DBConfig {
2 import slick.util.ClassLoaderUtil
3 import scala.util.control.NonFatal
4 import com.typesafe.config.ConfigFactory
5
6 def getDbConfig: String =
7 ConfigFactory.load().getString("app.dbconfig")
8
9 def getDbDriver(path: String): JdbcProfile = {
10 val config = ConfigFactory.load()
11 val n = config.getString((if (path.isEmpty) "" else path + ".") + "driver")
12 val untypedP = try {
13 if (n.endsWith("$")) ClassLoaderUtil.defaultClassLoader.loadClass(n).getField("MODULE$").get(null)
14 else ClassLoaderUtil.defaultClassLoader.loadClass(n).newInstance()
15 } catch {
16 case NonFatal(ex) =>
17 throw new SlickException(s"""Error getting instance of Slick driver "$n"""", ex)
18 }
19 untypedP.asInstanceOf[JdbcProfile]
20 }
21
22 override lazy val jdbcDriver: JdbcProfile = getDbDriver(getDbConfig)
23 val dbConf: DatabaseConfig[JdbcProfile] = DatabaseConfig.forConfig(getDbConfig)
24 override val db = dbConf.db
25 }
现在我们只需要改变application.conf里的app.dbconfig就可以转换目标数据库参数了。实际上，除了数据库配置，我们还可以在application.conf里进行其它类型的配置。然后用typesafe-config实时读取。如果不想在application.conf进行数据库之外的配置，可以把其它配置放在任何文件里，然后用ConfigFactory.load(path)来读取。
另外，在软件开发过程中跟踪除错也是很重要的。我们可以用logback来跟踪Slick、HikariCP等库的运行状态。logback配置在src/main/resources/logback.xml：
1 <?xml version="1.0" encoding="UTF-8"?>
2
3 <configuration>
4 <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
5 <encoder>
6 <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern>
7 </encoder>
8 </appender>
9
10 <logger name="application" level="DEBUG"/>
11 <logger name="com.zaxxer.hikari" level="DEBUG"/>
12 <logger name="slick" level="DEBUG"/>
13
14 <root level="DEBUG">
15 <appender-ref ref="STDOUT"/>
16 </root>
17 </configuration>
DEBUG值可以显示最详细的状态信息。
好了，我把这次示范代码提供在下面：
build.sbt：
1 name := "learn-slick301"
2
3 version := "1.0"
4
5 scalaVersion := "2.11.8"
6
7 libraryDependencies ++= Seq(
8 "com.typesafe.slick" %% "slick" % "3.1.1",
9 "com.h2database" % "h2" % "1.4.191",
10 "com.typesafe.slick" %% "slick-hikaricp" % "3.1.1",
11 "ch.qos.logback" % "logback-classic" % "1.1.7",
12 "org.typelevel" %% "cats" % "0.7.2"
13
14 )
src/main/resources/
application.conf:
1 app = {
2 dbconfig = h2
3 }
4
5 h2 {
6 driver = "slick.driver.H2Driver$"
7 db {
8 url = "jdbc:h2:~/slickdemo;mv_store=false"
9 driver = "org.h2.Driver"
10 connectionPool = HikariCP
11 numThreads = 10
12 maxConnections = 12
13 minConnections = 4
14 keepAliveConnection = true
15 }
16 }
17
18 h2mem = {
19 url = "jdbc:h2:mem:slickdemo"
20 driver = org.h2.Driver
21 connectionPool = disabled
22 keepAliveConnection = true
23 }
24
25 mysql {
26 driver = "slick.driver.MySQLDriver$"
27 db {
28 url = "jdbc:mysql://localhost/slickdemo"
29 driver = com.mysql.jdbc.Driver
30 keepAliveConnection = true
31 user="root"
32 password="123"
33 numThreads=10
34 maxConnections = 12
35 minConnections = 4
36 }
37 }
38
39 mysqldb = {
40 dataSourceClass = "com.mysql.jdbc.jdbc2.optional.MysqlDataSource"
41 properties {
42 user = "root"
43 password = "123"
44 databaseName = "slickdemo"
45 serverName = "localhost"
46 }
47 numThreads = 10
48 maxConnections = 12
49 minConnections = 4
50 }
51
52 postgres {
53 driver = "slick.driver.PostgresDriver$"
54 db {
55 url = "jdbc:postgresql://127.0.0.1/slickdemo"
56 driver = "org.postgresql.Driver"
57 connectionPool = HikariCP
58 user = "slick"
59 password = "123"
60 numThreads = 10
61 maxConnections = 12
62 minConnections = 4
63 }
64 }
65
66 postgressdb = {
67 dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"
68 properties = {
69 databaseName = "slickdemo"
70 user = "slick"
71 password = "123"
72 }
73 connectionPool = HikariCP
74 numThreads = 10
75 maxConnections = 12
76 minConnections = 4
77 }
78
79 mssql {
80 driver = "com.typesafe.slick.driver.ms.SQLServerDriver$"
81 db {
82 url = "jdbc:sqlserver://host:port"
83 driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
84 connectionTimeout = 30 second
85 connectionPool = HikariCP
86 user = "slick"
87 password = "123"
88 numThreads = 10
89 maxConnections = 12
90 minConnections = 4
91 keepAliveConnection = true
92 }
93 }
94
95 tsql {
96 driver = "slick.driver.H2Driver$"
97 db = ${h2mem}
98 }
logback.xml:
1 <?xml version="1.0" encoding="UTF-8"?>
2
3 <configuration>
4 <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
5 <encoder>
6 <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern>
7 </encoder>
8 </appender>
9
10 <logger name="application" level="DEBUG"/>
11 <logger name="com.zaxxer.hikari" level="DEBUG"/>
12 <logger name="slick" level="DEBUG"/>
13
14 <root level="DEBUG">
15 <appender-ref ref="STDOUT"/>
16 </root>
17 </configuration>
src/main/scala/config/AppConfig.scala:
1 package com.bayakala.learn.slick301.config
2 import slick.driver.JdbcProfile
3 trait DBConfig {
4 val jdbcDriver: JdbcProfile
5 import jdbcDriver.api._
6 val db: Database
7 }
src/main/scala/model/TableDefs.scala:
1 package com.bayakala.learn.slick301.model
2 import slick.driver.JdbcProfile
3 class TableDefs(val dbDriver: JdbcProfile) {
4 import dbDriver.api._
5 case class Supplier(id: Long
6 , name: String
7 , contact: Option[String]
8 , website: Option[String])
9 final class Suppliers(tag: Tag) extends Table[Supplier](tag,"SUPPLERS") {
10 def id = column[Long]("ID",O.AutoInc,O.PrimaryKey)
11 def name = column[String]("NAME")
12 def contact = column[Option[String]]("CONTACT")
13 def website = column[Option[String]]("WEBSITE")
14 def * = (id, name, contact, website) <> (Supplier.tupled,Supplier.unapply)
15 def nidx = index("NM_IDX",name,unique = true)
16 }
17 val suppliers = TableQuery[Suppliers]
18
19 case class Coffee(id: Long
20 ,name: String
21 ,supid: Long
22 ,price: Double
23 ,sales: Int)
24 final class Coffees(tag: Tag) extends Table[Coffee](tag, "COFFEES") {
25 def id = column[Long]("ID",O.AutoInc,O.PrimaryKey)
26 def name = column[String]("NAME")
27 def supid = column[Long]("SUPID")
28 def price = column[Double]("PRICE",O.Default(0.0))
29 def sales = column[Int]("SALES",O.Default(0))
30 def * = (id,name,supid,price,sales) <> (Coffee.tupled, Coffee.unapply)
31 def fk_sup = foreignKey("FK_SUP",supid,suppliers)(_.id,onDelete = ForeignKeyAction.Restrict,onUpdate = ForeignKeyAction.Cascade)
32 def supidx = index("SUP_IDX",supid,unique = false)
33 def nidx = index("NM_IDX",name,unique = true)
34 }
35 val coffees = TableQuery[Coffees]
36
37 }
src/main/scala/access/DAOs.scala:
1 package com.bayakala.learn.slick301.access
2 import com.bayakala.learn.slick301.config
3 import com.bayakala.learn.slick301.config.DBConfig
4 import com.bayakala.learn.slick301.model.TableDefs
5 trait DAOs { dbconf: DBConfig =>
6 import jdbcDriver.api._
7 //注入依赖
8 val tables = new TableDefs(dbconf.jdbcDriver)
9 import tables._
10 //suppliers queries
11 val createSupplierTable = suppliers.schema.create
12 val allSuppliers = suppliers.result
13 def insertSupplier(id:Long,name:String,address:Option[String],website:Option[String])
14 = suppliers += Supplier(id,name,address,website)
15 def insertSupbyName(n: String) = suppliers.map(_.name) += n
16 //coffees queries
17 val createCoffeeTable = coffees.schema.create
18 val allCoffees = coffees.result
19 def insertCoffee(c: (Long,String,Long,Double,Int)) =
20 coffees += Coffee(id=c._1, name=c._2,supid=c._3,price=c._4,sales=c._5)
21
22 }
src/main/scala/main/Main.scala:
1 package com.bayakala.learn.slick301.main
2 import com.bayakala.learn.slick301.config.DBConfig
3 import com.bayakala.learn.slick301.access.DAOs
4
5 import scala.concurrent.Await
6 import scala.util.{Failure, Success}
7 import scala.concurrent.duration._
8 import scala.concurrent.ExecutionContext.Implicits.global
9 import slick.backend.DatabaseConfig
10 import slick.driver.JdbcProfile
11
12 object Main {
13
14 object Actions extends DAOs with DBConfig {
15 import slick.SlickException
16 import slick.util.ClassLoaderUtil
17 import scala.util.control.NonFatal
18 import com.typesafe.config.ConfigFactory
19
20 def getDbConfig: String =
21 ConfigFactory.load().getString("app.dbconfig")
22
23 def getDbDriver(path: String): JdbcProfile = {
24 val config = ConfigFactory.load()
25 val n = config.getString((if (path.isEmpty) "" else path + ".") + "driver")
26 val untypedP = try {
27 if (n.endsWith("$")) ClassLoaderUtil.defaultClassLoader.loadClass(n).getField("MODULE$").get(null)
28 else ClassLoaderUtil.defaultClassLoader.loadClass(n).newInstance()
29 } catch {
30 case NonFatal(ex) =>
31 throw new SlickException(s"""Error getting instance of Slick driver "$n"""", ex)
32 }
33 untypedP.asInstanceOf[JdbcProfile]
34 }
35
36 override lazy val jdbcDriver: JdbcProfile = getDbDriver(getDbConfig)
37 val dbConf: DatabaseConfig[JdbcProfile] = DatabaseConfig.forConfig(getDbConfig)
38 override val db = dbConf.db
39 }
40 import Actions._
41
42
43 def main(args: Array[String]) = {
44
45 val res = db.run(createSupplierTable).andThen {
46 case Success(_) => println("supplier table created")
47 case Failure(_) => println("unable to create supplier table")
48 }
49 Await.ready(res, 3 seconds)
50
51 val res2 = db.run(insertSupbyName("Acme Coffee Co."))
52 Await.ready(res2, 3 seconds)
53
54 Await.ready(db.run(allSuppliers), 10 seconds).foreach(println)
55
56 val res10 = db.run(createCoffeeTable).andThen {
57 case Success(_) => println("coffee table created")
58 case Failure(_) => println("unable to create coffee table")
59 }
60 Await.ready(res10, 3 seconds)
61
62 val res11 = db.run(insertCoffee((101,"Columbia",1,158.0,0)))
63 Await.ready(res11, 3 seconds)
64
65 Await.ready(db.run(allCoffees), 10 seconds).foreach(println)
66
67 }
原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/12884.html