Crieria API query using criteriabuilder.construct with a non existing relationship

Question

Given this very simple DTO:

@Entity
public class Employee implements Serializable {

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

private String name;

@OneToOne
private Employee boss;

}

I'd like to make a query that gathers all employee names and their boss' id, put in a nice clean POJO:

public class EmployeeInfo {

private String name;
private Long bossId;

public EmployeeInfo(String name, Long bossId) {
    this.name = name;
    this.bossId = bossId;
}

}

This query should be of use:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<EmployeeInfo> query = cb.createQuery(EmployeeInfo.class);
Root<Employee> root = query.from(Employee.class);
query.select(
        cb.construct(EmployeeInfo.class,
                root.get("name").as(String.class),
                root.get("boss").get("id").as(Long.class)));
result = em.createQuery(query).getResultList();

When a bossId is present in the employee column this works just fine. But when no boss id is set the record will be completly ignored. So how do i treat this non existing boss relation as null or 0 for the construct/multiselect?

In pure SQL it is easy:

SELECT name, COALESCE(boss_id, 0) FROM EMPLOYEE;

But for the love of god i cannot make the criteria api do this.

cb.construct(EmployeeInfo.class,
        root.get("name").as(String.class),
        cb.coalesce(root.get("boss").get("id").as(Long.class), 0L)));

Answer 1

The problem is that root.get("boss") generate query with cross join like this from Employee employee, Employee boss where employee.boss.id=boss.id. So records where employee.boss.id is null are ignored.

To solve the problem you should use root.join("boss", JoinType.LEFT) instead of root.get("boss")