Scala Database类代码示例

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

import javax.inject.Inject

import anorm.{RowParser, ~}
import anorm.SqlParser._
import domain.Status.Status
import play.api.db.Database

case class Usuario(
  id: Long,
  status: Status,
  nickname: String,
  roles: Seq[Rol] = Seq())

class UsuarioRepo @Inject()(db: Database, rolRepo: RolRepo) {

  val parser: RowParser[Usuario] = {
      long("id") ~
      str("status") ~
      str("nickname") map {
      case id ~ status ~ nickname =>
        Usuario(id, Status.withName(status), nickname, rolRepo.findByUsuario(id))
    }
  }

} 

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

import javax.inject.Inject

import anorm._
import anorm.SqlParser._
import domain.Status.Status
import org.joda.time.DateTime
import play.api.db.Database

case class Rol(
  id: Long,
  status: Status,
  nombre: String,
  descripcion: String,
  fechaCreacion: DateTime,
  fechaUltimaModificacion: DateTime)

class RolRepo @Inject()(db: Database) {

  val tableName = "Rol"

  val parser: RowParser[Rol] = {
      long("id") ~
      int("status") ~
      str("nombre") ~
      str("descripcion") ~
      get[DateTime]("fecha_creacion") ~
      get[DateTime]("fecha_ultima_modificacion") map {
      case id ~ status ~ nombre ~ descripcion ~ fechaCreacion ~ fechaUltimaModificacion =>
        Rol(id, Status(status), nombre, descripcion, fechaCreacion, fechaUltimaModificacion)
    }
  }

  def findById(id: Long): Option[Rol] = {
    val selectQuery = s"SELECT * FROM $tableName WHERE id = {id}"
    db.withConnection { implicit connection =>
      SQL(selectQuery).on('id -> id).as(parser.singleOpt)
    }
  }

  def findByUsuario(idUsuario: Long): List[Rol] = {
    val selectQuery = s"SELECT i.* FROM $tableName r " +
      s"JOIN Usuario_Rol ur ON ur.id_rol = r.id " +
      s"WHERE id_usuario = {idUsuario}"
    db.withConnection { implicit connection =>
      SQL(selectQuery).on('idUsuario -> idUsuario).as(parser.*)
    }
  }
} 

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

import javax.inject.Inject

import anorm.{RowParser, ~}
import anorm.SqlParser._
import domain.EstadoItemDePedido.EstadoItemDePedido
import domain.Status.Status
import org.joda.time.DateTime
import play.api.db.Database

case class ItemDePedido(
  id: Long,
  pedido: Pedido,
  status: Status,
  estado: EstadoItemDePedido,
  cantidad: Int,
  comentario: String,
  abonado: Boolean,
  fechaUltimaModificacion: DateTime,
  idUsuarioUltimaModificacion: Long,
  itemDeMenu: ItemDeMenu)

class ItemDePedidoRepo @Inject()(db: Database, pedidoRepo: PedidoRepo, itemDeMenuRepo: ItemDeMenuRepo) {

  val tableName = "ItemDePedido"

  val parser: RowParser[ItemDePedido] = {
      long("id") ~
      long("id_pedido") ~
      int("status") ~
      int("estado") ~
      int("cantidad") ~
      str("comentario") ~
      bool("abonado")~
      get[DateTime]("fecha_ultima_modificacion") ~
      long("id_usuario_ultima_modificacion") ~
      long("id_item_de_menu")  map {
      case id ~ idPedido ~ status ~ estado ~ cantidad ~ comentario ~ abonado ~ fechaUltimaModificacion ~ idUsuarioUltimaModificacion ~ idItemDeMenu =>
        ItemDePedido(id, pedidoRepo.findById(idPedido).getOrElse(throw new RuntimeException("pedido.reference.not.found")), Status(status),
                     EstadoItemDePedido(estado), cantidad, comentario, abonado, fechaUltimaModificacion, idUsuarioUltimaModificacion,
                     itemDeMenuRepo.findById(idItemDeMenu).getOrElse(throw new RuntimeException("itemDeMenu.reference.not.found")))
    }
  }

} 

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

import javax.inject.Inject

import anorm._
import anorm.SqlParser._
import domain.EstadoPedido.EstadoPedido
import domain.Status.Status
import org.joda.time.DateTime
import play.api.db.Database

case class Pedido(
  id: Long,
  status: Status,
  menu: Menu,
  estado: EstadoPedido,
  comentario: String = "",
  abonado: Boolean,
  fechaCreacion: DateTime,
  fechaUltimaModificacion: DateTime,
  idUsuario: Long,
  idUsuarioUltimaModificacion: Long,
  items: Seq[ItemDePedido] = Nil,
  mesa: String = "")

class PedidoRepo @Inject()(db: Database, menuRepo: MenuRepo) {

  val tableName = "Pedido"

  val parser: RowParser[Pedido] = {
      long("id") ~
      int("status") ~
      long("id_menu") ~
      int("estado") ~
      str("comentario") ~
      bool("abonado") ~
      get[DateTime]("fecha_creacion") ~
      get[DateTime]("fecha_ultima_modificacion") ~
      long("id_usuario") ~
      long("id_usuario_ultima_modificacion")  map {
      case id ~ status ~ idMenu ~ estado ~ comentario ~ abonado ~ fechaCreacion ~ fechaUltimaModificacion ~ idUsuario ~ idUsuarioUltimaModificacion =>
        Pedido(id, Status(status), menuRepo.findById(idMenu).getOrElse(throw new RuntimeException("menu reference not found")),
               EstadoPedido(estado), comentario, abonado, fechaCreacion, fechaUltimaModificacion, idUsuario, idUsuarioUltimaModificacion)
    }
  }

  def findById(id: Long): Option[Pedido] = {
    val selectQuery = s"SELECT * FROM $tableName WHERE id = {id}"
    db.withConnection { implicit connection =>
      SQL(selectQuery).on('id -> id).as(parser.singleOpt)
    }
  }
} 

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

import javax.inject._
import com.google.inject.ImplementedBy
import play.api.db.Database
import models.Archivo

@ImplementedBy(classOf[DoobieStore])
trait FileStore {
  
  def store(name: String, bytes: Array[Byte], hash: String): Long

  def archivos(): List[Archivo]
}

class DoobieStore @Inject() (db: Database) extends FileStore {
  import doobie.imports._
  import cats._, cats.data._, cats.implicits._

  val xa = DataSourceTransactor[IOLite](db.dataSource)

  def insert(name: String, bytes: Array[Byte], hash: String): ConnectionIO[Long] =
    sql"""insert into file_info(name, hash, data)
          values($name, $hash, $bytes)""".update.withUniqueGeneratedKeys("id")

  def all: ConnectionIO[List[Archivo]] = {
    sql"""select id, name, hash, fecha from file_info""".query[Archivo].list
  }

  def store(name: String, bytes: Array[Byte], hash: String): Long = {
    insert(name, bytes, hash).transact(xa).unsafePerformIO
  }

  def archivos() = all.transact(xa).unsafePerformIO
} 

//设置package包名称以及导入依赖的类
package com.github.mszarlinski.playframework.petclinic.owners.port.adapters.anorm

import javax.inject.{Singleton, Inject}

import anorm.SqlParser._
import anorm._
import com.github.mszarlinski.playframework.petclinic.owners.domain.{Owner, OwnerRepository}
import play.api.db.Database


@Singleton
class OwnerSqlRepository @Inject()(db: Database) extends OwnerRepository {

  val ownerParser: RowParser[Owner] = {
    long("owners.id") ~
      str("owners.first_name") ~
      str("owners.last_name") map {
      case id ~ firstName ~ lastName =>
        Owner(id, firstName, lastName)
    }
  }

  override def findAll(): List[Owner] = {
    db.withConnection { implicit connection =>
      SQL(
        """
        SELECT o.id, o.first_name, o.last_name, o.address, o.city, o.telephone
        FROM owners o
        ORDER BY o.last_name, o.first_name
        """)
        .as(ownerParser *)
    }
  }

  override def findById(id: Long): Option[Owner] = ???

  override def save(owner: Owner): Option[Owner] = {
    db.withConnection { implicit connection =>
      SQL(
        """
        INSERT INTO owners(first_name, last_name)
        VALUES ({firstName}, {lastName})
        """).on('firstName -> owner.firstName, 'lastName -> owner.lastName)
        .executeInsert()
        .map(genId => owner.copy(id = genId))
    }
  }
} 

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

import javax.inject.{Inject, Singleton}

import play.api.db.{Database, NamedDatabase}
import scalikejdbc._

@Singleton
class MyDaoImpl @Inject() (@NamedDatabase("mySample") db: Database) extends MyDao {

  def exec(): Option[String] = {
    using(db.getConnection(autocommit = false)) { conn =>
      val ss = DB(conn).readOnly { implicit session =>
        sql"select 2".map(rs => rs.long(1)).single.apply()
      }
      ss.map(_.toString)
    }
  }
} 

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

import javax.inject.Inject

import akka.actor.ActorSystem
import org.jooq.impl.DSL
import org.jooq.{SQLDialect, DSLContext}
import play.api.db.Database

import scala.concurrent.{Future, ExecutionContext}

class DB @Inject()(db: Database, system: ActorSystem) {
  val databaseContext: ExecutionContext = system.dispatchers.lookup("contexts.database")

  def query[A](block: DSLContext => A): Future[A] = Future {
    db.withConnection { connection =>
      val sql = DSL.using(connection, SQLDialect.POSTGRES_9_4)
      block(sql)
    }
  }(databaseContext)

  def withTransaction[A](block: DSLContext => A): Future[A] = Future {
    db.withTransaction { connection =>
      val sql = DSL.using(connection, SQLDialect.POSTGRES_9_4)
      block(sql)
    }
  }(databaseContext)
} 

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

//import anorm._
import javax.inject.Singleton

import anorm.{SQL, SqlParser}
import anorm.{Macro, RowParser}
import com.google.inject.Inject
import play.api.db.Database



@Singleton
class StudentInfo @Inject()(db: Database) {

  val parser: RowParser[Students] = Macro.namedParser[Students]

  def create(students: Students) {
    db.withConnection { implicit connection =>
      SQL(
        """
        INSERT INTO students (name, stream, section, email)
        VALUES ({name}, {stream}, {section}, {email})
        """
      ).on(
        'name -> students.name,
        'stream -> students.stream,
        'section -> students.section,
        'email -> students.email
      )
    }
  }


  def findByName(name: String): Option[Students] = {
    db.withConnection { implicit connection =>
      SQL("select * from students where name = {name}").on('name -> name).as(parser.singleOpt)
    }
  }

  def findAll(): List[Students] = {
    db.withConnection { implicit connection =>
      SQL("select * from students").as(parser *)
    }
  }


}

case class Students
(name: String,
 stream: String,
 section: String,
 email: String
) 

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

import org.scalatest.concurrent.ScalaFutures
import org.scalatest.time.{Seconds, Span}
import org.scalatest.{MustMatchers, WordSpec}
import play.api.db.evolutions.Evolutions
import play.api.db.{Database, Databases}

class TodoRepositoryTest extends WordSpec
  with ScalaFutures
  with MustMatchers {

  def withMyDatabase[T](block: Database => T) = {
    Databases.withInMemory(
      name = "default",
      urlOptions = Map(
        "MODE" -> "PostgreSQL"
      ),
      config = Map(
        "logStatements" -> true
      )
    )(block)
  }

  def withRepo[T](block: TodoRepository => T) = withMyDatabase { db =>
    Evolutions.applyEvolutions(db)
    block(new TodoRepository(db))
  }

  "TodoRepository#getAllTodos" when {
    "has no TODOs" should {
      "return empty" in withRepo { repo =>
        whenReady(repo.getAllTodos, timeout(Span(6, Seconds))) { result =>
          result mustBe empty
        }
      }
    }
  }
} 

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

import java.sql.Connection
import javax.inject.{Inject, Singleton}

import akka.actor.ActorSystem
import play.api.db.Database

import scala.concurrent.{ExecutionContext, Future, Promise}
import scala.util.control.ControlThrowable
import scala.util.{Failure, Success}

@Singleton
class DB @Inject()(
                    db: Database,
                    system: ActorSystem
                  ) {

  private implicit val ec: ExecutionContext = system.dispatchers.lookup("contexts.database")

  def withConnection[A](block: Connection => A): Future[A] = {
    Future(db.withConnection(block))(ec)
  }

  def withTransaction[A](block: Connection => A): Future[A] = {
    Future(db.withTransaction(block))(ec)
  }

  def withTransactionF[A](block: Connection => Future[A]): Future[A] = {
    val p = Promise[A]
    val connection = db.getConnection(false)

    block(connection) onComplete {
      case Success(r) =>
        connection.commit()
        connection.close()
        p.success(r)

      case Failure(e: ControlThrowable) =>
        connection.commit()
        connection.close()
        p.failure(e)

      case Failure(e) =>
        connection.rollback()
        connection.close()
        p.failure(e)
    }

    p.future
  }

} 

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

import play.api.db.{Database, Databases}

import scala.util.Properties


object DatabaseConfig {
  val url = Properties.envOrElse("PLAY_PRODUCT_DB_TEST_URL", "jdbc:postgresql://localhost:15432/product-test")
  val username = Properties.envOrElse("PLAY_PRODUCT_DB_TEST_USERNAME", "root")
  val password = Properties.envOrElse("PLAY_PRODUCT_DB_TEST_PASSWORD", "localhost")

  val database = Databases(
    driver = "org.postgresql.Driver",
    url = url,
    name = "Product-Test",
    config = Map(
      "username" -> username,
      "password" -> password,
      "logStatements" -> true
    )
  )

  def withDatabase[T](block: Database => T): T = {
    Databases.withDatabase(
      driver = "org.postgresql.Driver",
      url = url,
      name = "Product-Test",
      config = Map(
        "username" -> username,
        "password" -> password,
        "logStatements" -> true
      )
    )(block)
  }
} 

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

import javax.inject.Inject

import play.api.mvc._
import databaseModels.WatersModel
import jsonModels.WaterData
import play.api.db.Database
import play.api.libs.json.Json
import play.api.mvc.{Action, Controller}


class GetDataController @Inject()(db: Database) extends Controller {
    val water: WatersModel = new WatersModel(db)

    //getAll???
    def getAll() = Action { implicit request =>
        def handle: Result = {
            val waterData = water.getAll()
            Ok(Json.toJson(waterData))
        }

        handle
    }

    //get???
    def get(id: String) = Action { implicit request =>
        def handle: Result = {
            val waterData = water.getFromId(id)
            if(waterData == null) Ok(Json.toJson(new WaterData(null, null, -1, -1, -1)))
            else Ok(Json.toJson(waterData))
        }

        handle
    }
} 

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

import daos.SubjectDao
import daos.doobie.DoobieImports._
import doobie.imports._
import doobie.util.transactor.DataSourceTransactor
import javax.inject.Inject
import models.{ Role, User }
import models.security.MySubject
import org.joda.time.Instant
import play.api.db.Database

class SubjectDaoDoobie @Inject() (
    db: Database
) extends SubjectDao {
  import SubjectDaoDoobie.subjectQuery

  private[this] implicit def xa() = DataSourceTransactor[IOLite](db.dataSource)

  def subjectByIdentifier(identifier: String): Option[MySubject] = {
    subjectQuery(identifier).option.transact(xa()).unsafePerformIO map {
      case (id, login, connected, lastAct, rid, rname) ?
        MySubject(
          User(id, login, "", 0, rid, connected, lastAct),
          Role(rid, rname)
        )
    }
  }
}

object SubjectDaoDoobie {

  def subjectQuery(login: String): Query0[(Long, String, Boolean, Option[Instant], Int, String)] =
    sql"""
      Select u.id, u.login, u.connected, u.last_activity, r.id, r.name
      from users u join roles r on u.role_id = r.id
      where u.login = $login""".query[(Long, String, Boolean, Option[Instant], Int, String)]
} 

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

import doobie.imports._
import javax.inject.Inject
import play.api.Logger
import play.api.db.Database


class EventDaoDoobie @Inject() (db: Database) extends EventDao {
  import daos.doobie.DoobieTransactor.transactor
  import EventDaoDoobie.qWrite

  def write(description: String): Unit = {
    Logger.debug(description)
    qWrite(description).run.transact(transactor(db)).unsafePerformIO
  }
}

object EventDaoDoobie {
  def qWrite(description: String): Update0 =
    sql"""insert into events(description, moment)
          values($description, now())""".update
}