Zest™
Introduction
Tutorials
Javadoc
Samples
Core
Libraries
Extensions
Tools
Glossary 

SQL

code

docs

tests

The SQL Library provides facilities for working with SQL databases.

The center piece is the DataSource support that comes with Circuit Breaker Library and JMX Library support. Facilities for doing SQL I/O with the I/O API are provided.

Tip

See the SQL Support Sample that demonstrate combined use of SQL Library, SQL EntityStore and SQL Index/Query.

Moreover, supplementary libraries helps dealing with different connection pool implementations and schema migrations. None of theses libraries depends on an actual JDBC driver, you are free to use the one that suits your needs.

Table 49. Artifact

Group IDArtifact IDVersion

org.qi4j.library

org.qi4j.library.sql

2.1


DataSource and connection pools

DataSource support comes in three flavors:

  • using the BoneCP connection pool
  • using the Apache DBCP connection pool
  • importing an existing DataSource provided at assembly time
Connection Pools

Connection Pools support is provided by supplementary libraries.

BoneCP

code

docs

tests

Table 50. Artifact

Group IDArtifact IDVersion

org.qi4j.library

org.qi4j.library.sql-bonecp

2.1


BoneCP support resides in the sql-bonecp module.

// Assemble the BoneCP based Service Importer
new BoneCPDataSourceServiceAssembler().
    identifiedBy( DS_SERVICE_ID ).
    visibleIn( Visibility.module ).
    withConfig( config, Visibility.layer ).
    assemble( module );

Apache DBCP

code

docs

tests

Table 51. Artifact

Group IDArtifact IDVersion

org.qi4j.library

org.qi4j.library.sql-dbcp

2.1


// Assemble the Apache DBCP based Service Importer
new DBCPDataSourceServiceAssembler().
    identifiedBy( DS_SERVICE_ID ).
    visibleIn( Visibility.module ).
    withConfig( config, Visibility.layer ).
    assemble( module );
DataSource

Assembly

// Assemble a DataSource
new DataSourceAssembler().
    withDataSourceServiceIdentity( DS_SERVICE_ID ).
    identifiedBy( DS_ID ).
    visibleIn( Visibility.module ).
    assemble( module );
// Another DataSource managed by the same C3P0 connection pool
new DataSourceAssembler().
    withDataSourceServiceIdentity( DS_SERVICE_ID ).
    identifiedBy( OTHER_DS_ID ).
    visibleIn( Visibility.module ).
    assemble( module );

Assembled DataSources must be visible from the connection pool importer service.

Configuration

You need to provide a DataSource Configuration Entity per assembled DataSource. See Configure a Service.

public interface DataSourceConfigurationState
        extends Enabled
{
    Property<String> driver();
    Property<String> url();
    @UseDefaults Property<String> username();
    @UseDefaults Property<String> password();
    @Optional Property<Integer> minPoolSize();
    @Optional Property<Integer> maxPoolSize();
    @Optional Property<Integer> loginTimeoutSeconds();
    @Optional Property<Integer> maxConnectionAgeSeconds();
    @Optional Property<String> validationQuery();
    @UseDefaults Property<String> properties();
}

Sample DataSource configuration defaults:

# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

enabled=true
url=jdbc:derby:memory:testdb;create=true
driver=org.apache.derby.jdbc.EmbeddedDriver
username=
password=
Importing an existing DataSource

Importing an existing DataSource at assembly time is usefull when your Zest Application runs in an environment where DataSource are already provided.

new ExternalDataSourceAssembler( externalDataSource ).
        visibleIn( Visibility.module ).
        identifiedBy( "datasource-external-id" ).
        withCircuitBreaker( DataSources.newDataSourceCircuitBreaker() ).
        assemble( module );

This mechanism is provided as an integration convenience and using the embedded connection pools described above is recommended.

Circuit Breaker

Assemblers for managed and external DataSource takes an optional CircuitBreaker and set it as MetaInfo of the DataSource.

CircuitBreaker circuitBreaker = newDataSourceCircuitBreaker( 5 /* threshold */,
                                                             1000 * 60 * 5 /* 5min timeout */ );
new DataSourceAssembler().
    withDataSourceServiceIdentity( DS_SERVICE_ID ).
    identifiedBy( DS_ID ).
    visibleIn( Visibility.layer ).
    withCircuitBreaker( circuitBreaker ).
    assemble( module );

Then, when you gets injected or lookup a DataSource it will be automatically wrapped by a CircuitBreaker proxy.

@Service
DataSource dataSource; // Wrapped with a CircuitBreaker proxy

I/O

Here is a simple example:

// Look up the DataSource
DataSource ds = module.findService( DataSource.class ).get();

// Instanciate Databases helper
Databases database = new Databases( ds );

// Assert that insertion works
assertTrue( database.update( "insert into test values ('someid', 'bar')" ) == 1 );
  [...snip...]

// Select rows and load them in a List
List<SomeValue> rows = new ArrayList<SomeValue>();
database.query( "select * from test" ).transferTo( map( toValue, collection( rows ) ) );

// Transfer all rows to System.out
Inputs.iterable( rows ).transferTo( Outputs.systemOut() );

JMX

Thanks to the JMX Library the Configuration of DataSources is exposed through JMX.

new DataSourceJMXAssembler().visibleIn( Visibility.module ).assemble( module );

Every DataSource visible from the DataSourceConfigurationManager Service will get its Configuration available using a JMX client.

Note that the JMX support does not apply to existing DataSource imported as described above.

Schema migration

Database schema migration can be delegated to Liquibase.

code

docs

tests

Table 52. Artifact

Group IDArtifact IDVersion

org.qi4j.library

org.qi4j.library.sql-liquibase

2.1


Assembly

new LiquibaseAssembler().
    withConfig( configModule, Visibility.layer ).
    assemble( module );

The LiquibaseService is activated on Application startup and if enabled it applies the configured changelog.

Configuration

public interface LiquibaseConfiguration
        extends ConfigurationComposite, Enabled
{
    @UseDefaults Property<String> contexts();
    @UseDefaults Property<String> changeLog();
}

For the Liquibase service to be enabled you must set it’s Configuration enabled Property to TRUE. contexts and changeLog are optional.