Convert PosgreSQL to Criteria API

Question

In my spring app, I have two tables. Among workers, there are bosses and both roles can make items. I want to know how many items were made by definite bosses workers (include bosses items) and made after 11.9.10 (as an example, it should be variable in method.) So the method should be

public Long fingCountByDateAndWorker(Worker worker, Instant date) here boss worker and date of creatin item

I got the right value by the script in PostgreSQL, but I can't make the right code by Criteria API (Java) This is the script :

select distinct COUNT(item.id) FROM worker w1
JOIN worker w2 ON w1.id = w2.boss_id
JOIN item it ON it.date_create >= '2020-01-10 15:51:33'
and (it.worker_id = w2.id or it.worker_id = 2(boss worker id))
where w1.id = 2(boss worker id)

How to convert it into java criteria API code?

Answer 1

You have to specify the id of the parent entity. Please try with the below method

public Long findCountByDateAndWorker_WorkerId(Instant date,Long workerId)

Answer 2

With the given Tables your Entities will be something like this:

@Entity
public class Worker{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    Worker boss;

    ...

}


@Entity
public class Item{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToOne
    Worker worker;

    LocalDate dateCreate;

    ...

}

Then you can try something like this in your Method:

public Long findCountByDateAndWorker(Worker worker, LocalDate date){
    Long result = 0l;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Long> cq = cb.createQuery(Long.class);
    Root<Item> r = cq.from(Item.class);
    cq.select(cb.count(r)).where(cb.and(
                        cb.or(cb.equal(r.get(Item_.worker), worker),
                              cb.equal(r.get(Item_.worker).get(Worker_.boss), worker)),
                        cb.greaterThanOrEqualTo(r.get(Item_.dateCreate, date))));
    
    TypedQuery<Long> query = em.createQuery(cq);
    
    try{
        result = query.getSingleResult();
    }catch(NoResultException nre) {}
    
    return result;
}