Description
The GSQL plugin enables lightweight access to database functionality. This plugin does NOT provide domain classes nor dynamic finders like GORM does.
Installation
The current version of griffon-gsql is 0.2
To install just issue the following command
griffon install-plugin gsql
Usage
Upon installation the plugin will generate the following artifacts at $appdir/griffon-app/conf:
- DataSource.groovy - contains the datasource and pool definitions. Its format is equal to GORM's requirements.
- BootstrapGsql.groovy - defines init/destroy hooks for data to be manipulated during app startup/shutdown.
A new dynamic method named withSql will be injected into all controllers, giving you access to a groovy.sql.Sql object, with which you'll be able to make calls to the database. Remember to make all calls to the database off the EDT otherwise your application may appear unresponsive when doing long computations inside the EDT.
Example
Follow these steps to create a simple application that displays data stored inside a database.
1. Create a new application named 'sample'
griffon create-app sample
 | Following steps must be executed inside the application's directory |
2. Install the GSQL plugin
griffon install-plugin gsql
3. Setup the database schema. Create the file griffon-app/resources/schema.ddl with the following content
DROP TABLE IF EXISTS persons; CREATE TABLE persons( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL );
 | DataBase Specific Content The schema.ddl file must contain RDBMS specific DDL statements |
4. Setup bootstrapping data. Edit griffon-app/conf/BootstrapGsql.groovy (We'll use the Griffon's team current roster, feel free to input your own data)
import groovy.sql.Sql class BootStrapGsql { def init = { Sql sql -> 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") persons.add(id: 4, name: "Guillaume", lastname: "Laforge") persons.add(id: 5, name: "Jim", lastname: "Shingler") persons.add(id: 6, name: "Josh", lastname: "Reed") } def destroy = { Sql sql -> } }
5. Download a copy of GlazedLists' latest stable release. Place it under $appdir/lib. Or install the Glazedlists Plugin.
6. Edit griffon-app/models/SampleModel.groovy by adding a personList property.
import groovy.beans.Bindable import ca.odell.glazedlists.EventList import ca.odell.glazedlists.BasicEventList import ca.odell.glazedlists.SortedList class SampleModel { EventList personsList = new SortedList(new BasicEventList(), {a, b -> a.id <=> b.id} as Comparator) }
GlazedLists' EventList simplifies working with JList, JTable and their models as you will soon find out.
7. Edit griffon-app/views/SampleView.groovy. We'll add a scrollPane and a table. The Table's model will be tied to model.personsList.
import ca.odell.glazedlists.* import ca.odell.glazedlists.gui.* import ca.odell.glazedlists.swing.* def createTableModel() { def columnNames = ["Id", "Name", "Lastname"] new EventTableModel(model.personsList, [ getColumnCount: {columnNames.size()}, getColumnName: {index -> columnNames[index]}, getColumnValue: {object, index -> object."${columnNames[index].toLowerCase()}" }] as TableFormat) } application(title: 'Gsql - sample', size: [320,240], 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]) { scrollPane { table(id: "personsTable", model: createTableModel()) new TableComparatorChooser(personsTable, model.personsList, AbstractTableComparatorChooser.SINGLE_COLUMN) } }
If you installed the Glazedlists plugin then the view code can be simplified further
application(title: 'Gsql',
size: [320, 240],
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]) {
scrollPane {
table(id: 'personsTable') {
tableFormat = defaultTableFormat(columnNames: ['Id', 'Name', 'Lastname'])
eventTableModel(source: model.personsList, format: tableFormat)
installTableComparatorChooser(source: model.personsList)
}
}
}
8. Finally edit griffon-app/controllers/SampleController.groovy. The controller will react to an application event, load the data into a temporal List then update model.personsList inside the EDT. Application event handlers are guaranteed to run outside of the EDT.
class SampleController {
def model
def onStartupEnd = { app ->
withSql { sql ->
def tmpList = []
sql.eachRow("SELECT * FROM persons") {
tmpList << [id: it.id,
name: it.name,
lastname: it.lastname]
}
edt { model.personsList.addAll(tmpList) }
}
}
}
9. Run the application by typing
griffon run-app
You should get a similar result as shown on the following image
