First, the JPA Criteria backend is shown. How to select the results of a request with logically nested conditions like a request with these parameters: “(quarters=’Q1′ or quarters=’Q2′) and (concept like ‘%Income%’ or (a concept like ‘%revenue%’ and value > 1000000″))” then the result is displayed in an Angular frontend with an Angular Component Tree that displays a Table component in each leaf, and how to make such a Tree perform well.
Flexible Queries in the Backend
The Financial Data Controller receives the post request. It is mapped into a SymbolFinancialQueryParamDto and sent to the FinancialDataService, which ensures a transaction wrapper. The SymbolFinancialsRepositoryBean creates the query in the ‘findSymbolFinancials(…)’ method:
@Override
public List<SymbolFinancials> findSymbolFinancials(SymbolFinancialsQueryParamsDto symbolFinancialsQueryParams) {
List<SymbolFinancials> result = List.of();
...
return the result if only financial elements are queried
...
final CriteriaQuery<SymbolFinancials> createQuery = this.entityManager.getCriteriaBuilder()
.createQuery(SymbolFinancials.class);
final Root<SymbolFinancials> root = createQuery.from(SymbolFinancials.class);
final List<Predicate> predicates = createSymbolFinancialsPredicates(
symbolFinancialsQueryParams, root);
root.fetch(FINANCIAL_ELEMENTS);
Path<FinancialElement> fePath = root.get(FINANCIAL_ELEMENTS);
this.createFinancialElementClauses(symbolFinancialsQueryParams.getFinancialElementParams(),
fePath, predicates);
if (!predicates.isEmpty()) {
createQuery.where(predicates.toArray(new Predicate[0])).distinct(true)
.orderBy(this.entityManager.getCriteriaBuilder().asc(root.get(SYMBOL)));
} else {
return new LinkedList<>();
}
LocalTime start1 = LocalTime.now();
result = this.entityManager.createQuery(createQuery)
.getResultStream().map(mySymbolFinancials -> removeDublicates(mySymbolFinancials))
.limit(100).collect(Collectors.toList());
LOGGER.info("Query1: {} ms", Duration.between(start1, LocalTime.now()).toMillis());
return result;
}
private List<Predicate> createSymbolFinancialsPredicates(
SymbolFinancialsQueryParamsDto symbolFinancialsQueryParams,
final Root<SymbolFinancials> root) {
final List<Predicate> predicates = new ArrayList<>();
if (symbolFinancialsQueryParams.getSymbol() != null
&& !symbolFinancialsQueryParams.getSymbol().isBlank()) {
predicates.add(this.entityManager.getCriteriaBuilder().equal(
this.entityManager.getCriteriaBuilder().lower(root.get(SYMBOL)),
symbolFinancialsQueryParams.getSymbol().trim().toLowerCase()));
}
if (symbolFinancialsQueryParams.getQuarters() != null
&& !symbolFinancialsQueryParams.getQuarters()
.isEmpty()) {
predicates.add(this.entityManager.getCriteriaBuilder().in(root.get(QUARTER))
.value(symbolFinancialsQueryParams.getQuarters()));
}
if (symbolFinancialsQueryParams.getYearFilter() != null
&& symbolFinancialsQueryParams.getYearFilter().getValue() != null
&& 0 >= BigDecimal.valueOf(1800).compareTo(
symbolFinancialsQueryParams.getYearFilter().getValue())
&& symbolFinancialsQueryParams.getYearFilter().getOperation() != null) {
switch (symbolFinancialsQueryParams.getYearFilter().getOperation()) {
case SmallerEqual -> predicates.add(this.entityManager.getCriteriaBuilder()
.lessThanOrEqualTo(root.get(FISCAL_YEAR),
symbolFinancialsQueryParams.getYearFilter().getValue()));
case LargerEqual ->
predicates.add(this.entityManager.getCriteriaBuilder()
.greaterThanOrEqualTo(root.get(FISCAL_YEAR),
symbolFinancialsQueryParams.getYearFilter().getValue()));
case Equal -> predicates.add(this.entityManager.getCriteriaBuilder()
.equal(root.get(FISCAL_YEAR),
symbolFinancialsQueryParams.getYearFilter().getValue()));
}
}
return predicates;
}The method gets the ‘SymbolFinancialQueryParamDto’ with the query parameters. It creates a CriteriaQuery, Root object, and Metamodel for the SymbolFinancials entity. Then the method ‘createSymbolFinancialsPredicates(…)’ is used to create the query predicates for the SymbolFinancials entity. First, the symbol parameter is checked, and a Predicate is created that adds a where clause for an equal lowercase match. Then the Quarter parameters are checked, and a Predicate is created that adds a where clause with the ‘in’ operator to match one of the quarters.
After the check of the ‘YearFilter’ with a check of operator and value (including a range check), the predicate is created. The supported operator values are implemented in the switch for the Operator Enum. For matching an Enum value, the predicate with an equals match is created and added. Then the created predicates are returned to the ‘findSymbolFinancials(…)’ method.
Then the FinancialElement entities of the SymbolFinancials entity are joined and fetched, and a path for the FinancialElement entities is created.
The method called ‘createFinancialElementClauses(…)’ creates the predicates for the nested FinancialElement entities and is described later.
Then it is checked if the predicates are empty, and a where clause is created with the predicates from the ‘predicates’ list. A ‘distinct’ and an ‘order by’ of the symbol property are added.
Then the query is executed and timed with a limit on the result stream to protect the database resources. The stream is filtered for duplicate FinancialElements entries. Some look like duplicates because they are added to several different ‘FinancialElementType’ entities, while others are duplicates of the imported data file.
Nested Conditions in the Query
To support nested conditions for the FinancialElement entities in the backend, the method ‘createFiancialElementClauses(…)’ is used:
private <T> void createFinancialElementClauses(List<FinancialElementParamDto> financialElementParamDtos,
final Path<FinancialElement> fePath, final List<Predicate> predicates) {
record SubTerm(DataHelper.Operation operation, Collection<Predicate> subTerms) {}
final LinkedBlockingQueue<SubTerm> subTermCollection = new LinkedBlockingQueue<>();
final Collection<Predicate> result = new LinkedList<>();
if (financialElementParamDtos != null) {
financialElementParamDtos.forEach(myDto -> {
switch (myDto.getTermType()) {
case TermStart -> {
try {
subTermCollection.put(new SubTerm(myDto.getOperation(), new ArrayList<>()));
} catch (InterruptedException e) {
new RuntimeException(e);
}
}
case Query -> {
Collection<Predicate> localResult = subTermCollection.isEmpty() ? result
: subTermCollection.peek().subTerms();
Optional<Predicate> conceptClauseOpt = financialElementConceptClause(fePath, myDto);
Optional<Predicate> valueClauseOpt = financialElementValueClause(fePath, myDto);
List<Predicate> myPredicates = List.of(conceptClauseOpt, valueClauseOpt).stream()
.filter(Optional::isPresent).map(Optional::get).toList();
if (myPredicates.size() > 1) {
localResult.add(
this.entityManager.getCriteriaBuilder().and(myPredicates.toArray(new Predicate[0])));
} else {
localResult.addAll(myPredicates);
}
}
case TermEnd -> {
if (subTermCollection.isEmpty()) {
throw new RuntimeException(String.format("subPredicates: %d", subTermCollection.size()));
}
SubTerm subTermColl = subTermCollection.poll();
Collection<Predicate> myPredicates = subTermColl.subTerms();
Collection<Predicate> baseTermCollection = subTermCollection.peek() == null ? result
: subTermCollection.peek().subTerms();
DataHelper.Operation operation = subTermColl.operation();
Collection<Predicate> resultPredicates = operation == null ? myPredicates : switch (operation) {
case And -> List.of(this.entityManager.getCriteriaBuilder()
.and(myPredicates.toArray(new Predicate[0])));
case AndNot -> List.of(this.entityManager.getCriteriaBuilder()
.not(this.entityManager.getCriteriaBuilder().and(myPredicates.toArray(new Predicate[0]))));
case Or -> List.of(this.entityManager.getCriteriaBuilder()
.or(myPredicates.toArray(new Predicate[0])));
case OrNot -> List.of(this.entityManager.getCriteriaBuilder()
.not(this.entityManager.getCriteriaBuilder().or(myPredicates.toArray(new Predicate[0]))));
};
baseTermCollection.addAll(resultPredicates);
}
}
});
}
// validate terms
if (!subTermCollection.isEmpty()) {
throw new RuntimeException(String.format("subPredicates: %d", subTermCollection.size()));
}
predicates.addAll(result);
}First, the SubTerm Record is declared, and the Fifo Queue that provides a stack for the nested terms is created. Then the ‘financialElementParamDtos’ are checked and iterated. A switch on the TermType enum handles the ‘TermStart,’ ‘Query,’ and ‘TermEnd.’
The TermStart case inserts a new SubTerm record into the Fifo Queue with the term operation (‘and,’ ‘or,’…). The InterruptedException is not relevant in this use case.
The ‘Query’ case checks if entries are in the ‘subTermCollection’ and returns the current one or the result list. The methods of the SymbolFinancialsRepositoryBean create the optional predicates for the concept and value where clauses/predicates appear. Then a list with the values of the Optionals is created. If the list size is greater than 1, an ‘and’ predicate is created, the values are added to the predicate, and the predicate is returned. Otherwise, the single-element list is added to the result list.
The ‘TermEnd’ case checks if the ‘subTermCollection’ is empty to verify the term structure. Then the SubTerm is polled from the fifo queue, and the predicates are read. Then the base term is set from the subterm collection or the result list, and the operation is read from the subterm collection record.
Then the switch handles the operation enum entry of the ‘TermStart’ SubTerm Record. The cases created matching logical predicates to which the SubTerm predicates/clauses were added. The predicate is then added to the resultPredicates.
In the end, the subTermQueue is checked again for the term structure, and the result predicates are added to the predicate list.
Conclusion Backend
Code that has to support flexible queries will not look trivial. The support of the JPA Criteria API is good, but studying the documentation in-depth first would have saved some time. The predicates are wrapped differently (wrap them in ‘and,’ ‘or,’… predicates) compared to a Jql query. The Criteria API works well and is very flexible once it is understood.
Angular Frontend
The frontend shows a tree of symbols, then years, and then a table of concepts with values and more:
The template for the ‘result-tree’ component looks like this:
<mat-tree
[dataSource]="dataSource"
[treeControl]="treeControl"
class="example-tree"
>
<!-- This is the tree node template for leaf nodes -->
<!-- There is inline padding applied to this node using styles.
This padding value depends on the mat-icon-button width. -->
<mat-tree-node *matTreeNodeDef="let node" matTreeNodeToggle>
<div *ngIf="!node?.finanicalElementExts">{{ node.name }}</div>
<mat-table
*ngIf="node.isOpen"
[dataSource]="node.finanicalElementExts"
class="mytable">
<ng-container matColumnDef="concept">
<mat-header-cell *matHeaderCellDef i18n="@@queryResultsConcept"
>Concept</mat-header-cell>
<mat-cell *matCellDef="let element" matTooltip="{{ element.concept }}">
{{ element.concept }}
</mat-cell>
</ng-container>
<ng-container matColumnDef="quarter">
<mat-header-cell *matHeaderCellDef i18n="@@queryResultsQuarter"
>Quarter</mat-header-cell>
<mat-cell *matCellDef="let element"> {{ element.quarter }} </mat-cell>
</ng-container>
<ng-container matColumnDef="currency">
<mat-header-cell *matHeaderCellDef i18n="@@queryResultsCurrency"
>Currency</mat-header-cell>
<mat-cell *matCellDef="let element"> {{ element.currency }} </mat-cell>
</ng-container>
<ng-container matColumnDef="value">
<mat-header-cell *matHeaderCellDef i18n="@@queryResultsValue"
>Value</mat-header-cell>
<mat-cell *matCellDef="let element"> {{ element.value }} </mat-cell>
</ng-container>
<mat-header-row *matHeaderRowDef="displayedColumns"></mat-header-row>
<mat-row *matRowDef="let row; columns: displayedColumns"></mat-row>
</mat-table>
</mat-tree-node>
<!-- This is the tree node template for expandable nodes -->
<mat-nested-tree-node *matTreeNodeDef="let node; when: hasChild">
<div class="mat-tree-node">
<button
mat-icon-button
(click)="toggleNode(node)"
[attr.aria-label]="'Toggle ' + node.name">
<mat-icon class="mat-icon-rtl-mirror">
{{ treeControl.isExpanded(node) ? "expand_more" : "chevron_right" }}
</mat-icon>
</button>
{{ node.name }}
</div>
<!-- There is inline padding applied to this div using styles.
This padding value depends on the mat-icon-button width. -->
<div
[class.example-tree-invisible]="!treeControl.isExpanded(node)"
role="group">
<ng-container matTreeNodeOutlet></ng-container>
</div>
</mat-nested-tree-node>
</mat-tree>First, construct a “mat-tree” tree of angular material components using the “dataSource” and “treeControl.”
The ‘mat-tree-node’ is used as a tree leaf and shows the Angular Material Components table if the flag ‘node.isOpen’ is set with the node values in the ‘datasource.’ That means the table is only created if the year is opened, which enables the good performance of the tree component in the browser. The columns are created with the ‘mat-header-cell’ for the header and the ‘mat-cell’ for the content. The ‘mat-row’ and ‘mat-header-row’ tags define the displayed columns of the table.
The ‘mat-nested-tree-node’ is used to open/display the tree branches like the symbols and years and is shown if the node is not a tree leaf. The button displays the chevrons and triggers the open and close of the children with the method ‘toggleNode(…)’. The child nodes are displayed in the ‘ng-container matTreeNodeOutlet.’ The visibility is controlled with the ‘example-tree-invisible’ CSS class.
The result-tree component looks like this:
@Component({
selector: "app-result-tree",
templateUrl: "./result-tree.component.html",
styleUrls: ["./result-tree.component.scss"],
})
export class ResultTreeComponent {
private _symbolFinancials: SymbolFinancials[] = [];
protected treeControl = new NestedTreeControl<ElementNode>(
(node) => node.children
);
protected dataSource = new MatTreeNestedDataSource<ElementNode>();
protected displayedColumns: string[] = [
"concept",
"value",
"currency",
"quarter",
];
protected hasChild = (_: number, node: ElementNode) =>
!!node.children && node.children.length > 0;
toggleNode(node: ElementNode): void {
this.treeControl.toggle(node);
node?.children?.forEach((childNode) => {
if (!childNode || !childNode?.children?.length) {
const myByElements = childNode as ByElements;
myByElements.isOpen = this.treeControl.isExpanded(node);
}
});
}
get symbolFinancials(): SymbolFinancials[] {
return this._symbolFinancials;
}
@Input()
set symbolFinancials(symbolFinancials: SymbolFinancials[]) {
this._symbolFinancials = symbolFinancials;
//console.log(symbolFinancials);
this.dataSource.data = this.createElementNodeTree(symbolFinancials);
}
private createElementNodeTree(
symbolFinancials: SymbolFinancials[]
): BySymbolElements[] {
const bySymbolElementExtsMap = FinancialsDataUtils.groupByKey<
FinancialElementExt,
string
>(FinancialsDataUtils.toFinancialElementsExt(symbolFinancials), "symbol");
//console.log(bySymbolElementExtsMap);
const myBySymbolElements: BySymbolElements[] = [];
bySymbolElementExtsMap.forEach((value, key) => {
const byYearElementsMap = FinancialsDataUtils.groupByKey<
FinancialElementExt,
number
>(value, "year");
const byYearElements: ByYearElements[] = [];
byYearElementsMap.forEach((value, key) => {
const myByElements = {
name: "Elements",
isOpen: false,
finanicalElementExts: value,
} as ByElements;
const myByYearElement = {
year: key,
name: key.toString(),
children: [myByElements],
byElements: [myByElements],
} as ByYearElements;
byYearElements.push(myByYearElement);
});
const myBySymbolElement = {
name: key,
symbol: key,
byYearElements: byYearElements,
children: byYearElements,
} as BySymbolElements;
myBySymbolElements.push(myBySymbolElement);
});
console.log(myBySymbolElements);
return myBySymbolElements;
}
}The ‘ResultTreeComponent’ has the treeControl function to toggle the tree children. The ‘displayedColumns’ array contains the names of the columns of the Angular Components table. The ‘hasChild’ function is used to check for child nodes.
The ‘toggleNode(…)’ method uses the toggle function to toggle the child nodes. Then the child nodes are checked to see if the tree leaf nodes are opened, and the ‘isOpen’ property of the leaf nodes is set accordingly. That triggers the rendering of the tables in the opened tree leaf nodes.
The parent component’s symbolFinancials are then inserted in the “set symbolFinancials(…)” method. They are set in the ‘_symbolFinanacials’ property, and the ‘createElementNodeTree(…)’ method is called to create the value for the ‘MatTreeNestedDataSource()’ of the tree component.
The method ‘createElementNodeTree(…)’ returns a ‘BySymbolElements’ array that contains the tree structure for the Angular Components Tree datasource. The method contains nested iterations for the symbols, years, and elements.
First, the ‘bySymbolExtsMap’ Map is created with ‘SymbolFinancials’ objects grouped by the symbol key.
Then the entries of the ‘bySymbolExtsMap’ Map are iterated, and the ‘byYearElementsMap’ is created, where the entries are grouped by the year key.
Then the entries of the ‘bySymbolExtsMap’ Map are iterated, and the tree leaf objects ‘myByElement’ are created with the ‘isOpen’ property set to false. The ‘financialElementExts’ property contains the elements of the table.
Then the ‘myByYearElement’ objects are created that contain the ‘name’ property with the year as a key string. The ‘myByElement’ array is added as a child.
The ‘myBySymbolElement’ objects are created that contain the ‘name’ property with the symbol as a key string, and the ‘myByYearElement’ array is added as children.
This tree data structure array is then returned to be used in the tree data source.
Conclusion Frontend
The Tree/Table Angular Components are very good and easy to use. Using the Tree components was surprisingly easy, and combining them with the table component worked well. The performance has to be considered because the Tree branches and leaves are rendered and hidden when the component is created. To get good performance, the table components need to be created after a tree leaf has been opened.
Source link
