I have three tables.
- parent
- parent_child_mapping
- 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; }
Advertisement
Answer
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") ) );