Scala Slick查询中的where in列表

Question

Scala Slick查询中的where in列表

29

我正在尝试学习使用Slick查询MySQL。我已经成功地使用以下类型的查询来获取单个Visit对象:

Q.query[(Int,Int), Visit]("""
    select * from visit where vistor = ? and location_code = ?
""").firstOption(visitorId,locationCode)

我想知道怎样修改上述查询以获取一组位置的 List[Visit]，就像这样：

val locationCodes = List("loc1","loc2","loc3"...)
Q.query[(Int,Int,List[String]), Visit]("""
    select * from visit where vistor = ? and location_code in (?,?,?...)
""").list(visitorId,locationCodes)

Slick 可以实现这个吗？

- ShatyUT

这个不起作用吗？应该可以的。 - Dominic Bou-Samra

你不能坚持使用值元组吗？这将确保您传递给查询的参数数量是固定的。 - pagoda_5b

4个回答

5

它不起作用是因为StaticQuery对象(Q)期望隐式地设置查询字符串中的参数，使用query方法的类型参数创建一种setter对象(类型为scala.slick.jdbc.SetParameter[T])。
SetParameter[T]的角色是将查询参数设置为类型为T的值，所需类型取自query[...]类型参数。

据我所见，对于通用的A，没有为T = List[A]定义这样的对象，这似乎是一个明智的选择，因为您实际上无法编写具有动态参数列表的sql查询，用于IN (?, ?, ?,...)子句。

我通过以下代码提供了一个这样的隐式值进行实验。

import scala.slick.jdbc.{SetParameter, StaticQuery => Q}

def seqParam[A](implicit pconv: SetParameter[A]): SetParameter[Seq[A]] = SetParameter {  
    case (seq, pp) =>
        for (a <- seq) {
            pconv.apply(a, pp)
        }
}

implicit val listSP: SetParameter[List[String]] = seqParam[String]

在这个范围内，您应该能够执行您的代码。

val locationCodes = List("loc1","loc2","loc3"...)
Q.query[(Int,Int,List[String]), Visit]("""
    select * from visit where vistor = ? and location_code in (?,?,?...)
""").list(visitorId,locationCodes)

但是，您必须始终手动确保 locationCodes 的大小与您的 IN 子句中的 ? 数量相同。

最后，我相信可以使用宏来创建更加干净的解决方案，以通用序列类型为基础。但是，考虑到序列大小的动态性问题，我不确定这是否是框架的明智选择。

- pagoda_5b

3

如果您有一个复杂的查询，且上述的for comprehension不是一个选项，您可以在Slick 3中使用以下类似的方法。但是，您需要确保自己验证列表查询参数中的数据，以避免SQL注入：

val locationCodes = "'" + List("loc1","loc2","loc3").mkString("','") + "'"
sql"""
  select * from visit where visitor = $visitor 
    and location_code in (#$locationCodes)
"""

在变量引用前面添加 # 可以禁用类型验证，并允许您在不提供列表查询参数的隐式转换函数的情况下解决此问题。

- markus

3

您可以像这样自动生成in子句：

  def find(id: List[Long])(implicit options: QueryOptions) = {
    val in = ("?," * id.size).dropRight(1)
    Q.query[List[Long], FullCard](s"""
        select 
            o.id, o.name 
        from 
            organization o
        where
            o.id in ($in)
        limit
            ?
        offset
            ?
            """).list(id ::: options.limits)
  }

并使用隐式SetParameter作为pagoda_5b says

  def seqParam[A](implicit pconv: SetParameter[A]): SetParameter[Seq[A]] = SetParameter {
    case (seq, pp) =>
      for (a <- seq) {
        pconv.apply(a, pp)
      }
  }

  implicit def setLongList = seqParam[Long]

- caiiiycuk

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Faiz · Accepted Answer

正如另一个答案所建议的那样，使用静态查询进行此操作很繁琐。静态查询接口要求您将绑定参数描述为 Product。（Int，Int，String *） 不是有效的Scala代码，而使用 （Int，Int，List [String]） 也需要一些不良处理方式。此外，必须确保 locationCodes.size 始终等于查询中的 (?, ?...) 的数量，这是脆弱的。

实际上，这并不是太大的问题，因为您应该使用查询单子（query monad）来解决，这是使用Slick的类型安全和推荐方式。

val visitorId: Int = // whatever
val locationCodes = List("loc1","loc2","loc3"...)
// your query, with bind params.
val q = for {
    v <- Visits 
    if v.visitor is visitorId.bind
    if v.location_code inSetBind locationCodes
  } yield v
// have a look at the generated query.
println(q.selectStatement)
// run the query
q.list

假设您的表格设置如下：

case class Visitor(visitor: Int, ... location_code: String)

object Visitors extends Table[Visitor]("visitor") {
  def visitor = column[Int]("visitor")
  def location_code = column[String]("location_code")
  // .. etc
  def * = visitor ~ .. ~ location_code <> (Visitor, Visitor.unapply _)
}

请注意，您始终可以将查询包装在方法中。

def byIdAndLocations(visitorId: Int, locationCodes: List[String]) = 
  for {
    v <- Visits 
    if v.visitor is visitorId.bind
    if v.location_code inSetBind locationCodes
  } yield v
}

byIdAndLocations(visitorId, List("loc1", "loc2", ..)) list