Close

JPQL Polymorphic Queries

[Updated: May 27, 2018, Created: May 25, 2018]

All JPQL queries are polymorphic.
That means, the FROM clause of a query returns not only instances of the specific entity class(es) to which it explicitly refers but instances of subclasses of those classes as well.

Entity type expressions can be used to restrict query polymorphism. This can be achieve by using TYPE operator.

Let's understand that with examples.

Example

Entities

package com.logicbig.example;

import javax.persistence.*;

@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;
    .............
}

Executing Query

public class ExampleMain {

  public static void main(String[] args) throws Exception {
      EntityManagerFactory emf =
              Persistence.createEntityManagerFactory("example-unit");
      try {
          persistEntities(emf);
          runQuery(emf);
          runQuery2(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 (Employee employee : createEmployees()) {
          em.persist(employee);
      }
      em.getTransaction().commit();
  }

  private static void runQuery(EntityManagerFactory emf) {
      System.out.println("-- running query for Employee --");
      EntityManager em = emf.createEntityManager();
      List<Employee> entityAList = em.createQuery("SELECT t FROM Employee t")
                                     .getResultList();
      entityAList.forEach(System.out::println);
      em.close();
  }

  private static void runQuery2(EntityManagerFactory emf) {
      System.out.println("-- running query for ContractEmployee --");
      EntityManager em = emf.createEntityManager();
      List<Employee> entityAList = em.createQuery("SELECT t FROM ContractEmployee t")
                                     .getResultList();
      entityAList.forEach(System.out::println);
      em.close();
  }

  private static List<Employee> createEmployees() {
      List<Employee> list = new ArrayList<>();
      FullTimeEmployee e = new FullTimeEmployee();
      e.setName("Sara");
      e.setSalary(100000);
      list.add(e);

      e = new FullTimeEmployee();
      e.setName("Mike");
      e.setSalary(90000);
      list.add(e);

      PartTimeEmployee e2 = new PartTimeEmployee();
      e2.setName("Jon");
      e2.setWeeklySalary(900);
      list.add(e2);

      e2 = new PartTimeEmployee();
      e2.setName("Jackie");
      e2.setWeeklySalary(1200);
      list.add(e2);

      ContractEmployee e3 = new ContractEmployee();
      e3.setName("Tom");
      e3.setHourlyRate(60);
      list.add(e3);

      e3 = new ContractEmployee();
      e3.setName("Aly");
      e3.setHourlyRate(90);
      list.add(e3);
      return list;
  }
}
-- Persisting entities --
-- running query for Employee --
FullTimeEmployee{id=1, name='Sara', salary=100000}
FullTimeEmployee{id=2, name='Mike', salary=90000}
PartTimeEmployee{id=3, name='Jon'weeklySalary=900}
PartTimeEmployee{id=4, name='Jackie'weeklySalary=1200}
ContractEmployee{id=5, name='Tom', hourlyRate='60'}
ContractEmployee{id=6, name='Aly', hourlyRate='90'}
-- running query for ContractEmployee --
ContractEmployee{id=5, name='Tom', hourlyRate='60'}
ContractEmployee{id=6, name='Aly', hourlyRate='90'}

As seen above, the first query returns all subclasses of Employee entity. The second query shows that we can target a subclass in a query as well.

Entity Type Expression

Let's see how to use TYPE operator to restrict polymorphic query.

public class ExampleMain2 {

  public static void main(String[] args) throws Exception {
      EntityManagerFactory emf =
              Persistence.createEntityManagerFactory("example-unit");
      try {
          persistEntities(emf);
          runTypeEqualsQuery(emf);
          runTypeInQuery(emf);
          runTypeNotInQuery(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 (Employee employee : createEmployees()) {
          em.persist(employee);
      }
      em.getTransaction().commit();
  }

  private static void runTypeEqualsQuery(EntityManagerFactory emf) {
      System.out.println("-- running TYPE with '=' query --");
      EntityManager em = emf.createEntityManager();
      List<Employee> entityAList = em.createQuery("SELECT t FROM Employee t WHERE TYPE(t) = FullTimeEmployee")
                                     .getResultList();
      entityAList.forEach(System.out::println);
      em.close();
  }

  private static void runTypeInQuery(EntityManagerFactory emf) {
      System.out.println("-- running TYPE with 'IN' query --");
      EntityManager em = emf.createEntityManager();
      List<Employee> entityAList =
              em.createQuery("SELECT t FROM Employee t WHERE TYPE(t) IN (ContractEmployee, PartTimeEmployee)")
                .getResultList();
      entityAList.forEach(System.out::println);
      em.close();
  }

  private static void runTypeNotInQuery(EntityManagerFactory emf) {
      System.out.println("-- running TYPE NOT 'IN' query --");
      EntityManager em = emf.createEntityManager();
      List<Employee> entityAList =
              em.createQuery("SELECT t FROM Employee t WHERE TYPE(t) NOT IN (ContractEmployee, PartTimeEmployee)")
                .getResultList();
      entityAList.forEach(System.out::println);
      em.close();
  }
    .............
}
-- Persisting entities --
-- running TYPE with '=' query --
FullTimeEmployee{id=1, name='Sara', salary=100000}
FullTimeEmployee{id=2, name='Mike', salary=90000}
-- running TYPE with 'IN' query --
PartTimeEmployee{id=3, name='Jon'weeklySalary=900}
PartTimeEmployee{id=4, name='Jackie'weeklySalary=1200}
ContractEmployee{id=5, name='Tom', hourlyRate='60'}
ContractEmployee{id=6, name='Aly', hourlyRate='90'}
-- running TYPE NOT 'IN' query --
FullTimeEmployee{id=1, name='Sara', salary=100000}
FullTimeEmployee{id=2, name='Mike', salary=90000}

As seen in above code we used TYPE with '=' for a single value and with IN operator for multiple values restriction.
It can also be used with named input parameter.
Also instead of entity name, we can use the discriminator value as well, as following snippet shows:

...
 em.createQuery("SELECT t FROM Employee t WHERE TYPE(t) = 'F' ")
..

where 'F' is the discriminator value for FullTimeEmployee.

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

JPQL Polymorphic Queries Select All Download
  • jpql-polymorphic-queries
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain2.java
          • resources
            • META-INF

    See Also