在Groovy的SQL查询中使用字符串变量

Question

我试图运行一个sql查询在脚本的其他地方定义的变量。当对数据库运行查询时，它只使用值?，而不是脚本前面定义为String的期望值。

我大致尝试如下:

def quickFilterQuery = """
SELECT "NAME","QUERY","RAPID_VIEW_ID" from "AO_60DB71_QUICKFILTER" WHERE "QUERY" like '%${CUSTOM_FIELD_NAME}%';
"""
log.warn "QF results: $quickFilterQuery"     
...
try {
StringBuffer slr = new StringBuffer()
sql.eachRow(quickFilterQuery) { it -> 

结果是:

WARN [runner.ScriptBindingsManager]: QF results: 
SELECT "NAME","QUERY","RAPID_VIEW_ID" from "AO_60DB71_QUICKFILTER" WHERE "QUERY" like '%Ontology%';

WARN [sql.Sql]: Found 0 parameter placeholders but supplied with 1 parameters
WARN [sql.Sql]: Failed to execute: 
SELECT "NAME","QUERY","RAPID_VIEW_ID" from "AO_60DB71_QUICKFILTER" WHERE "QUERY" like '%?%';

日志记录正确地捕获了变量应该是什么，但是查询错误似乎没有正确使用变量。

当我不使用变量(只是字符串)时，查询是否按预期工作。我想我的格式是关闭的，但我怎么也找不到问题。

Answer 1

V1

在执行quickFilterQuery之前将其转换为字符串。这种方法有sql注入问题——如果CUSTOM_FIELD_NAME包含quote'char

，它将无法工作。

def CUSTOM_FIELD_NAME = "Ontology"
def quickFilterQuery = """
SELECT "NAME","QUERY","RAPID_VIEW_ID" from "AO_60DB71_QUICKFILTER" WHERE "QUERY" like '%${CUSTOM_FIELD_NAME}%';
""" as String
sql.eachRow(quickFilterQuery){...}

V2

保持quickFilterQuery为GSteing的更好方法-因此，用于执行和传递查询参数的预处理语句(带?)

你必须去掉${CUSTOM_FIELD_NAME}周围的引号

def CUSTOM_FIELD_NAME = "%Ontology%"
def quickFilterQuery = """
SELECT "NAME","QUERY","RAPID_VIEW_ID" from "AO_60DB71_QUICKFILTER" WHERE "QUERY" like ${CUSTOM_FIELD_NAME};
"""
sql.eachRow(quickFilterQuery){...}