Slick 2 - 更新表中的列并返回整个表对象 [英] Slick 2 - Update columns in a table and return whole table object

问题描述

在使用 slick 时，如何在返回整个更新表的同时更新表中的几列?

How would you update a few columns in a table table while returning the entire updated table when using slick?

假设 SomeTables 是一些 TableQuery，你通常会写一个这样的查询，例如，向表中添加一个项目(并返回新的添加项目)

Assuming SomeTables is some TableQuery, you would typically write a query like this if you want to, for example, add an item to the table (and returning the newly added item)

val returnedItem = SomeTables returning SomeTables += someTable

如果你想更新一个项目并返回整个项目，你会怎么做，我怀疑你会做这样的事情

How would you do the same if you want to update an item and return the whole back the whole item, I suspect you would do something like this

val q = SomeTables.filter(_.id === id).map(x => (x.someColumn,x.anotherColumn)) returning SomeTables
val returnedItem = q.update((3,"test"))

但是以下代码不起作用，我看不到任何有关如何执行此操作的文档

The following code however does not work, and I can't see any documentation on how to do this

请注意，我知道您可以预先查询项目，更新它，然后在原始对象上使用副本，但这需要大量样板(以及数据库行程)

Note that I am aware you can just query the item beforehand, update it, and then use copy on the original object, however this requires a lot of boilerplate (and DB trips as well)

推荐答案

Slick(v2 或 v3-M1)不支持此功能；虽然我没有看到任何禁止它实施的具体原因，UPDATE ... RETURNING 不是标准的 SQL 功能(例如，H2 不支持它:http://www.h2database.com/html/grammar.html#update).我将留给读者作为练习，以探索如何安全有效地模拟缺少 UDPATE ... RETURNING 的 RDBMS 的功能.

This feature is not supported in Slick (v2 or v3-M1); although I don't see any specific reason prohibiting it's implementation, UPDATE ... RETURNING is not a standard SQL feature (for example, H2 does not support it: http://www.h2database.com/html/grammar.html#update). I'll leave as an exercise to the reader to explore how one might safely and efficiently emulate the feature for RDBMSes lacking UDPATE ... RETURNING.

当您在 scala.slick.lifted.Query 上调用returning"时，它会给您一个 JdbcInsertInvokerComponent$ReturningInsertInvokerDef.尽管有一个 insertOrUpdate 方法，但您不会发现 update 方法；但是，insertOrUpdate 仅在发生插入时返回 returning 表达式结果，None 返回用于更新，因此这里没有帮助.

When you call "returning" on a scala.slick.lifted.Query, it gives you a JdbcInsertInvokerComponent$ReturningInsertInvokerDef. You'll find no update method, although there is an insertOrUpdate method; however, insertOrUpdate only returns the returning expression result if an insert occurs, None is returned for updates, so no help here.

由此我们可以得出结论，如果您想使用 UPDATE ... RETURNING SQL 功能，您要么需要使用 StaticQuery 或将您自己的补丁发布到 Slick.您可以手动编写查询(并将表投影重新实现为 GetResult/SetParameter 序列化程序)，或者您可以尝试以下代码片段:

From this we can conclude that if you want to use the UPDATE ... RETURNING SQL feature, you'll either need to use StaticQuery or roll your own patch to Slick. You can manually write your queries (and re-implement your table projections as GetResult / SetParameter serializers), or you can try this snippet of code:

package com.spingo.slick

import scala.slick.driver.JdbcDriver.simple.{queryToUpdateInvoker, Query}
import scala.slick.driver.JdbcDriver.{updateCompiler, queryCompiler, quoteIdentifier}
import scala.slick.jdbc.{ResultConverter, CompiledMapping, JdbcBackend, JdbcResultConverterDomain, GetResult, SetParameter, StaticQuery => Q}
import scala.slick.util.SQLBuilder
import slick.ast._

object UpdateReturning {
  implicit class UpdateReturningInvoker[E, U, C[_]](updateQuery: Query[E, U, C]) {
    def updateReturning[A, F](returningQuery: Query[A, F, C], v: U)(implicit session: JdbcBackend#Session): List[F] = {
      val ResultSetMapping(_,
        CompiledStatement(_, sres: SQLBuilder.Result, _),
        CompiledMapping(_updateConverter, _)) = updateCompiler.run(updateQuery.toNode).tree

      val returningNode = returningQuery.toNode
      val fieldNames = returningNode match {
        case Bind(_, _, Pure(Select(_, col), _)) =>
          List(col.name)
        case Bind(_, _, Pure(ProductNode(children), _)) =>
          children map { case Select(_, col) => col.name } toList
        case Bind(_, TableExpansion(_, _, TypeMapping(ProductNode(children), _, _)), Pure(Ref(_), _)) =>
          children map { case Select(_, col) => col.name } toList
      }

      implicit val pconv: SetParameter[U] = {
        val ResultSetMapping(_, compiled, CompiledMapping(_converter, _)) = updateCompiler.run(updateQuery.toNode).tree
        val converter = _converter.asInstanceOf[ResultConverter[JdbcResultConverterDomain, U]]
        SetParameter[U] { (value, params) =>
          converter.set(value, params.ps)
        }
      }

      implicit val rconv: GetResult[F] = {
        val ResultSetMapping(_, compiled, CompiledMapping(_converter, _)) = queryCompiler.run(returningNode).tree
        val converter = _converter.asInstanceOf[ResultConverter[JdbcResultConverterDomain, F]]
        GetResult[F] { p => converter.read(p.rs) }
      }

      val fieldsExp = fieldNames map (quoteIdentifier) mkString ", "
      val sql = sres.sql + s" RETURNING ${fieldsExp}"
      val unboundQuery = Q.query[U, F](sql)
      unboundQuery(v).list
    }
  }
}

我确信以上可以改进；我根据我对 Slick 内部结构的有限理解编写了它，它对我有用，并且可以利用您已经定义的投影/类型映射.

I'm certain the above can be improved; I've written it based on my somewhat limited understanding of Slick internals, and it works for me and can leverage the projections / type-mappings you've already defined.

用法:

import com.spingo.slick.UpdateReturning._
val tq = TableQuery[MyTable]
val st = tq filter(_.id === 1048003) map { e => (e.id, e.costDescription) }
st.updateReturning(tq map (identity), (1048003, Some("such cost")))

这篇关于Slick 2 - 更新表中的列并返回整个表对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！