Nov 10, 2016 SQL comments
When working with database in Groovy, the first thing we want to do is to connect to a database server. We will not be able to read or manipulate data unless we have a connection. Below will show a simple example on how to connect to a MySQL database in Groovy.
Database Connection
Groovy SQL utilizes JDBC when working with databases. When connecting to a database, we need these basic information:
- Connection URL - The url typically contains which server to connect to, what port and which database schema to work with. Example: "jdbc:mysql://localhost:3306/test"
- Username and password - The credentials for authenticating the connection. Example: username = root and password = secret.
- JDBC Driver Class - The JDBC driver class to use to connect to the database. Example for MySQL case: "com.mysql.jdbc.Driver"
Having these information, here is an example on how to connect to a database in Groovy:
package sample
import groovy.sql.Sql
import java.sql.*
/**
* A simple Groovy example program that connects to a MySQL database,
* execute a simple query, and display the results to the console.
*/
class Sample {
static void main(String[] args) {
// connect to db
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/test",
"root", "secret", "com.mysql.jdbc.Driver")
// execute a simple query
sql.eachRow("SELECT 'Connection successful!' "){ row ->
// print data returned by the query
println row[0]
}
// close the connection
sql.close()
}
}
The example above connects to a database, executes a query without referencing a table, and prints the result to the console. The output is shown below:
Connection successful!