Spring Data JPA Specification for a ManyToMany Unidirectional Relationship

Question

I have a setup where cats can be owned by many owners, and each owner can own several cats. Given this, I would like to write a specification to help me find all cats with a given owner name.

Here is a simple class setup.

@Entity
public class Cat extends AbstractEntity {
  @Column
  private String name;
}

* No getters/setters for conciseness. Id field is in the superclass.

@Entity
public class Owner extends AbstractEntity {
  @Column
  private String name;

  @ManyToMany(fetch = FetchType.LAZY)
  @JoinTable(name = "OWNER_2_CATS", 
        joinColumns = @JoinColumn(name = "OWNER_ID"),
        inverseJoinColumns = @JoinColumn(name = "CAT_ID"))
  @OrderColumn(name = "order_column")
  private List<Cat> cats = Lists.newArrayList();
}

* No getters/setters for conciseness. Id field is in the superclass.

And here is a repository with a query that does work and a spec that does not work.

public interface CatRepository extends AtomicsRepository<Cat, Long> {

  // This query works.
  @Query("SELECT c FROM Owner o INNER JOIN o.cats c WHERE o.name = ?")
  List<Cat> findAllByOwner(String ownerName);

  // But how do I accomplish this in a specification?
  public static class Specs {
    static Specification<Cat> hasOwnerName(final String ownerName) {
      return (root, query, cb) -> {
        // These next lines don't work! What do I put here?
        Root<Owner> owner = query.from(Owner.class);
        owner.join("cats");
        return cb.equal(owner.get("name"), ownerName);
      };
    }
  }
}

Please help me write the specification.

What I'm having trouble with is that this relationship seems to need to go against the grain of how the relationship is expressed: an owner has a cats list, but a cat does not have an owners list.

Answer 1

Overview

The trickiness with this Spec is that you are querying the Cat with no direct relationship to Owner.

The general idea is to:

• Get a hold of the Owner.
• Relate the Owner with the Cat using Owner.cats relation membership. We can do this using just the entities, and like JPA handle the entity @Id correlation for us.
• Mark the query on Cat as distinct. Why? Because this is a @ManyToMany relationship and there may be multiple matching Owner for any given Cat depending on the Owner criteria used.

Approach 1 - subquery

My preferred approach would be to use a subquery to introduce the Owner:

// Subquery using Cat membership in the Owner.cats relation
public static class Specs {
    static Specification<Cat> hasOwnerName(final String ownerName) {
        return (root, query, cb) -> {
            query.distinct(true);
            Root<Cat> cat = root;
            Subquery<Owner> ownerSubQuery = query.subquery(Owner.class);
            Root<Owner> owner = ownerSubQuery.from(Owner.class);
            Expression<Collection<Cat>> ownerCats = owner.get("cats");
            ownerSubQuery.select(owner);
            ownerSubQuery.where(cb.equal(owner.get("name"), ownerName), cb.isMember(cat, ownerCats));
            return cb.exists(ownerSubQuery);
        };
    }
}

Which Hibernate 4.3.x generates SQL query like:

select cat0_.id as id1_1_
from cat cat0_
where 
    exists (
        select owner1_.id from owner owner1_ where
            owner1_.name=?
            and (cat0_.id in (
                select cats2_.cat_id from owner_2_cats cats2_ where owner1_.id=cats2_.owner_id
            ))
    )

Approach 2 - cartesian product

An alternative is to use a cartesian product to introduce the Owner:

// Cat membership in the Owner.cats relation using cartesian product
public static class Specs {
    static Specification<Cat> hasOwnerName(final String ownerName) {
        return (root, query, cb) -> {
            query.distinct(true);
            Root<Cat> cat = root;
            Root<Owner> owner = query.from(Owner.class);
            Expression<Collection<Cat>> ownerCats = owner.get("cats");
            return cb.and(cb.equal(owner.get("name"), ownerName), cb.isMember(cat, ownerCats));
        };
    }
}

Which Hibernate 4.3.x generates SQL query like:

select cat0_.id as id1_1_
from cat cat0_
cross join owner owner1_
where
    owner1_.name=?
    and (cat0_.id in (
        select cats2_.cat_id from owner_2_cats cats2_ where owner1_.id=cats2_.owner_id
    ))