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:

GetByKeyQuery query = databaseVersion.getQueryManager()
        .getByKeyQuery()
        .forTable("trades")
        .withFields(FieldPath.of("trader"))
        .toQuery();
IRecordReader record = databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(1023).run();
if (record != null) {
    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:

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) {
        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:

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:

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:

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:

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();
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.

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:

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 IDictionaryCursor that you can use to manipulate every record.

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

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