代码之家 › 专栏 › 技术社区 › MattGrommes

如何在Groovy中使用PL/SQL来更新变量?

ora-17004 plsql groovy oracle sql

MattGrommes · 技术社区 · 15 年前

我得到了下面的小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

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')

3 回复 | 直到 13 年前

Erich Kitzmueller 15 年前

尝试以下操作(我希望没有引入语法错误,这里没有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())

thoredge 12 年前

有类似问题的开发人员延迟回答。

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

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

... 它将sqlLine创建为GStringImpl对象。如果您像这样声明sqlLine:

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

Luixv 14 年前

实际上,您可以通过以下操作从数据源读取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

我用甲骨文约会和 时间戳

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;