Transforming an sql query into CriteriaQuery

Question

I have two tables and they maintain the parent-child relationship between them by a foreign key. The query looks something like below. I want to use the criteriaquery along with jpa. So can anyone help me with the criteriaquery & how the two entity classes would look like ps:if there is any custom enity class required apart from these two entities classes help me with that as well.

Select parent.notification_id,parent.city,parent.name,parent.accountNo,
case when child.accountNo is not null then 'Yes' else 'No' end as checked
FROM parent
JOIN child ON parent.notification_id=child.notification_id_child
AND child.accountNo='test' WHERE parent.city='delhi' or parent.city='all' or parent.accountNo="test";

The column 'notification_id_child' of table 'child' is the foreign key and refers to the primarykey of table 'parent'.

score 1 · Answer 1 · answered May 02 '22 at 12:20

There are multiple strategies that you can use to implement this:

MappedSuperclass (Parent class will be mapped with this annotation and not entity)
Single Table (Single table for each hierarchy, you can use @DiscriminatorColumn JPA annotation for identifying each hierarchy)
Joined Table (Each class for the parent and child) In this scenario, you would have to join both the tables on the common column to fetch the results.

These are some good answers on joining tables

Joining two table entities in Spring Data JPA

Link for some good answers on usage of discrimintaorColumn

How to access discriminator column in JPA

score 0 · Answer 2 · answered May 05 '22 at 18:24

Finally, I managed to solve the problem. My entity classes and criteria query looks something like the below.

Parent Entity

@Entity
@Table(name="parent")
public class Parent{
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name="notification_id")
   private Long notificationId;

   @Column(name="city")
   private String city;
   @Column(name="name")
   private String name;
   @Column(name="accountNo")
   private String accountNo;

   @JoinColumn(name="notification_id_child")
   @OneToMany
   private List<Child> child;

   //Getters Setters
}

Child Entity

@Entity
@Table(name="child")
public class Child{
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name="id")
   private Long id;
   @Column(name="accountNo")   
   private String accountNo;
   @Column(name="notification_id_child")
   private String notificationIdChild;
   //Getters Setters
}

Custom Entity

public class CustomEntity{
    private Long notificationId;
    private String city;
    private String accountNo;
    private String checked;
}

Criteria Query

 @PersistenceContext
 EntitiManager em;    
 CriteraBuilder cb = em.getCriteriaBuilder();
 CriteriaQuery<CustomEntity> cq = cb.createQuery(CustomEntity.class);
 Root<Parent> parentEntity = cq.from(Parent.class);
 Join<Parent,Child> join = parentEntity.join("child", JoinType.LEFT);
 join.on(cb.equal(join.get("accountNo"),"test"));
 
 Path<String> notificationIdPath = parentEntity.get("notificationId");
 Path<String> cityPath = parentEntity.get("city");
 Path<String> accountNoPath = parentEntity.get("accountNo");
 cq.multiselect(notificationIdPath, cityPath, accountNoPath,
 cb.selectCase().when(join.get("accountNo").isNotNull(),"Yes").otherwise("No"));
 
 Path<String> accountNoPath = parentEntity("accountNo");
 Predicate accountNoPredicate = cb.equal(accountNoPath, "test"); 
 Predicate cityPredicateAll = cb.equal(cityPath,"all");
 Predicate cityPredicateSpecified = cb.equal(cityPath,"delhi");
 
 cq.where(cb.or(cityPredicateAll, cityPredicateSpecified, accountNoPredicate));

 TypedQuery<CustomEntity> query = em.createQuery(cq);
 List<CustomEntity> CustomEntityList = query.getResult();

Transforming an sql query into CriteriaQuery

2 Answers2