在SQL GString Query中使用扩展变量
使用groovy.sql.Sql
类可以很容易地使用Groovy代码中的SQL数据库。 该类有几种方法来执行SQL查询,但是如果我们使用Sql
中带有GString
参数的方法,我们必须特别小心。Groovy将提取所有变量表达式,并将它们用作从SQL查询构造的PreparedStatement
中占位符的值。 如果我们有变量表达式不应该被提取为PreparedStatement
的参数,我们必须使用Sql.expand
方法。 此方法将使变量表达式成为groovy.sql.ExpandedVariable
对象。 此对象不用作PreparedStatement
查询的参数,但该值被评估为GString
变量表达式。
在下面的示例中,我们有一个类,它使用GString
查询值调用Sql
对象的几个方法。 我们可以看到何时使用Sql.expand
以及何时不需要:
package mrhaki
import groovy.sql.*
class SampleDAO {
private static final String TABLE_NAME = 'sample'
private static final String COLUMN_ID = 'id'
private static final String COLUMN_NAME = 'name'
private static final String COLUMN_DESCRIPTION = 'description'
private final Sql sql =
Sql.newInstance(
'jdbc:h2:test', 'sa', 'sa', 'org.h2.Driver')
Long create() {
// We need to use Sql.expand() in our GString query.
// If we don't use it the GString variable expressions are interpreted
// as a placeholder in a SQL prepared statement, but we don't
// that here.
final query =
"""
INSERT INTO ${Sql.expand(TABLE_NAME)} DEFAULT VALUES
"""
final insertedKeys = sql.executeInsert(query)
return insertedKeys[0][0]
}
void updateDescription(final Long id, final String description) {
// In the following GString SQL we need
// Sql.expand(), because we use executeUpdate
// with only the GString argument.
// Groovy will extract all variable expressions and
// use them as the placeholders
// for the SQL prepared statement.
// So to make sure only description and id are
// placeholders for the prepared statement we use
// Sql.expand() for the other variables.
final query =
"""
UPDATE ${Sql.expand(TABLE_NAME)}
SET ${Sql.expand(COLUMN_DESCRIPTION)} = ${description}
WHERE ${Sql.expand(COLUMN_ID)} = ${id}
"""
sql.executeUpdate(query)
}
void updateName(final Long id, final String name) {
// In the following GString SQL we don't need
// Sql.expand(), because we use the executeUpdate
// method with GString argument AND argument
// with values for the placeholders.
final query =
"""
UPDATE ${TABLE_NAME}
SET ${COLUMN_NAME} = :nameValue
WHERE ${COLUMN_ID} = :idValue
"""
sql.executeUpdate(query, nameValue: name, idValue: id)
}
}
用Groovy 2.5.4编写。