在上一集的讨论里我们介绍并实现了强类型返回结果行。使用强类型主要的目的是当我们把后端数据库SQL批次操作搬到内存里转变成数据流式按行操作时能更方便、准确、高效地选定数据字段。在上集讨论示范里我们用集合的foreach方式模拟了一个最简单的数据流,并把从数据库里批次读取的数据集转换成一串连续的数据行来逐行使用。一般来说完整的流式数据处理流程包括了从数据库中读取数据、根据读取的每行数据状态再对后台数据库进行更新,包括:插入新数据、更新、删除等。那么在上篇中实现的流式操作基础上再添加一种指令行类型就可以完善整个数据处理流程了,就像下面这个图示:
Database => Query -> Collection => Streaming -> DataRow => QueryAction(DataRow) -> ActionRow => execAction(ActionRow) -> Database
如果我们还是以Slick为目标FRM,那么这个ActionRow的类型就是Slick的DBIO[T]了:
1 package com.bayakala.funda.rowtypes 2 import slick.dbio._ 3 object ActionType { 4 type FDAAction[T] = DBIO[T] 5 }
记得有一次在一个Scala讨论区里遇到这样一个问题:如何把a表里的status字段更新成b表的status字段值,转化成SQL语句如下:
update a,b set a.status=b.status where a.id=b.id
那位哥们的问题是如何用Slick来实现对a表的更新,不能用sql"???" interpolation 直接调用SQL语句,可能因为要求compile time语法check保障吧。这个问题用Slick Query还真的不太容易解决(能不能解决就不想费功夫去想了),这是因为FRM的SQL批次处理弱点。如果用FunDA的流式操作思路就会很容易解决了,只要用join Query把b.status读出来再用b.id=a.id逐个更新a.status。刚好,下面我们就示范通过ActionRow来解决这个问题。先用下面这段代码来设置测试数据:
1 import slick.dbio.DBIO 2 import slick.driver.H2Driver.api._ 3 4 import scala.concurrent.duration._ 5 import scala.concurrent.{Await, Future} 6 import scala.util.{Failure, Success} 7 import scala.concurrent.ExecutionContext.Implicits.global 8 import slick.jdbc.meta.MTable 9 object ActionRowTest extends App { 10 11 class ATable(tag: Tag) extends Table[(Int,String,Int)](tag,"TA") { 12 def id = column[Int]("id",O.PrimaryKey) 13 def flds = column[String]("aflds") 14 def status = column[Int]("status") 15 def * = (id,flds,status) 16 } 17 val tableA = TableQuery[ATable] 18 19 class BTable(tag: Tag) extends Table[(Int,String,Int)](tag,"TB") { 20 def id = column[Int]("id",O.PrimaryKey) 21 def flds = column[String]("bflds") 22 def status = column[Int]("status") 23 def * = (id,flds,status) 24 } 25 val tableB = TableQuery[BTable] 26 27 val insertAAction = 28 tableA ++= Seq ( 29 (1,"aaa",0), 30 (2,"bbb",3), 31 (3,"ccc",1), 32 (4,"ddd",0), 33 (16,"kkk",16) 34 ) 35 val insertBAction = 36 tableB ++= Seq ( 37 (1,"aaa",1), 38 (2,"bbb",2), 39 (3,"ccc",3), 40 (4,"ddd",4), 41 (5,"kkk",5) 42 ) 43 44 val db = Database.forConfig("h2db") 45 46 47 def tableExists(tables: Vector[MTable], tblname: String) = 48 tables.exists {t =>t.name.toString.contains(tblname)} 49 50 def createSchemaIfNotExists(): Future[Unit] = { 51 db.run(MTable.getTables).flatMap { 52 case tables if !tableExists(tables,".TA") && !tableExists(tables,".TB") => 53 println("Creating schemas for TA and TB...") 54 db.run((tableA.schema ++ tableB.schema).create) 55 case tables if !tableExists(tables,".TA") => 56 println("Creating schema for TA ...") 57 db.run(tableA.schema.create) 58 case tables if !tableExists(tables,".TB") => 59 println("Creating schema for TB ...") 60 db.run(tableB.schema.create) 61 case _ => 62 println("Schema for TA, TB already created.") 63 Future.successful() 64 } 65 } 66 67 def insertInitialData(): Future[Unit] = { 68 val cleanInsert = DBIO.seq( 69 tableA.delete, tableB.delete, 70 insertAAction, 71 insertBAction) 72 db.run(cleanInsert).andThen { 73 case Success(_) => println("Data insert completed.") 74 case Failure(e) => println(s"Data insert failed [${e.getMessage}]") 75 } 76 } 77 78 Await.ready(db.run(sql"DROP TABLE TA; DROP TABLE TB".as[String]),Duration.Inf) 79 80 val initResult = createSchemaIfNotExists().flatMap {_ => insertInitialData()} 81 Await.ready(initResult,Duration.Inf) 82 83 84 85 86 }
用join query先把这两个表相关的字段值搬到内存转成强类型行FDADataRow:
1 val selectAB = for { 2 a <- tableA 3 b <- tableB 4 if (a.id === b.id) 5 } yield (a.id,b.id,a.status,b.status) 6 7 case class ABRow (id: Int, asts: Int, bsts: Int) 8 def toABRow(raw: (Int,Int,Int,Int)) = ABRow(raw._1,raw._3,raw._4) 9 10 import com.bayakala.funda.rowtypes.DataRowType 11 12 val loader = FDADataRow(slick.driver.H2Driver, toABRow _) 13 loader.getTypedRows(selectAB.result)(db).foreach {dataRow => 14 println(s"ID:${dataRow.id} Status A = ${dataRow.asts}, B = ${dataRow.bsts}") 15 }
初始结果如下:
ID:1 Status A = 0, B = 1 ID:2 Status A = 3, B = 2 ID:3 Status A = 1, B = 3 ID:4 Status A = 0, B = 4
现在我们把每条数据行DataRow转成动作行ActionRow。然后把每条DataRow的asts字段值替换成bsts的字段值:
1 import com.bayakala.funda.rowtypes.ActionType.FDAAction 2 def updateAStatus(row: ABRow): FDAAction[Int] = { 3 tableA.filter{r => r.id === row.id} 4 .map(_.status) 5 .update(row.asts) 6 } 7 8 9 loader.getTypedRows(selectAB.result)(db).map(updateAStatus(_)).foreach { 10 actionRow => 11 println(s"${actionRow.toString}") 12 }
显示结果如下:
slick.driver.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$7@492691d7 slick.driver.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$7@27216cd slick.driver.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$7@558bdf1f slick.driver.JdbcActionComponent$UpdateActionExtensionMethodsImpl$$anon$7@8576fa0
现在每条DataRow已经被转化成jdbc action类型了。 下一步我们只需要运行这些ActionRow就可以完成任务了:
1 def execAction(act: FDAAction[Int]) = db.run(act) 2 3 loader.getTypedRows(selectAB.result)(db) 4 .map(updateAStatus(_)) 5 .map(execAction(_))
现在再看看数据库中的TA表状态:
loader.getTypedRows(selectAB.result)(db).foreach {dataRow => println(s"ID:${dataRow.id} Status A = ${dataRow.asts}, B = ${dataRow.bsts}") } 结果: ID:1 Status A = 1, B = 1 ID:2 Status A = 2, B = 2 ID:3 Status A = 3, B = 3 ID:4 Status A = 4, B = 4
我们看到已经正确更新了TA的status字段值。
在这个示范中明显有很多不足之处:如果a.status=b.status应该省略更新步骤。这是因为foreach只能模拟最基本的数据流动。如果我们使用了具备强大功能的Stream工具库如scalaz-stream-fs2,就可以更好控制数据元素的流动。更重要的是scalaz-stream-fs2支持并行运算,那么上面所描述的流程:
Database => Query -> Collection => Streaming -> DataRow => QueryAction(DataRow) -> ActionRow => execAction(ActionRow) -> Database
几个 => 环节:Query、Streaming、QueryAction、execAction将可以并行运算,从而实现充分利用多核CPU硬件资源,提高运算效率的目的。
下面是这次讨论涉及的源代码:
1 package com.bayakala.funda.rowtypes 2 3 import scala.concurrent.duration._ 4 import scala.concurrent.Await 5 import slick.driver.JdbcProfile 6 7 object DataRowType { 8 class FDADataRow[SOURCE, TARGET](slickProfile: JdbcProfile,convert: SOURCE => TARGET){ 9 import slickProfile.api._ 10 11 def getTypedRows(slickAction: DBIO[Iterable[SOURCE]])(slickDB: Database): Iterable[TARGET] = 12 Await.result(slickDB.run(slickAction), Duration.Inf).map(raw => convert(raw)) 13 } 14 15 object FDADataRow { 16 def apply[SOURCE, TARGET](slickProfile: JdbcProfile, converter: SOURCE => TARGET): FDADataRow[SOURCE, TARGET] = 17 new FDADataRow[SOURCE, TARGET](slickProfile, converter) 18 } 19 20 }
1 package com.bayakala.funda.rowtypes 2 import slick.dbio._ 3 object ActionType { 4 type FDAAction[T] = DBIO[T] 5 }
1 import slick.dbio.DBIO 2 import slick.driver.H2Driver.api._ 3 4 import scala.concurrent.duration._ 5 import scala.concurrent.{Await, Future} 6 import scala.util.{Failure, Success} 7 import scala.concurrent.ExecutionContext.Implicits.global 8 import slick.jdbc.meta.MTable 9 object ActionRowTest extends App { 10 11 class ATable(tag: Tag) extends Table[(Int,String,Int)](tag,"TA") { 12 def id = column[Int]("id",O.PrimaryKey) 13 def flds = column[String]("aflds") 14 def status = column[Int]("status") 15 def * = (id,flds,status) 16 } 17 val tableA = TableQuery[ATable] 18 19 class BTable(tag: Tag) extends Table[(Int,String,Int)](tag,"TB") { 20 def id = column[Int]("id",O.PrimaryKey) 21 def flds = column[String]("bflds") 22 def status = column[Int]("status") 23 def * = (id,flds,status) 24 } 25 val tableB = TableQuery[BTable] 26 27 val insertAAction = 28 tableA ++= Seq ( 29 (1,"aaa",0), 30 (2,"bbb",3), 31 (3,"ccc",1), 32 (4,"ddd",0), 33 (16,"kkk",16) 34 ) 35 val insertBAction = 36 tableB ++= Seq ( 37 (1,"aaa",1), 38 (2,"bbb",2), 39 (3,"ccc",3), 40 (4,"ddd",4), 41 (5,"kkk",5) 42 ) 43 44 val db = Database.forConfig("h2db") 45 46 47 def tableExists(tables: Vector[MTable], tblname: String) = 48 tables.exists {t =>t.name.toString.contains(tblname)} 49 50 def createSchemaIfNotExists(): Future[Unit] = { 51 db.run(MTable.getTables).flatMap { 52 case tables if !tableExists(tables,".TA") && !tableExists(tables,".TB") => 53 println("Creating schemas for TA and TB...") 54 db.run((tableA.schema ++ tableB.schema).create) 55 case tables if !tableExists(tables,".TA") => 56 println("Creating schema for TA ...") 57 db.run(tableA.schema.create) 58 case tables if !tableExists(tables,".TB") => 59 println("Creating schema for TB ...") 60 db.run(tableB.schema.create) 61 case _ => 62 println("Schema for TA, TB already created.") 63 Future.successful() 64 } 65 } 66 67 def insertInitialData(): Future[Unit] = { 68 val cleanInsert = DBIO.seq( 69 tableA.delete, tableB.delete, 70 insertAAction, 71 insertBAction) 72 db.run(cleanInsert).andThen { 73 case Success(_) => println("Data insert completed.") 74 case Failure(e) => println(s"Data insert failed [${e.getMessage}]") 75 } 76 } 77 78 Await.ready(db.run(sql"DROP TABLE TA; DROP TABLE TB".as[String]),Duration.Inf) 79 80 val initResult = createSchemaIfNotExists().flatMap {_ => insertInitialData()} 81 Await.ready(initResult,Duration.Inf) 82 83 84 val selectAB = for { 85 a <- tableA 86 b <- tableB 87 if (a.id === b.id) 88 } yield (a.id,b.id,a.status,b.status) 89 90 case class ABRow (id: Int, asts: Int, bsts: Int) 91 def toABRow(raw: (Int,Int,Int,Int)) = ABRow(raw._1,raw._3,raw._4) 92 93 import com.bayakala.funda.rowtypes.DataRowType.FDADataRow 94 95 val loader = FDADataRow(slick.driver.H2Driver, toABRow _) 96 loader.getTypedRows(selectAB.result)(db).foreach {dataRow => 97 println(s"ID:${dataRow.id} Status A = ${dataRow.asts}, B = ${dataRow.bsts}") 98 } 99 100 import com.bayakala.funda.rowtypes.ActionType.FDAAction 101 def updateAStatus(row: ABRow): FDAAction[Int] = { 102 tableA.filter{r => r.id === row.id} 103 .map(_.status) 104 .update(row.bsts) 105 } 106 107 108 loader.getTypedRows(selectAB.result)(db).map(updateAStatus(_)).foreach { 109 actionRow => 110 println(s"${actionRow.toString}") 111 } 112 113 def execAction(act: FDAAction[Int]) = db.run(act) 114 115 loader.getTypedRows(selectAB.result)(db) 116 .map(updateAStatus(_)) 117 .map(execAction(_)) 118 119 loader.getTypedRows(selectAB.result)(db).foreach {dataRow => 120 println(s"ID:${dataRow.id} Status A = ${dataRow.asts}, B = ${dataRow.bsts}") 121 } 122 123 }
本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。
我来说两句