Close

JPQL Downcasting With TREAT Operator

[Updated: Dec 11, 2018, Created: May 26, 2018]

The operator TREAT can be used to cast an entity to its subclass value, i.e. downcast the related entities with inheritance.

This is typically used in JOIN queries where we want to impose a restriction in WHERE clause involving a subclass field

Example

Entities

@Entity
public class Project {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  @OneToMany(cascade = CascadeType.ALL)
  private List<Employee> employees;
    .............
}
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@Entity
@DiscriminatorColumn(name = "EMP_TYPE")
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
    .............
}
@Entity
@DiscriminatorValue("F")
public class FullTimeEmployee extends Employee {
  private int annualSalary;
    .............
}
@Entity
@DiscriminatorValue("P")
public class PartTimeEmployee extends Employee {
  private int weeklySalary;
    .............
}
@Entity
@DiscriminatorValue("C")
public class ContractEmployee extends Employee {
  private int hourlyRate;
    .............
}

Using TREAT operator

Following example will execute a query which will return all projects where FullTimeEmployee's annualSalary is greater than 10000. As FullTimeEmployee is a subclass of the relation 'Project#employees', we need to downcast it using TREAT operator.

public class ExampleMain {

  public static void main(String[] args) throws Exception {
      EntityManagerFactory emf =
              Persistence.createEntityManagerFactory("example-unit");
      try {
          persistEntities(emf);
          runProjectQuery(emf);
      } finally {
          emf.close();
      }
  }

  private static void persistEntities(EntityManagerFactory emf) throws Exception {
      System.out.println("-- Persisting entities --");
      EntityManager em = emf.createEntityManager();
      em.getTransaction().begin();
      for (Project project : createProjects()) {
          em.persist(project);
      }
      em.getTransaction().commit();
  }

  private static void runProjectQuery(EntityManagerFactory emf) {
      System.out.println("-- running query --");
      EntityManager em = emf.createEntityManager();
      List<Project> entityAList = em
              .createQuery("SELECT DISTINCT p FROM Project p JOIN TREAT(p.employees AS FullTimeEmployee) e"
                      + " WHERE e.annualSalary > 100000 ")
              .getResultList();
      entityAList.forEach(System.out::println);
      em.close();
  }


  private static List<Project> createProjects() {
      List<Project> projects = new ArrayList<>();

      FullTimeEmployee e1 = new FullTimeEmployee();
      e1.setName("Sara");
      e1.setAnnualSalary(120000);
      PartTimeEmployee e2 = new PartTimeEmployee();
      e2.setName("Jon");
      e2.setWeeklySalary(900);
      ContractEmployee e3 = new ContractEmployee();
      e3.setName("Tom");
      e3.setHourlyRate(60);
      projects.add(Project.create("Trade UI", e1, e2, e3));


      FullTimeEmployee e4 = new FullTimeEmployee();
      e4.setName("Mike");
      e4.setAnnualSalary(80000);
      PartTimeEmployee e5 = new PartTimeEmployee();
      e5.setName("Jackie");
      e5.setWeeklySalary(1200);
      ContractEmployee e6 = new ContractEmployee();
      e6.setName("Aly");
      e6.setHourlyRate(90);
      projects.add(Project.create("Broker UI", e4, e5, e6));

      return projects;
  }
}
-- Persisting entities --
-- running query --
Project{id=1, name='Trade UI', employees=[FullTimeEmployee{id=2, name='Sara', annualSalary=120000}, PartTimeEmployee{id=3, name='Jon'weeklySalary=900}, ContractEmployee{id=4, name='Tom', hourlyRate='60'}]}

Example Project

Dependencies and Technologies Used:

  • h2 1.4.197: H2 Database Engine.
  • hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
    Implements javax.persistence:javax.persistence-api version 2.1
  • JDK 1.8
  • Maven 3.3.9

TREAT operator Example Select All Download
  • jpql-treat-downcasting-example
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also