### 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 ID | Artifact ID | Version
--- | --- | ---
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 ID | Artifact ID | Version
--- | --- | ---
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 ID | Artifact ID | Version
--- | --- | ---
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 ID | Artifact ID | Version
--- | --- | ---
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.