Skip to main content

Database Queries

The guide will show you how to build and use the database queries.

Get-by-key queries​

Running a simple get-by-key query​

The get-by-key query retrieves the record corresponding to a key.
For example, given a table trades where rows are indexed by their trade id, we can retrieve the trader that made the trade 1023:

final GetByKeyQuery query = databaseVersion.getQueryManager()
        .getByKeyQuery()
        .forTable("trades")
        .withFields(FieldPath.of("trader"))
        .toQuery();
final IRecordReader record = databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(1023).run();
if (record != null) {
    final var trader = record.read(0);
}

You can find the list of key fields for a given store using IDataTable#getKeyFieldNames.

Compiling once, run twice​

If a query will be run several times, it is better to compile it once: 

final IPreparedGetByKeyQuery query = database.getQueryManager()
        .getByKeyQuery()
        .forTable("trader")
        .withFields(FieldPath.of("trader"))
        .compile();
And run it multiple times: 
for (int i = 0; i < 10; i++) {
    databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(i).run();
}

Using a get-by-key query​

The get-by-key query returns the result as a RecordReader. For example, we can create a service to retrieve the trader name for a trade id: 

private static class TraderNameService {
    IDatabase database;
    IPreparedGetByKeyQuery traderQuery;
    public TraderNameService(IDatabase database) {
        this.database = database;
        this.traderQuery = database.getQueryManager()
                .getByKeyQuery()
                .forTable("trader")
                .withFields(FieldPath.of("trader"))
                .compile();
    }
    public String getTraderName(int tradeId) {
        final IRecordReader recordReader = database.getHead(IEpoch.MASTER_BRANCH_NAME)
                .getQueryRunner()
                .getByKeyQuery(this.traderQuery)
                .withKey(tradeId)
                .run();
        return (String) recordReader.read(0);
    }
}

List queries​

Minimal list query​

The minimal list query asks a table name and retrieve all records:

final IPreparedListQuery query = database.getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withoutCondition()
        .selectingAllTableFields()
        .compile();

To run the query, use the QueryRunner of your database version:

databaseVersion.getQueryRunner().listQuery(preparedListQuery).withoutParameters().run();

Adding a condition​

The list query can be filtered using a condition builder.

Let's retrieve the trades made by John:

final IPreparedListQuery preparedListQuery = database.getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader")).as("parameter"))
        .selectingAllTableFields()
        .compile();
databaseVersion.getQueryRunner()
        .listQuery(preparedListQuery)
        .withParameters(Map.of("parameter", "Paul"))
        .run();

Selecting fields​

If not all fields are useful, we can select the fields returned by the query:

final IPreparedListQuery preparedListQuery = database.getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .withFields(FieldPath.of("tradeId"), FieldPath.of("date"))
        .compile();

Aliasing fields​

We can alias the fields returned by the query like in SQL queries:

final IPreparedListQuery preparedListQuery = database.getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .withAliasedFields(
                AliasedField.create("alias1", FieldPath.of("tradeId")),
                AliasedField.create("alias2", FieldPath.of("date")))
        .compile();
final var alias1Index =
        preparedListQuery.getQueryResultFormat().getPlainRecordFormat().getFieldIndex("alias1");

Adding a timeout or a result limit​

For performance reasons, we can restrict the query by adding a result limit and/or a timeout. The timeout will cancel the query if reached and an exception will be thrown. The result limit set the maximal number of rows that will be returned.

final IPreparedListQuery preparedListQuery = database.getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .withFields(FieldPath.of("tradeId"), FieldPath.of("date"))
        .withResultsLimit(1000)
        .compile();
databaseVersion.getQueryRunner()
        .listQuery(preparedListQuery)
        .withoutParameters()
        .withTimeout(Duration.ofSeconds(10L))
        .run();

Adding query tags​

Each query can have a map of tags to help monitoring the application. For example, we can add the name of the operation:

final IPreparedListQuery preparedListQuery = database.getQueryManager()
        .listQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "Paul"))
        .withFields(FieldPath.of("tradeId"), FieldPath.of("date"))
        .withQueryTags(Map.of("operation", "trade-inspection"))
        .compile();
databaseVersion.getQueryRunner().listQuery(preparedListQuery).withoutParameters().run();

Using a list query​

The list query will return a ICursor that you can use to manipulate every record.

For example, we can retrieve the sum of "value" by date:

public Map<Object, Double> getValueByDates() {
    final IPreparedListQuery query = database.getQueryManager()
            .listQuery()
            .forTable("trades")
            .withCondition(BaseConditions.in(FieldPath.of("City")).as("city"))
            .withFields(FieldPath.of("date"), FieldPath.of("value"))
            .compile();
    final ICursor result = databaseVersion.getQueryRunner()
            .listQuery(query)
            .withParameters(Map.of("city", Set.of("Paris", "London")))
            .run();
    final Map<Object, Double> valuesByDates = new HashMap<>();
    while (result.next()) {
        final Object date = result.getRecord().read(0);
        final double value = result.getRecord().readDouble(1);
        valuesByDates.merge(date, value, Double::sum);
    }
    return valuesByDates;
}

Distinct queries​

The distinct query allows to retrieve all distinct values for a field or a field combination from a record list. Record list could be filtered with some specified conditions.

Its characteristics are very similar to list query ones. It returns a ICursor, and provides following options:

  • Field aliasing
  • Limit
  • Timeout
  • Query tags

This sample query extracts all the counterparties john has traded with: 

final IPreparedDistinctQuery preparedDistinctQuery = database.getQueryManager()
        .distinctQuery() // the builder guides you and impose the steps order
        .forTable("trades")
        .withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
        .withFields(FieldPath.of("counterParty"))
        .withResultsLimit(1000)
        .withQueryTags(Map.of("operation", "trade-inspection"))
        .compile();
databaseVersion.getQueryRunner()
        .distinctQuery(preparedDistinctQuery)
        .withoutParameters()
        .withTimeout(Duration.ofSeconds(10L))
        .run();

Note on joined projection fields​

Projection fields belonging to a table other than the base table are joined with a left join. This implies that default or null values can be present in the distinct result.