Connect to a Database

Using a script to read data directly from a database is another way to access information. This could be used for data migration, or for updating an IJC database in a routine manner. Accessing databases from Groovy is rather simple (see the Groovy groovy.sql.SQL class).

One aspect of this deserves attention - setting up the JDBC connection to the database. Some strange aspects of how the class loading is done means that the standard connection mechanism (Connection conn = DriverManager.getConnection(url, username, password)) will not work. Instead, we suggest you obtain connections by creating a DataSource. Suggested connection scriptlets are shown below.

Example Derby connection:

    import org.apache.derby.jdbc.EmbeddedDataSource40
    EmbeddedDataSource40 ds = new EmbeddedDataSource40()
    ds.databaseName = 'C:/Users/username/Documents/IJCProjects/ijc-project1/.config/localdb/db'

Example MySQL connection:

    import com.mysql.jdbc.jdbc2.optional.MysqlDataSource
    MysqlDataSource ds = new MysqlDataSource()
    ds.user = 'ijc'
    ds.password = 'ijc'
    ds.url='jdbc:mysql://localhost/ijc'    

Example Oracle connection:

    import oracle.jdbc.pool.OracleDataSource
    OracleDataSource ds = new OracleDataSource()
    ds.user = 'ijc'
    ds.password = 'ijc'
    ds.driverType = 'thin'
    ds.serverName = '192.168.1.21'
    ds.portNumber = 1521
    ds.databaseName = 'orcl' // sid

This scriptlet is a simple example of how to read data in from a table in a database:

import groovy.sql.*

MysqlDataSource ds = new MysqlDataSource()
ds.user = 'ijc'
ds.password = 'ijc'
ds.url='jdbc:mysql://localhost/ijc'

Sql sql = new Sql(ds)

sql.eachRow('select * from YOUR_TABLE') { row ->
    // do something with each row
    println row.SOME_COLUMN
}

sql.close()

It is important to note that the demo scriptlet ends with closing the sql connection. This is a good practice, if not important. Also note that these databases do NOT have be IJC databases - they can be any accessible database.