浅谈Slick（4）－ Slick301：我的Slick开发项目设置

前面几篇介绍里尝试了一些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里的例子：

 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]

我们可以看到这段代码依赖了slick.driver.H2Driver.api，是专门针对H2 Database的了。我们可以用依赖注入（dependency injection, IOC）来解决这个依赖问题。先试试用最传统的依赖注入方式：传入参数来注入这个数据库驱动依赖，把代码放在src/main/scala/model/TableDefs.scala里：

 package com.bayakala.learn.slick301.model

 import slick.driver.JdbcProfile

 class TableDefs(val dbDriver: JdbcProfile) {

   import dbDriver.api._

   case class Supplier(id: Long

                       , name: String

                       , contact: Option[String]

                       , website: Option[String])

   final class Suppliers(tag: Tag) extends Table[Supplier](tag,"SUPPLERS") {

     def id = column[Long]("ID",O.AutoInc,O.PrimaryKey)

     def name = column[String]("NAME")

     def contact = column[Option[String]]("CONTACT")

     def website = column[Option[String]]("WEBSITE")

     def * = (id, name, contact, website) <> (Supplier.tupled,Supplier.unapply)

     def nidx = index("NM_IDX",name,unique = true)

   }

   val suppliers = TableQuery[Suppliers]

   case class Coffee(id: Long

                      ,name: String

                      ,supid: Long

                      ,price: Double

                      ,sales: Int)

   final class Coffees(tag: Tag) extends Table[Coffee](tag, "COFFEES") {

     def id = column[Long]("ID",O.AutoInc,O.PrimaryKey)

     def name = column[String]("NAME")

     def supid = column[Long]("SUPID")

     def price = column[Double]("PRICE",O.Default(0.0))

     def sales = column[Int]("SALES",O.Default())

     def * = (id,name,supid,price,sales) <> (Coffee.tupled, Coffee.unapply)

     def fk_sup = foreignKey("FK_SUP",supid,suppliers)(_.id,onDelete = ForeignKeyAction.Restrict,onUpdate = ForeignKeyAction.Cascade)

     def supidx = index("SUP_IDX",supid,unique = false)

     def nidx = index("NM_IDX",name,unique = true)

   }

   val coffees = TableQuery[Coffees]

 }

注意我们是把JdbcProfile作为参数注入了class TableDefs里。如果TableDefs经常需要作为其它类的父类继承的话，设计成trait能更加灵活的进行类型混合（type mixing）。这样的需求可以用cake pattern方式进行依赖注入。我们在需要src/main/scala/config/AppConfig.scala里定义依赖界面trait DBConfig：

 package com.bayakala.learn.slick301.config

 import slick.driver.JdbcProfile

 trait DBConfig {

   val jdbcDriver: JdbcProfile

   import jdbcDriver.api._

   val db: Database

 }

后面我们可以通过实现多种DBConfig实例方式来构建开发、测试、部署等数据库环境。为了方便示范，我们设计几个基本的Query Action，放在src/main/scala/access/DAOs.scala里，用cake pattern注入依赖DBConfig：

 package com.bayakala.learn.slick301.access

 import com.bayakala.learn.slick301.config

 import com.bayakala.learn.slick301.config.DBConfig

 import com.bayakala.learn.slick301.model.TableDefs

 trait DAOs { dbconf: DBConfig =>

   import jdbcDriver.api._

   //注入依赖

   val tables = new TableDefs(dbconf.jdbcDriver)

   import tables._

   //suppliers queries

   val createSupplierTable = suppliers.schema.create

   val allSuppliers = suppliers.result

   def insertSupplier(id:Long,name:String,address:Option[String],website:Option[String])

   = suppliers += Supplier(id,name,address,website)

   def insertSupbyName(n: String) = suppliers.map(_.name) += n

   //coffees queries

   val createCoffeeTable = coffees.schema.create

   val allCoffees = coffees.result

   def insertCoffee(c: (Long,String,Long,Double,Int)) =

     coffees += Coffee(id=c._1, name=c._2,supid=c._3,price=c._4,sales=c._5)

 }

dbconf: DBConfig => 的意思是在进行DAOs的实例化时必须混入（mixing）DBConfig类。

以上两个代码文件TableDefs.scala和DAOs.scala在注入依赖后都能够顺利通过编译了。

我们在src/main/scala/main/Main.scala里测试运算DAOs里的query action：

 package com.bayakala.learn.slick301.main

 import com.bayakala.learn.slick301.config.DBConfig

 import com.bayakala.learn.slick301.access.DAOs

 import scala.concurrent.{Await, Future}

 import scala.util.{Failure, Success}

 import scala.concurrent.duration._

 import scala.concurrent.ExecutionContext.Implicits.global

 import slick.backend.DatabaseConfig

 import slick.driver.{H2Driver, JdbcProfile}

 object Main {

   object Actions extends DAOs with DBConfig {

     override lazy val jdbcDriver: JdbcProfile = H2Driver

     val dbConf: DatabaseConfig[H2Driver] = DatabaseConfig.forConfig("h2")

     override val db = dbConf.db

   }

   import Actions._

   def main(args: Array[String]) = {

     val res = db.run(createSupplierTable).andThen {

       case Success(_) => println("supplier table created")

       case Failure(_) => println("unable to create supplier table")

     }

     Await.ready(res,  seconds)

     val res2 = db.run(insertSupbyName("Acme Coffee Co."))

     Await.ready(res2,  seconds)

     Await.ready(db.run(allSuppliers),  seconds).foreach(println)

     val res10 = db.run(createCoffeeTable).andThen {

       case Success(_) => println("coffee table created")

       case Failure(_) => println("unable to create coffee table")

     }

     Await.ready(res10,  seconds)

     val res11 = db.run(insertCoffee((,"Columbia",,158.0,)))

     Await.ready(res11,  seconds)

     Await.ready(db.run(allCoffees),  seconds).foreach(println)

   }

 }

Actions是DAOs的实例。我们看到必须把DBConfig混入（mixin）。但是我们构建的数据库又变成了专门针对H2的api了，这样的话每次变动数据库对象我们就必须重新编译Main.scala，不符合上面我们提到的要求。我们可以把目标数据库放到application.conf里，然后在Main.scala里用typesafe-config实时根据application.conf里的设置确定数据库参数。src/main/resources/application.conf内容如下：

 app = {

   dbconfig = h2

 }

 h2 {

   driver = "slick.driver.H2Driver$"

   db {

     url = "jdbc:h2:~/slickdemo;mv_store=false"

     driver = "org.h2.Driver"

     connectionPool = HikariCP

     numThreads =

     maxConnections =

     minConnections =

     keepAliveConnection = true

   }

 }

 h2mem = {

   url = "jdbc:h2:mem:slickdemo"

   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=""

     numThreads=

     maxConnections =

     minConnections =

   }

 }

 mysqldb = {

   dataSourceClass = "com.mysql.jdbc.jdbc2.optional.MysqlDataSource"

   properties {

     user = "root"

     password = ""

     databaseName = "slickdemo"

     serverName = "localhost"

   }

   numThreads =

   maxConnections =

   minConnections =

 }

 postgres {

   driver = "slick.driver.PostgresDriver$"

   db {

     url = "jdbc:postgresql://127.0.0.1/slickdemo"

     driver = "org.postgresql.Driver"

     connectionPool = HikariCP

     user = "slick"

     password = ""

     numThreads =

     maxConnections =

     minConnections =

   }

 }

 postgressdb = {

   dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"

   properties = {

     databaseName = "slickdemo"

     user = "slick"

     password = ""

   }

   connectionPool = HikariCP

   numThreads =

   maxConnections =

   minConnections =

 }

 mssql {

   driver = "com.typesafe.slick.driver.ms.SQLServerDriver$"

   db {

     url = "jdbc:sqlserver://host:port"

     driver = com.microsoft.sqlserver.jdbc.SQLServerDriver

     connectionTimeout =  second

     connectionPool = HikariCP

     user = "slick"

     password = ""

     numThreads =

     maxConnections =

     minConnections =

     keepAliveConnection = true

   }

 }

 tsql {

   driver = "slick.driver.H2Driver$"

   db = ${h2mem}

 }

现在application.conf里除了数据库配置外又加了个app配置。我们在Main.scala里实例化DAOs时可以用typesafe-config读取app.dbconfig值后设定jdbcDriver和db：

   object Actions extends DAOs with DBConfig {

     import slick.util.ClassLoaderUtil

     import scala.util.control.NonFatal

     import com.typesafe.config.ConfigFactory

     def getDbConfig: String =

         ConfigFactory.load().getString("app.dbconfig")

     def getDbDriver(path: String): JdbcProfile = {

       val config = ConfigFactory.load()

       val n = config.getString((if (path.isEmpty) "" else path + ".") + "driver")

       val untypedP = try {

         if (n.endsWith("$")) ClassLoaderUtil.defaultClassLoader.loadClass(n).getField("MODULE$").get(null)

         else ClassLoaderUtil.defaultClassLoader.loadClass(n).newInstance()

       } catch {

         case NonFatal(ex) =>

           throw new SlickException(s"""Error getting instance of Slick driver "$n"""", ex)

       }

       untypedP.asInstanceOf[JdbcProfile]

     }

     override lazy val jdbcDriver: JdbcProfile = getDbDriver(getDbConfig)

     val dbConf: DatabaseConfig[JdbcProfile] = DatabaseConfig.forConfig(getDbConfig)

     override val db = dbConf.db

   }

现在我们只需要改变application.conf里的app.dbconfig就可以转换目标数据库参数了。实际上，除了数据库配置，我们还可以在application.conf里进行其它类型的配置。然后用typesafe-config实时读取。如果不想在application.conf进行数据库之外的配置，可以把其它配置放在任何文件里，然后用ConfigFactory.load(path)来读取。

另外，在软件开发过程中跟踪除错也是很重要的。我们可以用logback来跟踪Slick、HikariCP等库的运行状态。logback配置在src/main/resources/logback.xml：

 <?xml version="1.0" encoding="UTF-8"?>

 <configuration>

     <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">

         <encoder>

             <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{} - %msg%n</pattern>

         </encoder>

     </appender>

     <logger name="application" level="DEBUG"/>

     <logger name="com.zaxxer.hikari" level="DEBUG"/>

     <logger name="slick" level="DEBUG"/>

     <root level="DEBUG">

         <appender-ref ref="STDOUT"/>

     </root>

 </configuration>

DEBUG值可以显示最详细的状态信息。

好了，我把这次示范代码提供在下面：

build.sbt：

 name := "learn-slick301"

 version := "1.0"

 scalaVersion := "2.11.8"

 libraryDependencies ++= Seq(

   "com.typesafe.slick" %% "slick" % "3.1.1",

   "com.h2database" % "h2" % "1.4.191",

   "com.typesafe.slick" %% "slick-hikaricp" % "3.1.1",

   "ch.qos.logback" % "logback-classic" % "1.1.7",

   "org.typelevel" %% "cats" % "0.7.2"

 )

src/main/resources/

application.conf:

 app = {

   dbconfig = h2

 }

 h2 {

   driver = "slick.driver.H2Driver$"

   db {

     url = "jdbc:h2:~/slickdemo;mv_store=false"

     driver = "org.h2.Driver"

     connectionPool = HikariCP

     numThreads =

     maxConnections =

     minConnections =

     keepAliveConnection = true

   }

 }

 h2mem = {

   url = "jdbc:h2:mem:slickdemo"

   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=""

     numThreads=

     maxConnections =

     minConnections =

   }

 }

 mysqldb = {

   dataSourceClass = "com.mysql.jdbc.jdbc2.optional.MysqlDataSource"

   properties {

     user = "root"

     password = ""

     databaseName = "slickdemo"

     serverName = "localhost"

   }

   numThreads =

   maxConnections =

   minConnections =

 }

 postgres {

   driver = "slick.driver.PostgresDriver$"

   db {

     url = "jdbc:postgresql://127.0.0.1/slickdemo"

     driver = "org.postgresql.Driver"

     connectionPool = HikariCP

     user = "slick"

     password = ""

     numThreads =

     maxConnections =

     minConnections =

   }

 }

 postgressdb = {

   dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"

   properties = {

     databaseName = "slickdemo"

     user = "slick"

     password = ""

   }

   connectionPool = HikariCP

   numThreads =

   maxConnections =

   minConnections =

 }

 mssql {

   driver = "com.typesafe.slick.driver.ms.SQLServerDriver$"

   db {

     url = "jdbc:sqlserver://host:port"

     driver = com.microsoft.sqlserver.jdbc.SQLServerDriver

     connectionTimeout =  second

     connectionPool = HikariCP

     user = "slick"

     password = ""

     numThreads =

     maxConnections =

     minConnections =

     keepAliveConnection = true

   }

 }

 tsql {

   driver = "slick.driver.H2Driver$"

   db = ${h2mem}

 }

logback.xml:

 <?xml version="1.0" encoding="UTF-8"?>

 <configuration>

     <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">

         <encoder>

             <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{} - %msg%n</pattern>

         </encoder>

     </appender>

     <logger name="application" level="DEBUG"/>

     <logger name="com.zaxxer.hikari" level="DEBUG"/>

     <logger name="slick" level="DEBUG"/>

     <root level="DEBUG">

         <appender-ref ref="STDOUT"/>

     </root>

 </configuration>

src/main/scala/config/AppConfig.scala:

 package com.bayakala.learn.slick301.config

 import slick.driver.JdbcProfile

 trait DBConfig {

   val jdbcDriver: JdbcProfile

   import jdbcDriver.api._

   val db: Database

 }

src/main/scala/model/TableDefs.scala:

 package com.bayakala.learn.slick301.model

 import slick.driver.JdbcProfile

 class TableDefs(val dbDriver: JdbcProfile) {

   import dbDriver.api._

   case class Supplier(id: Long

                       , name: String

                       , contact: Option[String]

                       , website: Option[String])

   final class Suppliers(tag: Tag) extends Table[Supplier](tag,"SUPPLERS") {

     def id = column[Long]("ID",O.AutoInc,O.PrimaryKey)

     def name = column[String]("NAME")

     def contact = column[Option[String]]("CONTACT")

     def website = column[Option[String]]("WEBSITE")

     def * = (id, name, contact, website) <> (Supplier.tupled,Supplier.unapply)

     def nidx = index("NM_IDX",name,unique = true)

   }

   val suppliers = TableQuery[Suppliers]

   case class Coffee(id: Long

                      ,name: String

                      ,supid: Long

                      ,price: Double

                      ,sales: Int)

   final class Coffees(tag: Tag) extends Table[Coffee](tag, "COFFEES") {

     def id = column[Long]("ID",O.AutoInc,O.PrimaryKey)

     def name = column[String]("NAME")

     def supid = column[Long]("SUPID")

     def price = column[Double]("PRICE",O.Default(0.0))

     def sales = column[Int]("SALES",O.Default())

     def * = (id,name,supid,price,sales) <> (Coffee.tupled, Coffee.unapply)

     def fk_sup = foreignKey("FK_SUP",supid,suppliers)(_.id,onDelete = ForeignKeyAction.Restrict,onUpdate = ForeignKeyAction.Cascade)

     def supidx = index("SUP_IDX",supid,unique = false)

     def nidx = index("NM_IDX",name,unique = true)

   }

   val coffees = TableQuery[Coffees]

 }

src/main/scala/access/DAOs.scala:

 package com.bayakala.learn.slick301.access

 import com.bayakala.learn.slick301.config

 import com.bayakala.learn.slick301.config.DBConfig

 import com.bayakala.learn.slick301.model.TableDefs

 trait DAOs { dbconf: DBConfig =>

   import jdbcDriver.api._

   //注入依赖

   val tables = new TableDefs(dbconf.jdbcDriver)

   import tables._

   //suppliers queries

   val createSupplierTable = suppliers.schema.create

   val allSuppliers = suppliers.result

   def insertSupplier(id:Long,name:String,address:Option[String],website:Option[String])

   = suppliers += Supplier(id,name,address,website)

   def insertSupbyName(n: String) = suppliers.map(_.name) += n

   //coffees queries

   val createCoffeeTable = coffees.schema.create

   val allCoffees = coffees.result

   def insertCoffee(c: (Long,String,Long,Double,Int)) =

     coffees += Coffee(id=c._1, name=c._2,supid=c._3,price=c._4,sales=c._5)

 }

src/main/scala/main/Main.scala:

 package com.bayakala.learn.slick301.main

 import com.bayakala.learn.slick301.config.DBConfig

 import com.bayakala.learn.slick301.access.DAOs

 import scala.concurrent.Await

 import scala.util.{Failure, Success}

 import scala.concurrent.duration._

 import scala.concurrent.ExecutionContext.Implicits.global

 import slick.backend.DatabaseConfig

 import slick.driver.JdbcProfile

 object Main {

   object Actions extends DAOs with DBConfig {

     import slick.SlickException

     import slick.util.ClassLoaderUtil

     import scala.util.control.NonFatal

     import com.typesafe.config.ConfigFactory

     def getDbConfig: String =

         ConfigFactory.load().getString("app.dbconfig")

     def getDbDriver(path: String): JdbcProfile = {

       val config = ConfigFactory.load()

       val n = config.getString((if (path.isEmpty) "" else path + ".") + "driver")

       val untypedP = try {

         if (n.endsWith("$")) ClassLoaderUtil.defaultClassLoader.loadClass(n).getField("MODULE$").get(null)

         else ClassLoaderUtil.defaultClassLoader.loadClass(n).newInstance()

       } catch {

         case NonFatal(ex) =>

           throw new SlickException(s"""Error getting instance of Slick driver "$n"""", ex)

       }

       untypedP.asInstanceOf[JdbcProfile]

     }

     override lazy val jdbcDriver: JdbcProfile = getDbDriver(getDbConfig)

     val dbConf: DatabaseConfig[JdbcProfile] = DatabaseConfig.forConfig(getDbConfig)

     override val db = dbConf.db

   }

   import Actions._

   def main(args: Array[String]) = {

     val res = db.run(createSupplierTable).andThen {

       case Success(_) => println("supplier table created")

       case Failure(_) => println("unable to create supplier table")

     }

     Await.ready(res,  seconds)

     val res2 = db.run(insertSupbyName("Acme Coffee Co."))

     Await.ready(res2,  seconds)

     Await.ready(db.run(allSuppliers),  seconds).foreach(println)

     val res10 = db.run(createCoffeeTable).andThen {

       case Success(_) => println("coffee table created")

       case Failure(_) => println("unable to create coffee table")

     }

     Await.ready(res10,  seconds)

     val res11 = db.run(insertCoffee((,"Columbia",,158.0,)))

     Await.ready(res11,  seconds)

     Await.ready(db.run(allCoffees),  seconds).foreach(println)

   }

浅谈Slick（4）－ Slick301：我的Slick开发项目设置的相关教程结束。