# 2020-10-02 #「Groovy」- 连接数据库（使用 MySQL 演示）

连接数据库，并查询第一条数据

Connecting to MySQL using Groovy - Stack Overflow

@GrabConfig(systemClassLoader=true)
@Grab('mysql:mysql-connector-java:8.0.21')
import java.sql.*; 
import groovy.sql.Sql

def connectionString = "jdbc:mysql://<ip-address>:<port-number>/<database>"
// jdbc:mysql://localhost/db?useUnicode=true&characterEncoding=UTF-8' // 
def username = "<username>"
def password = "<password>"
def driver = "com.mysql.jdbc.Driver"
def sqlInstance = Sql.newInstance(connectionString, username, password, driver)

// 查询第一条数据
def firstRow = sqlInstance.firstRow("select * from <database>.<table>")
print firstRow

指定连接使用 UTF-8 编码

How to set up MySQL on Windows to accept UTF-8 data via Groovy JDBC connections - Stack Overflow

jdbc:mysql://localhost/db?useUnicode=true&characterEncoding=UTF-8'

使用 Prepared Statement 语法

Sql (Groovy 2.5.4)
mysql - Insert multiple rows in one SQL statement in Groovy - Stack Overflow

解决数据转义、注入问题，代码如下：

def params = [10, 'Groovy', 'http://groovy.codehaus.org']
sql.execute('insert into PROJECT (id, name, url) values (?, ?, ?)', params)

插入多条数据：

def updateCounts = sql.withBatch('insert into TABLENAME(a, b, c) values (?, ?, ?)') { ps ->
     ps.addBatch([1, 2, 3])
     ps.addBatch([10, 20, 30])
     ps.addBatch(100, 200, 300) // 或者使用循环
}

如果不想处理数据库连接关闭

The Apache Groovy programming language - Working with a relational database

Sql.withInstance(url, user, password, driver) { sql ->
  // use 'sql' instance ...
  // i.e. sql.execute("sql statement")
}

相关链接

Groovy - Database - Tutorialspoint
Interacting with a SQL database

常见错误汇总

No suitable driver found for XXXXXXX

jdbc - No suitable driver found when connected to mysql in groovy - Stack Overflow
Dependency management with Grape
GrabConfig (Groovy 3.0.5)

在连接数据库时，产生如下错误：

Caught: java.sql.SQLException: No suitable driver found for jdbc:mysql://<ip-address>:<port-number>/<database>
java.sql.SQLException: No suitable driver found for jdbc:mysql://<ip-address>:<port-number>/<database>
        at demo.run(demo.groovy:6)

由于 JDBC 驱动的加载方式，在使用 JDBC 驱动前，需要 配置 Grape 将 JDBC 驱动依赖 附加到 系统类加载器，即代码：

@GrabConfig(systemClassLoader=true)

// Set to true if you want to use the system classloader when loading the grape. 
// This is normally only required when a core Java class needs to reference the 
// grabbed classes, e.g. for a database driver accessed using DriverManager.

或者在命令行中将 Jar 加入 ClassPath 中：

groovyconsole -cp mysql-connector-java-5.1.27-bin.jar

参考文献

K4NZ / 连接数据库（使用 MySQL 演示）
The Apache Groovy programming language - Working with a relational database