1. Introduction

The Sql2o plugin enables lightweight access to datasources using Sql2o. This plugin does NOT provide domain classes nor dynamic finders like GORM does.

Griffon version: 2.12.0

2. Usage

The following sections describe how you may use this plugin in a project.

2.1. Configuration

This plugin relies on the griffon-datasource-plugin. Please follow the instructions to configure this plugin first.

The plugin’s module registers a Sql2oHandler helper class that defines the base contract for accessing a datasource and issue SQL queries to it. This class has the following methods

griffon.plugins.sql2o.Sql2oHandler.java
@Nullable
<R> R withSql2o(@Nonnull Sql2oCallback<R> callback)
    throws RuntimeSql2oException;

@Nullable
<R> R withSql2o(@Nonnull String datasourceName, @Nonnull Sql2oCallback<R> callback)
    throws RuntimeSql2oException;

void closeSql2o();

void closeSql2o(@Nonnull String datasourceName);

These method are aware of multiple datasources. If no datasourceName is specified when calling them then the default datasource will be selected. You can inject an instance of this class anywhere it’s needed using @Inject. There is one callback you may use with this method: Sql2oCallback.

This callback is defined using a functional interface approach, which means you can apply lambda expressions if running with JDK8+ or closures if running Groovy.

griffon.plugins.sql2o.Sql2oCallback.java
public interface Sql2oCallback<R> {
    R handle(@Nonnull String datasourceName, @Nonnull Sql2o sql2o);
}

2.1.1. Bootstrap

You may execute arbitrary database calls during connection and disconnection from a datasource. Simply create a class that implements the Sql2oBootstrap interface and register it within a module, for example

src/main/groovy/com/acme/SampleSql2oBootstrap.groovy
package com.acme

import griffon.plugins.sql2o.Sql2oBootstrap
import org.sql2o.Sql2o

import javax.annotation.Nonnull
import javax.inject.Named

@Named("sample")
class SampleSql2oBootstrap implements Sql2oBootstrap {
    @Override
    void init(@Nonnull String datasourceName, @Nonnull Sql2o sql2o) {
        // operations after first connection to datasource
    }

    @Override
    void destroy(@Nonnull String datasourceName, @Nonnull Sql2o sql2o) {
        // operations before disconnecting from the datasource
    }
}
src/main/java/com/acme/ApplicationModule.java
package com.acme;

import griffon.plugins.sql2o.Sql2oBootstrap;
import griffon.core.injection.Module;
import org.codehaus.griffon.runtime.core.injection.AbstractModule;
import org.kordamp.jipsy.ServiceProviderFor;

@ServiceProviderFor(Module.class)
public class ApplicationModule extends AbstractModule {
    @Override
    protected void doConfigure() {
        bind(Sql2oBootstrap.class)
            .to(SampleSql2oBootstrap.class)
            .asSingleton();
    }
}

2.2. Example

The following is a trivial usage of the Sql2oHandler inside a Java service

com.acme.SampleService.java
package com.acme;

import griffon.core.artifact.GriffonService;
import griffon.metadata.ArtifactProviderFor;
import org.codehaus.griffon.runtime.core.artifact.AbstractGriffonService;

import griffon.plugins.sql2o.Sql2oHandler;
import griffon.plugins.sql2o.Sql2oCallback;
import org.sql2o.Sql2o;
import org.sql2o.StatementRunnableWithResult;

import javax.inject.Inject;

@ArtifactProviderFor(GriffonService.class)
public class SampleService {
    @Inject
    private Sql2oHandler sql2oHandler

    public Person findById(final int id) {
        return sql2oHandler.withSql2o(new Sql2oCallback<Person>() {
            @Override
            Person handle(@Nonnull String datasourceName, @Nonnull Sql2o sql2o) {
                return (Person) sql2o.withConnection(new StatementRunnableWithResult() {
                    @Override
                    Object run(Connection connection, Object argument) throws Throwable {
                        List<Person> people = connection.createQuery("SELECT * FROM person WHERE id = :id")
                            .addParameter("id", id)
                            .executeAndFetch(Person.class);
                        return people != null && people.size() > 0 ? people.get(0) : null;
                    }
                })
            }
        })
    }
}

Here’s the Groovy version of it

com.acme.SampleService.groovy
package com.acme

import griffon.core.artifact.GriffonService
import griffon.metadata.ArtifactProviderFor

import griffon.plugins.sql2o.Sql2oHandler
import org.sql2o.Sql2o
import org.sql2o.StatementRunnableWithResult

import javax.inject.Inject

@ArtifactProviderFor(GriffonService)
class SampleService {
    @Inject
    private Sql2oHandler sql2oHandler

    Person findById(final int id) {
         sql2oHandler.withSql2o { String datasourceName, Sql2o sql2o ->
             sql2o.withConnection({ connection, arg ->
                 connection.createQuery('SELECT * FROM person WHERE id = :id')
                         .addParameter('id', id)
                         .executeAndFetch(Person)[0]
             } as StatementRunnableWithResult)
         }
    }
}

2.3. Events

The following events will be triggered by Sql2oHandler

Sql2oConnectStart(String datasourceName, Map<String, Object> config)

Triggered before connecting to the datasource.

Sql2oConnectEnd(String datasourceName, Map<String, Object> config, Sql2o sql2o)

Triggered after connecting to the datasource.

Sql2oDisconnectStart(String datasourceName, Map<String, Object> config, Sql2o sql2o)

Triggered before disconnecting from the datasource.

Sql2oDisconnectEnd(String datasourceName, Map<String, Object> config)

Triggered after disconnecting from the datasource.

DataSource events may be triggered during connection and disconnection from a datasource.

2.4. AST Transformation

You can apply the @Sql2oAware AST transformation on any class. This injects the behavior of Sql2oHandler into said class. The previous Groovy service example can be rewritten as follows

com.acme.SampleService.groovy
package com.acme

import griffon.core.artifact.GriffonService
import griffon.metadata.ArtifactProviderFor
import griffon.transform.Sql2oAware

import org.sql2o.Sql2o
import org.sql2o.StatementRunnableWithResult

@Sql2oAware
@ArtifactProviderFor(GriffonService)
class SampleService {
    Person findById(final int id) {
         sql2oHandler.withSql2o { String datasourceName, Sql2o sql2o ->
             sql2o.withConnection({ connection, arg ->
                 connection.createQuery('SELECT * FROM person WHERE id = :id')
                         .addParameter('id', id)
                         .executeAndFetch(Person)[0]
             } as StatementRunnableWithResult)
         }
    }
}

2.5. DSL Descriptors

This plugin provides DSL descriptors for Intellij IDEA and Eclipse (provided you have the Groovy Eclipse plugin installed). These descriptors are found inside the griffon-sql2o-groovy-compile-2.1.0.jar, with locations

  • dsdl/griffon_sql2o.dsld

  • gdsl/griffon_sql2o.gdsl

3. Build Configuration

3.1. Gradle

You have two options for configuring this plugin: automatic and manual.

3.1.1. Automatic

As long as the project has the org.codehaus.griffon.griffon plugin applied to it you may include the following snippet in build.gradle

dependencies {
    griffon 'org.codehaus.griffon.plugins:griffon-sql2o-plugin:2.1.0'
}

The griffon plugin will take care of the rest given its configuration.

3.1.2. Manual

You will need to configure any of the following blocks depending on your setup

dependencies {
    compile 'org.codehaus.griffon.plugins:griffon-sql2o-core:2.1.0'
}
Compile Only
dependencies {
    compileOnly 'org.codehaus.griffon.plugins:griffon-sql2o-groovy-compile:2.1.0'
}

3.2. Maven

First configure the griffon-sql2o-plugin BOM in your POM file, by placing the following snippet before the <build> element

<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.codehaus.griffon.plugins</groupId>
            <artifactId>griffon-sql2o-plugin</artifactId>
            <version>2.1.0</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>
    </dependencies>
</dependencyManagement>

Next configure dependencies as required by your particular setup

<dependency>
    <groupId>org.codehaus.griffon.plugins</groupId>
    <artifactId>griffon-sql2o-core</artifactId>
</dependency>
Provided scope
<dependency>
    <groupId>org.codehaus.griffon.plugins</groupId>
    <artifactId>griffon-sql2o-groovy-compile</artifactId>
</dependency>

Don’t forget to configure all -compile dependencies with the maven-surefire-plugin, like so

<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-surefire-plugin</artifactId>
    <configuration>
        <classpathDependencyExcludes>
            <classpathDependencyExclude>
                org.codehaus.griffon:griffon-sql2o-groovy-compile
            </classpathDependencyExclude>
        </classpathDependencyExcludes>
    </configuration>
</plugin>

4. Modules

The following sections display all bindings per module. Use this information to successfully override a binding on your own modules or to troubleshoot a module binding if the wrong type has been applied by the Griffon runtime.

4.1. Sql2o

Module name: sql2o

Depends on: datasource

bind(Sql2oStorage.class)
    .to(DefaultSql2oStorage.class)
    .asSingleton();

bind(Sql2oFactory.class)
    .to(DefaultSql2oFactory.class)
    .asSingleton();

bind(Sql2oHandler.class)
    .to(DefaultSql2oHandler.class)
    .asSingleton();

bind(GriffonAddon.class)
    .to(Sql2oAddon.class)
    .asSingleton();

5. Links

Javadoc

API Sources