前面几篇介绍里尝试了一些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]
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(0))
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]
}
package com.bayakala.learn.slick301.config
import slick.driver.JdbcProfile
trait DBConfig {
val jdbcDriver: JdbcProfile
import jdbcDriver.api._
val db: Database
}
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)
}
以上两个代码文件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, 3 seconds)
val res2 = db.run(insertSupbyName("Acme Coffee Co."))
Await.ready(res2, 3 seconds)
Await.ready(db.run(allSuppliers), 10 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, 3 seconds)
val res11 = db.run(insertCoffee((101,"Columbia",1,158.0,0)))
Await.ready(res11, 3 seconds)
Await.ready(db.run(allCoffees), 10 seconds).foreach(println)
}
}
app = {
dbconfig = h2
}
h2 {
driver = "slick.driver.H2Driver$"
db {
url = "jdbc:h2:~/slickdemo;mv_store=false"
driver = "org.h2.Driver"
connectionPool = HikariCP
numThreads = 10
maxConnections = 12
minConnections = 4
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="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}
}
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
}
另外,在软件开发过程中跟踪除错也是很重要的。我们可以用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{36} - %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>
好了,我把这次示范代码提供在下面:
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"
)
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 = 10
maxConnections = 12
minConnections = 4
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="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}
}
<?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{36} - %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(0))
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]
}
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)
}
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, 3 seconds)
val res2 = db.run(insertSupbyName("Acme Coffee Co."))
Await.ready(res2, 3 seconds)
Await.ready(db.run(allSuppliers), 10 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, 3 seconds)
val res11 = db.run(insertCoffee((101,"Columbia",1,158.0,0)))
Await.ready(res11, 3 seconds)
Await.ready(db.run(allCoffees), 10 seconds).foreach(println)
}