java - JPA 一对多联接表给出不正确的子记录

Question

我正在使用Spring框架。我有两张表 Package & Item，关系是一个 Package 有很多 Item。表格设计类似于:

套餐

    @Entity
    @Table(name = "TB_PACKAGE")
    public class Packages implements Serializable{

    @Id
    @GeneratedValue(generator = "system-uuid")
    @GenericGenerator(name = "system-uuid", strategy = "uuid")
    @Column(name = "PACKAGE_ID")
    private String packageId;

    @Valid
    @OneToMany(mappedBy = "packages", cascade = { CascadeType.ALL })
    private List<Item> item;

项目

    @Entity
    @Table(name = "TB_ITEM")
    public class Item implements Serializable{

    @Id
    @GeneratedValue(generator = "system-uuid")
    @GenericGenerator(name = "system-uuid", strategy = "uuid")
    @Column(name = "ITEM_ID")
    private String itemId;

    @ManyToOne
    @JoinColumn(name="PACKAGE_ID")
    private Packages packages;

    @Transient
    private String packageId;

Sample records

这就是我如何使用 CriteriaBuilder 编写代码来连接表以获得这样的结果。

SQL:

    SELECT * FROM Packages p JOIN Item i ON p.packageId = i.packageId; 

Java:

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Packages> cq = cb.createQuery(Packages.class);
    Root<Packages> pRoot = cq.from(Packages.class);
    Join<Packages, Item> packagesItem = pRoot.join("item");
    List<Predicate> predicates = new ArrayList<Predicate>();

    if (StringUtils.isNotEmpty(itemName)) {
        predicates.add(cb.like(cb.lower(packagesItem.<String>get("itemName")), "%" + itemName.toLowerCase() + "%"));
    }

    Predicate[] predArray = new Predicate[predicates.size()];
    predicates.toArray(predArray);

    cq.where(predArray);
    cq.distinct(true);

    TypedQuery<Packages> query = em.createQuery(cq);

    return query.getResultList();

如果我输入“phone”作为参数，它应该会输出 JSON 格式的记录:

   "packageId": "P1",
   "item": [
        {
          "itemId": "Item 1",
          "temName" "Phone"
        }
    ]

但是，包总是加载其所有子项，如下所示:

   "packageId": "P1",
   "item": [
        {
          "itemId": "Item 1",
          "temName" "Phone"
        },
        {
          "itemId": "Item 2",
          "temName" "Laptop"
        },
        {
          "itemId": "Item 3",
          "temName" "Mouse"
        }
    ]

我已经尝试了一切:FetchType.Lazy、QueryDsl、Native Query、HQL 等，但仍然没有运气。有谁有解决办法吗？

Answer 1

我认为问题出在 SQL 语法上，在 JOIN 上.

INNER JOIN: Returns all rows when there is at least one match in BOTH tables

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

sometimes it's called LEFT OUTER JOIN.

RIGHT JOIN:返回右表中的所有行以及左表中的匹配行

FULL JOIN:当其中一个表中有匹配项时返回所有行

当您使用JOIN时默认情况下，至少对于 SQL Server 和 MySQL，等于 INNER JOIN 。我认为您的底层 lib/jar 还将为您提供其他 JOIN 的类类型。

尝试指定INNER JOIN ，或给 WHERE尝试一下。对我来说WHERE更加清晰。

这个问题可能有帮助:

Hibernate default joining for nullable many-to-one