在 Slick 中使用 DB 函数 (TRIM(LEADING '0' from column)) [英] Using DB Function (TRIM(LEADING '0' from column)) in Slick

问题描述

我想将 SQL 查询转换为 TableQuery:

I would like to translate an SQL query into a TableQuery:

SELECT ..., TRIM(LEADING '0' FROM mycolumn) FROM mytable WHERE TRIM(LEADING '0' FROM mycolumn) = '$key

应该变成MyTableQuery.filter(<_.mycolumn something something>)

我不能使用 implicit MappedColumnType 因为我从 String 映射到 String 并且有其他 String 列结果中.而且我不知道如何在 SELECT 和 WHERE

I cannot use an implicit MappedColumnType because I am mapping from String to String and have other String columns in the result. And I have no idea how I would use it in both SELECT and WHERE

对于 SELECT 部分，我创建了一个自定义函数，用于将结果元组映射到我的案例类，所以我只需要 WHERE 部分的解决方案.

For the SELECT part, I have created a custom function for mapping the result tuples to my case class, so I only need a solution for the WHERE part.

已阅读来自 SQL 和 <来自文档的 href="http://slick.typesafe.com/doc/3.1.1/userdefined.html" rel="nofollow">用户定义的功能 但不幸的是，我并不聪明.我想我可以使用 SimpleFunction.unary[String, String] 但还没有弄清楚如何使用.

Have read the Coming from SQL and User defined features from the docs but unfortunately, I am none the wiser. I suppose I could use a SimpleFunction.unary[String, String] but have not figured out how.

我会用 dropLeft 修剪一个常规的 String，但是 Rep[String] 没有这样的方法.ltrim 不接受参数.endsWith 在我的情况下是模棱两可的.

I would trim a regular String with dropLeft, however Rep[String] does not have such a method. ltrim does not accept a parameter. endsWith is ambiguous in my case.

我无法填充我的查询 String，因为并非数据库中的所有值都被填充并且具有不同的长度.

I cannot pad my query String, since not all values in the DB are padded and have different lengths.

我绝对可以使用普通 SQL 并映射结果，但我真的很想了解我在这里遗漏了什么.

I definitely can use plain SQL and map the results, but I would really like to understand what I am missing here.

我使用的是 Slick 3.1.1

I am using Slick 3.1.1

推荐答案

TRIM(LEADING) 的稍微奇怪的语法意味着您必须放弃使用 SimpleExpression 并使用可让您访问的 QueryBuilder.

The slightly odd syntax of TRIM(LEADING means you have to drop to using a SimpleExpression and use the QueryBuilder that gives you access to.

val trimLeading = SimpleExpression.binary[String, String, String] {
  (trimChar, str, queryBuilder) =>
    import slick.util.MacroSupport._
    import queryBuilder._
    b"TRIM(LEADING $trimChar FROM $str)"
}

这是一个练习它的例子

import com.typesafe.config.ConfigFactory
import slick.backend.DatabaseConfig
import slick.driver.JdbcProfile
import scala.concurrent.duration.Duration
import scala.concurrent.Await

object TrimLeading extends App {
  def trimLeadingExample(dbConfig: DatabaseConfig[JdbcProfile]): Unit = {
    import dbConfig.driver.api._
    val trimLeading = SimpleExpression.binary[String, String, String] {
      (trimChar, str, queryBuilder) =>
        import slick.util.MacroSupport._
        import queryBuilder._
        b"TRIM(LEADING $trimChar FROM $str)"
    }

    class ZeroTable(tag: Tag) extends Table[String](tag, "ZeroTable") {
      def zeros = column[String]("zeros")
      def * = zeros
    }
    val zeroTable = TableQuery[ZeroTable]
    exec(zeroTable.schema.create)
    exec(zeroTable ++= Seq("000000x", "00x", "000000000x", "00000x", "00xx"))

    exec(zeroTable.
          filter(s => trimLeading("0", s.zeros) === "x").
          map(s => trimLeading("0", s.zeros)).result).foreach(println)
    exec(zeroTable.schema.drop)
    def exec[T](action: DBIO[T]): T = Await.result(dbConfig.db.run(action), Duration.Inf)
  }
  val configStr =
    """
      |  driver = "freeslick.OracleProfile$"
      |  db {
      |    driver = oracle.jdbc.OracleDriver
      |    url="jdbc:oracle:thin:@//localhost:49161/xe"
      |    properties = {
      |      databaseName = "freeslicktest"
      |      user = "system"
      |      password = "oracle"
      |    }
      |  }
    """.stripMargin
  trimLeadingExample(DatabaseConfig.forConfig[JdbcProfile]("", ConfigFactory.parseString(configStr)))
}

从日志中，语句是

*** (s.jdbc.JdbcBackend.statement) 准备语句: select TRIM(LEADING '0' FROM "zeros") from "ZeroTable" where TRIM(LEADING '0' FROM "zeros") ='x'

这篇关于在 Slick 中使用 DB 函数 (TRIM(LEADING '0' from column))的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！