scala slick基本使用教程

大家好，我是讯享网，很高兴认识大家。

参考：
http://slick.lightbend.com/doc/3.2.1/introduction.html
http://www.cnblogs.com/tiger-xc/p/5898585.html
http://www.cnblogs.com/tiger-xc/p/5898585.html

slick是scala生态中的数据库连接组件，跟scala一样有良好的函数式编程特性，本文以mysql和sqlite为例简单列举了一些slick的常用操作，作为经常参考的代码片段，如果需要深入研究，请参考slick官网文档

引入依赖

libraryDependencies ++= Seq( "com.typesafe.slick" %% "slick" % "3.2.1", "org.slf4j" % "slf4j-nop" % "1.6.4", "mysql" % "mysql-connector-java" % "5.1.38", "com.typesafe.slick" %% "slick-hikaricp" % "3.2.1" )

讯享网

其中包含:

mysql的连接器jdbc
slick核心组件

头文件

讯享网import slick.jdbc.MySQLProfile.api._ import slick.lifted.TableQuery

（2）针对sqlite

libraryDependencies ++= Seq( "com.typesafe.slick" %% "slick" % "3.2.1", "org.slf4j" % "slf4j-nop" % "1.6.4", "org.xerial" % "sqlite-jdbc" % "3.20.0", "com.typesafe.slick" %% "slick-hikaricp" % "3.2.1" )

其中包含:

sqlite的连接器jdbc
slick核心组件

头文件

讯享网import slick.jdbc.SQLiteProfile.api._ import slick.lifted.TableQuery

配置数据库

slick支持多种方式配置，

方法1：配置url

val db = Database.forURL( url = "jdbc:mysql://localhost:3306/db_example?useUnicode=true&characterEncoding=UTF-8&useSSL=false", driver = "com.mysql.jdbc.Driver", user = "springuser", password = "ThePassword")

讯享网val sqlite_db = Database.forURL( url = "jdbc:sqlite:D:/Programs/sqlite3/newDB.db", driver = "org.sqlite.JDBC" )

方法2：配置config

db { mysql_db = { url = "jdbc:mysql://localhost:3306/db_example?useUnicode=true&characterEncoding=UTF-8&useSSL=false", driver = "com.mysql.jdbc.Driver", user = "springuser", password = "ThePassword") numThreads = 5 } sqlite_db = { url = "jdbc:sqlite:test.db", driver = "org.sqlite.JDBC" numThreads = 5 } }

在这个配置文件中可以添加更多属性，比如连接池，最大连接数等

讯享网

然后用typesafe的config类load进来

讯享网val file = new File("etc/database.conf") val config = ConfigFactory.parseFile(file)

（1）mysql

val db = Database.forConfig("mysql_db", config.getConfig("db"))

（2）sqlite

讯享网val sqlite_db = Database.forConfig("sqlite_db", config.getConfig("db"))

sqlite因为是本地数据库，跟读写文件差不多，不需要安装sqlite，slick自动会创建sqlite的文件并进行读写，url中配置的是sqlite的db文件的路径（绝对路径or相对路径）

数据库访问

数据库访问对于所有种类的数据库都是一致的

（1）通过绑定的数据结构

需要绑定scala对象和数据库表格

// define table structure(we can define many tables bindings) object SlickDB { // table name: scala_model case class UserInfo(id: Long, name: String, age: Int) class SlickModelTable(tag: Tag) extends Table[UserInfo](tag, "scala_model") { // define column attribute def id = column[Long]("id", O.PrimaryKey, O.AutoInc) def name = column[String]("name") def age = column[Int]("age") def * = (id, name, age) <> (UserInfo.tupled, UserInfo.unapply) } def slick_table = TableQuery[SlickModelTable] }

或者

讯享网// define table structure(we can define many tables bindings) object SlickDB { // table name: scala_model case class UserInfo(id: Long, name: String, age: Int) class SlickModelTable(tag: Tag) extends Table[UserInfo](tag, name) { // define column attribute def id = column[Long]("id", O.PrimaryKey, O.AutoInc) def name = column[String]("name") def age = column[Int]("age") def * = (id, name, age) <> (UserInfo.tupled, UserInfo.unapply) } class SlickModelSubTable(tag: Tag) extends SlickModelTable(tag, "scala_model") def slick_table = TableQuery[SlickModelSubTable] }

注意，同一个表结构可以绑定到多张数据库的表格，可以用class去继承，然后再绑定

然后就可以进行常用的增删改查操作，例如：

// query by condition val res2 = Await.result(db.run(slick_table.filter(_.age > 25).result), Duration.Inf)

需要注意的是，slick的数据库操作是异步的，返回的是Future对象，需要用Await.result或者使用回调来得到结果

（2）通过sql访问

slick也支持直接用sql语句访问，这样就不用定义额外的class了，例如：

讯享网// query sql val res6 = Await.result(db.run(sql"""select * from scala_model where name = 'mary'""".as[(Long, String, Int)]), Duration.Inf)

需要注意的是，返回的结果，需要对应到自定义的tuple结构中，字段类型一致

示例代码

import org.slf4j.LoggerFactory import slick.driver.MySQLDriver.api._ import scala.concurrent.ExecutionContext.Implicits.global // support andThen import scala.concurrent.Await import scala.concurrent.duration._ import scala.util.{Failure, Success} // define table structure(we can define many tables bindings) object SlickDB { // table name: scala_model case class UserInfo(id: Long, name: String, age: Int) class SlickModelTable(tag: Tag) extends Table[UserInfo](tag, "scala_model") { // define column attribute def id = column[Long]("id", O.PrimaryKey, O.AutoInc) // make sure here is primary key and auto inc(return column needed) def name = column[String]("name") def age = column[Int]("age") def * = (id, name, age) <> (UserInfo.tupled, UserInfo.unapply) } def slick_table = TableQuery[SlickModelTable] } import SlickDB._ object SlickTest extends App { // init logger val logger = LoggerFactory.getLogger(getClass.getSimpleName) logger.info("slick test end") // config database val db = Database.forURL( url = "jdbc:mysql://localhost:3306/db_example?useUnicode=true&characterEncoding=UTF-8&useSSL=false", driver = "com.mysql.jdbc.Driver", user = "springuser", password = "ThePassword") // ---- use function // query all // slick run returns a future, we can use andThen to get async response and use Await.result to get result // usage1 val query_action = slick_table.result val res1 = db.run(query_action).andThen { case Success(_) => println("query success") case Failure(e) => println("query failed ", e.getMessage) } // usage2 db.run(slick_table.result).map { result => println(result) } // block thread to get select result Await.result(res1, 10 seconds) // specify the timeout // query by condition val res2 = Await.result(db.run(slick_table.filter(_.age > 25).result), Duration.Inf) // add（only 1 record) // val user1 = UserInfo(6L, "scarllet", 19) // val res3 = Await.result(db.run(slick_table += user1), Duration.Inf) // return the insert numbers: 1, so no need to return // add(batch records) val user1 = UserInfo(6L, "scarllet", 19) val user2 = UserInfo(7L, "mary", 21) val newArray = Seq[UserInfo](user1, user2) val res3 = Await.result(db.run(slick_table ++= newArray), Duration.Inf) // return the insert numbers: 2, so no need to return // update val new_user = UserInfo(3L, "tashaxing", 23) val res4 = Await.result(db.run(slick_table.filter(_.id === new_user.id).update(new_user)), Duration.Inf) // return effected row numbers // delete val res5 = Await.result(db.run(slick_table.filter(_.name === "lucy").delete), Duration.Inf) // return main column after insert val user = UserInfo(0, "ethan", 21) val save_sql = (slick_table returning slick_table.map(_.id)) += user val user_id = Await.result(db.run(save_sql), Duration.Inf) // return created id // ---- use sql // query sql val res6 = Await.result(db.run(sql"""select * from scala_model where name = 'mary'""".as[(Long, String, Int)]), Duration.Inf) // insert sql val id = 10L val name = "wilson" val age = 29 val res7 = Await.result(db.run(sqlu"""insert into scala_model values($id, $name, $age)"""), Duration.Inf) // use variable outside // val res7 = Await.result(db.run(sqlu"""insert into scala_model values(10, 'bob', 28)"""), Duration.Inf) // use variable in string // update sql val res8 = Await.result(db.run(sqlu"""update scala_model set name='lily' where id=4"""), Duration.Inf) // delete sql val res9 = Await.result(db.run(sqlu"""delete from scala_model where name='mary'"""), Duration.Inf) logger.debug("slick test end") }