Usage of Coalesce or Case statement in JPA

Question

How to use coalesce or case statement in JPA 2 using CriteriaBuilder

For many records initiatedBy will be empty and as a result employeeName will be null for those records. I would like to display System Generated if employeeName is null for those projects where initiatedBy employee is null in database table.

I have the following relationships in Entities

Project

@Entity
@Table(name = "PROJECT") 
public class Project {

@Id
@Column(name = "PROJECTID")
private Long projectId;
....
....

@ManyToOne
@JoinColumn(name = "EMPLOYEENUMBER", referencedColumnName = "EMPLOYEENUMBER")
private Employee empNumber;

@ManyToOne
@JoinColumn(name = "INITIATEDBY", referencedColumnName = "EMPLOYEENUMBER")
private Employee initiatedBy;

Employee

@Entity
@Table(name = "EMPLOYEES")
public class Employee {

@Id
@Column(name = "EMPLOYEENUMBER")
private String employeeNo;

@Column(name = "EMPLOYEENAME")
private String employeeName;
.....
.....

@OneToMany(mappedBy = "empNumber")
private Set<Project> employeeProject;

@OneToMany(mappedBy = "initiatedBy")
private Set<Project> employeeInitiatedBy;

.....

in DAOImpl class I have

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Project> c = cb.createQuery(Project.class);
Root<Project> emp = c.from(Project.class);
c.orderBy(cb.desc(emp.get("projectNo")));
c.select(emp);

TypedQuery<Project> q =  entityManager.createQuery(c);

I have tried with

Coalesce<String> coalesce = cb.coalesce();
coalesce.value(emp.<String>get("employeeName"));
coalesce.value("System Generated");

However when I run, I am getting exception as

java.lang.IllegalArgumentException: Unable to resolve attribute 
[employeeName] against path

Any help on this is highly appreciable.

Mikko Maunu · Accepted Answer · 2013-07-29T20:18:07.280

First problem is possible misunderstanding in usage of JPA entities as a query result. When entities are used as result of query, they mirror state of database. Replacing Employee.empName with value other than one in database contradicts with that.

Until certain point that can be achieved via CriteriaBuilder.construct. Result objects (which can also be entities) are created via constructor which must take all the select items as an argument. Unfortunately that does not play well together with object graph (Project and connected Employee in this case).

Second problem is that in following get("employeeName") is called for Project, and it does not have such an attribute:

Root<Project> emp = c.from(Project.class);
...
coalesce.value(emp.<String>get("employeeName"));

In general coalesce can be used as follows (but because of what was said above, this does not alone solve problem):

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<String> c = cb.createQuery(String.class);
Root<Employee> emp = c.from(Employee.class);

CriteriaBuilder.Coalesce<String> coalesce = cb.coalesce();
coalesce.value(emp.<String>get("employeeName"));
coalesce.value("System Generated");
c.select(coalesce);

TypedQuery<String> q =  em.createQuery(c);

Mikko so the correct way of using `coalesce` is `coalesce.value(emp.get("employeeName"));`? — Jacob, Jul 29 '13 at 20:04
Not in this case, because in given code, despites it's name emp does not refer to Employee. It is Root emp. Employee does have employeeName attribute, Project doesn't have. I will add example of coalesce usage to answer. — Mikko Maunu, Jul 29 '13 at 20:16

Usage of Coalesce or Case statement in JPA

1 Answers1