play-slick基础：CRUD操作

Play框架提供了对Slick的集成模块play-slick。本文将介绍从后台数据库到前台请求的的全过程。

一、数据库建模

case class User(name:String, password:String,id:Option[Long] = None)

object User {

  implicit val userWrites: Writes[User] =
    (
      (JsPath \ "name").write[String]
        and (JsPath \ "password").write[String]
        and (JsPath \ "id").writeNullable[Long]
      )(unlift(User.unapply))

  implicit val userReads: Reads[User] = (
    (JsPath \ "name").read[String]
      and (JsPath \ "password").read[String]
      and (JsPath \ "id").readNullable[Long]
    )(User.apply _)

}

注意：id是一个可选的属性，必须放在最后面，否则会导致play在Json转换的时候报错。

建表：mysql. 注意 ID要定义为自动增长的列。

CREATE TABLE USER
(
  ID INT NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(40) NOT NULL,
  PASSWORD VARCHAR(40) NOT NULL,
  PRIMARY KEY(ID)
);

二、Slick schema

import model._
import slick.driver.JdbcProfile
trait UserSchema {
  protected val driver: JdbcProfile
  import driver.api._
  class UserTable(tag: Tag) extends Table[User](tag, "USER") {
    def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
    def name = column[String]("NAME")
    def password = column[String]("PASSWORD")
    def * = (name, password, id.?) <> (User.tupled, User.unapply _)
  }
}

三、Play Action定义

列出所有的用户

  def usersList = Action.async {
    implicit request => {
      dbConfig.db.run(Users.result).map(res =>
        Ok(Json.toJson(res.toList))
      )
    }
  }

创建一个用户

  def addUser = Action.async(BodyParsers.parse.json) {
    implicit request => {
      val user = request.body.validate[User].get
      val insertQuery = (
        Users returning Users.map(_.id)) += user
      dbConfig.db.run(insertQuery).map {
        result =>
          Ok(Json.toJson(user.copy(id = Some(result))))
      }
    }
  }

删除一个用户

     def deleteUser(id: Long) = Action {
    val query = Users.filter(_.id === id).delete
    dbConfig.db.run(query)
    Ok("ok")
  }

更新一个用户

def updateUser(id: Long) = Action.async(BodyParsers.parse.json) {
    implicit request => {
      val newUser = request.body.validate[User].get
      val query = Users.filter(_.id === newUser.id).update(newUser)
      dbConfig.db.run(query).map {
        result => Ok(Json.toJson(newUser.id))
      }
    }
  }

application.conf配置

slick.dbs.default.driver= "slick.driver.MySQLDriver$"
slick.dbs.default.db.driver="com.mysql.jdbc.Driver"
slick.dbs.default.db.properties.driver="com.mysql.jdbc.Driver"
slick.dbs.default.db.user="root"
slick.dbs.default.db.password="root"
slick.dbs.default.db.url="jdbc:mysql://localhost:3306/xyz?useUnicode=true&characterEncoding=UTF-8"

然后再定义Controller时，直接注入配置好得DB连接就可以了。

class UserApplication @Inject()(dbConfigProvider: DatabaseConfigProvider)
  extends Controller with UserSchema with HasDatabaseConfig[JdbcProfile] {
  val dbConfig = dbConfigProvider.get[JdbcProfile]
  import driver.api._
  val Users = TableQuery[UserTable]
}