Griffon + GSQL mini howto

1. Create a datasource script similar to those found in Grails, place it at griffon-app/conf/DataSource.groovy
dataSource { url = "jdbc:hsqldb:mem:devDB" driverClassname = "org.hsqldb.jdbcDriver" username = "sa" password = "" } 2. Create a database schema script, this will be used to generate tables and columns, place it at griffon-app/conf/DatabaseSchema.groovy. Mind the magic builder variable, it will be explained shortly
builder.database(name: "test") { table(name: "persons") { column(name: "id", type: "integer", required: true) column(name: "name", type: "varchar", size: 30, required: true) column(name: "lastname", type: "varchar", size: 30, required: true) } } 3. Create a script that contains data used to populate the database, place it at griffon-app/conf/Bootstrap.groovy. Mind the magic sql variable, it will also be explained in the following step
def persons = sql.dataSet("persons") persons.add(id: 1, name: "Danno", lastname: "Ferrin") persons.add(id: 2, name: "Andres", lastname: "Almiray") persons.add(id: 3, name: "James", lastname: "Williams") 4. Create a DbService class under griffon-app/services/DbService.groovy with the following contents
import groovy.sql.Sql import org.javanicus.gsql.* @Singleton class DbService { private sql def initDataSource( dataSource ) { sql = Sql.newInstance( dataSource.url, dataSource.username, dataSource.password, dataSource.driverClassname ) // read database schema def typeMap = new TypeMap() def binding = new Binding() binding.setVariable("builder", new RelationalBuilder(typeMap)) def database = new DatabaseSchema(binding).run() // create database schema def sqlGenerator = new SqlGenerator(typeMap,System.getProperty("line.separator","\n")) def writer = new StringWriter() sqlGenerator.writer = writer sqlGenerator.createDatabase(database,false) writer.flush() sql.execute(writer.toString()) // populate database binding = new Binding() binding.setVariable("sql", sql) new Bootstrap(binding).run() } def getSql() { sql } }A few things to notice here:

• DbService is implemented as a singleton, this task is dead simple thanks to @Singleton
• DbService.initDataSource() performs 3 tasks:
  1. initializes a database connection.
  2. generates the database schema.
  3. populates the database with bootstrap data.

5. We must instruct DbService to load the database, the earliest opportunity is after the application has bootstrapped itself, we can react to an event (BootstrapEnd) by placing the following code in griffon-app/conf/Events.groovy
onBootstrapEnd = { app -> def dataSource = new ConfigSlurper().parse(DataSource).dataSource DbService.instance.initDataSource(dataSource) }Here is how step #1 joins the rest, parsing configuration files with Groovy is very simple. This script is executed outside of the EDT always.

6. Let's create the view, a basic form with 2 textFields (name & lastname) that will be used to add a new person to the list of available persons, plus a table view displaying all persons
import ca.odell.glazedlists.* import ca.odell.glazedlists.gui.* import ca.odell.glazedlists.swing.* import net.miginfocom.swing.MigLayout def createTableModel() { def columnNames = ["Id", "Name", "Lastname"] new EventTableModel(model.persons, [ getColumnCount: {columnNames.size()}, getColumnName: {index -> columnNames[index]}, getColumnValue: {object, index -> object."${columnNames[index].toLowerCase()}" }] as TableFormat) } actions { action(id: "addPersonAction", name: "Add", enabled: bind{ model.enabled }, closure: controller.addPerson) } application(title: "Simple DB", size:[320,400], locationByPlatform: true, iconImage: imageIcon('/griffon-icon-48x48.png').image, iconImages: [imageIcon('/griffon-icon-48x48.png').image, imageIcon('/griffon-icon-32x32.png').image, imageIcon('/griffon-icon-16x16.png').image] ) { panel(layout: new MigLayout("fill","[left]")) { label("Person", constraints: "split, span, gaptop 10") separator( constraints: "growx, wrap, gaptop 10") label("Name:") textField(id: "name", columns: 30, constraints: "wrap", text: bind(target: model, targetProperty: "name", mutual: true)) label("Lastname:") textField(id: "lastname", columns: 30, constraints: "wrap", text: bind(target: model, targetProperty: "lastname", mutual: true)) button(addPersonAction, constraints: "span, grow, wrap") label("Persons", constraints: "split, span, gaptop 10") separator( constraints: "growx, wrap, gaptop 10") scrollPane(constraints: "span, grow") { table(id: "personTable", model: createTableModel()) } new TableComparatorChooser(personTable, model.persons, AbstractTableComparatorChooser.SINGLE_COLUMN) } }Now it would be a good time to drop in additional dependencies required by this little application, place the following on your app's lib directory

• GSQL http://svn.codehaus.org/gmod/gsql
• glazedlists http://publicobject.com/glazedlists
• miglayout http://miglayout.com