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.