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.Driverdb.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.Driverdb.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.Driverdefault.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 daoimport javax.inject.Inject
import models.User
import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import slick.jdbc.JdbcProfileimport 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 daoimport javax.inject.Inject
import models.User
import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import play.db.NamedDatabase
import slick.jdbc.JdbcProfileimport 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 = postgresENCODING = 'UTF8'TABLESPACE = pg_defaultLC_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),---自增iduser_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.mysqlimport 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.postgresqlimport 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