Open Source Connectors

MySQL

Speedment supports MySQL out-of-the-box. Please refer to the Speedment Initializer to setup your MySQL project.

Starting from version 3.0.11, the MySQL FieldPredicatView can be configured to use custom collations by modifying the following configuration parameters:

Name Default value
db.mysql.collationName utf8_general_ci
db.mysql.binaryCollationName utf8_bin

These values can be set to custom values using the application builder as depicted below:

     ApplicationBuilder app = new SakilaApplicationBuilder()
        .withPassword("sakila-password")
        .withParam("db.mysql.collationName", "utf8mb4_general_ci")
        .withParam("db.mysql.binaryCollationName", "utf8mb4_bin");
        .build();

The selected collations will be used for all MySQL tables.

Speedment officially supports the following MySQL JDBC version(s):

Database groupId artifactId version
MySQL mysql mysql-connector-java 5.1.46

PostgreSQL

Speedment supports PostgreSQL out-of-the-box. Please refer to the Speedment Initializer to setup your PostgreSQL project.

Speedment officially supports the following PostgreSQL JDBC version(s):

Database groupId artifactId version
PosgreSQL org.postgresql postgresql 42.2.2

MariaDB

Speedment supports MariaDB out-of-the-box. Please refer to the Speedment Initializer to setup your MariaDB project.

Starting from version 3.0.11, the MariaDB FieldPredicatView can be configured to use custom collations by modifying the following configuration parameters:

Name Default value
db.mysql.collationName utf8_general_ci
db.mysql.binaryCollationName utf8_bin

These values can be set to custom values using the application builder as depicted below:

     ApplicationBuilder app = new SakilaApplicationBuilder()
        .withPassword("sakila-password")
        .withParam("db.mysql.collationName", "utf8mb4_general_ci")
        .withParam("db.mysql.binaryCollationName", "utf8mb4_bin");
        .build();

The selected collations will be used for all MariaDB tables.

Speedment officially supports the following MariaDB JDBC version(s):

Database groupId artifactId version
MariaDB org.mariadb.jdbc mariadb-java-client 2.3.0

SQLite

Starting from Speedment version 3.1.10, SQLite is supported. SQLite is a lightweight database that can either be backed by a single file or run in-memory. Speedment supports both these, but the in-memory option is only usable once the speedment.json-file has been generated.

Speedment officially supports the following SQLite version(s):

Database groupId artifactId version
SQLite org.xerial sqlite-jdbc 3.25.2

SQLite Metadata

When Speedment parses the metadata given by the JDBC-connector, a lot of information is given that is not necessary enforced by the database. This involves (but is not limited to) the type and size of certain columns. Speedment will do its best to use such information to decide which Java types to use when representing the entity in generated code. This can, however, mean that the generated entity does not perfectly match the bounds enforced in the database. When the table definition in the metadata and the actual bounds enforced by the database conflict, Speedment will prioritize the former.

For an example, if a column is specified as INTEGER PRIMARY KEY in the table definition, Speedment will interpret that as an int in java. SQLite will however use 64-bits to store the value since that column will be considered an alias for the rowid. You could therefore argue that Speedment should interpret the column as a long, but it does not since it prioritizes the SQL-definition above the internal implementation used by the database engine.

Working with File-Based Databases

When SQLite is backed by a file, it is important to make sure that the file is not accessed by multiple threads at the same time. If you see the following error, this is likely the issue:

org.sqlite.SQLiteException: [SQLITE_LOCKED]  A table in the database is locked (database table is locked)
    at org.sqlite.core.DB.newSQLException(DB.java:909)
    at org.sqlite.core.DB.newSQLException(DB.java:921)
    at org.sqlite.core.DB.execute(DB.java:822)
    at org.sqlite.core.CoreStatement.exec(CoreStatement.java:75)
    at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:61)
    ...

To fix this, you need to do one of the following:

The two steps above can also be used togather.

Auto-Incrementing Columns

A table in SQLite always has a column named rowid that is used as the primary key. If a column in the table definition is set as INTEGER PRIMARY KEY, that column will be considered an alias for the rowid. This can be a bit confusing, and requires Speedment to make some decisions on how to interpret the database metadata. Speedment will create the rowid column and show it in entities only as long as there is no INTEGER PRIMARY KEY column present in the metadata. If a different primary key have been specified (for an example a CHAR PRIMARY KEY), that one will instead be considered a regular column with a UNIQUE INDEX.

There are two types of auto-increments in SQLite. A column specified as AUTOINCREMENT will work slightly different than the increment that is always present in the rowid column. For the intents and purposes in Speedment, these are equivalent and both are therefore considered auto-incrementing columns by Speedment.

Default values

If the table defintion has columns with default values specified, these has to be excluded when persisting and updating entities using Speedment. This can be done by defining a FieldSet object as explained here.

Enterprise Connectors

Support for additional enterprise database types can easily be obtained by adding an appropriate connector. Adding a connector is straight forward:

  • Add a connector dependency in your pom file
  • Mention the connector’s Bundle in the speedment enterprise plugin
  • Mention the connector’s Bundle in your ApplicationBuilder

Oracle

This chapter shows how to add support for Oracle in Speedment. Unfortunately, Oracle does not provide a JDBC driver that you can download via a dependency in your pom file. Instead, it has to be installed manually before you can use the Oracle connector. Here is Oracle’s official JDBC FAQ that provides information on how to install the Oracle JDBC driver.

Privileges

In order for the Speedment tool to read the schema metadata you need the following privileges:

Privilege Create Example
CREATE SESSION GRANT CREATE SESSION TO SPEEDMENT_USER;
SELECT GRANT SELECT ON t TO SPEEDMENT_USER; (*)
ANALYZE GRANT ANALYZE ANY TO SPEEDMENT_USER;
ANALYZE DICTIONARY GRANT ANALYZE DICTIONARY TO SPEEDMENT_USER;

(*) Repeat for each and every table t being used.

When the application runs, only the CREATE SESSION and the SELECT privileges are needed (plus UPDATE/DELETE if those operations are being used within the Speedment application).

Oracle POM

Always use the Initializer to get a complete POM file template as the POM snipes hereunder just show portions of what is needed.

Here is how you configure the speedment enterprise plugin:

    <plugin>
        <groupId>com.speedment.enterprise</groupId>
        <artifactId>speedment-enterprise-maven-plugin</artifactId>
        <version>${speedment.enterprise.version}</version>
        <dependencies>
            <dependency>
                <dependency>
                    <groupId>com.oracle</groupId>
                    <artifactId>ojdbc7</artifactId>
                    <version>12.1.0.1.0</version>
                    <scope>runtime</scope>
                </dependency>
            </dependency>
        </dependencies> 
        <configuration>
            <components>
                 <component>com.speedment.enterprise.connectors.oracle.OracleBundle</component>
            </components>
            <parameters>
                <parameter>
                    <name>licenseKey</name>
                    <value>(YOUR LICENSE CODE)</value>
                </parameter>
            </parameters>
        </configuration>
    </plugin>

You also have to depend on the Oracle connector and JDBC connector as a runtime dependency for your application:

    <dependencies>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc7</artifactId>
            <version>12.1.0.1.0</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.speedment.enterprise.connectors</groupId>
            <artifactId>oracle-connector</artifactId>
            <version>${speedment.enterprise.version}</version>
        </dependency>
        <dependency>
            <groupId>com.speedment.enterprise</groupId>
            <artifactId>runtime</artifactId>
            <version>${speedment.enterprise.version}</version>
            <type>pom</type>
        </dependency>
    </dependencies>

Oracle Application

When you build the application, the OracleBundle needs to be added to the runtime like this:

    YourApplication app = new YourApplicationBuilder()
        .withPassword("your-dbms-password")
        .withParam("licenseKey", "(YOUR LICENSE CODE)")
        .withBundle(OracleBundle.class)
        .build();

Dbms Application Info

The Oracle specific feature DBMS_APPLICATION_INFO is supported by Speedment. This feature allows client and module names to be visible in a number of locations such as Enterprise Manager performance graphs, ASH and AWR reports. Here is an example of how it might look like in the Enterprise Manager:

Enterprise Manager: app info
Enterprise Manager: app info

Here is an example how to activate the feature:

SpeedmentApplicationBuilder builder = new SpeedmentApplicationBuilder()
       .withPassword("speedmentpw")
       .withBundle(OracleBundle.class)
       .withComponent(OracleConnectionDecorator.class)
       .withParam(OracleConnectionDecorator.CLIENT_INFO, "test-client")
       .withParam(OracleConnectionDecorator.MODULE_NAME, "test-module");

This will mark every connection to the database with these parameters.

Read more about DBMS_APPLICATION_INFO here

SQL Server

This chapter shows how to add support for Microsoft SQL Server in Speedment.

SQL Server POM

Always use the Initializer to get a complete POM file template as the POM snipes hereunder just show portions of what is needed.

Here is how you configure the speedment enterprise plugin:

    <plugin>
        <groupId>com.speedment.enterprise</groupId>
        <artifactId>speedment-enterprise-maven-plugin</artifactId>
        <version>${speedment.enterprise.version}</version>
        <dependencies>
            <dependency>
                <groupId>com.microsoft.sqlserver</groupId>
                <artifactId>mssql-jdbc</artifactId>
                <version>6.1.0.jre8</version>
                <scope>runtime</scope>
            </dependency>
        </dependencies>
        <configuration>
            <components>
                <component>com.speedment.enterprise.connectors.sqlserver.SqlServerBundle</component>
            </components>
            <parameters>
                <parameter>
                    <name>licenseKey</name>
                    <value>(YOUR LICENSE CODE)</value>
                </parameter>
            </parameters>
        </configuration>
    </plugin>

You also have to depend on the Sql Server connector and JDBC connector as a runtime dependency for your application:

    <dependencies>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>6.1.0.jre8</version>
            <scope>runtime</scope>
        </dependency>
            <dependency>
            <groupId>com.speedment.enterprise.connectors</groupId>
            <artifactId>sqlserver-connector</artifactId>
            <version>${speedment.enterprise.version}</version>
        </dependency>
        <dependency>
            <groupId>com.speedment.enterprise</groupId>
            <artifactId>runtime</artifactId>
            <version>${speedment.enterprise.version}</version>
            <type>pom</type>
        </dependency>
    </dependencies>

SQL Server Application

When you build the application, the SqlServerBundle needs to be added to the runtime like this:

    YourApplication app = new YourApplicationBuilder()
        .withPassword("your-dbms-password")
        .withParam("licenseKey", "(YOUR LICENSE CODE)")
        .withBundle(SqlServerBundle.class)
        .build();

DB2

This chapter shows how to add support for IBM DB2 in Speedment. Unfortunately, IBM does not provide a JDBC driver that you can download via a dependency in your pom file. Instead, it has to be installed manually before you can use the DB2 connector. Here is IBM’s official JDBC download page.

DB2 POM

Always use the Initializer to get a complete POM file template as the POM snipes hereunder just show portions of what is needed.

Here is how you configure the speedment enterprise plugin:

    <plugin>
        <groupId>com.speedment.enterprise</groupId>
        <artifactId>speedment-enterprise-maven-plugin</artifactId>
        <version>${speedment.enterprise.version}</version>
        <dependencies>
            <dependency>
                <groupId>com.ibm.db2</groupId>
                <artifactId>db2jcc4</artifactId>
                <version>4.21.29</version>
                <scope>runtime</scope>
            </dependency>
        </dependencies>
        <configuration>
            <components>
                <component>com.speedment.enterprise.connectors.db2.Db2Bundle</component>
            </components>
            <parameters>
                <parameter>
                    <name>licenseKey</name>
                    <value>(YOUR LICENSE CODE)</value>
                </parameter>
            </parameters>
        </configuration>
    </plugin>

You also have to depend on the DB2 connector and JDBC connector as a runtime dependency for your application:

    <dependencies>
        <dependency>
            <groupId>com.ibm.db2</groupId>
            <artifactId>db2jcc4</artifactId>
            <version>4.21.29</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            groupId>com.speedment.enterprise.connectors</groupId>
            <artifactId>db2-connector</artifactId>
            <version>${speedment.enterprise.version}</version>
        </dependency>
        <dependency>
            <groupId>com.speedment.enterprise</groupId>
            <artifactId>runtime</artifactId>
            <version>${speedment.enterprise.version}</version>
            <type>pom</type>
        </dependency>
    </dependencies>

DB2 Application

When you build the application, the Db2Bundle needs to be added to the runtime like this:

    YourApplication app = new YourApplicationBuilder()
        .withPassword("your-dbms-password")
        .withParam("licenseKey", "(YOUR LICENSE CODE)")
        .withBundle(Db2Bundle.class)
        .build();

AS400

This chapter shows how to add support for IBM AS400 in Speedment.

AS400 POM

Always use the Initializer to get a complete POM file template as the POM snipes hereunder just show portions of what is needed.

Here is how you configure the speedment enterprise plugin:

    <plugin>
        <groupId>com.speedment.enterprise</groupId>
        <artifactId>speedment-enterprise-maven-plugin</artifactId>
        <version>${speedment.enterprise.version}</version>
        <configuration>
            <components>
                <component>com.speedment.enterprise.connectors.db2.Db2Bundle</component>
            </components>
            <parameters>
                <parameter>
                    <name>licenseKey</name>
                    <value>(YOUR LICENSE CODE)</value>
                </parameter>
            </parameters>
        </configuration>
    </plugin>

You also have to depend on the AS400 connector and JDBC connector as a runtime dependency for your application:

    <dependencies>
        <dependency>
            <groupId>net.sf.jt400</groupId>
            <artifactId>jt400-full</artifactId>
            <version>6.0</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            groupId>com.speedment.enterprise.connectors</groupId>
            <artifactId>db2-connector</artifactId>
            <version>${speedment.enterprise.version}</version>
        </dependency>
        <dependency>
            <groupId>com.speedment.enterprise</groupId>
            <artifactId>runtime</artifactId>
            <version>${speedment.enterprise.version}</version>
            <type>pom</type>
        </dependency>
    </dependencies>

AS400 Application

When you build the application, the Db2Bundle needs to be added to the runtime like this:

    YourApplication app = new YourApplicationBuilder()
        .withPassword("your-dbms-password")
        .withParam("licenseKey", "(YOUR LICENSE CODE)")
        .withBundle(Db2Bundle.class)
        .build();

The Db2Bundle supports both DB2 and AS400.

Discussion

Join the discussion in the comment field below or on Gitter