Databases with groovy.sql

SQL injection: GStrings passed to Sql methods become parameterized statements. Don't pre-build strings with + or .toString(). Use Sql.expand(...) only for trusted identifiers.

ORMs: GORM (Grails, Hibernate/MongoDB), Hibernate/JPA directly, jOOQ, Exposed (Kotlin), MyBatis.

@Grab('org.xerial:sqlite-jdbc:3.46.0.0')
@GrabConfig(systemClassLoader = true)
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:sqlite:app.db', 'org.sqlite.JDBC')
// Sql.withInstance(url, user, pass, driver) { sql -> ... } auto-closes

sql.execute '''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INT)'''

def name = "Ana"
sql.execute "INSERT INTO users (name, age) VALUES ($name, ${30})"   // GString → PreparedStatement (safe!)
sql.executeInsert "INSERT INTO users (name, age) VALUES (?, ?)", ["Bo", 25]
def keys = sql.executeInsert("INSERT INTO users (name, age) VALUES (:n, :a)", [n: "Cy", a: 40])

sql.eachRow("SELECT * FROM users WHERE age > ?", [20]) { row ->
    println "${row.id}: ${row.name} (${row.age})"
}
def rows = sql.rows("SELECT name, age FROM users ORDER BY age")
println rows*.name
def first = sql.firstRow("SELECT COUNT(*) AS cnt FROM users")
println first.cnt
println sql.executeUpdate("UPDATE users SET age = age + 1 WHERE name = ?", ["Ana"])

sql.withTransaction {
    sql.execute "UPDATE users SET age = 0 WHERE id = 1"
    sql.execute "DELETE FROM users WHERE age < 0"
}
sql.withBatch(100, "INSERT INTO users (name, age) VALUES (?, ?)") { ps
…