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'

    Example Oracle connection:

                import oracle.jdbc.pool.OracleDataSource
                OracleDataSource ds = new OracleDataSource()
                ds.user = 'ijc'
                ds.password = 'ijc'
                ds.driverType = 'thin'
                ds.serverName = ''
                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'
    Sql sql = new Sql(ds)
    sql.eachRow('select * from YOUR_TABLE') { row ->
    // do something with each row
    println row.SOME_COLUMN

    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.