Close

JPA Criteria API - Calling Database Function with CriteriaBuilder.function()

[Updated: Apr 9, 2019, Created: Apr 9, 2019]

In Criteria API following method of CriteriaBuilder can be used to execute a database function.

Expression<T> function(String name, Class<T> type, Expression<?>... args);

Quick Example

Following example uses H2 database DATEDIFF function.

DATEDIFF(datetimeField, aDateAndTime, bDateAndTime)

Above function returns the number of date-time unit between two date-time values in long. The 'datetimeField' indicates the unit.

   ...
   CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
   Root<Employee> employee = query.from(Employee.class);
   query.multiselect(employee.get(Employee_.name),
           criteriaBuilder.function("DATEDIFF", Long.class,
                   criteriaBuilder.literal("YEAR"),
                   employee.get(Employee_.JOIN_DATE),
                   criteriaBuilder.literal(Timestamp.valueOf(LocalDateTime.now()))
           ));
   List<Object[]> resultList = entityManager.createQuery(query).getResultList();
   .......        

Complete Example

JPA Entity

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  private Timestamp joinDate;
  private long salary;
    .............
}

Calling database function

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
          findEmployeeWithEmploymentYears();
      } finally {
          entityManagerFactory.close();
      }
  }

  private static void findEmployeeWithEmploymentYears() {
      System.out.println("-- Employee name with employment years  --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
      Root<Employee> employee = query.from(Employee.class);
      query.multiselect(employee.get(Employee_.name),
              criteriaBuilder.function("DATEDIFF", Long.class,
                      criteriaBuilder.literal("YEAR"),
                      employee.get(Employee_.JOIN_DATE),
                      criteriaBuilder.literal(Timestamp.valueOf(LocalDateTime.now()))
              ));
      List<Object[]> resultList = entityManager.createQuery(query).getResultList();
      resultList.forEach(arr -> System.out.println(Arrays.toString(arr)));
      entityManager.close();
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", 3000, LocalDate.of(1999, 11, 15));
      Employee employee2 = Employee.create("Rose", 4000, LocalDate.of(2011, 5, 1));
      Employee employee3 = Employee.create("Denise", 1500, LocalDate.of(2006, 1, 10));
      Employee employee4 = Employee.create("Mike", 2000, LocalDate.of(2015, 8, 20));

      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.persist(employee4);
      em.getTransaction().commit();
      em.close();
      System.out.println("-- employee persisted --");
      System.out.println(employee1);
      System.out.println(employee2);
      System.out.println(employee3);
      System.out.println(employee4);
  }
}
-- employee persisted --
Employee{id=1, name='Diana', joinDate=1999-11-14 18:00:00.0, salary=3000}
Employee{id=2, name='Rose', joinDate=2011-04-30 19:00:00.0, salary=4000}
Employee{id=3, name='Denise', joinDate=2006-01-09 18:00:00.0, salary=1500}
Employee{id=4, name='Mike', joinDate=2015-08-19 19:00:00.0, salary=2000}
-- Employee name with employment years --
[Diana, 20]
[Rose, 8]
[Denise, 13]
[Mike, 4]

Example Project

Dependencies and Technologies Used:

  • h2 1.4.199: H2 Database Engine.
  • hibernate-core 5.4.2.Final: Hibernate's core ORM functionality.
    Implements javax.persistence:javax.persistence-api version 2.2
  • hibernate-jpamodelgen 5.4.2.Final: Annotation Processor to generate JPA 2 static metamodel classes.
  • JDK 1.8
  • Maven 3.5.4

CriteriaBuilder.function() Example Select All Download
  • jpa-criteria-api-function-call
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also