10.2.7

Edit

Connect to external database with ODF 2

The ODF 2 provides the built-in ORM layer and comes with OrmLite as the default ORM framework used primarily to access Data Store data.

In some cases, you might require to work with data outside of Data Stores that is stored in some external database. The guide describes how to connect to an external database using the ODF 2 framework.

Establish custom Data Source

note

Since ODF 2 already defines a DataSource object inside a Feather context, use a qualifier to define any custom object with the same type. The same applies to any standard objects that come as part of ODF 2 core modules.

  1. Create a qualified annotation to separate custom Data Source-related objects from the standard ODF 2 core objects:

    @Qualifier
@Retention(RetentionPolicy.RUNTIME)
public @interface CustomDataSource {
}

  2. Create a new module and set up the DataSource and ConnectionSource objects:

    public class CustomDbModule implements OdfModule {

    @CustomDataSource
    @Provides
    @Singleton
    public DataSource customDataSource() {
        final DatabaseProperties properties = new DatabaseProperties();
        properties.setUrl("jdbc:external:url");
        properties.setUsername("username");
        properties.setPassword("password");
        properties.setMaxConnections(10);
        return DatabaseDataStoreServiceFactory.getInstance().getDataSource(properties);
    }

    @CustomDataSource
    @Provides
    @Singleton
    public ConnectionSource customConnectionSource(@CustomDataSource DataSource dataSource) throws SQLException {
        final DatabaseType databaseType = new H2DatabaseType();
        return new DataSourceConnectionSource(dataSource, databaseType);
    }

}

    Besides the external database's connection settings, specify a DatabaseType, which is an OrmLite definition needed to isolate the differences between the various databases. In the example, H2DatabaseType is used (refers to H2 Database). In your case, it might be different, for example, MysqlDatabaseType, and so on. Thus, you should choose it according to the database type you are connecting to. See the list of OrmLite-supported databases in the official documentation.

    Also, it's important to create a DataSource object using DatabaseDataStoreServiceFactory specifically, as shown in the listing above. It lets you employ the DataSource cache and connection pool from a BEP Worker.

    note

    Consider storing DataSource settings, for example, username and password, inside Secrets Vault.

    warning

    For the Oracle database, the process of creating DataSource and DatabaseType is different. See the instructions below.

    Expand to view the guide

    The process of creating an OracleDb DataSource is not implemented using DatabaseDataStoreServiceFactory but with the help of the mechanism provided by Oracle.

    1. Add the Oracle JDBC dependency to your Maven pom.xml.
    <dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>23.3.0.23.09</version>
    <scope>compile</scope>
</dependency>
    1. Create a DataSource. For details, refer to the official documentation.
    @CustomDataSource
@Provides
@Singleton
public DataSource customDataSource() throws SQLException {
    OracleDataSource dataSource = new OracleDataSource();
    dataSource.setURL("jdbc:oracle:external:url");
    dataSource.setUser("user");
    dataSource.setPassword("password");
    dataSource.setConnectionProperty("MaxLimit", "10");

    return dataSource;
}

@CustomDataSource
@Provides
@Singleton
public ConnectionSource customConnectionSource(@CustomDataSource DataSource dataSource) throws SQLException {
    final DatabaseType databaseType = new OracleDatabaseType();
    return new DataSourceConnectionSource(dataSource, databaseType);
}
    1. Check how your project works with an external database. In some cases, queries can be generated in the wrong format, with entities and table names specified inside double quotes. To fix this, override the OracleDatabaseType class as shown below:
    import com.j256.ormlite.jdbc.db.OracleDatabaseType;

public class FixedOracleDatabaseType extends OracleDatabaseType {

    @Override
    public void appendEscapedEntityName(StringBuilder sb, String name) {
        sb.append(name);
    }
}
    1. Configure settings in the Feather customConnectionSource bean.
    @CustomDataSource
@Provides
@Singleton
public ConnectionSource customConnectionSource(@CustomDataSource DataSource dataSource) throws SQLException {
    final DatabaseType databaseType = new FixedOracleDatabaseType();
    return new DataSourceConnectionSource(dataSource, databaseType);
}

  3. Create a JPA Entity and a related repository. This entity should represent a table in an external database you will work with.

    @DatabaseTable(tableName = "external_table")
public class ExternalEntity {

    @DatabaseField(columnName = "key", id = true, canBeNull = false)
    private String key;

    @DatabaseField(columnName = "value")
    private String value;

    public String getKey() {
        return key;
    }

    public void setKey(String key) {
        this.key = key;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

}

    public class ExternalEntityRepository {

    private final Dao<ExternalEntity, String> dao;

    public ExternalEntityRepository(ConnectionSource connectionSource) throws SQLException {
        dao = DaoManager.createDao(connectionSource, ExternalEntity.class);
    }

    public List<ExternalEntity> findAll() {
        try {
            return dao.queryForAll();
        } catch (SQLException e) {
            throw new OdfFrameworkException(e);
        }
    }

}

  4. Register the repository in your module (in the example, the same CustomDbModule is used) to let Bot Tasks inject it. Use the @CustomDataSource annotation to provide a connection source related to the external database:

    @Requires(OdfCoreModule.class)
public class CustomDbModule implements OdfModule {

    @Provides
    @Singleton
    public ExternalEntityRepository externalEntityRepository(@CustomDataSource ConnectionSource connectionSource) throws SQLException {
        return new ExternalEntityRepository(connectionSource);
    }

}

  5. Create a Bot Task and inject the repository:

    @BotTask
@Requires(CustomDbModule.class)
public class UsingCustomDataSourceTask implements AdHocTask {

    private final ExternalEntityRepository externalEntityRepository;

    @Inject
    public UsingCustomDataSourceTask(ExternalEntityRepository externalEntityRepository) {
        this.externalEntityRepository = externalEntityRepository;
    }

    @Override
    public TaskRunnerOutput run(TaskInput taskInput) {
        final SingleResult result = taskInput.asResult();

        externalEntityRepository.findAll().forEach(entity ->
                result.withColumn(entity.getKey(), entity.getValue()));

        return result;
    }

}

As you can see, from a Bot Task perspective, working with the external database does not differ from working with Data Stores.

Use raw SQL queries without OrmLite

Sometimes, working with OrmLite might be excessive or not applicable for any other reasons. In this case, you can only set up the DataSource object and work with it directly.

  1. Set up a DataSource object and define it inside the Feather module using the custom qualified annotation:

    public class CustomDbModule implements OdfModule {

    @CustomDataSource
    @Provides
    @Singleton
    public DataSource customDataSource() {
        final DatabaseProperties properties = new DatabaseProperties();
        properties.setUrl("jdbc:external:url");
        properties.setUsername("username");
        properties.setPassword("password");
        properties.setMaxConnections(10);
        return DatabaseDataStoreServiceFactory.getInstance().getDataSource(properties);
    }

}

  2. Create a Bot Task and inject the DataSource object directly in the constructor using the @CustomDataSource annotation:

    @BotTask
@Requires(CustomDbModule.class)
public class UsingRawQueriesTask implements AdHocTask {

    private final DataSource dataSource;

    @Inject
    public UsingRawQueriesTask(@CustomDataSource DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public TaskRunnerOutput run(TaskInput taskInput) {
        final SingleResult taskResult = taskInput.asResult();

        try (Connection connection = dataSource.getConnection()) {
            final ResultSet resultSet = connection.createStatement().executeQuery("select * from external_table");
            final Map<String, String> values = resultSetToMap(resultSet);
            values.forEach(taskResult::withColumn);
        } catch (SQLException e) {
            throw new IllegalStateException(e);
        }

        return taskResult;
    }

    private static Map<String, String> resultSetToMap(ResultSet resultSet) throws SQLException {
        final Map<String, String> result = new LinkedHashMap<>();
        while (resultSet.next()) {
            result.put(resultSet.getString("key"), resultSet.getString("value"));
        }
        return result;
    }

}
note

This approach requires fewer settings, but at the same time, you should put extra effort into writing SQL queries and doing the result set mapping manually.

← Avoid connection leaks while using ORMLiteML models →