Groovy SQL Database Connection Examples

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!