Play2.7+Slick3.0集成Postgresql+Mysql实现多数据源 Restful

皇甫飞飙

2023-12-01

1.需要的sbt依赖

    "org.scalatestplus.play" %% "scalatestplus-play" % "3.0.0" % Test,
    "mysql" % "mysql-connector-java" % "5.1.47",
    "com.typesafe.play" %% "play-slick" % "3.0.0",
    "com.typesafe.play" %% "play-slick-evolutions" % "3.0.0",
    "org.postgresql" % "postgresql" % "9.4.1212",

注意play slick postgresql的版本

2.在application.conf配置多数据源

其中default数据源使用mysql，postgresqldb（这是命名，随便写，和后面注入时一致即可）使用的是postgresql 10

slick.dbs.default {
  driver = "slick.driver.MySQLDriver$"
  db.driver = com.mysql.jdbc.Driver
  db.url = "jdbc:mysql://localhost/playtest"
  db.user = "root"
  db.password = "root"
  db.logSql = true
}
#登录 psql -h localhost -U postgres -d postgres -p 5432;
#查看客户端编码 show client_encoding;
#修改编码 set client_encoding to 'utf8';
slick.dbs.postgresqldb {
  driver = "slick.driver.PostgresDriver$"
  db.driver = org.postgresql.Driver
  db.url = "jdbc:postgresql://127.0.0.1:5432/playtest"
  db.user = "postgres"
  db.password = "root"
  db.logSql = true
}

default是默认的数据源，配置地点如下

play.db {
  # The combination of these two settings results in "db.default" as the
  # default JDBC pool:
  config = "db"
  default = "default"

  # Play uses HikariCP as the default connection pool.  You can override
  # settings by changing the prototype:
  prototype {
    # Sets a fixed JDBC connection pool size of 50
    #hikaricp.minimumIdle = 50
    #hikaricp.maximumPoolSize = 50
  }
}

因为config和default的默认值就是db、default，所以不需要改这里，可以忽略。

PS:如果是是slick2.x可以通过下面方式配置，slick3.0不需要考虑下面的，忽略即可。

db {
  default.driver = com.mysql.jdbc.Driver
  default.url = "jdbc:mysql://localhost/playtest"
  default.username = "root"
  default.password = "root"
  default.logSql = true
}

当然也可以写成db.default.driver=xxx 这里default同样是表示默认的数据源，此时需要的在sbt加上jdbc依赖，通过注入

db: Database来使用，如果不是default，则需要加上名字如：@NamedDatabase("yourdatebase") db: Database

3.在DAO注入并引用多数据源

1）mysql

package dao

import javax.inject.Inject
import models.User
import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import slick.jdbc.JdbcProfile

import scala.concurrent.{ExecutionContext, Future}

/**
 * User 数据库操作
 *
 * 使用mysqldb
 *
 * @author 梦境迷离
 * @version 1.0, 2019-04-03
 */
class UserMySqlDAO @Inject()(protected val dbConfigProvider: DatabaseConfigProvider)(implicit executionContext: ExecutionContext)
  extends HasDatabaseConfigProvider[JdbcProfile] {

    import profile.api._

    private val Users = TableQuery[UsersTable]

    //Table[(Int, String)]，用元组太麻烦了
    private class UsersTable(tag: Tag) extends Table[User](tag, "USER") {

        def id = column[Int]("id", O.PrimaryKey)

        def userName = column[String]("user_name")

        def * = (id, userName) <> ((User.apply _).tupled, User.unapply)

        //        def * : ProvenShape[(Int, String)] = (id, userName)
    }

    //根据id查找
    def findById(id: Int): Future[Seq[User]] = db.run(Users.filter(_.id === id).result)

    //查询所有，根据姓名排序
    def all(): Future[Seq[User]] = db.run(Users.sortBy(_.userName).result)

    //插入
    def insert(user: User): Future[Unit] = db.run(Users += user).map(_ => ())

    //部分有值
    def insert2(user: User): Future[Unit] = db.run(Users.map(user => (user.id, user.userName)) += (user.id, user.userName)).map(_ => ())

    //根据id删除
    def deleteUserById(id: Int): Future[Int] = db.run(Users.filter(_.id === id).delete)

    //更新单列姓名
    def updateUserName(user: User) = db.run(Users.filter(_.id === user.id).map(_.userName).update(user.userName))

    //根据传入的user对象，查询id，并修改值 (修改多列)
    def updateUser(user: User) = db.run(Users.filter(_.id === user.id).map(user => (user.id, user.userName)).update(user.id, user.userName))


}

2）postgresql

package dao

import javax.inject.Inject
import models.User
import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import play.db.NamedDatabase
import slick.jdbc.JdbcProfile

import scala.concurrent.{ExecutionContext, Future}

/**
 * 使用postgresqldb
 *
 * @author 梦境迷离
 * @version 1.0, 2019-04-04
 */
class UserPostgresqlDAO @Inject()(@NamedDatabase("postgresqldb") protected val dbConfigProvider: DatabaseConfigProvider)(implicit executionContext: ExecutionContext)
  extends HasDatabaseConfigProvider[JdbcProfile] {

    import profile.api._

    private val Users = TableQuery[UsersTable]

    //Table[(Int, String)]，用元组太麻烦了
    private class UsersTable(tag: Tag) extends Table[User](tag, "user") {

        def id = column[Int]("id", O.PrimaryKey)

        def userName = column[String]("user_name")

        def * = (id, userName) <> ((User.apply _).tupled, User.unapply)

        //        def * : ProvenShape[(Int, String)] = (id, userName)
    }

    //根据id查找
    def findById(id: Int): Future[Seq[User]] = db.run(Users.filter(_.id === id).result)

    //查询所有，根据姓名排序
    def all(): Future[Seq[User]] = db.run(Users.sortBy(_.userName).result)

    //插入
    def insert(user: User): Future[Unit] = db.run(Users += user).map(_ => ())

    //部分有值
    def insert2(user: User): Future[Unit] = db.run(Users.map(user => (user.id, user.userName)) += (user.id, user.userName)).map(_ => ())

    //根据id删除
    def deleteUserById(id: Int): Future[Int] = db.run(Users.filter(_.id === id).delete)

    //更新单列姓名
    def updateUserName(user: User) = db.run(Users.filter(_.id === user.id).map(_.userName).update(user.userName))

    //根据传入的user对象，查询id，并修改值 (修改多列)
    def updateUser(user: User) = db.run(Users.filter(_.id === user.id).map(user => (user.id, user.userName)).update(user.id, user.userName))


}

这两个控制器所做的操作是一样的，一个使用mysql,一个使用postgresql

4.mysql和postgresql的表结构

------mysql----------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '张');
INSERT INTO `user` VALUES ('3', '王五');
INSERT INTO `user` VALUES ('4', 'sss');

------postgresql--------
---建库
CREATE DATABASE playtest WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'Chinese (Simplified)_China.936'
       LC_CTYPE = 'Chinese (Simplified)_China.936'
       CONNECTION LIMIT = -1;
---建表
CREATE TABLE public."user"
(
  id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),---自增id
  user_name character varying(255),
  CONSTRAINT user_pkey PRIMARY KEY (id)
) WITH ( OIDS=FALSE );

ALTER TABLE public."user" OWNER TO postgres;

---插入数据
INSERT INTO public."user"(id, user_name) VALUES (?, ?);

5.控制器中使用这两个dao

1.mysql

package controllers.mysql

import dao.UserMySqlDAO
import javax.inject.{Inject, Singleton}
import models.User
import play.api.libs.json.{JsError, Json}
import play.api.mvc.{AbstractController, ControllerComponents}

import scala.concurrent.ExecutionContext

/**
 * 使用mysql+slick进行增删查改
 *
 * 成功统一返回json
 *
 * 使用slick时需要删除jdbc依赖
 *
 * @author 梦境迷离
 * @version 1.0, 2019-04-03
 */
@Singleton
class MysqlSlickController @Inject()(userDao: UserMySqlDAO, cc: ControllerComponents)(implicit ec: ExecutionContext)
  extends AbstractController(cc) {


    /**
     * 根据路径参数查询一个单一的用户
     *
     * @param id
     * @return
     */
    def findOne(id: Int) = Action.async {
        userDao.findById(id).map {
            case user => Ok(Json.toJson(user))
        }
    }

    /**
     * 查询所有用户
     *
     * @return
     */
    def findAll = Action.async {
        userDao.all().map {
            case user => Ok(Json.toJson(user))
        }
    }

    /**
     * 插入数据库
     *
     * 重复插入无效
     *
     * @return
     */
    def insertUser = Action(parse.json) { implicit request =>
        val user = request.body.validate[User]
        user.fold(
            errors => {
                BadRequest(Json.obj("status" -> "ERROR", "message" -> JsError.toJson(errors)))
            },
            userData => {
                userDao.insert(userData)
                Ok(Json.obj("status" -> "OK", "count" -> 1))
            }
        )
    }

    /**
     * 根据id删除，返回删除成功标记 1
     *
     * @param id
     * @return
     */
    def deleteById(id: Int) = Action.async {
        userDao.deleteUserById(id).map {
            case count => Ok(Json.obj("count" -> count))
        }

    }


    /**
     * 根据id更新，返回更新成功标记 1
     *
     * @return
     */
    def updateUserWithSlick = Action(parse.json).async { implicit request =>
        val user = request.body.validate[User]
        userDao.updateUser(user.get).map {
            case count => Ok(Json.obj("count" -> count))
        }
    }

}

2.postgresql

package controllers.postgresql

import dao.UserPostgresqlDAO
import javax.inject.Inject
import models.User
import play.api.libs.json.{JsError, Json}
import play.api.mvc.{AbstractController, ControllerComponents}

import scala.concurrent.ExecutionContext

/**
 * @author 梦境迷离
 * @version 1.0, 2019-04-04
 */
class PostgresqlSlickController @Inject()(userDao: UserPostgresqlDAO, cc: ControllerComponents)(implicit ec: ExecutionContext)
  extends AbstractController(cc) {
    /**
     * 根据路径参数查询一个单一的用户
     *
     * @param id
     * @return
     */
    def findOne(id: Int) = Action.async {
        userDao.findById(id).map {
            case user => Ok(Json.toJson(user))
        }
    }

    /**
     * 查询所有用户
     *
     * @return
     */
    def findAll = Action.async {
        userDao.all().map {
            case user => Ok(Json.toJson(user))
        }
    }

    /**
     * 插入数据库
     *
     * 重复插入无效
     *
     * @return
     */
    def insertUser = Action(parse.json) { implicit request =>
        val user = request.body.validate[User]
        user.fold(
            errors => {
                BadRequest(Json.obj("status" -> "ERROR", "message" -> JsError.toJson(errors)))
            },
            userData => {
                userDao.insert(userData)
                Ok(Json.obj("status" -> "OK", "count" -> 1))
            }
        )
    }

    /**
     * 根据id删除，返回删除成功标记 1
     *
     * @param id
     * @return
     */
    def deleteById(id: Int) = Action.async {
        userDao.deleteUserById(id).map {
            case count => Ok(Json.obj("count" -> count))
        }

    }


    /**
     * 根据id更新，返回更新成功标记 1
     *
     * @return
     */
    def updateUserWithSlick = Action(parse.json).async { implicit request =>
        val user = request.body.validate[User]
        userDao.updateUser(user.get).map {
            case count => Ok(Json.obj("count" -> count))
        }
    }
}

注意包的路径是 controllers.postgresql

6.配置路由

#使用slick+mysqlcrud
GET           /mysql/findAll                         controllers.mysql.MysqlSlickController.findAll
POST          /mysql/insertUser                      controllers.mysql.MysqlSlickController.insertUser
DELETE        /mysql/deleteById/:id                  controllers.mysql.MysqlSlickController.deleteById(id:Int)
PUT           /mysql/updateUserWithSlick             controllers.mysql.MysqlSlickController.updateUserWithSlick
GET           /mysql/findOne/:id                     controllers.mysql.MysqlSlickController.findOne(id:Int)
#使用slick+postgresql db
GET           /postgresql/findAll                    controllers.postgresql.PostgresqlSlickController.findAll
POST          /postgresql/insertUser                 controllers.postgresql.PostgresqlSlickController.insertUser
DELETE        /postgresql/deleteById/:id             controllers.postgresql.PostgresqlSlickController.deleteById(id:Int)
PUT           /postgresql/updateUserWithSlick        controllers.postgresql.PostgresqlSlickController.updateUserWithSlick
GET           /postgresql/findOne/:id                controllers.postgresql.PostgresqlSlickController.findOne(id:Int)

项目源码 https://github.com/jxnu-liguobin/scala-play-learn