看完Slick官方网站上关于Slick3.1.1技术文档后决定开始动手建一个项目来尝试一下Slick功能的具体使用方法。我把这个过程中的一些了解和想法记录下来和大家一起分享。首先我用IntelliJ-Idea创建了一个scala项目。下一步就是如何选择数据库了。Slick是集成jdbc的更高层的Query编程语言,可以通过jdbc的url、DataSource等来指定目标数据库类型及相关的参数。对应Slick中的具体函数有:
val db = Database.forConfig("mydb") val db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", driver="org.h2.Driver") val db = Database.forDataSource(dataSource: slick.jdbc.DatabaseUrlDataSource)
在Slick的Database配置方面forConfig("confItem")是比较灵活、方便实用的。confItem是resources/application.conf文件里的一个配置项目。Slick是通过typesafe-config来解析配置文件的。forConfig函数用typesafe-config库里的函数载入application.conf文件解析confItem并获取项目里的数据库配置参数,下面是项目中resources/application.conf文件内容:
h2mem { url = "jdbc:h2:mem:slickdemo" driver = "org.h2.Driver" connectionPool = disabled keepAliveConnection = true } h2 = { url = "jdbc:h2:~/slickdemo;mv_store=false;MODE=MSSQLServer;DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE" 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} }
在我使用的application.conf文件中汇集了一些常用数据库的配置,我一并提供出来。除h2之外其它都没进行测试验证,具体配置参数和方法要参考数据库开发商提供的技术文档。我在这个示范里选用了h2配置:它会在我的用户根目录下创建一个slickdemo.h2.db数据库文件。
好了,选择了数据库,下面我们就来试试使用它。基本流程是这样的:首先在数据库里创建表,跟着写入一些数据,然后再读出显示。整个过程会涉及:表结构schema定义,数据插写Insert,数据读取Query及简单的Query运算方法和数据显示方法。
现在我们先设计表结构schema:
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](&