许多单向关系的Spring Data JPA规范

Question

我有一个设置，猫可以由许多主人拥有，每个主人可以拥有几只猫。有鉴于此，我想写一个规范来帮助我找到所有拥有给定主人名字的猫。

下面是一个简单的类设置。

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

*简洁性方面没有获得者/接受者。Id字段位于超类中。

@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();
}

*简洁性方面没有获得者/接受者。Id字段位于超类中。

这是一个存储库，其中有一个有效的查询和一个无效的规范。

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);
      };
    }
  }
}

请帮我写一下说明书。

我遇到的问题是，这种关系似乎需要与这种关系的表达方式背道而驰：主人有一个猫列表，但猫没有主人列表。

Answer 1

简单的回答是：

 Page<Cat> findByOwner_Name(String ownerName, Pageable p);

但困难在于如何查询那些不属于任何人的猫，这等于

select * from cat where cat.id NOT IN (select cat.id from owner_has_cat);

有人知道吗？

Answer 2

您可以使用子查询生成一个IN谓词来获取cat的id值。

public static class Specs {
  static Specification<Cat> hasOwnerName(final String ownerName) {
    return (root, query, cb) -> {
      //EntityType<Cat> Cat_ = root.getModel();
      final Subquery<Long> queryOwner = query.subquery(Long.class);// Check type of the Cat's ID attribute
      final Root<Owner> aliasOwner = queryOwner.from(Owner.class);
      //EntityType<Owner> Owner_ = aliasOwner.getModel();
      //final Join<Owner, Cat> aliasCatsOwner = aliasOwner.join(Owner_.cats);
      final Join<Owner, Cat> aliasCatsOwner = aliasOwner.join("cats");
      //queryOwner.select(aliasCatsOwner.<Long> get(Cat_.id)));
      queryOwner.select(aliasCatsOwner.<Long> get("id")));// Check type and name of the Cat's ID attribute
      queryOwner.where(cb.equal(Owner.<String> get("name"), ownerName));
      //return cb.in(root.get(Cat_.id).value(queryOwner);
      return cb.in(root.get("id").value(queryOwner);//check the name of ID attribute!
    };
  }
}

Answer 3

该规范的难点在于，您查询的是与所有者没有直接关系的猫。

总体思路是：

• 与车主取得联系

我首选的方法是使用子查询来介绍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);
        };
    }
}

Hibernate4.3。x生成SQL查询，如下所示：

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
            ))
    )

另一种方法是使用笛卡尔乘积来介绍所有者：

// 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));
        };
    }
}

Hibernate4.3。x生成SQL查询，如下所示：

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
    ))