JPA One-To-Many join table give incorrect child records

Question

I'm using Spring framework. I have two tables Package & Item and the relationship is one Package has many Items. The tables design is something like:

Packages

    @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;

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

This is how I write my code to join table by using CriteriaBuilder to get result like this.

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

If I enter 'phone' as my parameter, it suppose should come out with a record as JSON:

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

However the packages always load with all its child like this:

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

I have tried everything: FetchType.Lazy, QueryDsl, Native Query, HQL and etc. but still no luck. Anyone has the solution?

Abdullah Khan · Answer 1 · 2016-11-16T10:55:07.590

0

Your @OneToMany mapping doesn't seem to be right. Try changing

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

to

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

Once you are done with your mappings you will have to check JOIN FETCH

edited Nov 16 '16 at 10:55

answered Nov 16 '16 at 07:29

Abdullah Khan

12,010
6
65
78

If your mapping are right, i guess the issue is with the fetching. You will have to use `join fetch`. http://stackoverflow.com/questions/12790080/criteria-eager-fetch-joined-collection this link might help you with that. – Abdullah Khan Nov 16 '16 at 08:30
Thank you for you answer! It works like a charm! I have seen this JOIN FETCH before but I have no idea how to apply this on CriteriaBuilder. After you mentioned it I search through it again and try to apply it, then it works! [Reference](http://stackoverflow.com/questions/17306655/using-the-jpa-criteria-api-can-you-do-a-fetch-join-that-results-in-only-one-joi) – Javatar Nov 16 '16 at 09:18

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

I think the problem is on the SQL syntax, on 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: Return all rows from the right table, and the matched rows from the left table

FULL JOIN: Return all rows when there is a match in ONE of the tables

When you use JOIN, by default, at least for SQL Server and MySQL, is equivalant to INNER JOIN. I think your underlying lib/jar will also provide you with classes for other JOIN types.

Try specify INNER JOIN, or give WHERE a try. For me WHERE is more clear.

This question may help:

Hibernate default joining for nullable many-to-one

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 16 '16 at 08:04

WesternGun

11,303
6
88
157

Thank you for your answer but I have found the answer, it is join fetch. – Javatar Nov 16 '16 at 09:18
So in your code what part is wrong? That you must use another class, etc.? Just to learn something new. – WesternGun Nov 16 '16 at 09:21
In my CriteriaBuilder I need to do a fetch rather than a Join. Join packagesItem = (Join) pRoot.fetch("item"); – Javatar Nov 16 '16 at 09:56

score 0 · Answer 3 · edited Mar 04 '22 at 09:02

0

What you need is JOIN FETCH instead of JOIN

Join<Packages, Item> packagesItem = pRoot.fetch("item");

as a JPQL

Select packages from Packages packages join fetch packages.item where packages.item.temName like '%'+itemName+'%';

how-to-properly-express-jpql-join-fetch-with-where-clause-as-jpa-2-criteriaq

edited Mar 04 '22 at 09:02

Glorfindel

21,988
13
81
109

answered Nov 16 '16 at 11:30

Sagar

818
1
6
13

JPA One-To-Many join table give incorrect child records

Packages

Item

SQL:

Java:

3 Answers3

Linked