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.