如何在 Groovy 中将 PL/SQL to_date 与变量一起使用？

Question

我有以下 Groovy 小脚本，它只计算数据库中特定日期的行数。

import groovy.sql.Sql

def today= new GregorianCalendar()
def dateString = "${today.get(Calendar.MONTH)+1}/${today.get(Calendar.DAY_OF_MONTH)-1}/${today.get(Calendar.YEAR)}"

def sql = Sql.newInstance("jdbc:oracle:thin:bc/bc@nemesis:1521:billctr", "bc","bc", "oracle.jdbc.OracleDriver")

def sqlLine = "select count(id) as count from bc_payment where trunc(paymentdate) = to_date(${dateString}, \'MM/DD/YYYY\')"
println(sqlLine)
def payCount = sql.execute(sqlLine)
println payCount

to_date 需要在传入的日期周围加上单引号。如果我不加它们，我会收到 SQLException: Invalid column type 但如果我在变量周围加上 \'，我会收到来自 Groovy 的

WARNING: In Groovy SQL please do not use quotes around dynamic expressions (which start with $) as this means we cannot use a JDBC PreparedStatement and so is a security hole. Groovy has worked around your mistake but the security hole is still there. The expression so far is: select count(id) as count from bc_payment where trunc(paymentdate) = to_date('?', 'MM/DD/YYYY')

警告在没有 to_date 或以不同方式格式化变量的情况下执行此操作的更好方法？我是 Groovy 的新手，所以欢迎提出任何建议。提前致谢！

Answer 1

尝试以下操作（我希望我没有引入语法错误，这里没有 Groovy...）

import groovy.sql.Sql

def today= new java.sql.Date(new java.util.Date().getTime())

def sql = Sql.newInstance("jdbc:oracle:thin:bc/bc@nemesis:1521:billctr", "bc","bc", "oracle.jdbc.OracleDriver")

def sqlLine = "select count(id) as count from bc_payment where trunc(paymentdate) = ?"
println(sqlLine)
def payCount = sql.execute(sqlLine, [today])
println payCount

编辑：替换

def today = new Date()

为

def today= new java.sql.Date(new java.util.Date().getTime())

Answer 2

有类似问题的开发人员较晚回答。

我发现问题可以通过更改声明来解决：

def sqlLine = "... ${yourString} ..."

...它将 sqlLine 创建为 GStringImpl 对象。相反，如果您像这样声明 sqlLine：

String sqlLine = "... ${yourString} ..."

... 我们会内联解析变量并接收一个 String 对象。这样 groovy.sql.Sql 永远不会知道我们动态创建了 sql。

Answer 3

实际上，您可以执行以下操作从数据源读取 sql 实例参数：

def _url      = ConfigurationHolder.config.dataSource.url
def _username = ConfigurationHolder.config.dataSource.username
def _password = ConfigurationHolder.config.dataSource.password
def _driver   = ConfigurationHolder.config.dataSource.driverClassName

def sql = Sql.newInstance(_url, _username, _password, _driver)

// For the paging
def int max    = Math.min(params.max ? params.max.toInteger() : 25,  100)
def int offset = params.offset.toInteger()
def int last   = offset + max

def month= params.month_value

我使用 Oracle TO_DATE 和 TO_TIMESTAMP 函数。就我而言，情况如下：

query = "select * from " +
          "(SELECT  reporting.id, " +
          "company_id as comp, " +      
          "to_date(TO_CHAR(invoice,'dd.mm.YYYY')) as invoice, " +
          "TO_CHAR(last_updated,'dd.mm.YYYY HH:MI') as erstelltAm, " +
          "row_number() over (" + sortByStr + ") as row_num FROM reporting, company " +
          "WHERE reporting.company_id = company.id) " +
              "reporting.month = TO_TIMESTAMP(" + month + ", 'dd.mm.yy')""
        "where ROW_NUM between " + offset + " and " + last;