Scala DBs类代码示例

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

import scalikejdbc._
import scalike.common.{Initializer, Users}
import scalikejdbc.DB
import scalikejdbc.config.DBs

object SimpleQuery extends Initializer {

  def main(args: Array[String]) = {
    DBs.setup()
    try {

      init()

      DB readOnly { implicit session =>

        println(findNameById1(2))
        println(findNameById1(2))
        println(findById1(2))
        println(findById2(2))
        println(findListMapByAge(20))
        println(findFirst())

      }

    } finally {
      DBs.close()
    }

  }

  def findNameById1(id: Int)(implicit session: DBSession): Option[String] =
    sql"select name from users where id = ${id}".map(rs => rs.string("name")).single.apply()

  def findNameById2(id: Int)(implicit session: DBSession): Option[String] = {
    val nameOnly = (rs: WrappedResultSet) => rs.string("name")
    sql"select name from users where id = ${id}".map(nameOnly).single.apply()
  }

  def findById1(id: Int)(implicit session: DBSession): Option[Users] =
    sql"select id, name, age, companyId from users where id = ${id}"
      .map(rs => Users(rs.int("id"), rs.string("name"), rs.int("age"), rs.int("companyId"))).single.apply()

  // use DSL
  def findById2(id: Int)(implicit session: DBSession): Option[Users] = {
    val u = Users.syntax("u")
    withSQL { select.from(Users as u).where.eq(u.id, id) }.map(Users(u.resultName)).single.apply()
  }

  def findListMapByAge(age: Int)(implicit session: DBSession): Seq[Map[String, Any]] =
    sql"select id, name, age, companyId from users where age = ${age}"
        .map(rs => rs.toMap).list.apply()

  // use DSL
  def findFirst()(implicit session: DBSession): Option[String] = {
    val u = Users.syntax("u")
    withSQL { select(u.name).from(Users as u) }.map(_.string(u.name)).first().apply()
  }

} 

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

import scalikejdbc._
import scalike.common.{Initializer, Users}
import scalikejdbc.DB
import scalikejdbc.config.DBs

object SimpleUpdate extends Initializer {

  def main(args: Array[String]) = {
    DBs.setup()
    try {

      init()

      DB localTx { implicit session =>

        val user1 = Users(0, "?????", 19, 3)
        println(insert1(user1))
        println(insert2(user1))
        println(insert3(user1))
        println(insert4(user1))

      }

    } finally {
      DBs.close()
    }

  }

  def insert1(user: Users)(implicit session: DBSession): Unit =
    sql"""insert into users (name, age, companyId)
      values (${user.name}, ${user.age}, ${user.companyid})"""
      .update.apply()

  // get auto increment key
  def insert2(user: Users)(implicit session: DBSession): Long = {
    sql"""insert into users (name, age, companyId)
      values (${user.name}, ${user.age}, ${user.companyid})"""
      .updateAndReturnGeneratedKey.apply()
  }

  // use DSL
  def insert3(user: Users)(implicit session: DBSession): Unit = {
    val column = Users.column
    withSQL {
      insert.into(Users).namedValues(
        column.name -> user.name,
        column.age -> user.age,
        column.companyid -> user.companyid
      )
    }.update.apply()
  }

  def insert4(user: Users)(implicit session: DBSession): Users =
    Users.create(user.name, user.age, user.companyid)

} 

//设置package包名称以及导入依赖的类
import java.sql.{Driver, DriverManager}

import org.scalatest.BeforeAndAfter
import org.scalatestplus.play.PlaySpec
import play.api.Logger
import scalikejdbc._
import scalikejdbc.config.DBs

import scala.collection.mutable.Stack

class DatabaseSpec extends PlaySpec with BeforeAndAfter {
  implicit var session = AutoSession

  before {
    DBs.setupAll()
  }

  after {
    DBs.closeAll()
  }

  "Database" must {
    "List of database drivers" in {
      val drivers = DriverManager.getDrivers

      while (drivers.hasMoreElements) {
        val driver:Driver = drivers.nextElement()
        Logger.info(driver.toString)
      }
    }
    "Fetch users" in {
      val sql: SQL[Nothing, NoExtractor] = sql"select * from users"
      val map: SQL[Map[String, Any], HasExtractor] = sql.toMap
      val list: SQLToList[Map[String, Any], HasExtractor] = map.list
      val users: List[Map[String, Any]] = list.apply()
    }
  }

  "Slick" must {
    "How to use Scala test" in {
      // TODO
    }

    "throw NoSuchElementException if an empty stack is popped" in {
      val emptyStack = new Stack[Int]
      a [NoSuchElementException] must be thrownBy {
        emptyStack.pop()
      }
    }
  }
} 

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

import mapdomain.graph.Coordinate
import mapdomain.publictransport.{ PublicTransportCombination, PublicTransportCombinationPath }
import mapdomain.repository.BaseRepositoryDBSpec
import mapdomain.sidewalk.Ramp
import org.scalatest.{ BeforeAndAfterAll, BeforeAndAfterEach, FlatSpec, Matchers }
import scalikejdbc.config.DBs

import scala.math._

class PublicTransportCombinationRepositoryDBSpec extends FlatSpec with Matchers with BeforeAndAfterAll with BeforeAndAfterEach
    with BaseRepositoryDBSpec {

  val precision: Double = pow(10, -8)

  override def beforeAll(): Unit = DBs.setupAll()

  override def beforeEach(): Unit = deleteAll()

  override def afterAll(): Unit = DBs.closeAll()

  "PublicTransportCombinationPath Repository" should "create PublicTransportCombinationPaths correctly" in {
    val ptcp1 = PublicTransportCombinationPathRepository.create(PublicTransportCombinationPath(1, 2, "test-walkPath-1"))
    val ptcp2 = PublicTransportCombinationPathRepository.create(PublicTransportCombinationPath(3, 4, "test-walkPath-2"))

    val ptcs = PublicTransportCombinationPathRepository.findAll
    ptcs.size shouldBe 2

    assertWalkPath(ptcp1)
    assertWalkPath(ptcp2)

    val ptcp = PublicTransportCombinationPathRepository.findByStopAndTravelInfo(50, 50)
    ptcp.isDefined shouldBe false
  }

  def assertWalkPath(combinationPath: PublicTransportCombinationPath): Unit = {
    val ptcp = PublicTransportCombinationPathRepository.findByStopAndTravelInfo(combinationPath.fromStopId, combinationPath.toTravelInfoId)
    ptcp.isDefined shouldBe true
    ptcp.get.fromStopId shouldBe combinationPath.fromStopId
    ptcp.get.toTravelInfoId shouldBe combinationPath.toTravelInfoId
    ptcp.get.walkPath shouldBe combinationPath.walkPath
  }

} 

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

import mapdomain.graph.Coordinate
import mapdomain.publictransport.{ Path, StopUnsaved, TravelInfo, TravelInfoUnsaved }
import mapdomain.repository.BaseRepositoryDBSpec
import org.scalatest.{ BeforeAndAfterAll, BeforeAndAfterEach, FlatSpec, Matchers }
import scalikejdbc.config.DBs

import scala.math._

class TravelInfoRepositoryDBSpec extends FlatSpec with Matchers with BeforeAndAfterAll with BeforeAndAfterEach
    with BaseRepositoryDBSpec {

  val precision: Double = pow(10, -8)

  override def beforeAll(): Unit = DBs.setupAll()

  override def beforeEach(): Unit = deleteAll()

  override def afterAll(): Unit = DBs.closeAll()

  "Travel Info Repository" should "create travelInfo correctly" in {

    val travelInfoUnsaved: TravelInfoUnsaved = TravelInfoUnsaved(
      description = "Line 150", firstStopId = Some(1), lastStopId = Some(10),
      branch = "A", name = "Linea 123", sentido = "Forward", `type` = "BUS")
    var travelInfo: TravelInfo = TravelInfoRepository.create(travelInfoUnsaved)
    val path: Path = PathRepository.create("some coordinates")

    val firstStop = StopRepository.create(
      StopUnsaved(Coordinate(10l, 11l), sequence = 1, pathId = path.id,
        travelInfoId = travelInfo.id, isAccessible = false))
    val lastStop = StopRepository.create(
      StopUnsaved(Coordinate(12l, 13l), sequence = 2, pathId = path.id,
        travelInfoId = travelInfo.id, isAccessible = true))

    travelInfo = travelInfo.copy(firstStopId = firstStop.id, lastStopId = lastStop.id)
    TravelInfoRepository.save(travelInfo)

    travelInfo = TravelInfoRepository.find(travelInfo.id).get
    travelInfo.description shouldBe travelInfoUnsaved.description
    travelInfo.branch shouldBe travelInfoUnsaved.branch
    travelInfo.`type` shouldBe travelInfoUnsaved.`type`
    travelInfo.name shouldBe travelInfoUnsaved.name
    travelInfo.sentido shouldBe travelInfoUnsaved.sentido
    travelInfo.firstStopId shouldBe firstStop.id
    travelInfo.lastStopId shouldBe lastStop.id

    TravelInfoRepository.deleteAll
  }

} 

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

import com.softwaremill.macwire._
import controllers._
import play.api.ApplicationLoader.Context
import play.api._
import play.api.libs.concurrent.Execution.Implicits._
import play.api.routing.Router
import play.filters.csrf.CSRFComponents
import router.Routes
import scalikejdbc.config.DBs

import scala.concurrent.Future
import scala.language.postfixOps



class AppApplicationLoader extends ApplicationLoader {
  def load(context: Context): Application = {
    LoggerConfigurator(context.environment.classLoader).foreach { configurator =>
      configurator.configure(context.environment)
    }
    createApp(context).application
  }

  def createApp(context: Context) =
    new BuiltInComponentsFromContext(context) with AppComponents
}

trait FiltersConfig extends BuiltInComponents with CSRFComponents {
  lazy override val httpFilters = Seq(csrfFilter)
}

trait PlayCoreComponents extends BuiltInComponents with Controllers {
  lazy val assets: Assets = wire[Assets]
  lazy val prefix: String = "/"
  lazy val router: Router = wire[Routes]
}

trait AppComponents extends BuiltInComponents
  with PlayCoreComponents
  with MqttConfig
  with FiltersConfig
  with SqlH2Config
  with SilhouetteAppModule
  with Controllers {

  Logger.info("The app is about to start")
  initDb
  initDbAggregation()
  initializeListeners()
  initWsQuerying()

  applicationLifecycle.addStopHook(() => {
    mqttConnector.disconnect().map(_ =>
      Future {
        DBs.closeAll()
      }
    )
  })
} 

//设置package包名称以及导入依赖的类
import controllers.Assets
import play.api.ApplicationLoader.Context
import play.api._
import play.api.db.{DBComponents, HikariCPComponents}
import play.api.db.evolutions.{DynamicEvolutions, EvolutionsComponents}
import play.api.routing.Router
import router.Routes
import com.softwaremill.macwire._
import controllers._
import dao._
import scalikejdbc.config.DBs
import security.{UserAuthAction, UserAwareAction}
import services._

import scala.concurrent.Future

class AppLoader extends ApplicationLoader {
  def load(context: Context) = {
    LoggerConfigurator(context.environment.classLoader).foreach { configurator =>
      configurator.configure(context.environment)
    }
    (new BuiltInComponentsFromContext(context) with AppComponents).application
  }
}

trait AppComponents extends BuiltInComponents
 with EvolutionsComponents with DBComponents with HikariCPComponents {
  lazy val assets: Assets = wire[Assets]
  lazy val prefix: String = "/"
  lazy val router: Router = wire[Routes]
  lazy val maybeRouter = Option(router)
  override lazy val httpErrorHandler = wire[ProdErrorHandler]

  lazy val mainController = wire[MainController]
  lazy val authController = wire[AuthController]

  lazy val sessionDao = wire[SessionDao]
  lazy val userDao = wire[UserDao]

  lazy val userService = wire[UserService]
  lazy val authService = wire[AuthService]
  lazy val userAuthAction = wire[UserAuthAction]
  lazy val userAwareAction = wire[UserAwareAction]

  override lazy val dynamicEvolutions = new DynamicEvolutions

  applicationLifecycle.addStopHook { () =>
    DBs.closeAll()
    Future.successful(Unit)
  }

  val onStart = {
    DBs.setupAll()
    applicationEvolutions
  }
} 

//设置package包名称以及导入依赖的类
package com.github.twinra.lendmanager.rest

import javax.servlet.ServletContext

import com.github.twinra.lendmanager.dao.LendingRepository
import com.github.twinra.lendmanager.repo.{ItemRepository, PersonRepository}
import org.scalatra.LifeCycle
import scalikejdbc.config.DBs

class ScalatraBootstrap extends LifeCycle {
  override def init(context: ServletContext) {
    DBs.setup()
    implicit val itemsRepo = new ItemRepository {
      init()
    }
    implicit val peopleRepo = new PersonRepository {
      init()
    }
    implicit val lendingsRepo = new LendingRepository {
      init()
    }

    context.mount(new PeopleServlet, "/people/*")
    context.mount(new ItemsServlet, "/items/*")
    context.mount(new LendingsServlet, "/lendings/*")
  }

  override def destroy(context: ServletContext): Unit = DBs.closeAll()
} 

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

import akka.actor.ActorSystem
import akka.http.scaladsl.Http
import akka.stream.ActorMaterializer
import com.example.dao.PostTableImpl
import com.example.service.{HealthCheckServiceImpl, PostService}
import com.typesafe.config.ConfigFactory
import scalikejdbc.config.DBs

object Main {
  def main(args: Array[String]): Unit = {

    implicit val system = ActorSystem("app")
    implicit val materializer = ActorMaterializer()
    implicit val executionContext = system.dispatcher

    val serverPort = ConfigFactory.load().getInt("serverPort")

    DBs.setup()

    val postService = new PostService(PostTableImpl)
    val healthCheckService = HealthCheckServiceImpl

    val route = new Route(postService, healthCheckService)

    Http().bindAndHandle(route.route, "0.0.0.0", serverPort)

    sys.addShutdownHook {
      healthCheckService.makeUnhealthy()
      Thread.sleep(3000L) // wait to close connections gracefully.
    }
  }
} 

//设置package包名称以及导入依赖的类
package siliconValley.infra.rdbms

import scalikejdbc.config.DBs
import scalikejdbc.{SQL, AutoSession, DBSession}

trait WithTestTable {

  implicit val session: DBSession = AutoSession

  protected val testTableName:String

  def deleteAllTestRecord(): Unit = {
    SQL(
      s"DELETE FROM `$testTableName`"
    ).execute().apply()(session)
    ()
  }

  def withSession[T](f: => T): T = {
    try {
      DBs.setup()
      f
    } finally {
     // deleteAllTestRecord()
      DBs.close()
    }
  }
} 

//设置package包名称以及导入依赖的类
import actors.StatsActor
import actors.StatsActor.Ping
import akka.actor.Props
import controllers.{Assets, HomeController}
import play.api.ApplicationLoader.Context
import play.api._
import play.api.routing.Router
import router.Routes
import com.softwaremill.macwire._
import play.api.libs.ws.ahc.{AhcConfigBuilder, AhcWSComponents}
import services.{SunService, WeatherService}
import filters.StatsFilter
import play.api.mvc._
import scalikejdbc.config.DBs

import scala.concurrent.Future

class AppApplicationLoader extends ApplicationLoader{
  def load(context: Context) = {
    LoggerConfigurator(context.environment.classLoader).foreach {
      configuration => configuration.configure(context.environment)
    }
    (new BuiltInComponentsFromContext(context) with AppComponents).application

  }
}

trait AppComponents extends BuiltInComponents with AhcWSComponents {
  lazy val assets: Assets = wire[Assets]
  lazy val prefix: String = "/"
  lazy val router: Router = wire[Routes]
  lazy val applicationController = wire[HomeController]
  lazy val sunService = wire[SunService]
  lazy val weatherService = wire[WeatherService]

  lazy val statsFilter: Filter = wire[StatsFilter]

  override lazy val httpFilters = Seq(statsFilter)
  lazy val statsActor = actorSystem.actorOf(
    Props(wire[StatsActor]), StatsActor.name)

  applicationLifecycle.addStopHook{()=>
    Logger.info("The app is about to stop")
    DBs.closeAll()
    Future.successful(Unit)
  }

  val onStart = {
    Logger.info("The app is about to start")
    DBs.setupAll()
    statsActor ! Ping
  }




 } 

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

import scalike.common.{Initializer, Users}
import scalikejdbc._
import scalikejdbc.{AutoSession, DB, DBSession, ReadOnlyAutoSession}
import scalikejdbc.config.DBs

object AutoSessionTest extends Initializer {

  def main(args: Array[String]) = {
    DBs.setup()
    try {

      init()

      // auto session
      println(findById(1))
      println(insert())

      DB localTx { implicit session =>
        println(findById(1))
        println(insert())
      }

    } finally {
      DBs.close()
    }
  }

  def findById(id: Int)(implicit session: DBSession = ReadOnlyAutoSession): Option[Users] =
    sql"select id, name, age, companyId from users where id = ${id}"
      .map(rs => Users(rs.int("id"), rs.string("name"), rs.int("age"), rs.int("companyId"))).single.apply()

  def insert()(implicit session: DBSession = AutoSession): Users =
    Users.create("bob", 25, 1)

}