浅谈Slick（3）－ Slick201：从fp角度了解Slick

最新推荐文章于 2024-11-03 18:07:18 发布
最新推荐文章于 2024-11-03 18:07:18 发布
阅读量158 收藏
点赞数
文章标签： scala 数据库 java
原文链接：http://www.cnblogs.com/tiger-xc/p/5922560.html
版权

  我在上期讨论里已经成功的创建了一个简单的Slick项目，然后又尝试使用了一些最基本的功能。Slick是一个FRM（Functional Relational Mapper）,是为fp编程提供的scala SQL Query集成环境，可以让编程人员在scala编程语言里用函数式编程模式来实现对数据库操作的编程。在这篇讨论里我想以函数式思考模式来加深了解Slick。我对fp编程模式印象最深的就是类型匹配：从参数类型和返回结果类型来了解函数功能。所以上面我所指的函数式思考方式主要是从Slick函数的类型匹配角度来分析函数所起的作用和具体使用方式。

我们先了解一下建表过程：

 1 import slick.driver.H2Driver.api._
 2 object slick201 {
 3   //projection case classes 表列模版
 4   case class Coffee(
 5                      id: Option[Long]
 6                      ,name: String
 7                      ,sup_ID: Int
 8                      ,price: Double
 9                      ,grade: Grade
10                      ,total: Int
11                    )
12   case class Supplier(
13                        id: Option[Int]
14                        ,name: String
15                        ,address: String
16                        ,website: Option[String]
17                      )
18   //自定义字段
19   abstract class Grade(points: Int)
20   object Grade {
21     case object Premium extends Grade(2)
22     case object Quality extends Grade(1)
23     case object Bestbuy extends Grade(0)
24 
25     def fromInt(p: Int) = p match {
26         case 2 => Premium
27         case 1 => Quality
28         case 0 => Bestbuy
29     }
30     def toInt(g: Grade) = g match {
31       case Premium => 2
32       case Quality => 1
33       case Bestbuy => 0
34     }
35     implicit val customColumn: BaseColumnType[Grade] =
36       MappedColumnType.base[Grade,Int](Grade.toInt, Grade.fromInt)
37   }
38   //schema 表行结构定义
39   class Coffees(tag: Tag) extends Table[Coffee](tag, "COFFEES") {
40     def id = column[Long]("COF_ID", O.AutoInc, O.PrimaryKey)
41     def name = column[String]("COF_NAME")
42     def price = column[Double]("COF_PRICE")
43     def supID = column[Int]("COF_SUP")
44     def grade = column[Grade]("COF_GRADE", O.Default(Grade.Bestbuy))
45     def total = column[Int]("COF_TOTAL", O.Default(0))
46 
47     def * = (id.?,name,supID,price,grade,total) <> (Coffee.tupled, Coffee.unapply)
48 
49     def supplier = foreignKey("SUP_FK",supID,suppliers)(_.id,onUpdate = ForeignKeyAction.Restrict, onDelete = ForeignKeyAction.Cascade)
50     def nameidx = index("NM_IX",name,unique = true)
51   }
52   val coffees = TableQuery[Coffees]
53 
54   class Suppliers(tag: Tag) extends Table[Supplier](tag, "SUPPLIERS") {
55     def id = column[Int]("SUP_ID", O.PrimaryKey, O.AutoInc)
56     def name = column[String]("SUP_NAME")
57     def address = column[String]("SUP_ADDR", O.Default("-"))
58     def website = column[Option[String]]("SUP_WEB")
59 
60     def * = (id.?, name, address, website) <> (Supplier.tupled, Supplier.unapply)
61     def addidx = index("ADDR_IX",(name,address),unique = true)
62   }
63   val suppliers = TableQuery[Suppliers]
64 
65 }

我尽量把经常会遇到的情况如：定义字段、建索引、默认值、自定义字段等都作了尝试。coffees和suppliers代表了最终的数据表Query，def * 定义了这个Query的默认返回结果字段。

所有的定义都是围绕着表行（Table Row）结构进行的，包括：表属性及操作（Table member methods）、字段（Column）、字段属性（ColumnOptions）。表行定义操作方法基本都在slick.lifted.AbstractTable里、表属性定义在slick.model命名空间里、而大部分的帮助支持函数都在slick.lifted命名空间的其它对象里。

表行的实际类型如下：

abstract class Table[T](_tableTag: Tag, _schemaName: Option[String], _tableName: String) extends AbstractTable[T](_tableTag, _schemaName, _tableName) { table => ...}
 
/** The profile-independent superclass of all table row objects.
  * @tparam T Row type for this table. Make sure it matches the type of your `*` projection. */
abstract class AbstractTable[T](val tableTag: Tag, val schemaName: Option[String], val tableName: String) extends Rep[T] {...}

如上所示，Table[T] extends AbstractTable[T]。现在所有表行定义操作函数应该在slick.profile.relationalTableComponent.Table里可以找得到。值得注意的是表行的最终类型是Rep[T]，T可能是case class或者Tuple，被升格（lift）到Rep[T]。所以大部分表行定义的支持函数都是在slick.lifted命名空间内的。

上面我们使用了模版对应表行定义方式，所有列都能和模版case class对应。那么在定义projection def * 时就需要使用<>函数：

  def <>[R : ClassTag](f: (U => R), g: (R => Option[U])) = new MappedProjection[R, U](shape.toNode(value), MappedScalaType.Mapper(g.andThen(_.get).asInstanceOf[Any => Any], f.asInstanceOf[Any => Any], None), implicitly[ClassTag[R]])

f,g是两个case class <> Tuple转换函数。在上面的例子里我们提供的是tupled和unapply，效果就是这样的：

1   Coffee.tupled
2   //res2: ((Option[Long], String, Int, Double, Grade, Int)) => Coffee = <function1>
3   Coffee.unapply _
4   //res3: Coffee => Option[(Option[Long], String, Int, Double, Grade, Int)] = <function1>

res2 >>> 把tuple: (...)转成coffee，res2 >>> 把coffee转成Option[(...)]

TableQuery[T]继承了Query[T]：slick.lifted.Query.scala

/** Represents a database table. Profiles add extension methods to TableQuery
  * for operations that can be performed on tables but not on arbitrary
  * queries, e.g. getting the table DDL. */
class TableQuery[E <: AbstractTable[_]](cons: Tag => E) extends Query[E, E#TableElementType, Seq] {...}
...
sealed trait QueryBase[T] extends Rep[T]

/** An instance of Query represents a query or view, i.e. a computation of a
  * collection type (Rep[Seq[T]]). It is parameterized with both, the mixed
  * type (the type of values you see e.g. when you call map()) and the unpacked
  * type (the type of values that you get back when you run the query).
  *
  * Additional extension methods for queries containing a single column are
  * defined in [[slick.lifted.SingleColumnQueryExtensionMethods]].
  */
sealed abstract class Query[+E, U, C[_]] extends QueryBase[C[U]] { self =>...}

所有Query对象里提供的函数TableQuery类都可以调用。上面例子里coffees,suppliers实际是数据库表COFFEES,SUPPLIERS的Query实例，它们的默认字段集如：coffees.result是通过def * 定义的（除非用map或yield改变默认projection）。在slick.profile.RelationalProfile.TableQueryExtensionMethods里还有专门针对TableQuery类型的函数如schema等。

好了，来到了Query才算真正进入主题。Query可以说是Slick最核心的类型了。所有针对数据库的读写操作都是通过Query产生SQL语句发送到数据库实现的。Query是个函数式类型，即高阶类型Query[A]。A代表生成SQL语句的元素，通过转变A可以实现不同的SQL语句构建。不同功能的Query包括读取（retreive）、插入（insert）、更新（update）、删除（delete）都是通过Query变形（transformation）实现的。所有Query操作函数的款式：Query[A] => Query[B]，是典型的函数式编程方式，也是scala集合操作函数款式。我们先从数据读取Query开始，因为上面我们曾经提到过可以通过map来决定新的结果集结构（projection）：

 1 val q1 = coffees.result
 2   q1.statements.head
 3   //res0: String = select "COF_ID", "COF_NAME", "COF_SUP", "COF_PRICE", "COF_GRADE", "COF_TOTAL" from "COFFEES"
 4 
 5   val q2 = coffees.map(r => (r.id, r.name)).result
 6   q2.statements.head
 7   //res1: String = select "COF_ID", "COF_NAME" from "COFFEES"
 8 
 9   val q3 = (for (c <- coffees) yield(c.id,c.name)).result
10   q3.statements.head
11   //res2: String = select "COF_ID", "COF_NAME" from "COFFEES"

因为map和flatMap的函数款式是：

map[A,B](Q[A])(A=>B]):Q[B], flatMap[A,B](Q[A])(A => Q[B]):Q[B]

所以不同的SQL语句基本上是通过Query[A] => Query[B]这种对高阶类型内嵌元素进行转变的函数式操作方式实现的。下面是一个带筛选条件的Query：

 1   val q = coffees.filter(_.price > 100.0).map(r => (r.id,r.name)).result
 2   q.statements.head
 3   //res3: String = select "COF_ID", "COF_NAME" from "COFFEES" where "COF_PRICE" > 100.0
 4 
 5   val q4 = coffees.filter(_.price > 100.0).take(4).map(_.name).result
 6   q4.statements.head
 7   //res4: String = select "COF_NAME" from "COFFEES" where "COF_PRICE" > 100.0 limit 4
 8 
 9   val q5 = coffees.sortBy(_.id.desc.nullsFirst).map(_.name).drop(3).result
10   q5.statements.head
11   //res5: String = select "COF_NAME" from "COFFEES" order by "COF_ID" desc nulls first limit -1 offset 3

再复杂一点的Query，比如说join两个表：

 1 val q6 = for {
 2     (c,s) <- coffees join suppliers on (_.supID === _.id)
 3   } yield(c.id,c.name,s.name)
 4   q6.result.statements.head
 5   //res6: String = select x2."COF_ID", x2."COF_NAME", x3."SUP_NAME" from "COFFEES" x2, "SUPPLIERS" x3 where x2."COF_SUP" = x3."SUP_ID"
 6   
 7   val q7 = for {
 8     c <- coffees
 9     s <- suppliers.filter(c.supID === _.id)
10   } yield(c.id,c.name,s.name)
11   q7.result.statements.head
12   //res7: String = select x2."COF_ID", x2."COF_NAME", x3."SUP_NAME" from "COFFEES" x2, "SUPPLIERS" x3 where x2."COF_SUP" = x3."SUP_ID"

还有汇总类型的Query:

1 coffees.map(_.price).max.result.statements.head
2   //res10: String = select max("COF_PRICE") from "COFFEES"
3   coffees.map(_.total).sum.result.statements.head
4   //res11: String = select sum("COF_TOTAL") from "COFFEES"
5   coffees.length.result.statements.head
6   //res12: String = select count(1) from "COFFEES"
7   coffees.filter(_.price > 100.0).exists.result.statements.head
8   //res13: String = select exists(select "COF_TOTAL", "COF_NAME", "COF_SUP", "COF_ID", "COF_PRICE", "COF_GRADE" from "COFFEES" where "COF_PRICE" > 100.0)

Query是个monad，它可以实现函数组合（functional composition）。如上所示：所有Query操作函数都是Query[A]=>Query[B]形式的。由于Query[A]里面的A类型是Rep[T]类型，是SQL语句组件类型。典型函数如flatMap的调用方式是：flatMap{a => MakeQuery(a ...)}，可以看到下一个Query的构成可能依赖a值，而a的类型是表行或列定义。所以Query的函数组合就是SQL语句的组合，最终结果是产生目标SQL语句。

Slick处理数据的方式是通过组合相应的SQL语句后发送给数据库去运算的，相关SQL语句的产生当然是通过Query来实现的：

 1   val qInsert = coffees += Coffee(Some(0),"American",101,56.0,Grade.Bestbuy,0)
 2   qInsert.statements.head
 3 //res10: String = insert into "COFFEES" ("COF_NAME","COF_SUP","COF_PRICE","COF_GRADE","COF_TOTAL")  values (?,?,?,?,?)
 4   val qInsert2 = coffees.map{r => (r.name, r.supID, r.price)} += ("Columbia",101,102.0)
 5   qInsert2.statements.head
 6 //res11: String = insert into "COFFEES" ("COF_NAME","COF_SUP","COF_PRICE")  values (?,?,?)
 7   val qInsert3 = (suppliers.map{r => (r.id,r.name)}).
 8     returning(suppliers.map(_.id)) += (101,"The Coffee Co.,")
 9   qInsert3.statements.head
10 //res12: String = insert into "SUPPLIERS" ("SUP_NAME")  values (?)

从qInsert3产生的SQL语句来看：jdbc返回数据后还必须由Slick进一步处理后才能返回用户要求的结果值。下面是一些其它更改数据的Query示范：

1   val qDelete = coffees.filter(_.price === 0.0).delete
2   qDelete.statements.head
3   //res17: String = delete from "COFFEES" where "COFFEES"."COF_PRICE" = 0.0
4   val qUpdate = for (c <- coffees if (c.name === "American")) yield c.price
5   qUpdate.update(10.0).statements.head
6   //res18: String = update "COFFEES" set "COF_PRICE" = ? where "COFFEES"."COF_NAME" = 'American'

update query必须通过for-comprehension的yield来确定更新字段。
Slick3.x最大的改进就是采用了functional I/O技术。具体做法就是引进DBIOAction类型，这是一个free monad。通过采用free monad的延迟运算模式来实现数据库操作动作的可组合性（composablility）及多线程运算（concurrency）。

DBIOAction类型款式如下：

sealed trait DBIOAction[+R, +S <: NoStream, -E <: Effect] extends Dumpable {
...}
package object dbio {
  /** Simplified type for a streaming [[DBIOAction]] without effect tracking */
  type StreamingDBIO[+R, +T] = DBIOAction[R, Streaming[T], Effect.All]

  /** Simplified type for a [[DBIOAction]] without streaming or effect tracking */
  type DBIO[+R] = DBIOAction[R, NoStream, Effect.All]
  val DBIO = DBIOAction
}

DBIO[+R]和StreamingDBIO[+R,+T]分别是固定类型参数S和E的类型别名，用它们来简化代码。所有的数据库操作函数包括result、insert、delete、update等都返回DBIOAction类型结果：

  def result: DriverAction[R, S, Effect.Read] = {...}
  def delete: DriverAction[Int, NoStream, Effect.Write] = {...}
  def update(value: T): DriverAction[Int, NoStream, Effect.Write] = {...}
  def += (value: U): DriverAction[SingleInsertResult, NoStream, Effect.Write] = {...}

上面的DriverAction是DBIOAction的子类。因为DBIOAction是个free monad，所以多个DBIOAction可以进行组合，而在过程中是不会立即产生DBIO副作用的。我们只能通过DBIOAction类型的运算器来对DBIOAction的组合进行运算才会正真进行数据库数据读写。DBIOAction运算函数款式如下：

/** Run an Action asynchronously and return the result as a Future. */
    final def run[R](a: DBIOAction[R, NoStream, Nothing]): Future[R] = runInternal(a, false)

run函数返回Future[R]，代表在异步线程运算完成后返回R类型值。一般来讲Query.result返回R类型为Seq[?]。

DBIOAction只是对数据库操作动作的描述，不是实际的读写，所以DBIOAction可以进行组合。所谓组合的意思实际上就是把几个动作连续起来。DBIOAction的函数组件除monad通用的map、flatMap、sequence等，还包括了andThen、zip等合并操作函数，andThen可以返回最后一个动作结果、zip在一个pair里返回两个动作的结果。因为DBIOAction是monad，所以for-comprehension应该是最灵活、最强大的组合方式了。我们来试试用上面Query产生的动作来进行一些组合示范：

1   val initSupAction = suppliers.schema.create andThen qInsert3
2   val createCoffeeAction = coffees.schema.create
3   val insertCoffeeAction = qInsert zip qInsert2
4   val initSupAndCoffee = for {
5     _ <- initSupAction
6     _ <- createCoffeeAction
7     (i1,i2) <- insertCoffeeAction 
8   } yield (i1,i2)

我们可以任意组合这些操作步骤，因为它们的返回结果类型都是DBIOAction[R]：一个free monad。大多数时间这些动作都是按照一定的流程顺序组合的。可能有些时候下一个动作需要依赖上一个动作产生的结果，这个时候用for-comprehension是最适合的了：

 1 //先选出所有ESPRESSO开头的coffee名称，然后逐个删除
 2   val delESAction = (for {
 3     ns <- coffees.filter(_.name.startsWith("ESPRESSO")).map(_.name).result
 4     _ <- DBIO.seq(ns.map(n => coffees.filter(_.name === n).delete): _*)
 5   } yield ()).transactionally
 6   //delESAction: slick.dbio.DBIOAction[Unit,slick.dbio.NoStream,slick.dbio.Effect.Read ...
 7 
 8   //对一个品种价格升10%
 9   def raisePriceAction(i: Long, np: Double, pc: Double) =
10     (for(c <- coffees if (c.id === i)) yield c.price).update(np * pc)
11   //raisePriceAction: raisePriceAction[](val i: Long,val np: Double,val pc: Double) => slick.driver.H2Driver.DriverAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write]
12   //对所有价格<100的coffee加价
13   val updatePriceAction = (for {
14     ips <- coffees.filter(_.price < 100.0).map(r => (r.id, r.price)).result
15     _ <- DBIO.seq{ips.map { ip => raisePriceAction(ip._1, ip._2, 110.0)}: _* }
16   } yield()).transactionally
17   //updatePriceAction: slick.dbio.DBIOAction[Unit,slick.dbio.NoStream,slick.dbio.Effect.Read ...

另外，像monad的point：successful(R)可以把R升格成DBIOAction，failed(T)可以把T升格成DBIOAction[T]：

1   DBIO.successful(Supplier(Some(102),"Coffee Company","",None))
2   //res19: slick.dbio.DBIOAction[Supplier,slick.dbio.NoStream,slick.dbio.Effect] = SuccessAction(Supplier(Some(102),Coffee Company,,None))
3 
4   DBIO.failed(new Exception("oh my god..."))
5   //res20: slick.dbio.DBIOAction[Nothing,slick.dbio.NoStream,slick.dbio.Effect] = FailureAction(java.lang.Exception: oh my god...)

DBIOAction还有比较完善的事后处理和异常处理机制：

 1 //主要示范事后处理机制用法，不必理会功能的具体目的是否有任何意义
 2   qInsert.andFinally(qDelete)
 3   //res21: slick.dbio.DBIOAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write with slick.dbio.Effect.Write] = slick.dbio.SynchronousDatabaseAction$$anon$6@1d46b337
 4 
 5   updatePriceAction.cleanUp (
 6     { case Some(e) => initSupAction; DBIO.failed(new Exception("oh my..."))
 7       case _ => qInsert3
 8     }
 9       ,true
10   )
11   //res22: slick.dbio.DBIOAction[Unit,slick.dbio.NoStream,slick.dbio.Effect.Read ...
12 
13   raisePriceAction(101,10.0,110.0).asTry
14   //res23: slick.dbio.DBIOAction[scala.util.Try[Int],slick.dbio.NoStream,slick.dbio.Effect.Write] = slick.dbio.SynchronousDatabaseAction$$anon$9@60304a44

从上面的这些示范例子我们认识到DBIOAction的函数组合就是数据库操作步骤组合、实际上就是程序的组合或者是功能组合：把一些简单的程序组合成功能更全面的程序，然后才运算这个组合而成的程序。DBIOAction的运算函数run的函数款式如下： 

/** Run an Action asynchronously and return the result as a Future. */
    final def run[R](a: DBIOAction[R, NoStream, Nothing]): Future[R] = runInternal(a, false)

对DBIOAction进行运算后的结果是个Future类型，也是一个高阶类型，同样可以用map、flatMap、sequence、andThen等泛函组件进行函数组合。可以参考下面的这个示范：

 1   import slick.jdbc.meta.MTable
 2   import scala.concurrent.ExecutionContext.Implicits.global
 3   import scala.concurrent.duration.Duration
 4   import scala.concurrent.{Await, Future}
 5   import scala.util.{Success,Failure}
 6 
 7   val db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", driver="org.h2.Driver")
 8 
 9   def recreateCoffeeTable: Future[Unit] = {
10     db.run(MTable.getTables("Coffees")).flatMap {
11       case tables if tables.isEmpty => db.run(coffees.schema.create).andThen {
12         case Success(_) => println("coffee table created")
13         case Failure(e) => println(s"failed to create! ${e.getMessage}")  
14       }
15       case _ => db.run((coffees.schema.drop andThen coffees.schema.create)).andThen {
16         case Success(_) => println("coffee table recreated")
17         case Failure(e) => println(s"failed to recreate! ${e.getMessage}")
18       }   
19     }
20   }

好了，下面是这次讨论的示范代码：

  1 import slick.driver.H2Driver.api._
  2 
  3 object slick201 {
  4   //projection case classes 表列模版
  5   case class Coffee(
  6                      id: Option[Long]
  7                      ,name: String
  8                      ,sup_ID: Int
  9                      ,price: Double
 10                      ,grade: Grade
 11                      ,total: Int
 12                    )
 13   case class Supplier(
 14                        id: Option[Int]
 15                        ,name: String
 16                        ,address: String
 17                        ,website: Option[String]
 18                      )
 19   //自定义字段
 20   abstract class Grade(points: Int)
 21   object Grade {
 22     case object Premium extends Grade(2)
 23     case object Quality extends Grade(1)
 24     case object Bestbuy extends Grade(0)
 25 
 26     def fromInt(p: Int) = p match {
 27         case 2 => Premium
 28         case 1 => Quality
 29         case 0 => Bestbuy
 30     }
 31     def toInt(g: Grade) = g match {
 32       case Premium => 2
 33       case Quality => 1
 34       case Bestbuy => 0
 35     }
 36     implicit val customColumn: BaseColumnType[Grade] =
 37       MappedColumnType.base[Grade,Int](Grade.toInt, Grade.fromInt)
 38   }
 39   //schema 表行结构定义
 40   class Coffees(tag: Tag) extends Table[Coffee](tag, "COFFEES") {
 41     def id = column[Long]("COF_ID", O.AutoInc, O.PrimaryKey)
 42     def name = column[String]("COF_NAME")
 43     def price = column[Double]("COF_PRICE")
 44     def supID = column[Int]("COF_SUP")
 45     def grade = column[Grade]("COF_GRADE", O.Default(Grade.Bestbuy))
 46     def total = column[Int]("COF_TOTAL", O.Default(0))
 47 
 48     def * = (id.?,name,supID,price,grade,total) <> (Coffee.tupled, Coffee.unapply)
 49 
 50     def supplier = foreignKey("SUP_FK",supID,suppliers)(_.id,onUpdate = ForeignKeyAction.Restrict, onDelete = ForeignKeyAction.Cascade)
 51     def nameidx = index("NM_IX",name,unique = true)
 52   }
 53   val coffees = TableQuery[Coffees]
 54 
 55   class Suppliers(tag: Tag) extends Table[Supplier](tag, "SUPPLIERS") {
 56     def id = column[Int]("SUP_ID", O.PrimaryKey, O.AutoInc)
 57     def name = column[String]("SUP_NAME")
 58     def address = column[String]("SUP_ADDR", O.Default("-"))
 59     def website = column[Option[String]]("SUP_WEB")
 60 
 61     def * = (id.?, name, address, website) <> (Supplier.tupled, Supplier.unapply)
 62     def addidx = index("ADDR_IX",(name,address),unique = true)
 63   }
 64   val suppliers = TableQuery[Suppliers]
 65 
 66   class Bars(tag: Tag) extends Table[(Int,String)](tag,"BARS") {
 67     def id = column[Int]("BAR_ID",O.AutoInc,O.PrimaryKey)
 68     def name = column[String]("BAR_NAME")
 69     def * = (id, name)
 70   }
 71   val bars = TableQuery[Bars]
 72 
 73   Coffee.tupled
 74   //res2: ((Option[Long], String, Int, Double, Grade, Int)) => Coffee = <function1>
 75   Coffee.unapply _
 76   //res3: Coffee => Option[(Option[Long], String, Int, Double, Grade, Int)] = <function1>
 77 
 78 
 79   val q1 = coffees.result
 80   q1.statements.head
 81   //res0: String = select "COF_ID", "COF_NAME", "COF_SUP", "COF_PRICE", "COF_GRADE", "COF_TOTAL" from "COFFEES"
 82   
 83   val q2 = coffees.map(r => (r.id, r.name)).result
 84   q2.statements.head
 85   //res1: String = select "COF_ID", "COF_NAME" from "COFFEES"
 86 
 87   val q3 = (for (c <- coffees) yield(c.id,c.name)).result
 88   q3.statements.head
 89   //res2: String = select "COF_ID", "COF_NAME" from "COFFEES"
 90 
 91 
 92   val q = coffees.filter(_.price > 100.0).map(r => (r.id,r.name)).result
 93   q.statements.head
 94   //res3: String = select "COF_ID", "COF_NAME" from "COFFEES" where "COF_PRICE" > 100.0
 95 
 96   val q4 = coffees.filter(_.price > 100.0).take(4).map(_.name).result
 97   q4.statements.head
 98   //res4: String = select "COF_NAME" from "COFFEES" where "COF_PRICE" > 100.0 limit 4
 99 
100   val q5 = coffees.sortBy(_.id.desc.nullsFirst).map(_.name).drop(3).result
101   q5.statements.head
102   //res5: String = select "COF_NAME" from "COFFEES" order by "COF_ID" desc nulls first limit -1 offset 3
103 
104   val q6 = for {
105     (c,s) <- coffees join suppliers on (_.supID === _.id)
106   } yield(c.id,c.name,s.name)
107   q6.result.statements.head
108   //res6: String = select x2."COF_ID", x2."COF_NAME", x3."SUP_NAME" from "COFFEES" x2, "SUPPLIERS" x3 where x2."COF_SUP" = x3."SUP_ID"
109 
110   val q7 = for {
111     c <- coffees
112     s <- suppliers.filter(c.supID === _.id)
113   } yield(c.id,c.name,s.name)
114   q7.result.statements.head
115   //res7: String = select x2."COF_ID", x2."COF_NAME", x3."SUP_NAME" from "COFFEES" x2, "SUPPLIERS" x3 where x2."COF_SUP" = x3."SUP_ID"
116 
117   coffees.map(_.price).max.result.statements.head
118   //res10: String = select max("COF_PRICE") from "COFFEES"
119   coffees.map(_.total).sum.result.statements.head
120   //res11: String = select sum("COF_TOTAL") from "COFFEES"
121   coffees.length.result.statements.head
122   //res12: String = select count(1) from "COFFEES"
123   coffees.filter(_.price > 100.0).exists.result.statements.head
124   //res13: String = select exists(select "COF_TOTAL", "COF_NAME", "COF_SUP", "COF_ID", "COF_PRICE", "COF_GRADE" from "COFFEES" where "COF_PRICE" > 100.0)
125   val qInsert = coffees += Coffee(Some(0),"American",101,56.0,Grade.Bestbuy,0)
126   qInsert.statements.head
127   //res14: String = insert into "COFFEES" ("COF_NAME","COF_SUP","COF_PRICE","COF_GRADE","COF_TOTAL")  values (?,?,?,?,?)
128   val qInsert2 = coffees.map{r => (r.name, r.supID, r.price)} += ("Columbia",101,102.0)
129   qInsert2.statements.head
130   //res15: String = insert into "COFFEES" ("COF_NAME","COF_SUP","COF_PRICE")  values (?,?,?)
131   val qInsert3 = (suppliers.map{r => (r.id,r.name)}).
132     returning(suppliers.map(_.id)) += (101,"The Coffee Co.,")
133   qInsert3.statements.head
134   //res16: String = insert into "SUPPLIERS" ("SUP_NAME")  values (?)
135 
136   val qDelete = coffees.filter(_.price === 0.0).delete
137   qDelete.statements.head
138   //res17: String = delete from "COFFEES" where "COFFEES"."COF_PRICE" = 0.0
139   val qUpdate = for (c <- coffees if (c.name === "American")) yield c.price
140   qUpdate.update(10.0).statements.head
141   //res18: String = update "COFFEES" set "COF_PRICE" = ? where "COFFEES"."COF_NAME" = 'American'
142 
143   val initSupAction = suppliers.schema.create andThen qInsert3
144   val createCoffeeAction = coffees.schema.create
145   val insertCoffeeAction = qInsert zip qInsert2
146   val initSupAndCoffee = for {
147     _ <- initSupAction
148     _ <- createCoffeeAction
149     (i1,i2) <- insertCoffeeAction
150   } yield (i1,i2)
151 
152   //先选出所有ESPRESSO开头的coffee名称，然后逐个删除
153   val delESAction = (for {
154     ns <- coffees.filter(_.name.startsWith("ESPRESSO")).map(_.name).result
155     _ <- DBIO.seq(ns.map(n => coffees.filter(_.name === n).delete): _*)
156   } yield ()).transactionally
157   //delESAction: slick.dbio.DBIOAction[Unit,slick.dbio.NoStream,slick.dbio.Effect.Read with slick.dbio.Effect.Write with slick.dbio.Effect.Transactional] = CleanUpAction(AndThenAction(Vector(slick.driver.JdbcActionComponent$StartTransaction$@6e76c850, FlatMapAction(slick.driver.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1@2005bce5,<function1>,scala.concurrent.impl.ExecutionContextImpl@245036ad))),<function1>,true,slick.dbio.DBIOAction$sameThreadExecutionContext$@294c4c1d)
158 
159   //对一个品种价格升10%
160   def raisePriceAction(i: Long, np: Double, pc: Double) =
161     (for(c <- coffees if (c.id === i)) yield c.price).update(np * pc)
162   //raisePriceAction: raisePriceAction[](val i: Long,val np: Double,val pc: Double) => slick.driver.H2Driver.DriverAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write]
163   //对所有价格<100的coffee加价
164   val updatePriceAction = (for {
165     ips <- coffees.filter(_.price < 100.0).map(r => (r.id, r.price)).result
166     _ <- DBIO.seq{ips.map { ip => raisePriceAction(ip._1, ip._2, 110.0)}: _* }
167   } yield()).transactionally
168   //updatePriceAction: slick.dbio.DBIOAction[Unit,slick.dbio.NoStream,slick.dbio.Effect.Read with slick.dbio.Effect.Write with slick.dbio.Effect.Transactional] = CleanUpAction(AndThenAction(Vector(slick.driver.JdbcActionComponent$StartTransaction$@6e76c850, FlatMapAction(slick.driver.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1@49c8a41f,<function1>,scala.concurrent.impl.ExecutionContextImpl@245036ad))),<function1>,true,slick.dbio.DBIOAction$sameThreadExecutionContext$@294c4c1d)
169 
170   DBIO.successful(Supplier(Some(102),"Coffee Company","",None))
171   //res19: slick.dbio.DBIOAction[Supplier,slick.dbio.NoStream,slick.dbio.Effect] = SuccessAction(Supplier(Some(102),Coffee Company,,None))
172 
173   DBIO.failed(new Exception("oh my god..."))
174   //res20: slick.dbio.DBIOAction[Nothing,slick.dbio.NoStream,slick.dbio.Effect] = FailureAction(java.lang.Exception: oh my god...)
175 
176   //示范事后处理机制，不必理会功能的具体目的
177   qInsert.andFinally(qDelete)
178   //res21: slick.dbio.DBIOAction[Int,slick.dbio.NoStream,slick.dbio.Effect.Write with slick.dbio.Effect.Write] = slick.dbio.SynchronousDatabaseAction$$anon$6@1d46b337
179 
180   updatePriceAction.cleanUp (
181     { case Some(e) => initSupAction; DBIO.failed(new Exception("oh my..."))
182       case _ => qInsert3
183     }
184       ,true
185   )
186   //res22: slick.dbio.DBIOAction[Unit,slick.dbio.NoStream,slick.dbio.Effect.Read with slick.dbio.Effect.Write with slick.dbio.Effect.Transactional with slick.dbio.Effect.Write] = CleanUpAction(CleanUpAction(AndThenAction(Vector(slick.driver.JdbcActionComponent$StartTransaction$@6e76c850, FlatMapAction(slick.driver.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1@1f7aad00,<function1>,scala.concurrent.impl.ExecutionContextImpl@245036ad))),<function1>,true,slick.dbio.DBIOAction$sameThreadExecutionContext$@294c4c1d),<function1>,true,scala.concurrent.impl.ExecutionContextImpl@245036ad)
187 
188   raisePriceAction(101,10.0,110.0).asTry
189   //res23: slick.dbio.DBIOAction[scala.util.Try[Int],slick.dbio.NoStream,slick.dbio.Effect.Write] = slick.dbio.SynchronousDatabaseAction$$anon$9@60304a44
190 
191 
192   import slick.jdbc.meta.MTable
193   import scala.concurrent.ExecutionContext.Implicits.global
194   import scala.concurrent.duration.Duration
195   import scala.concurrent.{Await, Future}
196   import scala.util.{Success,Failure}
197 
198   val db = Database.forURL("jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1", driver="org.h2.Driver")
199 
200   def recreateCoffeeTable: Future[Unit] = {
201     db.run(MTable.getTables("Coffees")).flatMap {
202       case tables if tables.isEmpty => db.run(coffees.schema.create).andThen {
203         case Success(_) => println("coffee table created")
204         case Failure(e) => println(s"failed to create! ${e.getMessage}")
205       }
206       case _ => db.run((coffees.schema.drop andThen coffees.schema.create)).andThen {
207         case Success(_) => println("coffee table recreated")
208         case Failure(e) => println(s"failed to recreate! ${e.getMessage}")
209       }
210     }
211   }
212 
213 }

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/tiger-xc/p/5922560.html

weixin_30405421
关注
play24-slick3-auth-example:play24-slick3-auth-example
05-01
play24-slick3-auth-example 带有 ， ， 管理页面示例。 模板使用 。 （不包括Flot图表和Morris.js图表​​，请应用 ）要求安装非常简单！用法克隆存储库git clone ...
scala slick基本使用教程
踏莎行的博客
11-28 9149
参考： http://slick.lightbend.com/doc/3.2.1/introduction.html http://www.cnblogs.com/tiger-xc/p/5898585.html http://www.cnblogs.com/tiger-xc/p/5898585.html slick是scala生态中的数据库连接组件，跟scala一样有良...
【已解决】使用Slick的时候出现OPTION SQL_SELECT_LIMIT=DEFAULT错误
Aspirin's Nest
03-08 369
首先看一下报错信息： Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version f...
解决stick版本，不能update/commit的情况
budapest的专栏
03-17 884
一次从cvs中checkout 出来是stick的版本，后来造成不能update/commit到最新的版本。这儿stick的意思可能就是使local的版本文件成为粘性的，表示你不能从cvs上checkout非此版本的文件，如果这个文件有改动，你也不能这个文件commit进到cvs中去。后在该文件夹的CVS目录Entries配置文件中有如下配置：/layout.css/1.3/Fri M
SlickGrid 插件开发(2)：单元格合并功能实现
dusea的博客
09-25 2970
转自 http://blog.csdn.net/besley/article/details/9150001 在SlickGrid官方示例中，给出了同一行单元格合并的示例，主要是使用了colspan的方法，但是对于同一列的不同行的单元格合并，却没有好的实现方法；然而这个功能一般在做数据处理时，又是必须需要的，经过对slickgrid的dataview的功能分析，参考公司小陈对for
vue-slick-carousel：:horizontal_traffic_light:真正的SSR的Vue Slick旋转木马，专为:high_voltage:FluxLuxstay
02-05
vue-slick-carousel继承了广受欢迎的slick-carousel功能，提供了多种功能。 它已被完全重写为vue组件。 如果您试图在Vue中使用圆滑传送带，那将是一个完美的选择。 您可以以vue组件的方式使用它，而不会出现任何...
ngx-slick-carousel：slick插件的Angular 6+包装器
02-03
ngx-slick-carousel 支持Angular 7+ 支持...slick-carousel --save消耗图书馆将库发布到npm后，可以通过运行以下命令将库导入任何Angular应用程序中： $ npm install ngx-slick-carousel --save 然后从您的Angular AppM
slick-migration-api：用于Slick的模式操作方言和DSL
01-30
**slick-migration-api** 是一个专门针对 **Slick** 数据库访问库的扩展，它提供了模式操作的方言和领域特定语言（DSL），使得在Scala中进行数据库迁移变得更加方便和直观。Slick是Scala的一个强大的、类型安全的SQL...
play-slick3步：使用scala Play Framework和Slick的示例应用程序
02-03
这个示例项目"play-slick3-steps"旨在演示如何在Play Framework中集成Slick 3.x版本，以便与MySQL数据库进行交互。通过这个教程，你将学习到以下关键知识点： 1. **环境配置**： - 首先，确保已经安装了Java、...
scala结合slick操作MySQL数据库
Shea1992的博客
01-25 749
开发十年，就只剩下这套架构体系了！ >>> ...
Scala持久层框架Slick介绍
Simon
01-13 9243
FRM介绍 最近看到了一个FRM的框架Slick。 FRM的意思是Functional Relational Mapping， 一种基于函数式的ORM。 举一个最简单的例子： val queryResult = db.query(queryStr) queryResult.onSuccess { result => result.doSomething ... }
浅谈Slick（1）－ 基本功能描述
weixin_30897233的博客
09-21 255
Slick (Scala language-integrated connection kit)是scala的一个FRM（Functional Relational Mapper），即函数式的关系数据库编程工具库。Slick的主要目的是使关系数据库能更容易、更自然的融入函数式编程模式，它可以使使用者像对待scala集合一样来处理关系数据库表。也就是说可以用scala集合的那些丰富的...
探索万恶的 Slick 3 类型系统 | Prologue
Simon
11-08 381
这两天在给Samsara Aquarius的各个Service写分页的时候，设计了一个抽象层，想把一些公共的分页逻辑放在这个trait中，但是被Slick的类型系统折磨了一点时间。。今天粗略看了一下相关的源码，结合这几天遇到的问题，总结一下。因此就把这篇文章作为Prologue吧。。。（Slick的版本为3.1.1） 蛋疼的类型参数 在Slick里面，分页可以通过drop和take函数实现。q...
SlickGrid 基本方法篇
热门推荐
上步七星
05-07 1万+
前言：SlickGrid 是一个Javascript编写的数据控件，其采用数据虚拟显示的特性备受后来的Grid推崇，如ExtJS DataGrid，其架构设计优秀，UI交互功能非常丰富，插件化的可扩展功能开发非常值得Web开发人员学习，本文列出基本方法的使用，供开发人员参考；并在后文继续总结列出已经开发出的插件，以供开发人员参考。 0. SlickGrid 基本样式示例 功能包括：
2. Flink快速上手
howard2005的专栏
10-29 347
通过今天的实战，您应该已经了解了如何快速上手Apache Flink，并进行简单的批处理和流处理操作。希望这些内容能够帮助您在实际项目中更好地使用Flink。
记本地第一次运行seatunnel示例项目
大尾猴的博客
10-30 511
静态源码编译通过：https://blog.csdn.net/u011924665/article/details/143372464。
spark==centos安装hadoop集群，安装spark standalone集群，编写pyspark/java/scala代码使用集群
最新发布
hebian1994的博客
11-03 151
master地址 hadoop100:8080历史服务器 hadoop100:18080hdfs地址1centos安装hadoop集群，上传文件到hdfs2安装spark standalone集群，查看自带的pyspark使用的python版本，然后安装annaconda安装该版本的虚拟环境，安装该版本的pyspark依赖包3 python pyspark代码pycharm远程选择python解释器编写pyspark代码。
oceanbase V4.2.2社区版集群离线部署
king_harry的专栏
11-01 681
版本，选择是上传文件，包含oceanbase-ce、oceanbase-ce-libs、oceanbase-ce-utils。关闭时展示，为 OBProxy 集群的访问地址，仅用于生成租户的连接串，不影响实际使用，需要自主配置负载均衡，如果是 VIP 地址，还需要您自主申请并绑定到 OBProxy Server。集群配置选项卡—更多配置—更改系统内存保留选项，默认是30G，如果测试机内存只有16G，则需要调小，本测试调整为2G，这样ocp-server安装不会失败。自定义待管理的集群的名称。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值