「Groovy」- 连接数据库(使用 MySQL 演示)

  CREATE BY JENKINSBOT
原文链接:「Groovy」- 连接数据库(使用 MySQL 演示)
文章分类:「05.Programming_Language:6.Apache_Groovy:4.Package_Managers_and_Build_Tools:Database_Connection」
文章标识:「b2d63765」

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

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 驱动前,需要 配置 GrapeJDBC 驱动依赖 附加到 系统类加载器,即代码:

@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

参考文献