Zest™
Introduction
Tutorials
Javadoc
Samples
Core
Libraries
Extensions
Tools
Glossary 

SQL EntityStore

code

docs

tests

EntityStore service backed by a SQL database.

This extension fully leverage the SQL Library meaning that you must use it to assemble your DataSource and that you get Circuit Breaker and JMX integration for free.

Tip

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

The following SQL databases are supported:

Each entity state is stored as a single row so maximum number of entities is the maximum number of rows per table supported by the underlying SQL database.

Implementations per database Vendor share a generic codebase but can override about everything SQL. As a consequence they can have strong differences in terms of performance if they use vendor specific extensions.

Table 70. Artifact

Group IDArtifact IDVersion

org.qi4j.extension

org.qi4j.extension.entitystore-sql

2.1


Configuration

SQL EntityStore Configuration is optional and provides only one configuration property: schemaName defaulted to qi4j_es. On SQL databases that don’t support schemas this configuration property is simply ignored.

The assembly snippets below show the DataSource assembly alongside the SQL EntityStore assembly. Remember to configure the DataSource properly, see SQL Library and Configure a Service.

PostgreSQL

Maximum number of entities is unlimited.

Assembly is done using the provided Assembler:

public void assemble( ModuleAssembly module )
    throws AssemblyException
{
  [...snip...]

    // DataSourceService
    new DBCPDataSourceServiceAssembler().
        identifiedBy( "postgresql-datasource-service" ).
        visibleIn( Visibility.module ).
        withConfig( config, Visibility.layer ).
        assemble( module );

    // DataSource
    new DataSourceAssembler().
        withDataSourceServiceIdentity( "postgresql-datasource-service" ).
        identifiedBy( "postgresql-datasource" ).
        visibleIn( Visibility.module ).
        withCircuitBreaker().
        assemble( module );

    // SQL EntityStore
    new PostgreSQLEntityStoreAssembler().
        visibleIn( Visibility.application ).
        withConfig( config, Visibility.layer ).
        assemble( module );
}

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:postgresql://localhost:5432/jdbc_test_db
driver=org.postgresql.Driver
username=jdbc_test_login
password=password

MySQL and MariaDB

Maximum number of entities depends on the choosen storage engine.

Assembly is done using the provided Assembler:

public void assemble( ModuleAssembly module )
    throws AssemblyException
{
  [...snip...]

    // DataSourceService
    new DBCPDataSourceServiceAssembler().
        identifiedBy( "mysql-datasource-service" ).
        visibleIn( Visibility.module ).
        withConfig( config, Visibility.layer ).
        assemble( module );

    // DataSource
    new DataSourceAssembler().
        withDataSourceServiceIdentity( "mysql-datasource-service" ).
        identifiedBy( "mysql-datasource" ).
        visibleIn( Visibility.module ).
        withCircuitBreaker().
        assemble( module );

    // SQL EntityStore
    new MySQLEntityStoreAssembler().
        visibleIn( Visibility.application ).
        withConfig( config, Visibility.layer ).
        assemble( module );
}

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:mysql://localhost:3306/jdbc_test_db?profileSQL=true
driver=com.mysql.jdbc.Driver
username=root
password=

SQLite

Maximum number of entities is unlimited.

The Xerial SQLite JDBC driver is recommended. It provides native support on Linux, Windows and MaxOSX, pure Java on other OSes.

Assembly is done using the provided Assembler:

public void assemble( ModuleAssembly module )
    throws AssemblyException
{
  [...snip...]

    // DataSourceService
    new DBCPDataSourceServiceAssembler().
        identifiedBy( "sqlite-datasource-service" ).
        visibleIn( Visibility.module ).
        withConfig( config, Visibility.layer ).
        assemble( module );

    // DataSource
    new DataSourceAssembler().
        withDataSourceServiceIdentity( "sqlite-datasource-service" ).
        identifiedBy( "sqlite-datasource" ).
        visibleIn( Visibility.module ).
        withCircuitBreaker().
        assemble( module );

    // SQL EntityStore
    new SQLiteEntityStoreAssembler().
        visibleIn( Visibility.application ).
        withConfig( config, Visibility.layer ).
        assemble( module );
}

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:sqlite::memory:
driver=org.sqlite.JDBC
username=
password=

H2 Database Engine

Maximum number of entities is 2^64.

Assembly is done using the provided Assembler:

public void assemble( ModuleAssembly module )
    throws AssemblyException
{
  [...snip...]

    // DataSourceService
    new DBCPDataSourceServiceAssembler().
        identifiedBy( "h2-datasource-service" ).
        visibleIn( Visibility.module ).
        withConfig( config, Visibility.layer ).
        assemble( module );

    // DataSource
    new DataSourceAssembler().
        withDataSourceServiceIdentity( "h2-datasource-service" ).
        identifiedBy( "h2-datasource" ).
        visibleIn( Visibility.module ).
        withCircuitBreaker().
        assemble( module );

    // SQL EntityStore
    new H2SQLEntityStoreAssembler().
        visibleIn( Visibility.application ).
        withConfig( config, Visibility.layer ).
        assemble( module );
}

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:h2:mem:test
driver=org.h2.Driver
username=
password=

Apache Derby and Oracle JavaDB

Maximum number of entities is unlimited.

Assembly is done using the provided Assembler:

public void assemble( ModuleAssembly module )
    throws AssemblyException
{
  [...snip...]

    // DataSourceService
    new DBCPDataSourceServiceAssembler().
        identifiedBy( "derby-datasource-service" ).
        visibleIn( Visibility.module ).
        withConfig( config, Visibility.layer ).
        assemble( module );

    // DataSource
    new DataSourceAssembler().
        withDataSourceServiceIdentity( "derby-datasource-service" ).
        identifiedBy( "derby-datasource" ).
        visibleIn( Visibility.module ).
        withCircuitBreaker().
        assemble( module );

    // SQL EntityStore
    new DerbySQLEntityStoreAssembler().
        visibleIn( Visibility.application ).
        withConfig( config, Visibility.layer ).
        assemble( module );
}

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=