slick使用普通的sql操作数据

1.无论使用slick的"集合"来操作数据还是使用sql来操作数据,最终还是构建DBIOAction,让后交给DB处理,类是FP中monid的action

需要注意的是run

Run an Action asynchronously and return the result as a Future

sqlu,sql,tsql的字面意思来构建一个DBAction

sqlu是用于单行的DML操作,而非结果集的操作

例如

    sqlu"insert into suppliers values(101, 'Acme, Inc.', '99 Market Street', 'Groundsville', 'CA', '95199')"

sql是用于结果集的

sql"""select c.name, s.name
      from coffees c, suppliers s
      where c.price < $price and s.id = c.sup_id""".as[(String, String)]

tsql是对类型进行检查的

例如上面的sql是没有进行类型检测的,他通过as将结果转化成元组

(

as是一个带implicit参数的方法特别是你在将查询结果转化成自定的类型是需要

如

    implicit val getCoffeeResult = GetResult(r => Coffee(r.<<, r.<<, r.<<, r.<<, r.<<))
    val table = "coffees"
    val name  = "Colombian"

    val query2:DBIO[Seq[Coffee]] = sql"""SELECT * FROM #$table where name = $name""".as[Coffee]

)
一个完整的例子

object SlickTest2 extends App{

  val db = Database.forConfig("mysql")

  def createCoffees:DBIO[Int] = sqlu"""create table coffees(
    name varchar(20) not null,
    sup_id int not null,
    price double not null,
    sales int not null,
    total int not null,
    foreign key(sup_id) references suppliers(id))"""

  def createSuppliers:DBIO[Int] = sqlu"""create table suppliers(
    id int not null primary key,
    name varchar(20) not null,
    street varchar(30) not null,
    city varchar(30) not null,
    state varchar(80) not null,
    zip varchar(10) not null)"""
  def insert2(c:Coffee) : DBIO[Int] = sqlu""" insert into coffees values(${c.name},${c.sup_id},${c.price},${c.sales},${c.total})  """

  def insert1(s:Supplier) : DBIO[Int] = sqlu""" insert into suppliers values(${s.id},${s.name},${s.street},${s.city},${s.state},${s.zip})   """



  try{
    val tables:DBIO[Unit] = DBIO.seq(
       //createSuppliers,
       //createCoffees
    )
    /*val f1:Future[Unit] = db.run(tables)
    val f2 = f1.flatMap { _ =>

        val set1:Seq[DBIO[Int]] = Seq(
          Supplier(101, "Acme, Inc."     , "99 Market Street", "Groundsville", "CA", "95199"),
          Supplier( 49, "Superior Coffee", "1 Party Place"   , "Mendocino"   , "CA", "95460"),
          Supplier(150, "The High Ground", "100 Coffee Lane" , "Meadows"     , "CA", "93966")
        ).map(insert1)

        val set2:Seq[DBIO[Int]] = Seq(
          Coffee("Colombian",          101,  7.99, 0, 0),
          Coffee("French_Roast",       49,   8.99, 0, 0),
          Coffee("Espresso",           150,  9.99, 0, 0),
          Coffee("Colombian_Decaf",    101,  8.99, 0, 0),
          Coffee("French_Roast_Decaf", 49,   9.99, 0, 0)
        ).map(insert2)

        val f:Future[Seq[Int]] = db.run(DBIO.sequence(set2))

        f.map(println)
    }*/

    //val query1 = sql"""SELECT FROM C.NAME,S.NAME FROM COFFEES C,SUPPLIERS S WHERE C.PRICE < $PRICE AND S.ID = C.SUP_ID""".as[(String,String)]
    implicit val getSupplierResult = GetResult(r => Supplier(r.nextInt, r.nextString, r.nextString, r.nextString, r.nextString, r.nextString))
    implicit val getCoffeeResult = GetResult(r => Coffee(r.<<, r.<<, r.<<, r.<<, r.<<))
    val table = "coffees"
    val name  = "Colombian"

    val query2:DBIO[Seq[Coffee]] = sql"""SELECT * FROM #$table where name = $name""".as[Coffee]
    val resultQuery2:Future[Seq[Coffee]] = db.run(query2)

    resultQuery2 onComplete {
      case Success(data) => data.foreach(println)
      case Failure(msg)  => println(msg.toString)
    }

    //堵塞线程,避免数据库被关闭
    Await.result(resultQuery2,Duration.Inf)

  } finally db.close
}