Using an @Embeddable entity in a JPA CriteriaQuery

Question

Let's say I have the following example entities - one is an @Embeddable, embedded inside another @Entity:

@Embeddable
public class ContactInfoEntity {

    @Column
    private String phone;

    @Column
    private String zipCode;
}

@Entity
@Table(name = "EMPLOYEE")
public class EmployeeEntity {

    @Id
    @Column(name = "EMPLOYEE_ID")
    private Long employeeId;

    @Embedded
    @AttributeOverrides({
        @AttributeOverride(name = "phone",
                           column = @Column(name = "EMPLOYEE_PHONE")),
        @AttributeOverride(name = "zipCode",
                           column = @Column(name = "EMPLOYEE_ZIP_CODE"))
    })
    private ContactInfoEntity employeeContactInfo;
}

The meta-model classes generated by the openjpa-maven-plugin include only an employeeContactInfo variable, not the @AttributeOverride columns.

Now suppose I want to do this:

Select the EMPLOYEE_ID and EMPLOYEE_PHONE where the EMPLOYEE_ZIP_CODE is equal to "123456"

How do I create this as a CriteriaQuery?

CriteriaBuilder cb = entityManager.getCriteriaBuilder();

CriteriaQuery<String> qDef = cb.createQuery(String.class);
Root<EmployeeEntity> e = qDef.from(EmployeeEntity.class);
qDef.select(e.get(EmployeeEntity_.employeeId),
            e.get(????))
    .where(cb.equal(e.get(????), "123456"));

return entityManager.createQuery(qDef).getResultList();

Grossularite

How are ContactInfoEntity and EmployeeEntity related? Shouldn't ResourceNodeEntity be replaced with ContactInfoEntity? – Inly

@Inly Pretend you never saw that :) Copy-paste typo leaking into sample code. Fixed! – Grossularite

Answer 1

An example approach may look like this:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> qDef = cb.createQuery(Object[].class);
Root<EmployeeEntity> e = qDef.from(EmployeeEntity.class);
qDef.multiselect(
     e.get(EmployeeEntity_.employeeId),
     e.get(EmployeeEntity_.employeeContactInfo).get(ContactInfoEntity_.phone));
qDef.where(
     cb.equal(
        e.get(EmployeeEntity_.employeeContactInfo).get(ContactInfoEntity_.zipCode), 
        cb.literal("123456")));

List<Object[]> objects = em.createQuery(qDef).getResultList();
for (Object[] element : objects) {
    System.out.format("%d %s", element[0], element[1]);
}

---

Depending on your preferences you may also want to get the results of the query as:

• constructor expression

  public class EmployeeEntityResult {
      private int id;
      private String phone;
  
      public EmployeeEntityResult(int id, String phone) {
          this.id = id;
          this.phone = phone;
      }
      ...
  }
  

  CriteriaQuery<EmployeeEntityResult> cq = cb.createQuery(EmployeeEntityResult.class);
  ...
  List<EmployeeEntityResult> result = em.createQuery(cq).getResultList();
  for (EmployeeEntityResult element : result) {
      System.out.format("%d %s", element.getId(), element.getPhone());
  }
  

• tuple

  CriteriaQuery<Tuple> cq = cb.createTupleQuery();
  ...
  cq.select(
     cb.tuple(
        e.get(EmployeeEntity_.employeeId)
         .alias("id"),
        e.get(EmployeeEntity_.employeeContactInfo).get(ContactInfoEntity_.phone)
         .alias("phone")));
  ...
  List<Tuple> tuple = em.createQuery(cq).getResultList();
  for (Tuple element : tuple) {
      System.out.format("%d %s", element.get("id"), element.get("phone"));
  }
  

---

The JPQL query looks as follows:

SELECT e.id, e.employeeContactInfo.phone
FROM EmployeeEntity e
WHERE e.employeeContactInfo.zipCode = '123456'

Inly