CODEWITHSUNDEEP
javahibernatepredicatehibernate-criteria
Pamba
Pamba

Reputation: 822

How to join 3 table using hibernate criteria?

I have three tables.

  1. parent
  2. parent_child_mapping
  3. child

I want to join the 3 tables & filter by child name using 'CriteriaBuilder' & 'Predicate'. How can i implement the below sql query using 'CriteriaBuilder' & 'Predicate'.

  SELECT p.parent_id
  FROM parent p
  JOIN parent_child_mapping pcmap on p.parent_id = pcmap.mapping_parent_id
  JOIN child c on pcmap.mapping_child_id = c.child_id
  WHERE c.child_name = 'abc'

Parent entity

@Entity
@Table(name = "parent")
public class Parent {

    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "parent_id")
    private Long parentId;
    
    ....

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "mappingParentId")
    private Collection<ParentChildMapping> parentChildMappingCollection;
}

Parent child mapping entity

@Entity
@Table(name = "parent_child_mapping")
public class ParentChildMapping{

    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "mapping_id")
    private Long mappingId;
       
    @JoinColumn(name = "mapping_child_id")
    @ManyToOne(optional = false)
    private ChildEntity mappingChildId;
    
    @JoinColumn(name = "mapping_parent_id")
    @ManyToOne(optional = false)
    private ParentEntity mappingParentId;
}

Child entity

@Data
@Entity
@Table(name = "child")
public class Child implements Serializable 
{
    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "child_id")
    private Long childId;
    
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 255)
    @Column(name = "child_name")
    private String childName;
}

Upvotes: 0

Views: 1173

Answers (1)

Christian Beikov
Christian Beikov

Reputation: 16400

You just create a root for every entity and add the condition to the where clause. Something like this:

Root<Parent> parent = criteriaQuery.from(Parent.class);
Root<ParentChildMapping> mapping = criteriaQuery.from(ParentChildMapping.class);
Root<Child> child = criteriaQuery.from(Child.class);
criteriaQuery.select(parent.get("parentId"));
criteriaQuery.where(
  criteriaBuilder.and(
    criteriaBuilder.equal(parent.get("parentId"), mapping.get("mappingParentId")),
    criteriaBuilder.equal(child.get("childId"), mapping.get("mappingChildId")),
    criteriaBuilder.equal(child.get("childName"), "abc")
  )
);

Upvotes: 1

Related Questions