浅谈Slick（4）－ Slick301：我的Slick开发项目设置
来源：互联网 发布：淘宝联盟登不上去 编辑：程序博客网 时间：2024/04/28 22:11
前面几篇介绍里尝试了一些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.slick101import 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.modelimport slick.driver.JdbcProfileclass 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.configimport slick.driver.JdbcProfiletrait DBConfig { val jdbcDriver: JdbcProfile import jdbcDriver.api._ val db: Database}package com.bayakala.learn.slick301.accessimport com.bayakala.learn.slick301.configimport com.bayakala.learn.slick301.config.DBConfigimport com.bayakala.learn.slick301.model.TableDefstrait 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.mainimport com.bayakala.learn.slick301.config.DBConfigimport com.bayakala.learn.slick301.access.DAOsimport scala.concurrent.{Await, Future}import scala.util.{Failure, Success}import scala.concurrent.duration._import scala.concurrent.ExecutionContext.Implicits.globalimport slick.backend.DatabaseConfigimport 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.configimport slick.driver.JdbcProfiletrait DBConfig { val jdbcDriver: JdbcProfile import jdbcDriver.api._ val db: Database}src/main/scala/model/TableDefs.scala:
package com.bayakala.learn.slick301.modelimport slick.driver.JdbcProfileclass 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.accessimport com.bayakala.learn.slick301.configimport com.bayakala.learn.slick301.config.DBConfigimport com.bayakala.learn.slick301.model.TableDefstrait 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.mainimport com.bayakala.learn.slick301.config.DBConfigimport com.bayakala.learn.slick301.access.DAOsimport scala.concurrent.Awaitimport scala.util.{Failure, Success}import scala.concurrent.duration._import scala.concurrent.ExecutionContext.Implicits.globalimport slick.backend.DatabaseConfigimport slick.driver.JdbcProfileobject 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) } 0 0
- 浅谈Slick（4）－ Slick301：我的Slick开发项目设置
- 浅谈Slick（2）－ Slick101：第一个动手尝试的项目
- 浅谈Slick（1）－ 基本功能描述
- slick
- 浅谈Slick（3）－ Slick201：从fp角度了解Slick
- Slick-pg v0.6.5.2 ，Slick 的 PG 扩展
- 细谈Slick（5）－ 学习体会和将来实际应用的一些想法
- 细谈Slick（6）－ Projection：ProvenShape，强类型的Query结果类型
- slick 旋转木马（轮播图）插件清空滑块slide的方法
- Slick教程
- 认识slick
- slick Hello
- Slick学习之Hello slick
- slick使用普通的sql操作数据
- slick与mysql
- SLICK+SQLITE+SCALA
- scala+play+slick + mysql
- slick log日志配置文件
- eclipse 导入android项目失败
- 9-多媒体
- 有关gdb
- 十秒倒计时
- UML行为图（状态图，活动图）
- 浅谈Slick（4）－ Slick301：我的Slick开发项目设置
- 自定义ToolBar
- TCP协议的学习2
- Freemarker入门案例
- C#中CA加密与DES加密的混合使用
- latex中公式过长问题的解决
- linux内存管理mmap详情
- light oj 1079 - Just another Robbery 【01背包】
- 二叉树的递归与非递归遍历
