Skip to main content

Using Custom Types with DirectQuery

When using DirectQuery, Atoti+ retrieves data from the remote database. DirectQuery has sensible defaults to read the different data types from the remote database into common data types. However, in some situations where the remote type is complex or one wishes to customize the resulting type, it is possible to adjust it.

This guide describes how to do this.

Define the Custom Type​

First, we define two custom types: they are simple wrappers around a String and a double.

@Data
public static class CustomShop {
    final String shopName;
    public static CustomShop getDefaultValue() {
        return new CustomShop("N/A");
    }
    @Override
    public String toString() {
        return "CustomShop(shopName=" + this.shopName + ")";
    }
}
@Data
public static class CustomPrice {
    final double price;
    public static CustomPrice getDefaultValue() {
        return new CustomPrice(0.0);
    }
    @Override
    public String toString() {
        return "CustomPrice(price=" + this.price + ")";
    }
}

Adjust the database schema​

We customize the field in the "SALES" table definition.

final Table salesTable =
        session.discoverTable(new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "SALES"));
final Field shopField = salesTable.getField("SHOP");
final Field updatedShopField = shopField.withCustomType(
        CustomShop.class,
        CustomShop.getDefaultValue(),
        (final Object value) -> new CustomShop((String) value));
final Table updatedSalesTable = salesTable.updateField("SHOP", updatedShopField);

If the column contains a primitive types (such as int or double), it is more efficient to use a specific API.

We use such an API to customize the "PRODUCTS" table definition:

final Table productsTable =
        session.discoverTable(new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "PRODUCTS"));
final Field priceField = productsTable.getField("PURCHASE_PRICE");
final Field updatedPriceField = priceField
        .withCustomTypeFromDouble(CustomPrice.class, CustomPrice.getDefaultValue(), CustomPrice::new);
final Table updatedProductsTable = productsTable.updateField("PURCHASE_PRICE", updatedPriceField);

The schema and database are created as usual:

final SqlJoin join = SqlJoin.builder()
        .name("SALES_TO_PRODUCTS")
        .fromTable("SALES")
        .toTable("PRODUCTS")
        .fieldMappings(Set.of(new FieldMapping("PRODUCT", "PRODUCT_ID")))
        .build();
final Schema schema = Schema.builder()
        .withExternalTables(List.of(updatedSalesTable, updatedProductsTable), List.of(join))
        .build();

Run a query​

We can run a simple query, to check it works:

final Application app = session.applicationBuilder().schema(schema).build();
final IDatabaseVersion databaseVersion = app.getDatabase().getHead(MASTER_BRANCH_NAME);
final ListQuery query = databaseVersion.getQueryManager()
        .listQuery()
        .forTable("SALES")
        .withCondition(BaseConditions.equal(FieldPath.of("SALE_ID"), "S0017"))
        .withAliasedFields(
                AliasedField.fromFieldName("SALE_ID"),
                AliasedField.fromFieldName("SHOP"),
                AliasedField.create("PURCHASE_PRICE", FieldPath.of("SALES_TO_PRODUCTS", "PURCHASE_PRICE")))
        .toQuery();
final ICursor res = databaseVersion.getQueryRunner().listQuery(query).run();
assertThat(res.hasNext()).isTrue();
assertThat(res.next()).isTrue();
final IRecordReader recordReader = res.getRecord();
System.out.println(recordReader.read(1)); // Printed CustomShop(shopName=shop_3)
System.out.println(recordReader.read(2)); // Printed CustomPrice(price=38.0)

Conclusion​

That's it. Every query on the 'SHOP' (resp. 'PURCHASE_PRICE') column will generate an Object of class CustomShop (resp. CustomPrice).