Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

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.8
To install just issue the following command

Code Block
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.
This method is aware of multiple datasources. If no datasourceName is specified when calling it then the default dataSource will be selected. Here are two example usages, the first queries against the default datasource while the second queries a datasource whose name has been configured as 'internal'

Code Block
package sample
class SampleController {
    def queryAllDataSources = {
        withSql { dsName, sql -> ... }
        withSql('internal') { dsName, sql -> ... }
    }
}

This method is also accessible to any component through the singleton griffon.plugins.datasource.DataSourceConnector. You can inject these methods to non-artifacts via metaclasses. Simply grab hold of a particular metaclass and call DataSourceConnector.enhance(metaClassInstance).

Configuration

Dynamic method injection

The withSql() dynamic method will be added to controllers by default. You can change this setting by adding a configuration flag in Config.groovy

Code Block
griffon.datasource.injectInto = ["controller", "service"]

Events

The following events will be triggered by this addon

  • DataSourceConnectStart[config, dataSourceName] - triggered before connecting to the datastore
  • DataSourceConnectEnd[dataSourceName, dataSource] - triggered after connecting to the datastore
  • DataSourceDisconnectStart[config, dataSourceName, dataSource] - triggered before disconnecting from the datastore
  • DataSourceDisconnectEnd[config, dataSourceName] - triggered after disconnecting from the datastore

Multiple DataSources

The config file DataSource.groovy defines a default dataSource block. As the name implies this is the dataSource used by default, however you can configure named dataSources by adding a new config block. For example connecting to a dataSource whose name is 'internal' can be done in this way

Code Block
dataSources {
    internal {
        driverClassName = 'org.h2.Driver'
        username = 'sa'
        password = ''
        tokenizeddl = false // set this to true if using MySQL or any other
                            // RDBMS that requires execution of DDL statements
                            // on separate calls
        pool {
            maxWait = 60000
            maxIdle = 5
            maxActive = 8
        }
    }
}

This block can be used inside the environments() block in the same way as the default dataSource block is used.

Example

Follow these steps to create a simple application that displays data stored inside a database.

1. Create a new application named 'sample'

Code Block
griffon create-app sample
Info

Following steps must be executed inside the application's directory

2. Install the GSQL plugin

Code Block
griffon install-plugin gsql

3. Setup the database schema. Create the file griffon-app/resources/schema.ddl with the following content

Code Block
DROP TABLE IF EXISTS people;
CREATE TABLE people(
   id INTEGER NOT NULL PRIMARY KEY,
   name VARCHAR(30) NOT NULL,
   lastname VARCHAR(30) NOT NULL
);
Note
titleDataBase 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)

Code Block
import groovy.sql.Sql

class BootstrapGsql {
   def init = { String dataSourceName, Sql sql ->
      def people = sql.dataSet("people")
      people.add(id: 1, name: "Danno", lastname: "Ferrin")
      people.add(id: 2, name: "Andres", lastname: "Almiray")
      people.add(id: 3, name: "James", lastname: "Williams")
      people.add(id: 4, name: "Guillaume", lastname: "Laforge")
      people.add(id: 5, name: "Jim", lastname: "Shingler")
      people.add(id: 6, name: "Josh", lastname: "Reed")
   }

   def destroy = { String, dataSourceName, 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.

Code Block
import groovy.beans.Bindable
import ca.odell.glazedlists.EventList
import ca.odell.glazedlists.BasicEventList
import ca.odell.glazedlists.SortedList

class SampleModel {
   EventList peopleList = 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.peopleList.

Code Block
import ca.odell.glazedlists.*
import ca.odell.glazedlists.gui.*
import ca.odell.glazedlists.swing.*

def createTableModel() {
   def columnNames = ["Id", "Name", "Lastname"]
   new EventTableModel(model.peopleList, [
          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: "peopleTable", model: createTableModel())
    new TableComparatorChooser(peopleTable,
        model.peopleList, AbstractTableComparatorChooser.SINGLE_COLUMN)
  }
}

If you installed the Glazedlists plugin then the view code can be simplified further

Code Block
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: 'peopleTable') {
            tableFormat = defaultTableFormat(columnNames: ['Id', 'Name', 'Lastname'])
            eventTableModel(source: model.peopleList, format: tableFormat)
            installTableComparatorChooser(source: model.peopleList)
        }
    }
}

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.peopleList inside the EDT.

Code Block
class SampleController {
   def model

   def onStartupEnd = { app ->
      withSql { dataSourceName, sql ->
         def tmpList = []
         sql.eachRow("SELECT * FROM people") {
            tmpList << [id: it.id,
                        name: it.name,
                        lastname: it.lastname]
         }
         edt { model.peopleList.addAll(tmpList) }
      }
   }
}

9. Run the application by typing

Code Block
griffon run-app

You should get a similar result as shown on the following image

Full source code for this sample application can be found at https://github.com/aalmiray/griffon_sample_apps/tree/master/persistence/gsql

History

Version

Date

Notes

0.8

10-21-11

Release sync with Griffon 0.9.4

0.7

08-15-11

Added Datasource plugin as dependency

0.6.1

03-19-11

Fix GRIFFON-343

0.6

02-10-11

Release sync with Griffon 0.9.2

0.5.1

11-08-10

Fix a metaclass problem when injecting dynamic methods

0.5

10-27-10

Release sync with Griffon 0.9.1

0.4

07-22-10

Release sync with Griffon 0.9

0.3

03-01-10

Upgraded to Griffon 0.3

0.2

01-07-10

Removed schema generation via GSQL

0.1

09-10-09

Initial release