Close

JPA - Using JDBC escape syntax Date Literals in JPQL with Eclipse Link

This example shows that, the JDBC escape syntax for date, time, and timestamp literals works in EclipseLink (it does not work with Hibernate though, check out this tutorial).

Example

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

  public static void main(String[] args) {
      try {
          persistEmployees();
          System.out.println("-- Finding Employee by join date --");
          findEmployeeByJoinDate();
      } finally {
          entityManagerFactory.close();
      }
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", Dept.IT, 3000, LocalDate.of(1999, 11, 15), true);
      Employee employee2 = Employee.create("Rose", Dept.ADMIN, 4000, LocalDate.of(2011, 5, 1), false);
      Employee employee3 = Employee.create("Denise", Dept.IT, 1500, LocalDate.of(2006, 1, 10), true);
      Employee employee4 = Employee.create("Mike", Dept.SALE, 2000, LocalDate.of(2015, 8, 20), false);
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.persist(employee4);
      em.getTransaction().commit();
      em.close();
  }

  private static void findEmployeeByJoinDate() {
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT e FROM Employee e WHERE e.joinDate > {d '2010-05-01'}");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }
}
-- Finding Employee by join date --
Employee{id=2, name='Rose', salary=4000, dept=ADMIN, joinDate=2011-04-30 19:00:00.0, fullTime=false}
Employee{id=4, name='Mike', salary=2000, dept=SALE, joinDate=2015-08-19 19:00:00.0, fullTime=false}

Example Project

Dependencies and Technologies Used:

  • h2 1.4.197: H2 Database Engine.
  • javax.persistence 2.1.1: javax.persistence build based upon git transaction 40384f5.
  • eclipselink 2.7.1: EclipseLink build based upon Git transaction bd47e8f.
    Related JPA version: org.eclipse.persistence:javax.persistence version 2.2.0
  • JDK 1.8
  • Maven 3.3.9

jpql-date-literal-with-eclipse-link-example Select All Download
  • jpql-date-literal-with-eclipse-link-example
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also