Query from combined spring data specification has multiple joins on same table

Question

Sorry if my terminology isn't correct.

We are using spring data, JpaRepositories and criteria queries as our method to query data from our database.

I have a problem that when I combine two specifications such as I do with hasTimeZone and hasCity in hasCityAndTimeZone in the code example below it does a join on the same table twice, so the query below will look something like

select * from Staff, Location, Location

Is there any way to have the two specifications use the same join instead of each defining their own join that is essentially the same?

Sorry the code probably isn't complete I was just trying to show a quick example.

class Staff {
    private Integer id;
    private Location location;
}

class Location {
    private Integer id; 
    private Integer timeZone;
    private Integer city;
}

class StaffSpecs {
    public static Specification<Staff> hasTimeZone(Integer timeZone) {
        return new Specification<Staff>() {
            @Override
            public Predicate toPredicate(Root<Staff> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                Path<Integer> timeZonePath = root.join(Staff_.location).get(Location_.timeZone);
                return cb.equal(timeZonePath, timeZone);
            }
        }
    }

    public static Specification<Staff> hasCity(Integer city) {
        return new Specification<Staff>() {
            @Override
            public Predicate toPredicate(Root<Staff> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                Path<Integer> cityPath = root.join(Staff_.location).get(Location_.city);
                return cb.equal(cityPath, city);
            }
        }
    }

    public static Specification<Staff> hasCityAndTimeZone(Integer city, Integer timeZone) {
        return where(hasCity(city)).and(hasTimeZone(timeZone));
    }
}

Answer 1

private static Join<?, ?> getOrCreateJoin(From<?, ?> from, String attribute) {

    for (Join<?, ?> join : from.getJoins()) {

        boolean sameName = join.getAttribute().getName().equals(attribute);

        if (sameName && join.getJoinType().equals(JoinType.LEFT)) {
            return join;
        }
    }

    return from.join(attribute, JoinType.LEFT);
}

And in CustomSpecification

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

    query.distinct(true);

    String[] parts = criteria.getKey().split("\\.");
    Path<?> path = root;
    for (String part : parts) {
        if(path.get(part).getJavaType() == Set.class){
            path = getOrCreateJoin(root, part);
        }else{
            path = path.get(part);
            }
        }
    }

....

if (path.getJavaType() == String.class) {
            return builder.like(path.as(String.class), "%" + criteria.getValue().toString() + "%");

....

Answer 2

There's no out of the box way unfortunately. Spring Data internally uses some reuse of joins within QueryUtils.getOrCreateJoin(…). You could find out about potentially already existing joins on the root and reuse them where appropriate:

private static Join<?, ?> getOrCreateJoin(From<?, ?> from, String attribute) {

  for (Join<?, ?> join : from.getJoins()) {

    boolean sameName = join.getAttribute().getName().equals(attribute);

    if (sameName && join.getJoinType().equals(JoinType.LEFT)) {
      return join;
    }
  }

  return from.join(attribute, JoinType.LEFT);
}

Note, that this only works as we effectively know which joins we add ourselves. When using Specifications you should also do, but I just want to make sure nobody considers this a general solution for all cases.

Answer 3

Based on @Oliver answer I created an extension to Specification interface

JoinableSpecification.java

public interface JoinableSpecification<T> extends Specification<T>{

  /**
   * Allow reuse of join when possible
   * @param <K>
   * @param <Z>
   * @param query
   * @return
   */

  @SuppressWarnings("unchecked")
  public default <K, Z> ListJoin<K, Z> joinList(From<?, K> from, ListAttribute<K,Z> attribute,JoinType joinType) {

    for (Join<K, ?> join : from.getJoins()) {

      boolean sameName = join.getAttribute().getName().equals(attribute.getName());

      if (sameName && join.getJoinType().equals(joinType)) {

        return (ListJoin<K, Z>) join; //TODO verify Z type it should be of Z after all its ListAttribute<K,Z>
      }
    }
    return from.join(attribute, joinType);
  }

  /**
   * Allow reuse of join when possible
   * @param <K>
   * @param <Z>
   * @param query
   * @return
   */
  @SuppressWarnings("unchecked")
  public default <K, Z> SetJoin<K, Z> joinList(From<?, K> from, SetAttribute<K,Z> attribute,JoinType joinType) {

    for (Join<K, ?> join : from.getJoins()) {

      boolean sameName = join.getAttribute().getName().equals(attribute.getName());

      if (sameName && join.getJoinType().equals(joinType)) {
        return (SetJoin<K, Z>) join; //TODO verify Z type it should be of Z after all its ListAttribute<K,Z>
      }
    }
    return from.join(attribute, joinType);
  }

  /**
   * Allow reuse of join when possible
   * @param <K>
   * @param <Z>
   * @param query
   * @return
   */
  @SuppressWarnings("unchecked")
  public default <K, Z> Join<K, Z> joinList(From<?, K> from, SingularAttribute<K,Z> attribute,JoinType joinType) {

    for (Join<K, ?> join : from.getJoins()) {

      boolean sameName = join.getAttribute().getName().equals(attribute.getName());

      if (sameName && join.getJoinType().equals(joinType)) {
        return (Join<K, Z>) join; //TODO verify Z type it should be of Z after all its ListAttribute<K,Z>
      }
    }
    return from.join(attribute, joinType);
  }

}

How to use

class StaffSpecs {
 public static Specification<Staff> hasTimeZone(Integer timeZone) {
    return new JoinableSpecification<Staff>() {
        @Override
        public Predicate toPredicate(Root<Staff> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            Path<Integer> timeZonePath = this.joinList(root,Staff_.location,JoinType.INNER).get(Location_.timeZone);
            return cb.equal(timeZonePath, timeZone);
        }
    }
}

 public static Specification<Staff> hasCity(Integer city) {
    return new JoinableSpecification<Staff>() {
        @Override
        public Predicate toPredicate(Root<Staff> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            Path<Integer> cityPath = this.joinList(root,Staff_.location,JoinType.INNER).get(Location_.city);
            return cb.equal(cityPath, city);
        }
    }
}