动态SQL参数与Anorm和Scala播放框架

前端之家收集整理的这篇文章主要介绍了动态SQL参数与Anorm和Scala播放框架前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
是否有可能为anorm的“on”方法动态创建一个列表?

我有一个带有可选输入的表单,目前我检查每个选项,并创建一个列表与定义的选项,并尝试将其传递给anorm.目前我收到这个编译错误

  1. type mismatch; found : List[java.io.Serializable] required: (Any,anorm.ParameterValue[_])

我不知道如何创建这个列表.
当前代码

  1. val onList = List(
  2. 'school_id = input.school,if(input.rooms isDefined) ('rooms -> input.rooms) else "None",if(input.bathrooms isDefined) ('bathrooms -> input.bathrooms) else "None",if(input.houseType isDefined) ('houseType -> input.houseType) else "None",if(input.priceLow isDefined) ('priceLow -> input.priceLow) else "None",if(input.priceHigh isDefined) ('priceHigh -> input.priceHigh) else "None",if(input.utilities isDefined) ('utilities -> input.utilities) else "None"
  3. ).filter(_!="None")
  4. sql("SELECT * FROM Houses WHERE " + whereString).on(onList).as(sqlToHouse *)

我试过这样做,因为最初我以为会是一样的

  1. .on('rooms -> input.rooms,'bathroom -> input.bathrooms... etc)

编辑:

代码现在是:

  1. val onList = Seq(
  2. ('school_id -> input.school),if(input.rooms isDefined) ('rooms -> input.rooms.get) else None,if(input.bathrooms isDefined) ('bathrooms -> input.bathrooms.get) else None,if(input.houseType isDefined) ('houseType -> input.houseType.get) else None,if(input.priceLow isDefined) ('priceLow -> input.priceLow.get) else None,if(input.priceHigh isDefined) ('priceHigh -> input.priceHigh.get) else None,if(input.utilities isDefined) ('utilities -> input.utilities.get) else None
  3. ).filter(_!=None).asInstanceOf[Seq[(Any,anorm.ParameterValue[_])]]

使用sql命令:

  1. sql("SELECT * FROM Houses WHERE " + whereString).on(onList:_*).as(sqlToHouse *)

现在得到例外

  1. [ClassCastException: java.lang.Integer cannot be cast to anorm.ParameterValue]

解决方法

重要的是您必须创建ParameterValue类型的值.
这通常使用toParameterValue()函数完成.

一种方法是创建一个你平展的选项序列:

  1. val onList = Seq(
  2. Some('school_id -> input.school),input.rooms.map('rooms -> _),input.bathrooms.map('bathrooms -> _)
  3. ).flatten

然后可以将此序列映射到正确的值:

  1. sql(
  2. "SELECT * FROM Houses WHERE " + whereString
  3. ).on(
  4. onList.map(v => v._1 -> toParameterValue(v._2)): _*
  5. )

这可以简化为:

  1. val onList = Seq(
  2. Some('school_id -> input.school),input.bathrooms.map('bathrooms -> _)
  3. ).flatMap(_.map(v => v._1 -> toParameterValue(v._2)))
  4.  
  5. sql(
  6. "SELECT * FROM Houses WHERE " + whereString
  7. ).on(
  8. onList: _*
  9. )

或者也许最简单的解决方案是:

  1. val onList = Seq(
  2. Some('school_id -> toParameterValue(input.school)),input.rooms.map('rooms -> toParameterValue(_)),input.bathrooms.map('bathrooms -> toParameterValue(_))
  3. ).flatten
  4.  
  5. sql(
  6. "SELECT * FROM Houses WHERE " + whereString
  7. ).on(
  8. onList: _*
  9. )

猜你在找的MsSQL相关文章