I’m setting up a JPA Specification based repository implementation that utilizes jpa specifications(constructed based on RSQL filter strings) to filter the results, define result ordering and remove any duplicates via “distinct” that would otherwise be returned due to joined tables. The JPA Specification builder method joins several tables and sets the “distinct” flag:
public final class MySpec implements Specification<Tag>
{
@Override
public Predicate toPredicate(
final Root<Tag> root,
final CriteriaQuery<?> query,
final CriteriaBuilder builder)
{
final Join<Tag, Label> labelsJoin = root.join("labels", JoinType.INNER);
final Join<Label, LabelIdentity> labelIdentityJoin = labelsJoin.join("labelIdentity", JoinType.INNER);
final Predicate labelKeyPredicate = builder.equal(labelIdentityJoin.get("key"), property);
query.distinct(true);
return builder.and(
labelKeyPredicate,
builder.like(labelsJoin.get("value"), argument.replace('*', '%')));
}
}
To allow sorting by joined table columns, I’ve applied the “HINT_PASS_DISTINCT_THROUGH” hint to the relevant repository method(otherwise, sorting by joined table columns returns an error along the lines of “sort column must be included in the SELECT DISTINCT query”).
After those changes, filtering and sorting seems to work as required. However, the hint seems to cause “distinct” filtering to be applied after the result page is already constructed, thus reducing the number of returned entities in the page from the configured “size” PageRequest argument, to whatever is left after the duplicates are filtered out.
My question is:
Is it possible to remove the need to use distinct (and thus solve the paging issue) by somehow reusing the Join instances amoung different Specification instances? For example construct the Join instances, and pass the same Join instance into each new Specification instance (e.g. via the constuctor)?
For example, I’ve tried to create something like the following, and then passed this JoinCache instance into each Specification instance, however, I got errors about incorrect alias, so not sure if something like this is even supported?
public class JoinCache
{
private final CriteriaBuilder criteriaBuilder;
private final CriteriaQuery<Tag> criteriaQuery;
private final Root<Tag> tagRoot;
private final Join<Tag, Label> labelJoin;
private final Join<Label, LabelIdentity> labelIdentityJoin;
public JoinCache(final CriteriaBuilder criteriaBuilder)
{
this.criteriaBuilder = criteriaBuilder;
this.criteriaQuery = this.criteriaBuilder.createQuery(Tag.class);
this.tagRoot = criteriaQuery.from(Tag.class);
this.labelJoin = tagRoot.join("labels", JoinType.INNER);
this.labelIdentityJoin = labelJoin.join("labelIdentity", JoinType.INNER);
}
public Join<Tag, Label> getLabelJoin()
{
return labelJoin;
}
public Join<Label, LabelIdentity> getLabelIdentityJoin()
{
return labelIdentityJoin;
}
public CriteriaBuilder getCriteriaBuilder()
{
return criteriaBuilder;
}
public CriteriaQuery<Tag> getCriteriaQuery()
{
return criteriaQuery;
}
public Root<Tag> getTagRoot()
{
return tagRoot;
}
}