Scala TableQuery类代码示例

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

import lunatech.lunchplanner.persistence.{ DishTable, MenuDishTable, MenuPerDayPerPersonTable, MenuPerDayTable, MenuTable, UserProfileTable, UserTable }
import slick.driver.PostgresDriver.api._
import slick.lifted.TableQuery

import scala.concurrent._
import scala.concurrent.duration._

trait TestDatabaseProvider {
  self: DBConnectionProvider =>

  val defaultTimeout = 10.seconds

  val userProfileTable: TableQuery[UserProfileTable] = TableQuery[UserProfileTable]
  val userTable: TableQuery[UserTable] = TableQuery[UserTable]
  val dishTable: TableQuery[DishTable] = TableQuery[DishTable]
  val menuTable: TableQuery[MenuTable] = TableQuery[MenuTable]
  val menuDishTable: TableQuery[MenuDishTable] = TableQuery[MenuDishTable]
  val menuPerDayTable: TableQuery[MenuPerDayTable] = TableQuery[MenuPerDayTable]
  val menuPerDayPerPersonTable: TableQuery[MenuPerDayPerPersonTable] = TableQuery[MenuPerDayPerPersonTable]

  def cleanDatabase() = {
    Await.result(db.run(menuPerDayPerPersonTable.delete), defaultTimeout)
    Await.result(db.run(menuPerDayTable.delete), defaultTimeout)
    Await.result(db.run(menuDishTable.delete), defaultTimeout)
    Await.result(db.run(dishTable.delete), defaultTimeout)
    Await.result(db.run(menuTable.delete), defaultTimeout)
    Await.result(db.run(userProfileTable.delete), defaultTimeout)
    Await.result(db.run(userTable.delete), defaultTimeout)
  }

} 

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

import models.{Category, CategoryTable, DatabaseConnection}
import slick.driver.PostgresDriver.api._
import slick.lifted.TableQuery

import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.Future


object CategoryService {

  val db = DatabaseConnection.connection
  val categories = TableQuery[CategoryTable]

  def add(category: Category): Future[String] = {
    db.run(categories += category)
      .map(res => "Category successfully added")
      .recover {
        case ex: Exception => ex.getCause.getMessage
      }
  }

  def saveOrUpdate(category: Category): Future[String] = {
    if (category.id == 0) {
      add(category)
    } else {
      db.run(categories
        .filter(_.id === category.id)
        .update(category))
        .map(res => s"Category $category.id successfully updated")
        .recover {
          case ex: Exception => ex.getCause.getMessage
        }
    }
  }

  def delete(id: Long): Future[Int] = {
    db.run(categories
      .filter(_.id === id)
      .delete)
  }

  def get(id: Long): Future[Option[Category]] = {
    db.run(categories
      .filter(_.id === id)
      .result
      .headOption)
  }

  def listAll: Future[Seq[Category]] = {
    db.run(categories
      .sortBy(_.id)
      .result)
  }
} 

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

import models.DriverModel
import java.sql.Timestamp
import slick.lifted.TableQuery
import database.MyPostgresDriver.api._

class DriverStorage(tag: Tag) extends Table[DriverModel](tag, "drivers") {
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def vehicle = column[String]("vehicle")
  def createdAt = column[Timestamp]("created_at")
  def updatedAt = column[Timestamp]("updated_at")

  override def * =
    (id, name, vehicle, createdAt, updatedAt) <> (DriverModel.tupled, DriverModel.unapply)
}

object DriverStorage {
  val tableQuery = TableQuery[DriverStorage]
} 

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

import models.DriverLocationModel
import java.sql.Timestamp
import com.vividsolutions.jts.geom.Geometry
import slick.lifted.{ProvenShape, TableQuery}
import database.MyPostgresDriver.api._
import database.MyPostgresDriver.api.PgArrayPositionedResult


class DriverLocationStorage(tag: Tag) extends Table[DriverLocationModel](tag, "drivers_location") {
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def driverId = column[Long]("driver_id")
  def location = column[Geometry]("location")
  def createdAt = column[Timestamp]("created_at")
  def updatedAt = column[Timestamp]("updated_at")

  override def * =
    (id, driverId, location, createdAt, updatedAt) <> (DriverLocationModel.tupled, DriverLocationModel.unapply)
}

object DriverLocationStorage {
  val tableQuery = TableQuery[DriverLocationStorage]
} 

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

import db.DatabaseConfigProvider
import db.currentJdbcProfile.profile
import db.currentJdbcProfile.api._

import shared.models.Entity
import shared.DefaultThreadPool._

import slick.lifted.TableQuery

import scala.concurrent.Future
import scala.reflect.ClassTag

abstract class DatabaseService[E <: Entity, K <: profile.Table[E]: ClassTag](cons: Tag => K) { this: DatabaseConfigProvider[profile.type] =>

  val q: TableQuery[K] = TableQuery[K](cons)
  val tableName: String = q.baseTableRow.tableName

  def all(): Future[Seq[E]] = db.run(q.result)
  def insert(e: E): Future[Unit] = db.run(q += e).map(_ => ())
  def findById(id: Long): Future[Option[E]] =
    db.run(findByIdQuery(id).result.headOption)
  def update(id: Long, entity: E): Future[Unit] =
    db.run(findByIdQuery(id).update(entity)).map(_ => ())
  def deleteAll(): Future[Unit] =
    db.run(q.delete).map(_ => ())

  def findByIdQuery(id: Long): Query[K, E, Seq] =
    q.filter(m => m.getClass.getMethod("id").invoke(m).asInstanceOf[Rep[Long]] === id)
} 

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

import cats.data.Reader
import slick.jdbc.JdbcBackend.Database
import slick.lifted.TableQuery
import slick.driver.H2Driver.api._
import io.scalac.wtf.domain.tables.Users

trait DatabaseDependancy {

  val db = Database.forConfig("h2mem1")

  //Used for showcase only, the table should already exist in a realistic scenario
  def createSchemaWork = Reader((config: Config) =>
    {
      implicit val ec = config.ec
      for {
        _ <- TableQuery[Users].schema.create
      } yield ()
    }
  )
} 

//设置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 com.grimey.staticpage

import java.time.LocalDateTime

import slick.jdbc.JdbcBackend
import slick.driver.MySQLDriver.api._
import slick.lifted.TableQuery

import scala.concurrent.Future
import scala.concurrent.ExecutionContext.Implicits.global

private[staticpage] object StaticPagesDAO extends TableQuery(new StaticPagesTable(_)) {
  def edit(htmlContent: String, pageType: String, db: JdbcBackend#DatabaseDef): Future[StaticPage] = {
    // Todo: Don't forget to update the updated field.
    ???
  }

  def getAllStaticPages(db: JdbcBackend#DatabaseDef): Future[Seq[StaticPage]] = db.run(this.to[Seq].result)

  def find(pageType: String, db: JdbcBackend#DatabaseDef): Future[Option[StaticPage]] = {
    db.run(this.filter(_.pageType === pageType).result).map(_.headOption)
  }

  def create(htmlContent: String, pageType: String, db: JdbcBackend#DatabaseDef,
             created: LocalDateTime = LocalDateTime.now(), updated: LocalDateTime = LocalDateTime.now()): Future[StaticPage] = {
    db.run(this returning this.map(_.id)
      into ((staticPage, id) => staticPage.copy(id - id)) += StaticPage(0, htmlContent, pageType, created, updated))
  }

  def delete(pageType: String, db: JdbcBackend#DatabaseDef): Future[Int] = {
    db.run(this.filter(_.pageType === pageType).delete)
  }
} 

//设置package包名称以及导入依赖的类
package com.snapswap.db.helpers.runner

import com.opentable.db.postgres.embedded.EmbeddedPostgres
import com.snapswap.db.errors.{EntityNotFound, InternalDataError}
import org.scalatest.{AsyncWordSpec, Matchers, OneInstancePerTest}
//import slick.jdbc.PostgresProfile.api._
import com.snapswap.db.driver.ExtendedPostgresProfile.api._
import slick.lifted.TableQuery

class DbRunHelperSpec
  extends AsyncWordSpec
    with Matchers
    with OneInstancePerTest {

  import runSafe._

  val epg: EmbeddedPostgres = EmbeddedPostgres.start
  val db: Database = Database.forDataSource(epg.getPostgresDatabase, None)
  val tbl: TableQuery[TestTable] = TableQuery[TestTable]

  "SlickRunWrapper" should {
    "replace known errors by appropriate DataError" in {
      val action = for {
        _ <- db.runSafe(tbl.schema.create)
        result <- db.runSafe(sql"""select "IntColumn" from "TestTable" """.as[Int].head)
      } yield result

      action.failed.map { r =>
        r shouldBe a[EntityNotFound]
      }
    }
    "replace unknown errors by InternalDataError with original error description" in {
      val action = for {
        _ <- db.runSafe(tbl.schema.create)
        result <- db.runSafe(DBIO.seq(sql"""select IntColumn from NotExistingTable""".as[Int]).map(_ => ()))
      } yield result

      action.failed map { result =>
        result shouldBe a[InternalDataError]
        result.getMessage should startWith("ERROR: relation \"notexistingtable\" does not exist\n  Position: 23")
      }
    }
    "when success - return Future result" in {
      val action = for {
        _ <- db.runSafe(tbl.schema.create)
        result <- db.runSafe((tbl += 1).andThen(tbl.map(_.IntColumn).result))
      } yield result

      action.map { result =>
        result should equal(Seq(1))
      }
    }
  }

  class TestTable(tag: Tag) extends Table[Int](tag, "TestTable") {
    def IntColumn = column[Int]("IntColumn")

    def * = IntColumn
  }

} 

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

import akka.actor.{Actor, ActorLogging}
import com.example.MySQLserver
import com.example.actors.TimetableActor.DaoTimetableQueryMessage
import com.example.tables.TrainTimetableT
import com.example.ticketHub.DaoTimetableResponseMessage
import slick.driver.MySQLDriver.api._
import slick.lifted.TableQuery

import scala.concurrent.Future

class TimetableDao extends Actor with ActorLogging{

  val timetables = TableQuery[TrainTimetableT]

  override def receive = {
    case DaoTimetableQueryMessage(ctx,trainRef) =>
      log.debug("DAO - get data for trainRef : {}", trainRef)
      val timetableQuery = timetables.filter(_.trainRef==trainRef).map(timetable => (timetable.time,timetable.stop))
      val queryAction = timetableQuery.result
      val matchedResults:Future[Seq[(String,String)]] = MySQLserver.db.run(queryAction)
      sender() ! DaoTimetableResponseMessage(ctx,matchedResults)
  }

} 

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

import java.sql.Timestamp
import java.util.{ Date, UUID }
import javax.inject.Inject

import models.{ Prediction, TaggingImage }
import models.tables.PredictionTable
import play.api.db.slick.DatabaseConfigProvider
import slick.backend.DatabaseConfig
import slick.driver.JdbcProfile
import slick.jdbc.JdbcBackend
import slick.lifted.TableQuery

import scala.concurrent.Future


class PredictionDAOImpl @Inject() (protected val dbConfigProvider: DatabaseConfigProvider) extends PredictionDAO {

  val dbConfig: DatabaseConfig[JdbcProfile] = dbConfigProvider.get[JdbcProfile]
  val db: JdbcBackend#DatabaseDef = dbConfig.db

  import dbConfig.driver.api._
  import PredictionDAOImpl._
  import play.api.libs.concurrent.Execution.Implicits.defaultContext
  override def create(predictions: Seq[Prediction]): Future[Seq[Prediction]] = {
    val query = predictionsTable.returning(predictionsTable.map(_.predictionId)).into((prediction, id) => prediction.copy(predictionId = id)) ++= predictions
    db.run(query).map(_.seq)
  }

  override def getPredictions(images: Seq[UUID]): Future[Seq[Prediction]] = {
    val query = predictionsTable.filter(_.imageId inSet images).result
    db.run(query)
  }
}

object PredictionDAOImpl {
  val predictionsTable = TableQuery[PredictionTable]
} 

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

import play.api.db.slick.DatabaseConfigProvider
import slick.driver.JdbcProfile
import play.api.Play
import slick.lifted.TableQuery
import mode.RestaurantTableDef
import mode.CompleteRestaurant
import scala.concurrent.Future
import slick.driver.PostgresDriver.api._
import scala.concurrent.ExecutionContext.Implicits.global
import mode.Restaurant


object Restaurants {
  val dbConfig=DatabaseConfigProvider.get[JdbcProfile](Play.current)
  val restaurants=TableQuery[RestaurantTableDef]
  def list:Future[Seq[CompleteRestaurant]]={
    dbConfig.db.run(restaurants.result)
  }
  def getById(id:Long):Future[Option[CompleteRestaurant]]={
    dbConfig.db.run(restaurants.filter(_.id===id).result.headOption)
  }
  def save(restaurant:CompleteRestaurant):Future[String]={
    dbConfig.db.run(restaurants+=restaurant).map(res => "Restaurant saved").recover{
      case ex: Exception => ex.getCause.getMessage
    }
  }
  def update(restaurant:CompleteRestaurant):Future[Int]={
    dbConfig.db.run(restaurants.filter(_.id===restaurant.id).update(restaurant))
  }
  def delete(id:Long):Future[Int]={
    dbConfig.db.run(restaurants.filter(_.id===id).delete)
  }
} 

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

import TableDef.LocationTableDef
import models.SearchLocation
import play.api.Play
import play.api.db.slick.DatabaseConfigProvider
import slick.driver.JdbcProfile
//import slick.driver.MySQLDriver.api._
import slick.driver.PostgresDriver.api._
import slick.lifted.TableQuery
import scala.concurrent.Future


object LocationDao {
    // Getting the DB Config from application.conf
    val dbConfig = DatabaseConfigProvider.get[JdbcProfile](Play.current)

    // Getting the Location object mapped to table
    val locations = TableQuery[LocationTableDef]

    val getListofPlaces: Future[Seq[SearchLocation]] = {
      dbConfig.db.run(locations.result)
    }

    val getCount: Future[Int] = dbConfig.db.run(locations.length.result)

    def searchMulti(placeQuery: String) : Future[Seq[SearchLocation]] = {
        dbConfig.db.run(locations.
          filter(x => (x.category.toLowerCase.indexOf(placeQuery.toLowerCase) >= 0)
                         || (x.locName.toLowerCase.indexOf(placeQuery.toLowerCase) >= 0))
                .result)
    }
} 

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

import com.stulsoft.scs.server.data.{DataTable, TtlTable}
import com.typesafe.scalalogging.LazyLogging
import slick.jdbc.H2Profile.api._
import slick.lifted.TableQuery

import scala.concurrent.Await
import scala.concurrent.duration._


package object server extends LazyLogging {
  val dataTable: TableQuery[DataTable] = TableQuery[DataTable]
  val ttlTable: TableQuery[TtlTable] = TableQuery[TtlTable]
  val db: Database = Database.forConfig("h2mem1")
  private val setup = DBIO.seq(
    (dataTable.schema ++ ttlTable.schema).create
  )

  logger.info("Database initialization started")
  Await.result(db.run(setup), 2.seconds)
  logger.info("Database initialization completed")

} 

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

import slick.lifted.{ProvenShape, ForeignKeyQuery}
import slick.lifted.TableQuery
import slick.driver.PostgresDriver.api._


class Suppliers(tag: Tag) 
   extends Table[(Int, String, String, String, String, String)] (tag, "SUPPLIERS") {

	def id:Rep[Int] = column[Int]("SUP_ID", O.PrimaryKey)
			def name = column[String]("SUP_NAME")
			def street = column[String]("STREET")
			def city = column[String]("CITY")
			def state = column[String]("STATE")
			def zip = column[String]("ZIP")
			def * = (id, name, street, city, state, zip)
}



class Coffees(tag: Tag) extends Table[(String, Int, Double, Int, Int)](tag, "COFFEES") {
  def name = column[String]("COF_NAME", O.PrimaryKey)
  def supID = column[Int]("SUP_ID")
  def price = column[Double]("PRICE")
  def sales = column[Int]("SALES")
  def total = column[Int]("TOTAL")
  def * = (name, supID, price, sales, total)
  
  // foreign key
  def supplier = foreignKey("SUP_FK", supID, TableQuery[Suppliers])(_.id)
}