Scala DBIO类代码示例

//设置package包名称以及导入依赖的类
package testhelpers

import config.ExampleComponents
import org.scalatest.BeforeAndAfterEach
import org.scalatestplus.play.PlaySpec
import org.scalatestplus.play.components.OneServerPerTestWithComponents
import play.api.db.evolutions.Evolutions
import play.api.http.Status
import play.api.libs.ws.WSClient
import play.api.test.{DefaultAwaitTimeout, FutureAwaits, Helpers, TestServer}
import play.api.{Application, Configuration}
import slick.dbio.DBIO

trait PlayWithFoodWithServerBaseTest extends PlaySpec
  with OneServerPerTestWithComponents
  with DefaultFutureDuration
  with DefaultExecutionContext
  with Status
  with DefaultAwaitTimeout
  with FutureAwaits
  with BeforeAndAfterEach {

  class PlayWithFoodWithTestComponents extends ExampleComponents(context) {

    override def configuration: Configuration = {
      val testConfig = Configuration.from(TestUtils.config)
      val config = super.configuration
      val testConfigMerged = config ++ testConfig
      config.toString
      testConfigMerged
    }

    implicit lazy val testWsClient: WSClient = wsClient
  }

  override def components: PlayWithFoodWithTestComponents = new PlayWithFoodWithTestComponents

  private def runServerAndCleanUpInMemDb(c: PlayWithFoodWithTestComponents) = {
    runServerAndExecute(c.application) {
      val dbapi = c.dbApi
      Evolutions.cleanupEvolutions(dbapi.database("default"))
    }
  }

  override protected def afterEach(): Unit = {
    runServerAndCleanUpInMemDb(components)
  }

  protected def runServerAndExecute[T](app: Application)(blockWithComponents: => T): T = {
    Helpers.running(TestServer(port, app))(blockWithComponents)
  }

  def runAndAwaitResult[T](action: DBIO[T])(implicit components: ExampleComponents): T = {
    TestUtils.runAndAwaitResult(action)(components.actionRunner, duration)
  }

} 

//设置package包名称以及导入依赖的类
package slick3.common

import slick.dbio.DBIO
import slick.driver.MySQLDriver.api._

object Initializer {

  def init(): DBIO[Unit] =
    DBIO.seq(
      sqlu"""DELETE FROM items""",
      sqlu"""DELETE FROM users""",
      sqlu"""DELETE FROM companies""",
      sqlu"""INSERT INTO companies (id, name) VALUES
                (1, '?????')
               ,(2, '?????')
               ,(3, '?????')""",
      sqlu"""INSERT INTO users (id, name, age, companyId) VALUES
                (1, '?????', 23, 1)
               ,(2, '?????', 30, 2)
               ,(3, '?????', 36, 1)
               ,(4, '?????', 20, 2)""",
      sqlu"""INSERT INTO items (userId, name, status) VALUES
                (1, '????', 0)
               ,(1, '??????', 1)
               ,(1, '????', 2)
               ,(3, '????', 0)
               ,(3, '?????', 1)
               ,(3, '??????', 2)""",
      sqlu"""DELETE FROM includeSomeType""",
      sqlu"""INSERT INTO includeSomeType (column02, column03, column04, column05, column06, column07, column08, column09) VALUES
                ('AAA', 'BBB', 1, 2, 3, 4, '2016-11-13 13:00;00', CURRENT_TIME)
               ,('CCC', 'DDD', 5, 6, 7, 8, '2016-11-13 13:00;00', CURRENT_TIME)""",
      sqlu"""DELETE FROM columns22over""",
      sqlu"""INSERT INTO columns22over (column02, column03, column04, column05, column06, column07, column08, column09, column10, column11, column12, column13, column14, column15, column16, column17, column18, column19, column20, column21, column22, column23) VALUES
                ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V')"""
    )

} 

//设置package包名称以及导入依赖的类
package controllers.datastore

import play.api.db.slick.DatabaseConfigProvider
import play.api.db.slick.HasDatabaseConfigProvider
import slick.dbio.DBIO
import play.api.libs.concurrent.Execution.Implicits.defaultContext
import slick.driver.JdbcProfile
import scala.concurrent.Future
import scala.util.{Try, Success, Failure}
import javax.inject._

class DataStore @Inject()(protected val dbConfigProvider: DatabaseConfigProvider,
                          private val users: Users,
                          private val stockPrices: StockPrices,
                          private val exchangeRates: ExchangeRates)
    extends HasDatabaseConfigProvider[JdbcProfile] {

  // val initializer = DBIO.seq(users.schema.create,
  //                            stockPrices.schema.create,
  //                            exchangeRates.schema.create)

  // def initialize = db.run(initializer)
  
} 

//设置package包名称以及导入依赖的类
package im.actor.api.rpc

import cats.data.Xor
import im.actor.server.acl.ACLUtils

import scala.concurrent.ExecutionContext
import akka.actor.ActorSystem
import slick.dbio.DBIO
import im.actor.api.rpc.files.ApiFileLocation
import im.actor.server.persist.files.FileRepo

object FileRpcErrors {
  val FileNotFound = RpcError(404, "FILE_NOT_FOUND", "File not found.", false, None)
  val FileTooLarge = RpcError(400, "FILE_TOO_LARGE", "File is too large.", false, None)
  val LocationInvalid = RpcError(400, "LOCATION_INVALID", "", false, None)
  val UnsupportedSignatureAlgorithm = RpcError(400, "SIGNATURE_ALGO_NOT_SUPPORTED", "", false, None)
}

object FileHelpers {

  def withFileLocation[R <: RpcResponse](fileLocation: ApiFileLocation, maxSize: Long)(f: ? DBIO[RpcError Xor R])(implicit ec: ExecutionContext, s: ActorSystem) = {
    FileRepo.find(fileLocation.fileId) flatMap {
      case Some(file) ?
        if (!file.isUploaded) {
          DBIO.successful(Error(FileRpcErrors.LocationInvalid))
        } else if (file.size > maxSize) {
          DBIO.successful(Error(FileRpcErrors.FileTooLarge))
        } else if (ACLUtils.fileAccessHash(file.id, file.accessSalt) != fileLocation.accessHash) {
          DBIO.successful(Error(FileRpcErrors.LocationInvalid))
        } else {
          f
        }
      case None ? DBIO.successful(Error(FileRpcErrors.FileNotFound))
    }
  }

} 

//设置package包名称以及导入依赖的类
package authentication.services

import authentication.models.api.{NewSecurityUser, PlainTextPassword}
import authentication.models.{PasswordHash, SecurityUser, SecurityUserId}
import authentication.repositories.SecurityUserRepo
import authentication.services.api.{SecurityUserCreator, SecurityUserProvider}
import javax.inject.Inject

import commons.models.Login
import org.mindrot.jbcrypt.BCrypt
import slick.dbio.DBIO

private[authentication] class SecurityUserService(securityUserRepo: SecurityUserRepo)
  extends SecurityUserProvider
    with SecurityUserCreator {

  override def create(newSecUser: NewSecurityUser): DBIO[SecurityUser] = {
    require(newSecUser != null)

    val passwordHash = hashPass(newSecUser.password)
    securityUserRepo.create(SecurityUser(SecurityUserId(-1), newSecUser.login, passwordHash, null, null))
  }

  private def hashPass(password: PlainTextPassword): PasswordHash = {
    val hash = BCrypt.hashpw(password.value, BCrypt.gensalt())
    PasswordHash(hash)
  }

  override def byLogin(login: Login): DBIO[Option[SecurityUser]] = {
    require(login != null)

    securityUserRepo.byLogin(login)
  }
} 

//设置package包名称以及导入依赖的类
package authentication.repositories

import authentication.models.{PasswordHash, SecurityUser, SecurityUserId}
import commons.models.{IdMetaModel, Login, Property}
import commons.repositories._
import commons.repositories.mappings.{JavaTimeDbMappings, LoginDbMappings}
import slick.dbio.DBIO
import slick.jdbc.MySQLProfile.api.{DBIO => _, MappedTo => _, Rep => _, TableQuery => _, _}
import slick.lifted.{ProvenShape, _}

private[authentication] class SecurityUserRepo(
                                                         override protected val dateTimeProvider: DateTimeProvider)
  extends BaseRepo[SecurityUserId, SecurityUser, SecurityUserTable]
    with AuditDateTimeRepo[SecurityUserId, SecurityUser, SecurityUserTable]
    with LoginDbMappings
    with SecurityUserDbMappings {

  def byLogin(login: Login): DBIO[Option[SecurityUser]] = {
    query
      .filter(_.login === login)
      .result
      .headOption
  }

  override protected val mappingConstructor: Tag => SecurityUserTable = new SecurityUserTable(_)

  override protected val modelIdMapping: BaseColumnType[SecurityUserId] = MappedColumnType.base[SecurityUserId, Long](
    vo => vo.value,
    id => SecurityUserId(id)
  )

  override protected val metaModel: IdMetaModel = SecurityUserMetaModel

  override protected val metaModelToColumnsMapping: Map[Property[_], (SecurityUserTable) => Rep[_]] = Map(
    SecurityUserMetaModel.id -> (table => table.id),
    SecurityUserMetaModel.login -> (table => table.login),
    SecurityUserMetaModel.password -> (table => table.password)
  )

}

protected class SecurityUserTable(tag: Tag) extends IdTable[SecurityUserId, SecurityUser](tag, "security_user")
  with AuditDateTimeTable
  with JavaTimeDbMappings
  with LoginDbMappings
  with SecurityUserDbMappings {

  def login: Rep[Login] = column("login")

  def password: Rep[PasswordHash] = column("password")

  def * : ProvenShape[SecurityUser] = (id, login, password, createdAt, modifiedAt) <> (SecurityUser.tupled,
    SecurityUser.unapply)
}

private[authentication] object SecurityUserMetaModel extends IdMetaModel {
  override type ModelId = SecurityUserId

  val login: Property[Login] = Property("login")
  val password: Property[PasswordHash] = Property("password")
} 

//设置package包名称以及导入依赖的类
package commons.repositories

import java.time.LocalDateTime

import commons.models.WithDateTimes
import commons.repositories.mappings.JavaTimeDbMappings
import slick.dbio.DBIO
import slick.jdbc.MySQLProfile.api.{DBIO => _, MappedTo => _, Rep => _, TableQuery => _, _}
import slick.lifted._

trait AuditDateTimeRepo[ModelId <: BaseId[Long],
WithDatesModel <: WithId[Long, ModelId] with WithDateTimes[WithDatesModel],
ModelTable <: IdTable[ModelId, WithDatesModel] with AuditDateTimeTable]
  extends BaseRepo[ModelId, WithDatesModel, ModelTable]
    with JavaTimeDbMappings {
  protected val dateTimeProvider: DateTimeProvider

  override def create(model: WithDatesModel): DBIO[WithDatesModel] = {
    val now = dateTimeProvider.now
    val modelWithDates = model.updateCreatedAt(now)
      .updateModifiedAt(now)
    super.create(modelWithDates)
  }

  override def update(model: WithDatesModel): DBIO[WithDatesModel] = {
    val now = dateTimeProvider.now
    val modelWithDates = model.updateModifiedAt(now)
    super.update(modelWithDates)
  }


}

trait AuditDateTimeTable {
  _: Table[_] with JavaTimeDbMappings =>

  def createdAt: Rep[LocalDateTime] = column("created_at")

  def modifiedAt: Rep[LocalDateTime] = column("modified_at")
} 

//设置package包名称以及导入依赖的类
package commons.repositories

import commons.models.WithName
import slick.dbio.DBIO
import slick.jdbc.MySQLProfile.api.{DBIO => _, MappedTo => _, Rep => _, TableQuery => _, _}
import slick.lifted._

trait UniqueNameRepo[ModelId <: BaseId[Long],
WithNameModel <: WithId[Long, ModelId] with WithName,
ModelTable <: IdTable[ModelId, WithNameModel] with WithNameBaseTable]
  extends BaseRepo[ModelId, WithNameModel, ModelTable] {

  def byName(name: String): DBIO[Option[WithNameModel]] = {
    query
      .filter(_.name === name)
      .result
      .headOption
  }

}

trait WithNameBaseTable {
  self: Table[_] =>

  def name: Rep[String] = column("name")
} 

//设置package包名称以及导入依赖的类
package users.services

import javax.inject.Inject

import authentication.models.api.NewSecurityUser
import authentication.services.api.SecurityUserCreator
import commons.exceptions.ValidationException
import commons.validations.{Failure, Success}
import slick.dbio.DBIO
import users.models.{User, UserId, UserRegistration}
import users.services.api.UserCreator

import scala.concurrent.ExecutionContext.Implicits.global

private[users] class UserRegistrationService(userRegistrationValidator: UserRegistrationValidator,
                                                       securityUserCreator: SecurityUserCreator,
                                                       userCreator: UserCreator) {

  def register(userRegistration: UserRegistration): DBIO[User] = {
    userRegistrationValidator.validate(userRegistration) match {
      case Success => doRegister(userRegistration)
      case Failure(violatedConstraints) => throw new ValidationException(violatedConstraints)
    }
  }

  private def doRegister(userRegistration: UserRegistration) = {
    val newSecurityUser = NewSecurityUser(userRegistration.login, userRegistration.password)
    userCreator.create(User(UserId(-1), userRegistration.login))
      .zip(securityUserCreator.create(newSecurityUser))
      .map(_._1)
  }
} 

//设置package包名称以及导入依赖的类
package users.repositories

import javax.inject.Inject

import commons.models.{IdMetaModel, Login, Property}
import commons.repositories.mappings.LoginDbMappings
import commons.repositories.{BaseRepo, IdTable}
import slick.lifted.ProvenShape
import users.models.{User, UserId, UserMetaModel}
import slick.dbio.DBIO
import slick.jdbc.MySQLProfile.api.{DBIO => _, MappedTo => _, Rep => _, TableQuery => _, _}
import slick.lifted._

class UserRepo()
  extends BaseRepo[UserId, User, UserTable] {

  def byLogin(login: Login): DBIO[Option[User]] = {
    query
      .filter(_.login === login)
      .result
      .headOption
  }

  override protected val mappingConstructor: Tag => UserTable = new UserTable(_)

  override protected val modelIdMapping: BaseColumnType[UserId] = MappedColumnType.base[UserId, Long](
    vo => vo.value,
    id => UserId(id)
  )

  override protected val metaModel: IdMetaModel = UserMetaModel

  override protected val metaModelToColumnsMapping: Map[Property[_], (UserTable) => Rep[_]] = Map(
    UserMetaModel.id -> (table => table.id),
    UserMetaModel.login -> (table => table.login)
  )

  // TODO use shared login mapping
  implicit val loginMapping: BaseColumnType[Login] = MappedColumnType.base[Login, String](
    login => login.value,
    str => Login(str)
  )

}

protected class UserTable(tag: Tag) extends IdTable[UserId, User](tag, "user")
  with LoginDbMappings {
  def login: Rep[Login] = column[Login]("login")

  def * : ProvenShape[User] = (id, login) <> (User.tupled, User.unapply)
} 

//设置package包名称以及导入依赖的类
package com.grimey

import com.grimey.staticpage.StaticPagesTable
import slick.backend.DatabaseConfig
import slick.dbio.{DBIO, DBIOAction}
import slick.driver.MySQLDriver
import slick.jdbc.meta.MTable
import slick.lifted.TableQuery
import slick.driver.MySQLDriver.api._

import scala.concurrent.Await
import scala.concurrent.duration._
import scala.concurrent.ExecutionContext.Implicits.global

class GrimeyDatabaseContext(config: DatabaseContextConfig) {
  private lazy val dbConfig: DatabaseConfig[MySQLDriver] = DatabaseConfig.forConfig("slick.dbs.default")
  lazy val db = dbConfig.db

  init()

  private def init(): Unit = {
    if (tableExists("static_pages") == false) {  // Todo: Don't hard code the table name.
      val staticPages = TableQuery[StaticPagesTable]

      val setup: DBIO[Unit] = DBIOAction.seq(staticPages.schema.create)
      val setupFuture = db.run(setup)

      val result = setupFuture.map { r =>  }

      Await.result(result, config.defaultTimeout)
    }
  }

  def tableExists(name: String) = {
    Await.result(db.run(MTable.getTables), config.defaultTimeout)
      .toList
      .map(_.name.name)
      .contains(name)
  }
} 

//设置package包名称以及导入依赖的类
package infrastructure.repository

import Tables._
import domain.model.Place
import slick.dbio.DBIO
import util.Id64
import Tables.profile.api._

import scala.concurrent.ExecutionContext


class PlaceRepository {
  def register(place: Place)(implicit ec: ExecutionContext): DBIO[Int] = {
    Places += PlacesRow(place.id, place.name, place.address)
  }

  def update(id: Long, place: Place)(implicit ec: ExecutionContext): DBIO[Int] = {
    Places.filter(_.placeId === id.bind)
      .map(row => (row.name, row.address))
      .update((place.name, place.address))
  }

  def findBy(placeId: Long)(implicit ec: ExecutionContext): DBIO[Option[Place]] = {
    Places
      .filter(_.placeId === placeId.bind)
      .result
      .headOption
      .map(_.map(p => Place(p.placeId, p.name, p.address)))
  }

  def findBy(placeName: String)(implicit ec: ExecutionContext): DBIO[Option[Place]] = {
    Places
      .filter(_.name === placeName.bind)
      .result
      .headOption
      .map(_.map(p => Place(p.placeId, p.name, p.address)))
  }

} 

//设置package包名称以及导入依赖的类
package domain.service

import javax.inject.Inject

import domain.model.Place
import infrastructure.repository.PlaceRepository
import util.Id64
import slick.dbio.DBIO

import scala.concurrent.ExecutionContext


class PlaceService @Inject() (placeRepository: PlaceRepository) {
  def find(placeName: String)(implicit ec: ExecutionContext): DBIO[Place] = {
    for {
      place <- placeRepository.findBy(placeName)
      p <- place.fold {
        val newPlace = Place(Id64.nextAscId(), placeName, "")
        placeRepository.register(newPlace).map(_ => newPlace)
      }(p => DBIO.successful(p))
    } yield p
  }
} 

//设置package包名称以及导入依赖的类
package spider.database

import slick.jdbc.MySQLProfile.api._
import slick.lifted.Tag
import slick.dbio.DBIO

import com.github.nscala_time.time.Imports._

import java.sql.Date

import spider.spider3w3n.Record3w3n
import spider.Util.DateTimeUtil.{ toSQLDate, stripHourMinSec }

class FarmTable(tag: Tag)
  extends Table[(String, Option[String], Double, String, Date)](
    tag, Some("farm"), "Farm") {
  def name = column[String]("NAME")
  def subtype = column[Option[String]]("SUBTYPE")
  def price = column[Double]("PRICE")
  def market = column[String]("MARKET")
  def date = column[Date]("DATE")
  def * = (name, subtype, price, market, date)
}

object FarmTable extends TableQuery(new FarmTable(_)) {
  def insert(name: String, record: Record3w3n) = {
    this += ((
      name,
      Some(record.name),
      record.price,
      record.market,
      toSQLDate(record.date)))
  }

  def clearRecordsAction(dates: Seq[DateTime]) = {
    DBIO.sequence(
      dates map { date ?
        (this filter (_.date === toSQLDate(stripHourMinSec(date)))).delete
      })
  }
}

class CategoryTable(tag: Tag)
  extends Table[(String, String)](tag, Some("farm"), "Category") {
  def category = column[String]("CATEGORY")
  def name = column[String]("NAME")
  def * = (category, name)
}

object CategoryTable extends TableQuery(new CategoryTable(_)) {
  def insert(category: String, name: String) = {
    this += ((category, name))
  }
  def clearAll = this.delete
} 

//设置package包名称以及导入依赖的类
package spider
package database

import slick.jdbc.JdbcBackend._
import slick.jdbc.MySQLProfile.api.{ Database ? _, DBIOAction ? _, _ }
import slick.dbio.DBIO
import slick.dbio._

import scala.concurrent.{ Await, ExecutionContext }
import scala.concurrent.duration.Duration.Inf

import com.typesafe.scalalogging.Logger

object FarmDB {

  lazy val logger = Logger("spider.database")

  def prepareDbAndTable(config: DBConfig): Unit = {
    import scala.concurrent.ExecutionContext.Implicits.global
    if (config.db.isEmpty) return
    val db = getConnection(config.copy(db = None))
    val actions = Seq(
      createDatabaseSQL(config.db.get),
      createProductTable,
      createCategoryTable)
    actions map { action ?
      Await.result(db.run(action) recover {
        case e ? logger.info(e.getMessage)
      }, Inf)
    }
  }

  def getConnection(config: DBConfig): Database = {
    prepareDbAndTable(config)
    Database.forURL(mysqlURL(config), driver = "com.mysql.jdbc.Driver")
  }

  private def createDatabaseSQL(db: String) = {
    // NO INJECTION PREVENTION
    sqlu"CREATE DATABASE IF NOT EXISTS #$db"
  }

  private def mysqlURL(config: DBConfig): String = {
    val builder = new StringBuilder(s"jdbc:mysql://${config.host}:${config.port}")
    for (db ? config.db) builder ++= s"/$db"
    builder ++= s"?user=${config.username}"
    for (pass ? config.password) builder ++= s"&password=$pass"
    for ((key, value) ? config.properties) builder ++= s"&$key=$value"
    builder.toString
  }

  def createProductTable = FarmTable.schema.create
  def createCategoryTable = CategoryTable.schema.create

  implicit class SyncDB(db: Database) {
    def runSync[R](a: DBIOAction[R, NoStream, Nothing])(
      implicit ec: ExecutionContext): R = {
      Await.result(db.run(a), Inf)
    }
  }
}