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

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.