Scala DBApi类代码示例

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

import javax.inject.Inject

import models.{FileMetadata, Account, File, Path}
import play.api.db.DBApi
import repositories.ValidationError

import scala.concurrent.ExecutionContext

class FileRepository @Inject()(
  dbApi: DBApi,
  nodeRepository: FsNodeRepository,
  fileSourceRepository: FileSourceRepository,
  fileMetadataRepository: FileMetadataRepository
)(
 implicit ec: ExecutionContext
){

  
  def delete(file: File)(implicit account: Account): Either[ValidationError, Unit] = {
    nodeRepository.delete(file.node) match {
      case Left(error) => Left(error)
      case Right(_) => Right(Unit)
    }
  }

  // TODO Search file by name/path, content ?
} 

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

import java.sql.Connection
import java.util.UUID
import javax.inject.Inject

import anorm.SqlParser._
import anorm._
import models.{FsNode, Permission}
import play.api.db.DBApi

import scala.concurrent.ExecutionContext

class [email protected]()(
  dbApi: DBApi
)(
  implicit ec: ExecutionContext
) {

  import PermissionRepository._

  private[filesystem] def insertNonAtomic(fileSystemElement: FsNode, permission: Permission)(implicit c: Connection) = {
    insertPermission(fileSystemElement, permission).execute()
  }

}

object PermissionRepository {

  val table = "permission"

  val parser = {
    get[UUID]("account_id") ~
    get[Array[String]]("permissions")  map {
      case account_id ~ permissions
        => Permission(account_id, permissions)
    }
  }

  private def insertPermission(fileSystemElement: FsNode, permission: Permission) = SQL"""
     INSERT INTO #$table (
       account_id,
       directory_id,
       permissions)
     VALUES (
       ${permission.accountId}::uuid,
       ${fileSystemElement.id}::uuid,
       ${permission.permissions.toArray[String]}
     );
    """
} 

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

import javax.inject.Inject

import models.{File, Account, Directory, Path}
import play.api.db.DBApi
import repositories.ValidationError

import scala.concurrent.ExecutionContext

class DirectoryRepository @Inject()(
  dbApi: DBApi,
  nodeRepository: FsNodeRepository
)(
  implicit ec: ExecutionContext
){

  
  def delete(directory: Directory)(implicit account: Account): Either[ValidationError, Unit] = {
    nodeRepository.delete(directory.node) match {
      case Left(error) => Left(error)
      case Right(_) => Right(Unit)
    }
  }

  // TODO Search directory by name/path
} 

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

import anorm._
import play.api.db.DBApi
import scala.concurrent.Future
import scala.util.Random
import javax.inject.{Inject, Singleton}

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

@Singleton
class UserCollection @Inject() (dbs: DBApi)(implicit ec: DatabaseExecutionContext) {

  private val db = dbs.database("default")
  private val parser = Macro.namedParser[User]


  def create(username: String, password: String): Unit = {
      Future {
        val token = Random.nextString(20)
        db.withConnection { implicit conn =>
          SQL("INSERT INTO users(username, password, token) VALUES ({username}, {password}, {token})").
            on('username -> username, 'password -> password, 'token -> token).
            executeInsert()
        }
      }
  }

  def delete(username: String, password: String): Unit = {
    Future {
      db.withConnection { implicit conn =>
        SQL("DELETE FROM useres WHERE username = {username} AND password = {password}").
          on('username -> username, 'password -> password).
          execute()
      }
    }
  }

  def login(username: String, password: String): Future[Option[User]] = {
    Future {
      db.withConnection { implicit conn =>
        SQL("SELECT * FROM users WHERE username = {username} AND password = {password}").
          on('username -> username, 'password -> password).
          executeQuery().as(parser.*).headOption
      }
    }
  }

  def findByToken(token: String): Future[Option[User]] = {
    Future {
      db.withConnection { implicit conn =>
        SQL("SELECT * FROM users WHERE token = {token}").
          on('token -> token).
          executeQuery().as(parser.*).headOption
      }
    }
  }

} 

//设置package包名称以及导入依赖的类
import com.github.stonexx.play.db.slick.DB
import org.scalatestplus.play._
import play.api.db.DBApi
import play.api.test._
import play.api.test.Helpers._

import scala.concurrent.ExecutionContext


class DBSpec extends PlaySpec with guice.GuiceOneAppPerTest {

  "DB" should {
    "work as expected" in {
      import models._
      import slick.Tables._, profile.api._

      implicit val ec = app.injector.instanceOf[ExecutionContext]

      val cats = TableQuery[Cats]

      val testKitties = Seq(
        Cat("kit", "black", flag = true, Cat.State.Normal),
        Cat("garfield", "orange", flag = true, Cat.State.Good),
        Cat("creme puff", "grey", flag = true, Cat.State.VeryGood)
      )
      await(DB.run((for {
        oldCats <- cats.result
        _ <- cats.delete
        _ <- cats ++= testKitties
        _ <- cats.result.map(_ must equal(testKitties))
        _ <- cats.delete
        _ <- cats ++= oldCats
      } yield ()).transactionally))
    }

    "use the correct db settings when specified" in {
      app.injector.instanceOf[DBApi].database("specific").withConnection { implicit conn =>
        conn.getMetaData.getURL must equal("jdbc:h2:mem:veryspecialindeed")
      }
    }

    "use the default db settings when no other possible options are available" in {
      app.injector.instanceOf[DBApi].database("default").withConnection { implicit conn =>
        conn.getMetaData.getURL must equal("jdbc:h2:mem:play")
      }
    }
  }

} 

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

import javax.inject.Inject

import play.api.db.DBApi
import anorm._
import anorm.SqlParser.{int, str}

@javax.inject.Singleton
class ProductService @Inject() (dbApi: DBApi) {

  private val DB = dbApi.database("default")

  val defaultParser = int("id") ~ str("name") map {
    case id ~ name  => Product(id, name)
  }

  def save(product: Product) = {
    println("Product saved!!")
    DB.withConnection { implicit c =>
      SQL("INSERT INTO Products(id, name) VALUES ({id}, {name});")
        .on('id -> product.id, 'name -> product.name).executeInsert()
    }
  }


  def update(product: Product) = {


    DB.withConnection { implicit c =>
      SQL("UPDATE Products SET name = {name} WHERE id = {id}")
        .on('name -> product.name, 'id -> product.id).executeUpdate()
    }
  }


  def delete(id: Long) = {
    DB.withConnection { implicit c =>
      SQL("DELETE FROM Products WHERE id={id};")
        .on('id -> id).executeUpdate()
    }
  }


  def get(id: Long) = {
    DB.withConnection { implicit c =>
      SQL("SELECT * FROM Products WHERE id={id};")
        .on('id -> id).executeQuery().singleOpt(defaultParser)
    }
  }

  def all = {
    DB.withConnection { implicit c =>
      SQL("SELECT * FROM Products;").executeQuery().list(defaultParser)
    }
  }
} 

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

import javax.inject.Inject

import anorm.SqlParser._
import anorm._
import models.Tourney
import play.api.db.DBApi

import scala.language.postfixOps

@javax.inject.Singleton
class TourneyService @Inject()(dbapi: DBApi) {

  private val db = dbapi.database("default")

  val simple = {
    get[Option[Long]]("tourney.id") ~
      get[String]("tourney.name") map {
      case id ~ name => Tourney(id, name)
    }
  }

  def findById(id: Long): Tourney = {
    db.withConnection { implicit connection =>
      SQL(
        """
          select * from tourney where id = {id}
        """
      ).on(
        'id -> id
      ).as(simple.single)
    }
  }

  def insert(t: Tourney): Long = {
    db.withConnection { implicit connection =>
      SQL(
        """
          insert into tourney values ((select next value for tourney_seq), {name})
        """
      ).on(
        'name -> t.name
      ).executeInsert(scalar[Long].single)
    }
  }


} 

//设置package包名称以及导入依赖的类
package cores.test.scalatest

import org.scalatest.BeforeAndAfterEach
import org.scalatestplus.play.{OneAppPerSuite, PlaySpec}
import play.api.db.DBApi
import play.api.db.evolutions.Evolutions

import scala.util.control.Exception._



  override def afterEach(): Unit = {
    withFinally {
      super.afterEach()
    }
  }

  // Java???finally??
  // http://seratch.hatenablog.jp/entry/20111126/1322309305
  private def withFinally = {
    ultimately {
      Evolutions.cleanupEvolutions(databaseApi.database("default"))
    }
  }

  // Google Guice?injection??????????????????
  private lazy val databaseApi: DBApi = app.injector.instanceOf[DBApi] //here is the important line
} 

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

import javax.inject.Inject

import anorm.SqlParser._
import anorm._

import play.api.db.DBApi

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

@javax.inject.Singleton
class CompanyService @Inject() (dbapi: DBApi) {

  private val db = dbapi.database("default")

  
  def options: Seq[(String,String)] = db.withConnection {
    implicit connection =>
      SQL("select * from company order by name").as(simple *).
        foldLeft[Seq[(String, String)]](Nil) {
          (cs, c) =>  //cs: column sets(result), c: column(each record)
            //cs: Seq[(String, String)], c: (String,String), c.id: String
            c.id.fold(cs) {  //use column's id to fold with cs
              id => //this id is c.id
                cs :+ (id.toString -> c.name) //finally gather to cs
            }
        }
  }

} 

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

import javax.inject._

import play.api.Logger
import play.api.inject.ApplicationLifecycle

import scala.concurrent.Future
import play.api.db.DBApi
import utils.DefaultDB
import play.api.cache.CacheApi


trait LoadCache {
  def start()
}

@Singleton
class LoadCacheImpl @Inject()(appLifecycle: ApplicationLifecycle, cache:CacheApi, db:DBApi) extends LoadCache{
  
  // You can do this, or just explicitly call `hello()` at the end
  def start(): Unit = {
    val defaultDB = new DefaultDB(db)
    val shortURLs = defaultDB.findShortURLs()

    for(shortURL <- shortURLs){
      Logger.info(s"inserting url key ${shortURL.url} value $shortURL into cache.")
      cache.set(shortURL.url, shortURL)
      Logger.info(s"inserting url key ${shortURL.shortURL} value $shortURL into cache.")
      cache.set(shortURL.shortURL, shortURL)

    }

  }
  
  // Called when this singleton is constructed (could be replaced by `hello()`)
  start()
} 

//设置package包名称以及导入依赖的类
import org.scalatest.{BeforeAndAfterAll, Suite}
import java.util.Calendar
import java.util.concurrent.TimeUnit
import play.api.Mode
import play.api.db.DBApi
import play.api.inject.guice.GuiceApplicationBuilder

trait IntegrationDb extends BeforeAndAfterAll { this: Suite =>
  lazy val appBuilder = new GuiceApplicationBuilder().in(Mode.Test)
  lazy val injector = appBuilder.injector()
  lazy val databaseApi = injector.instanceOf[DBApi]
  lazy val db = databaseApi.database("default")
  lazy val connection = db.getConnection()
    def cleanup() = {
    connection.close()
    db.shutdown()
    databaseApi.shutdown()
  }

  override def afterAll() = {
    cleanup()
  }

  def nowAsTimestamp(daysDiff: Int = 0) = {
    val utilDate = new java.util.Date()
    val now = Calendar.getInstance()
    now.setTime(utilDate)
    now.add(Calendar.DATE, daysDiff)
    val nowDiff = new java.sql.Timestamp(now.getTimeInMillis())
    nowDiff
  }
} 

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

import com.google.inject.AbstractModule
import com.mohiva.play.silhouette.api.Environment
import com.mohiva.play.silhouette.test.FakeEnvironment
import com.typesafe.config.ConfigFactory
import models.User
import net.codingwell.scalaguice.ScalaModule
import org.scalatest.{BeforeAndAfterEach, Suite, TestData}
import org.scalatestplus.play.{OneAppPerTest, PlaySpec}
import play.api.db.DBApi
import play.api.db.evolutions.Evolutions
import play.api.{Application, Configuration, Mode}
import play.api.inject.Injector
import play.api.inject.guice.GuiceApplicationBuilder
import utils.auth.DefaultEnv

import scala.concurrent.ExecutionContext.Implicits.global
import scala.reflect.ClassTag

trait PlogSpec extends PlaySpec with OneAppPerTest with BeforeAndAfterEach { this: Suite =>

  lazy val appBuilder: GuiceApplicationBuilder = new GuiceApplicationBuilder()
    .overrides(new FakeModule)
    .in(Mode.Test)
    .overrides(new FakeModule)
    .loadConfig(Configuration(ConfigFactory.load("application.test.conf")))

  lazy val injector: Injector = appBuilder.injector()
  lazy val databaseApi: DBApi = injector.instanceOf[DBApi]

  override def newAppForTest(testData: TestData): Application = {
    appBuilder
      .build()
  }

  override def beforeEach {
    val dbApi = databaseApi
    Evolutions.applyEvolutions(dbApi.database("default"))
  }

  override def afterEach {
    Evolutions.cleanupEvolutions(databaseApi.database("default"))
  }

  
  implicit val env: Environment[DefaultEnv] = new FakeEnvironment[DefaultEnv](
    Seq(
      barUser.loginInfo -> barUser,
      fooUser.loginInfo -> fooUser
    )
  )

  class FakeModule extends AbstractModule with ScalaModule {
    def configure() = {
      bind[Environment[DefaultEnv]].toInstance(env)
    }
  }
} 

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

import javax.inject._
import org.scalatestplus.play._
import org.scalatest._
import play.api._
import play.api.test._
import play.api.test.Helpers._
import play.api.inject.guice._
import play.api.db.{ Database, DBApi}
import play.api.db.evolutions.Evolutions

trait DatabaseSpec extends PlaySpec with OneAppPerSuite with BeforeAndAfterEach {
  implicit override lazy val app: Application = new GuiceApplicationBuilder().build

  // Prepare and clean database
  // http://stackoverflow.com/questions/33392905/how-to-apply-manually-evolutions-in-tests-with-slick-and-play-2-4
  lazy val injector = app.injector

  lazy val databaseApi = injector.instanceOf[DBApi] //here is the important line

  override def beforeEach() = {
    Evolutions.applyEvolutions(databaseApi.database("default"))
  }

  override def afterEach() = {
    Evolutions.cleanupEvolutions(databaseApi.database("default"))
  }
} 

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

import javax.inject.{Inject, Singleton}

import anorm._
import anorm.SqlParser._
import play.api.db.DBApi
import scala.concurrent.Future

case class Vote(pollId: Long, userId: String, selection: Int) {
  val userMentionStr = s"<@$userId>"
}

trait VoteRepository {
  def select(pollId: Long): Future[Seq[Vote]]

  def vote(v: Vote): Future[Int]
}

@Singleton
class VoteRepositoryImpl @Inject()(dbapi: DBApi) extends VoteRepository {
  private val logger = org.slf4j.LoggerFactory.getLogger(this.getClass)
  private val db = dbapi.database("bob")

  val simple: RowParser[Vote] =
    get[Long]("poll_id") ~ get[String]("user_id") ~ get[Int]("candidate_serial_no") map {
      case pollId ~ userId ~ candidateSerialNo => Vote(pollId, userId, candidateSerialNo)
    }

  def select(pollId: Long): Future[Seq[Vote]] = {
    Future.successful {
      db.withConnection { implicit conn =>
        SQL(
          """SELECT poll_id, user_id, candidate_serial_no
            |FROM vote_history
            |WHERE poll_id = {poll_id}
          """.stripMargin)
          .on('poll_id -> pollId)
          .as(simple.*)
      }
    }
  }

  def vote(v: Vote): Future[Int] = {
    Future.successful {
      db.withConnection { implicit conn =>
        SQL("INSERT INTO vote_history VALUES ({poll_id}, {user_id}, {candidate_serial_no})")
          .on(
            'poll_id -> v.pollId,
            'user_id -> v.userId,
            'candidate_serial_no -> v.selection
          )
          .executeUpdate
      }
    }
  }
} 

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

import javax.inject.{Inject, Singleton}

import anorm._
import anorm.SqlParser._
import play.api.cache._
import play.api.db.DBApi

import scala.concurrent.duration._
import scala.concurrent.{Await, Future}

final case class Bob(id: Long, name: String)

trait BobRepository {
  val simple: RowParser[Bob] = {
    get[Long]("id") ~ get[String]("name") map {
      case id ~ name => Bob(id, name)
    }
  }

  def add(name: String): Future[Int]

  def getList: Future[Seq[Bob]]

  val list: Seq[Bob]

  val asMap: Map[Long, String]
}

@Singleton
class BobRepositoryImpl @Inject()(cache: CacheApi, dbapi: DBApi) extends BobRepository {
  private val logger = org.slf4j.LoggerFactory.getLogger(this.getClass)
  private val db = dbapi.database("bob")

  val list: Seq[Bob] = cache.getOrElse("bob"){
    val bob = Await.result(getList, 10 seconds)
    bob
  }

  override def getList: Future[Seq[Bob]] = {
    Future.successful {
      db.withConnection { implicit connection =>
        SQL("SELECT id, name FROM bob ORDER BY id ASC")
          .as(simple.*)
      }
    }
  }

  def add(name: String): Future[Int] = {
    Future.successful {
      db.withConnection { implicit conn =>
        SQL("INSERT INTO bob (name) values ({name})")
          .on('name -> name)
          .executeUpdate()
      }
    }
  }

  val asMap: Map[Long, String] = list.map(l => l.id -> l.name).toMap
}