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.
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);