JPA Criteria API join on 3 tables and some null elements

user_12735455 0
Viewed 526

I have one parent entity that has two child entities as attributes. I want to select all elements from the parent entity that have EITHER a childOne with a given parameter as personal attribute OR childTwo with that same given parameter as personal attribute.

Here are my three classes simplified:

The Parent Object:

@Entity
public class ParentObject {

    @Id
    private int id;

    private int fkChildOne;

    private int fkChildTwo;

    @ManyToOne
    @JoinColumn(name = "fk_child_one_id", referencedColumnName = 
    "child_one_id")
    private ChildOne childOne;

    @ManyToOne
    @JoinColumn(name = "fk_child_one_id", referencedColumnName = 
    "child_one_id")
    private ChildTwo childTwo;

// getters and setters

}

The Child One Object:

@Entity 
public class ChildOne {

    @Id
    private int childOneId;

    private String nameChildOne;

    @OneToMany
    @JoinColumn(name = "fk_child_one_id")
    private List<ParentObject> parents;


// getters and setters

}

The Child Two Object:

@Entity
public class ChildTwo {

    @Id
    private int childOneId;

    private String nameChildTwo;

    @OneToMany
    @JoinColumn(name = "fk_child_two_id")
    private List<ParentObject> parents;


// getters and setters

}

The Specs Class:

   public static Specification<ParentObject> checkName(String name) {

     return Specifications.where(
             (root, query, builder) -> {

                 final Join<ParentObject, ChildOne> joinchildOne = 
                 root.join("childOne");

                 final Join<ParentObject, ChildTwo > joinchildTwo = 
                 root.join("childTwo");

                 return builder.or(
                         builder.equal(joinchildOne .get("nameChildOne"), name),
                         builder.equal(joinchildTwo .get("nameChildTwo"), name)
                         );
             }
     );
    }

When this spec is called in my service, I get no results. However, if I comment out one of the two joins and the corresponding Predicate in my builder.or method, then I get some results but they obviously don't match what I'm looking for, which is to select every ParentObject that have either ChildOne with that parameter or ChildTwo with that paramater.

Any clue what's wrong with the code ?

criteria-api jpa
2 Answers

Finally got the solution : to fetch all the corresponding results, I had to add the type of the join which would be left join, since I wanted to fetch all ParentObjects regardless of owning childOne or ChildTwo objects.

final Join<ParentObject, ChildOne> joinchildOne = 
                 root.join("childOne", JoinType.LEFT);

                 final Join<ParentObject, ChildTwo > joinchildTwo = 
                 root.join("childTwo", JoinType.LEFT);
user_127354550

Great, now you have to choose if you need to join or fetch.To optimize the query and the memory, you should establish the relations as Lazy (@ManyToMany (fetch = FetchType.LAZY)), so you will only bring the objects that you demand.

The main difference is that Join defines the crossing of tables in a variable and allows you to use it, to extract certain fields in the select clause, for example, on the other hand, fetch makes it feed all the objects of that property. On your example, a select from parent with join of children (once the relation is set to lazy) would only bring initialized objects of type parent, however if you perform a fetch, it would bring the parent and child objects initialized.

Another modification I would make is to change the type of the identifier to non-primitive, so that it accepts null values, necessary for insertion using sequences

user_104652740
Related