Building conditions

Usage of DbAssist-jpa classes

TODO rest

Building complex conditions on query

To add a single condition you have to create a new ConditionsBuilder instance, and use it to apply the a HierarchyCondition returned by one of the following methods (similar to ones in JPA’s CriteriaBuilder):

• equal
• greaterThan
• greaterThanOrEqualTo
• lessThan
• lessThanOrEqualTo
• in
• notIn
• like
• notLike
• isNull
• isNotNull

For example, to get a list of users on condition that the value of its field id is less than 15, you can use the following lines:

ConditionsBuilder cb = new ConditionsBuilder();
HierarchyCondition hc = cb.lessThan("id", 15);
cb.apply(hc);
List<User> users = uRepo.find(cb);

The code will construct the following query:

... WHERE user.id < 15

The key thing to remember is that whenever we call the ConditionsBuilder’s method such as lessThan(), we do not add the condition to the list of conditions. We have to do it by calling method ConditionsBuilder.apply(hierarchyCondition). The hierarchyCondition can be a result of complex and and or combinations of conditions. The following code shows usage of and method.

ConditionsBuilder cb = new ConditionsBuilder();
HierarchyCondition c1 = cb.lessThan("id", 15);
HierarchyCondition c2 = cb.equal("name", 'Mont');
cb.apply(and(c1, c2));
List<User> users = uRepo.find(cb);

Result:

... WHERE user.id < 15 AND user.name = 'Mont'

As mentioned before, there is a way to combine many conditions in more complex expression using logical AND and OR operators, which are a part of ConditionsBuilder API.

ConditionsBuilder cb = new ConditionsBuilder();

//prepare conditions
HierarchyCondition c1 = cb.lessThan("id", 15);
HierarchyCondition c2 = cb.equal("name", 'Mont');
...
HierarchyCondition c5 = ...

//construct logical expression
HierarchyCondition hc = or(
        and(c1, c2),
        or(c3, and(c4, c5))
);
cb.apply(hc);

List<User> users = uRepo.find(cb);

Result:

WHERE (c1 AND c2) OR c3 OR (c4 AND c5)

Building complex conditions on JOIN query

If we want to create a query with conditions on different entities, we first need to join proper entities using ConditionsBuilder API (similarly as in JPA):

ConditionsBuilder builderUsers = new ConditionsBuilder();
ConditionsBuilder builderCerts = builderUsers.join("certificates", JoinType.LEFT);

Then we introduce conditions, remembering that we have to call the builder corresponding to the entity, we want to put the condition on:

//create conditions
HierarchyCondition cUserId = builderUsers.lessThan("id", 15);
HierarchyCondition cCertName = builderCerts.equal("name", "Java Cert");

HierarchyCondition hc = or(cUserId, cCertName);

builderUsers.apply(hc);
List<User> users = uRepo.find(builderUsers);

Result:

... WHERE user.id < 15 OR certificate.name = 'Java Cert'

It is possible to chain multiple joins, and add conditions on each entity separately, as shown below:

//handle joins for entities chain: User - Certificate - Provider - Country
ConditionsBuilder cb = new ConditionsBuilder();
ConditionsBuilder builderCerts = cb.join("certificates", JoinType.LEFT);
ConditionsBuilder builderProvider = builderCerts.join("provider", JoinType.LEFT);
ConditionsBuilder builderCountry = builderProvider.join("country", JoinType.LEFT);

//create conditions
HierarchyCondition conUserIdGreaterThanOrEqual = cb.greaterThanOrEqualTo("id", 0);
HierarchyCondition conUserIdLessThan = cb.lessThan("id", 15);
HierarchyCondition conCertIdLessThan = builderCerts.lessThan("id", 5);
HierarchyCondition conCertIdGreaterThanOrEqual = builderCerts.greaterThanOrEqualTo("id", 0);
HierarchyCondition conProvName = builderProvider.equal("name", "Provider 1");
HierarchyCondition conCountryName = builderCountry.equal("name", "USA");

//create complex logical expression
HierarchyCondition hc = or(
        and(conUserIdGreaterThanOrEqual, conUserIdLessThan),
        or(
                and(conCertIdGreaterThanOrEqual, conCertIdLessThan),
                or(conProvName, conCountryName)
        )
);

//apply hierarchy of conditions on root ConditionsBuilder, cb
cb.apply(hc);
List<User> users = uRepo.find(cb);

It is important to remember that when creating complex logical expression using static .or(...) and .and(...) methods, they are not instantly applied on the root ConditionsBuilder. By root builder, we mean the one, which will be the argument passed to the repository's find(...) method. The library is handling it in a deferred manner, so that we can first prepare the desired logical hierarchy of conditions and then, once we want to actually use them, we call the apply method on the root ConditionsBuilder. It is also worth mentioning that the individual conditions (leafs) keep track of the entities they belong to by themselves, so we do not have to worry about it.

Result:

... WHERE (user.id >= 0 AND user.id >= 15) OR (certificate.id >= 0 AND certificate.id < 5) OR provider.name = 'Provider 1' OR country.name = 'USA'