首页 > 代码库 > 浅谈Slick（4）－ Slick301：我的Slick开发项目设置
浅谈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里的例子：
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: String11 )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: Long20 ,name: String21 ,supid: Long22 ,price: Double23 ,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.config2 import slick.driver.JdbcProfile3 trait DBConfig {4 val jdbcDriver: JdbcProfile5 import jdbcDriver.api._6 val db: Database7 }后面我们可以通过实现多种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 queries11 val createSupplierTable = suppliers.schema.create12 val allSuppliers = suppliers.result13 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) += n16 //coffees queries17 val createCoffeeTable = coffees.schema.create18 val allCoffees = coffees.result19 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.DatabaseConfig10 import slick.driver.{H2Driver, JdbcProfile}11 object Main {12 13 object Actions extends DAOs with DBConfig {14 override lazy val jdbcDriver: JdbcProfile = H2Driver15 val dbConf: DatabaseConfig[H2Driver] = DatabaseConfig.forConfig("h2")16 override val db = dbConf.db17 }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 = HikariCP11 numThreads = 1012 maxConnections = 1213 minConnections = 414 keepAliveConnection = true15 }16 }17 18 h2mem = {19 url = "jdbc:h2:mem:slickdemo"20 driver = org.h2.Driver21 connectionPool = disabled22 keepAliveConnection = true23 }24 25 mysql {26 driver = "slick.driver.MySQLDriver$"27 db {28 url = "jdbc:mysql://localhost/slickdemo"29 driver = com.mysql.jdbc.Driver30 keepAliveConnection = true31 user="root"32 password="123"33 numThreads=1034 maxConnections = 1235 minConnections = 436 }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 = 1048 maxConnections = 1249 minConnections = 450 }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 = HikariCP58 user = "slick"59 password = "123"60 numThreads = 1061 maxConnections = 1262 minConnections = 463 }64 }65 66 postgressdb = {67 dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"68 properties = {69 databaseName = "slickdemo"70 user = "slick"71 password = "123"72 }73 connectionPool = HikariCP74 numThreads = 1075 maxConnections = 1276 minConnections = 477 }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.SQLServerDriver84 connectionTimeout = 30 second85 connectionPool = HikariCP86 user = "slick"87 password = "123"88 numThreads = 1089 maxConnections = 1290 minConnections = 491 keepAliveConnection = true92 }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.db25 }现在我们只需要改变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 = HikariCP11 numThreads = 1012 maxConnections = 1213 minConnections = 414 keepAliveConnection = true15 }16 }17 18 h2mem = {19 url = "jdbc:h2:mem:slickdemo"20 driver = org.h2.Driver21 connectionPool = disabled22 keepAliveConnection = true23 }24 25 mysql {26 driver = "slick.driver.MySQLDriver$"27 db {28 url = "jdbc:mysql://localhost/slickdemo"29 driver = com.mysql.jdbc.Driver30 keepAliveConnection = true31 user="root"32 password="123"33 numThreads=1034 maxConnections = 1235 minConnections = 436 }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 = 1048 maxConnections = 1249 minConnections = 450 }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 = HikariCP58 user = "slick"59 password = "123"60 numThreads = 1061 maxConnections = 1262 minConnections = 463 }64 }65 66 postgressdb = {67 dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"68 properties = {69 databaseName = "slickdemo"70 user = "slick"71 password = "123"72 }73 connectionPool = HikariCP74 numThreads = 1075 maxConnections = 1276 minConnections = 477 }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.SQLServerDriver84 connectionTimeout = 30 second85 connectionPool = HikariCP86 user = "slick"87 password = "123"88 numThreads = 1089 maxConnections = 1290 minConnections = 491 keepAliveConnection = true92 }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.config2 import slick.driver.JdbcProfile3 trait DBConfig {4 val jdbcDriver: JdbcProfile5 import jdbcDriver.api._6 val db: Database7 }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: Long20 ,name: String21 ,supid: Long22 ,price: Double23 ,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 queries11 val createSupplierTable = suppliers.schema.create12 val allSuppliers = suppliers.result13 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) += n16 //coffees queries17 val createCoffeeTable = coffees.schema.create18 val allCoffees = coffees.result19 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.DatabaseConfig10 import slick.driver.JdbcProfile11 12 object Main {13 14 object Actions extends DAOs with DBConfig {15 import slick.SlickException16 import slick.util.ClassLoaderUtil17 import scala.util.control.NonFatal18 import com.typesafe.config.ConfigFactory19 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.db39 }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 }
浅谈Slick（4）－ Slick301：我的Slick开发项目设置
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。