But, what if you have a simple standalone process that requires single or multiple database access? Currently GORM has trouble working outside of grails, and creating a new grails project just to support a simple, no-client script is overkill. For that, I use groovy's database access classes in groovy.sql and a small custom library to easily access multiple data sources and provide simple query tools optimized for batch operations.
Here's an example: Tracking multiple UPS shipments. The application must access existing shipment requests from a local database, then query the UPS tracking remote service to discover shipment status. When a shipment is picked up or delivered, another local database is updated and a third system is updated via XML feed.
To summarize, the requirements are:
- provide simultaneous access to multiple databases
- query UPS tracking via HTTP requests
- create XML responses and write to a messaging system
As always, I start with the tests. Using groovy test case, the database queries, inserts, and updates tests were created, then the target classes and methods. Queries are always the easiest, and updates are straight forward as well. Inserts with auto generated IDs are a bit tricker, but here is where groovy sql comes to the rescue.
Creating new tables presented a new problem: should I create SQL/DDL scripts? No! For this, it was easy enough to access the current grails application and create the new domain classes, run grails to have Hibernate create the new tables.
The groovy Sql class provides a method called executeInsert(). The magic in this method is that it returns an array of all auto-generated keys. So, if I'm working in MySql or Oracle or whatever, inserts all look the same--no database specific code required.
Database access defaults to the production environment but database tests must operate on the test database (similar to grails). This is easy to accomplish by simply overriding the default behavior in the test scripts.
- Use groovy Sql for small, non-web applications that require single or multiple database access.
- Use Grails/GORM for all database/table creation and maintenance (no DDL scripts). This also has the additional benefit of enabling access to new tables through grails if required.
- Use executeInsert() to return auto-generated keys when inserting new rows.