scala – Slick – 更新完整对象或超过22列

发布时间：2020-12-16 08:56:25 所属栏目：安全来源：网络整理

导读：我有一个表user_permissions,它有46个权限列以及id和created_date.此表具有相应的UserPermissions类： class UserPermission(val id: Long,val createdDate: Option[Timestamp],val permission1: Boolean,val permission2: Boolean,... val permission46: Bo

我有一个表user_permissions,它有46个权限列以及id和created_date.此表具有相应的UserPermissions类：

class UserPermission(val id: Long,val createdDate: Option[Timestamp],val permission1: Boolean,val permission2: Boolean,...
  val permission46: Boolean)

和光滑的映射表

class UserPermissions(tag: Tag) extends Table[UserPermission](tag,"users_permissions") {
  def * = (
    id ::
    createdDate ::
    permission1 ::
    permission2 ::
    ...
    permission46 ::
    HNil).shaped <> (
    { case x => UserPermission(
         x(0),x(1),x(2),... x(47))
    },{
       UserPermission.unapply _
    }
  }
  ... <columns defined here>
)

现在我想更新由id标识的UserPermission集.我的功能是：

object UserPermissions {
  val userPermissions = TableQuery[UserPermissions]

  def update(userPermission: UserPermission)(implicit session: Session) = {
    userPermissions.filter(_.id === userPermission.id.get).update(userPermission)
  }
}

这不起作用并抛出异常：

play.api.Application$$anon$1: Execution exception[[SQLServerException: Cannot update identity column 'id'.]]

这是有意义的,因为Slick生成的SQL是：

update "users_permissions" set "id" = ?,"created_date" = ?,...

问题1
所以我的第一个问题是我无法使用光滑更新完整的UserPermission对象.如果我能解决这个问题,那就太好了.

由于我无法更新完整对象,因此我想要生成我想要更新的列,然后触发更新查询.代码如下所示：

def update(obj: UserPermission)(implicit session: Session) = {
    val query = for {
      p <- userPermissions
      if p.id === obj.id.get
    } yield (p.permission1,p.permission2,... p.permission46)
    query.update(obj.permission1,obj.permission2,... obj.permission46)
}

问题2现在光滑不会更新query.update()函数中的46列.它一次只能处理22列.如何更新UserPermissions对象？

我能想到的一个糟糕的解决方案是第一次更新22次,然后是22秒,然后是第三次查询中的2次.这将是我不想要的3 db更新查询.

我问题的任何解决方案？

依赖关系是：

scalaVersion := "2.11.4"

"com.typesafe.play" %% "play-slick" % "0.8.1"
"com.typesafe.slick" %% "slick-extensions" % "2.1.0"

解决方法

Stefan Zeiger,Slick领先,said我们做不到.但他建议我们在平22列表上嵌套投影：

// 2 classes for the nested structure
case class Part(i1: Int,i2: Int,i3: Int,i4: Int,i5: Int,i6: Int)
case class Whole(id: Int,p1: Part,p2: Part,p3: Part,p4: Part)

// Note that it's a Table[Int] -- we only map the primary key in *
object T extends Table[Int]("t_wide") {
  def id = column[Int]("id",O.PrimaryKey)
  def p1i1 = column[Int]("p1i1")
  def p1i2 = column[Int]("p1i2")
  def p1i3 = column[Int]("p1i3")
  def p1i4 = column[Int]("p1i4")
  def p1i5 = column[Int]("p1i5")
  def p1i6 = column[Int]("p1i6")
  def p2i1 = column[Int]("p2i1")
  def p2i2 = column[Int]("p2i2")
  def p2i3 = column[Int]("p2i3")
  def p2i4 = column[Int]("p2i4")
  def p2i5 = column[Int]("p2i5")
  def p2i6 = column[Int]("p2i6")
  def p3i1 = column[Int]("p3i1")
  def p3i2 = column[Int]("p3i2")
  def p3i3 = column[Int]("p3i3")
  def p3i4 = column[Int]("p3i4")
  def p3i5 = column[Int]("p3i5")
  def p3i6 = column[Int]("p3i6")
  def p4i1 = column[Int]("p4i1")
  def p4i2 = column[Int]("p4i2")
  def p4i3 = column[Int]("p4i3")
  def p4i4 = column[Int]("p4i4")
  def p4i5 = column[Int]("p4i5")
  def p4i6 = column[Int]("p4i6")
  // This is just the default projection -- It doesn't have to contain all columns
  def * = id
  // Instead,we use nested tuples for a full projection:
  def all = (
    id,(p1i1,p1i2,p1i3,p1i4,p1i5,p1i6),(p2i1,p2i2,p2i3,p2i4,p2i5,p2i6),(p3i1,p3i2,p3i3,p3i4,p3i5,p3i6),(p4i1,p4i2,p4i3,p4i4,p4i5,p4i6)
  )
  // And override create_* to get the DDL for all columns.
  // Yeah,this is ugly. It used to be much simpler in ScalaQuery.
  // We can add a helper method to simplify it.
  override def create_* =
    all.shaped.packedNode.collect {
      case Select(Ref(IntrinsicSymbol(in)),f: FieldSymbol) if in == this => f
    }.toSeq.distinct
}

T.ddl.create
// Insert into T.all. The extra ".shaped" call is needed because we cannot
// get the types in an implicit conversion due to SI-3346
T.all.shaped.insert(
  0,(11,12,13,14,15,16),(21,22,23,24,25,26),(31,32,33,34,35,36),(41,42,43,44,45,46)
)

// Get the nested tuples in a query
val q1 = T.map(_.all)
println(q1.first)

// Map the result to the case classes
val i2 = q1.mapResult { case (id,p1,p2,p3,p4) =>
  Whole(id,Part.tupled.apply(p1),Part.tupled.apply(p2),Part.tupled.apply(p3),Part.tupled.apply(p4))
}
println(i2.first)

现在是Slick的test,包括version 3的一个.至于更新：

val oData = Whole(0,Part(11,Part(21,Part(31,Part(41,46)
)
val oData2 = Whole(10,Part(111,Part(121,Part(131,Part(141,46)
)

ts.ddl.create

ts.insert(oData)
assertEquals(oData,ts.first)

ts.filter(_.p1i2 === 12).update(oData2)
assertEquals(oData2,ts.first)

具有Slick投影的嵌套对象可以为您引入的单个对象展平,或者带走.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!