Scala DB类代码示例

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

import java.util.Date

import com.fasterxml.jackson.core.JsonParseException
import play.api.Logger
import play.api.db.DB
import play.api.libs.json.{Json, JsObject}
import slick.driver.MySQLDriver.api._
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.duration._
import play.api.Play.current
object Slick extends Slick {
    def apply(name: String) = new Slick {
        override lazy val DBName = name
    }
}

trait Slick {
    protected lazy val DBName = "default"
    val duration = Duration(5, SECONDS)
    implicit def db = Database.forDataSource(DB.getDataSource(DBName))
}


trait ExtMapper {
    implicit val date2SqlDate = MappedColumnType.base[Date, java.sql.Timestamp](d => new java.sql.Timestamp(d.getTime), d => new java.util.Date(d.getTime))
    implicit val list2String = MappedColumnType.base[List[String], String](array => array.mkString(","), string => string.split(",").toList)
    implicit val jsonObjMapper = MappedColumnType.base[JsObject, String](json => json.toString(), s => try {
        Json.parse(s).as[JsObject]
    } catch {
        case e: JsonParseException => {
            Logger.error(s"JsObjectMapper Error [data:$s]", e)
            Json.obj()
        }
    })

} 

//设置package包名称以及导入依赖的类
import org.squeryl.adapters.{H2Adapter, MySQLAdapter, PostgreSqlAdapter}
import org.squeryl.internals.DatabaseAdapter
import org.squeryl.{Session, SessionFactory}
import play.api.db.DB
import play.api.{Application, GlobalSettings}

object Global extends GlobalSettings {

  override def onStart(app: Application) {
    SessionFactory.concreteFactory = app.configuration.getString("db.default.driver") match {
      case Some("org.h2.Driver") => Some(() => getSession(new H2Adapter, app))
      case Some("org.postgresql.Driver") => Some(() => getSession(new PostgreSqlAdapter, app))
      case Some("com.mysql.jdbc.Driver") => Some(() => getSession(new MySQLAdapter, app))
      case _ => sys.error("Database driver must be either org.h2.Driver or org.postgresql.Driver")
    }
  }

  def getSession(adapter:DatabaseAdapter, app: Application) = Session.create(DB.getConnection()(app), adapter)

} 

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


  def saveResults(tweets: List[Tweet]) = {
    for (tweet <- tweets) {
      println(f"${tweet.id}, ${tweet.username}, ${tweet.date}, ${tweet.text}")
      DB.withConnection { implicit connection =>
        try {
          val rowsChanged =
            SQL("INSERT INTO tweet VALUES (DEFAULT,{tweetID},{tweetUserName},{tweetTime},{tweetText})")
              .on('tweetID -> tweet.id.toString, 'tweetUserName -> tweet.username,
                'tweetTime -> tweet.date, 'tweetText -> tweet.text)
              .executeUpdate()
          println(f"Inserted Tweet:${tweet.id}%s into table.")
        } catch {
          case e: Exception => e.printStackTrace()
        }
      }
    }
    DB.withConnection { implicit connection =>
      val savedResults =
        SQL("SELECT count(id) as id FROM tweet").apply.head
      val resultsCount = savedResults[Int]("id")
      println(f"$resultsCount%d results saved.")
    }
  }

  def getSampleTweets(): List[TweetSet] = DB.withConnection { implicit connection =>
    val statuses: List[(String,String,String,String)] = {
      SQL("SELECT status_id as id, status_user as user, " +
        "status_time as time, status_text as text FROM tweet").as(tweetSetParser)
    }
    val tweets: List[TweetSet] = for ((id,user,time,text) <- statuses) yield new TweetSet(id.toLong,user,time,text)
    tweets
  }
} 

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

import play.api.Play.current
import play.api.db.DB
import play.api.libs.concurrent.Execution.Implicits._
import slick.driver.PostgresDriver.api._

import scala.concurrent.Future

trait DAOComponent {

  def insert(employee: Employee): Future[Int]
  def update(id: Long, employee: Employee): Future[Int]
  def delete(id: Long): Future[Int]
  def list(page: Int = 0, pageSize: Int = 10, orderBy: Int = 1, filter: String = "%"): Future[Page[Employee]]
  def listActive(page: Int = 0, pageSize: Int = 10, orderBy: Int = 1, filter: String = "%"): Future[Page[Employee]]
  def listArchived(page: Int = 0, pageSize: Int = 10, orderBy: Int = 1, filter: String = "%"): Future[Page[Employee]]
  def findById(id: Long): Future[Employee]
  def count: Future[Int]

}

object DAO extends DAOComponent {

  private val employees = TableQuery[Employees]

  private def db: Database = Database.forDataSource(DB.getDataSource())

  
  override def listArchived(page: Int = 0, pageSize: Int = 10, orderBy: Int = 1, filter: String = "%"): Future[Page[Employee]] = {
    try {
      val offset = pageSize * page
      val query =
        (for {
          employee <- employees if (employee.name.toLowerCase like filter.toLowerCase) && (employee.status like "Archived")
        } yield (employee)).drop(offset).take(pageSize)
      val totalRows = count(filter)
      val result = db.run(query.result)
      result flatMap (employees => totalRows map (rows => Page(employees, page, offset, rows)))
    } finally { db.close() }
  }
} 

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

import anorm._
import anorm.SqlParser._
import org.joda.time.DateTime
import play.api.db.DB
import play.api.Play.current

case class Client(id: Long, name: String, birth: java.util.Date, email: String)

object Client {
  val sql: SqlQuery = SQL("SELECT * from Client ORDER BY name asc")


  val defaultParser = {
    int("id") ~
      str("name") ~
      get[java.util.Date]("birth") ~
      str("email") map {
      case id ~ name ~ birth ~ email => Client(id, name, birth, email)
    }
  }

  def all(): List[Client] = DB.withConnection { implicit c =>
    SQL("SELECT * FROM Client ORDER BY name").as(defaultParser *)
  }

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

  def create(client: Client) = {
    DB.withConnection { implicit c =>
      SQL("INSERT INTO Client (name,birth,email) VALUES ({name},{birth},{email});")
        .on( 'name -> client.name, 'birth -> client.birth, 'email -> client.email).executeInsert()
    }
  }

  def update(id: Long, name: String, birth: java.util.Date, email: String) = {
    DB.withConnection { implicit c =>
      SQL("UPDATE Client SET name = {name}, birth = {birth}, email = {email} WHERE id = {id}")
        .on('name -> name, 'birth -> birth, 'email -> email, 'id -> id).executeUpdate()
    }
  }

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


} 

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

import play.api.Logger
import play.api.Play.current
import anorm.Row
import com.kindone.infinitewall.data.{ Sheet, Wall }
import play.api.db.DB
import anorm._


case class SheetLog(sheetId: Long, logId: Long, actionType: Int, action: Option[String])

class SheetLogManager {
  def find(sheetId: Long)(implicit userId: Long) = DB.withConnection { implicit c =>
    SQL"""select log.sheet_id,log.log_id, log.action_type, log.action
          from sheet_logs as log, sheets_in_wall, walls_of_user
          where sheets_in_wall.sheet_id = $sheetId
          and walls_of_user.user_id = $userId
          and sheets_in_wall.wall_id = walls_of_user.wall_id
          and walls_of_user.wall_id = sheets_in_wall.wall_id
          and log.sheet_id = $sheetId""".map {
      case Row(sheetId: Long, logId: Long,
        actionType: Int, action: Option[String]) => SheetLog(sheetId, logId, actionType, action)
    }.list
  }

  def create(sheetLog: SheetLog)(implicit userId: Long): Long = {
    // returns id

    DB.withTransaction { implicit c =>
      val maxId =
        SQL"""select COALESCE(MAX(log_id),0) from sheet_logs
             where sheet_id = ${sheetLog.sheetId}""".map {
          case Row(id: Long) => id
        }.single()

      SQL"""insert into sheet_logs(sheet_id, log_id, action_type, action)
           VALUES(${sheetLog.sheetId}, ${maxId}+1, ${sheetLog.actionType}, ${sheetLog.action})
        """.executeInsert()

      maxId
    }
    //    Logger.info(find(sheetLog.sheetId).toString)
  }

} 

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

import play.api.Logger
import play.api.Play.current
import anorm.Row
import com.kindone.infinitewall.data.{ Wall }
import play.api.db.DB
import anorm._


case class WallLog(wallId: Long, logId: Long, actionType: Int, action: Option[String])

class WallLogManager {
  def find(wallId: Long)(implicit userId: Long) = DB.withConnection { implicit c =>
    SQL"""select log.wall_id,log.log_id, log.action_type, log.action
          from wall_logs as log, walls_of_user
          where walls_of_user.user_id = $userId
          and walls_of_user.wall_id = log.wall_id
          and log.wall_id = $wallId""".map {
      case Row(wallId: Long, logId: Long,
        actionType: Int, action: Option[String]) => WallLog(wallId, logId, actionType, action)
    }.list
  }

  def create(wallLog: WallLog)(implicit userId: Long): Long = {
    // returns id

    DB.withTransaction { implicit c =>
      val maxId =
        SQL"""select COALESCE(MAX(log_id),0) from wall_logs
             where wall_id = ${wallLog.wallId}""".map {
          case Row(id: Long) => id
        }.single()

      SQL"""insert into wall_logs(wall_id, log_id, action_type, action)
           VALUES(${wallLog.wallId}, ${maxId}+1, ${wallLog.actionType}, ${wallLog.action})
        """.executeInsert()

      maxId
    }
    //    Logger.info(find(wallLog.wallId).toString)
  }

} 

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

import domain.{Email, PasswordHash, Person}
import anorm._
import play.api.db.DB
import play.api.Play.current
/**
 * Created by waisy on 14/12/2015.
 */
class UserRepository {
  def addPerson(person: Person, passwordHash: PasswordHash, registerIP: String): Unit = {
    DB.withConnection(implicit c =>
      SQL("INSERT INTO users values ({email}, {displayName}, {passHash}, {hashAlgo})")
        .on(
          "email" -> person.email.email,
          "displayName" -> person.displayName,
          "passHash" -> passwordHash.hash,
          "hashAlgo" -> passwordHash.hashAlgo
        ).executeInsert()
    )
  }

  def getPersonWithEmail(email: String): Option[Person] = {
    DB.withConnection(implicit c =>
      SQL("SELECT displayName FROM users where email = {email}")
        .on("email"->email)()
        .collectFirst {
        case Row(displayName: String) => Person(Email(email), displayName)
      }
    )
  }
} 

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

import anorm._
import anorm.SqlParser._
import play.api.db.DB
import  play.api.Play.current

case class Task(id: Long, label: String)

object Task {

  val task = {
    get[Long]("id") ~
      get[String]("label") map {
      case id~label => Task(id, label)
    }
  }

  def all(): List[Task] = DB.withConnection { implicit c =>
    SQL("select * from task").as(task *)
  }

  def create(label: String) {
    DB.withConnection { implicit c =>
      SQL("insert into task (label) values ({label})").on(
        'label -> label
      ).executeUpdate()
    }
  }

  def delete(id: Long) {
    DB.withConnection { implicit c =>
      SQL("delete from task where id = {id}").on(
        'id -> id
      ).executeUpdate()
    }
  }
} 

//设置package包名称以及导入依赖的类
import com.gabry.weixin.utils.{Common, QiniuFile}
import play.api._
import play.api.mvc._
import play.api.Logger
import play.api.db.DB
import play.api.Play.current
import scala.concurrent.Future
import play.api.libs.concurrent.Execution.Implicits.defaultContext
import anorm._
import scala.io.Source
object Global extends WithFilters() {

  
      val remoteDbFileScript = QiniuFile(Common.localConfig.getString("qiniu.dbfilepath"),Common.localConfig.getString("qiniu.dbfilename"))
      Source.fromFile("./DataBaseBackup.sql").getLines().foreach(println)
      //remoteDbFileScript.saveFile("./DataBaseBackup.sql")
      true
    }
    else false
    }
  }
  override def onStart(app:Application): Unit ={
    Common.logger.info("????")
    loadDB()
  }
  override def onStop(app:Application)={
    Common.logger.info("????")
    saveDB()
  }
} 

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

import anorm.SQL
import play.api.Play.current
import play.api.db.DB
import anorm.NamedParameter.symbol
import models.User



  def registeredUsers: List[User] = {
    DB.withConnection { implicit c =>
      val selectUsers = SQL("Select * from Users;")
      // Transform the resulting Stream[Row] to a List[(Long, String, String, BigDecimal, String)]
      val users = selectUsers().map(row => User(row[Long]("id"), row[String]("name"), row[String]("password"), row[BigDecimal]("distance"), row[Boolean]("admin"), row[Boolean]("active"))).toList
      users
    }
  }
}

object UserDao extends UserDaoT 

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

import anorm.SQL
import play.api.Play.current
import play.api.db.DB
import models.{AddItem, Item}



  def showActiveItem: List[Item] = {
    DB.withConnection { implicit c =>
      val selectItems = SQL("Select Items.id, Items.name, Items.cat_id, Items.price, Items.active, Cat.id, Cat.name From Items, Cat where Items.cat_id = Cat.id AND active = true Order by Cat.name DESC;")
      // Transform the resulting Stream[Row] to a List[(String,String)]
      val items = selectItems().map(row => Item(row[Long]("Items.id"), row[String]("Cat.name"), row[String]("Items.name"), row[BigDecimal]("Items.price"), row[Boolean]("Items.active"))).toList
      items
    }
  }
}

object ItemDao extends ItemDaoT 

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

import anorm.SQL
import play.api.Play.current
import play.api.db.DB
import models.Category



  def showCategory: List[Category] = {
    DB.withConnection { implicit c =>
      val selectCategory = SQL("Select * From Cat;")
      // Transform the resulting Stream[Row] to a List[(String,String)]
      val category = selectCategory().map(row => Category(row[Long]("id"), row[String]("name"))).toList
      category
    }
  }
}

object CategoryDao extends CategoryDaoT 

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

import anorm.SQL
import play.api.Play.current
import play.api.db.DB
import models.{Extras, Item}



  def showExtra: List[Extras] = {
    DB.withConnection { implicit c =>
      val selectExtras = SQL("Select * From Extras;")
      // Transform the resulting Stream[Row] to a List[(String,String)]
      val extras = selectExtras().map(row => Extras(row[Long]("id"), row[String]("name"), row[BigDecimal]("price"))).toList
      extras
    }
  }
}

object ExtrasDao extends ExtrasDaoT 

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

import anorm._
import play.api.db.DB
import play.api.Play.current

case class ContactModel (id: Long, name: String, emailAddress: String)


object ContactModel {
  
  def all = {
    DB.withConnection { implicit connection =>
      
      SQL("SELECT * FROM contacts")().map { row => 
        ContactModel(
            id = row[Long]("id"),
            name = row[String]("name"),
            emailAddress = row[String]("emailAddress")
            )
        }.toList
      
    }
  }
  
  
  def create(contact: ContactModel) {
    DB.withConnection { implicit connection =>
      SQL("INSERT INTO contacts(name, emailAddres) VALUES ({name}, {emailAddress})")(
        "name" -> contact.name,
        "emailAddress" -> contact.emailAddress
    ).execute()
  }
  }
  
  // validate data
  import play.api.data._
  import play.api.data.Forms._
  
  val form = Form(
      mapping(
            "id" -> ignored(0L),
            "name" -> nonEmptyText,
            "emailAdress" -> email
            )(ContactModel.apply)(ContactModel.unapply)
  )
  
}