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")
.withTableFields("trader")
.toQuery();
final IRecordReader record = databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(1023).run();
if (record != null) {
final var trader = record.read(0);
}
If the table has multiple key fields (e.g. year
, month
, and day
), we can pass key values either as variadic
arguments or as a list:
databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(1998, 11, 21).run();
databaseVersion.getQueryRunner().getByKeyQuery(query).withKey(List.of(1998, 11, 21)).run();
You can find the list of key fields for a given store using IDataTable#getKeyFieldNames
. Key values should be
passed to withKey()
in this exact order.
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").withTableFields("trader").compile();
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")
.withTableFields("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"), "John"))
.selectingAllTableFields()
.compile();
Dynamic conditions
The condition can be parameterized at the query compilation, and its parameter values and the parameter values specified at query execution.
Let's retrieve the trades made by Paul with that mechanism:
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. There are 3 different ways of doing so.
Aliasing fields
This is the generic approach. 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("ToProduct", "currency")))
.compile();
final var alias1Index =
preparedListQuery.getQueryResultFormat().getPlainRecordFormat().getFieldIndex("alias1");
All fields belong to queried table
If all the fields come from the queried table, there is a shorter method that will use the field name as alias:
.withAliasedFields(
AliasedField.create("tradeId", FieldPath.of("tradeId")),
AliasedField.create("date", FieldPath.of("date")))
This can be replaced by:
.withTableFields("tradeId", "date")
Fields without alias
In case only the position (and not the alias) of the fields in the results matters, then the following is also possible:
final IPreparedListQuery preparedListQuery = database.getQueryManager()
.listQuery() // the builder guides you and impose the steps order
.forTable("trades")
.withCondition(BaseConditions.equal(FieldPath.of("trader"), "John"))
.withFieldsWithoutAlias(FieldPath.of("tradeId"), FieldPath.of("ToProduct", "currency"))
.compile();
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"))
.withTableFields("tradeId", "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"))
.withTableFields("tradeId", "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"))
.withTableFields("date", "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"))
.withTableFields("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.