JPQL supports the invocation of built-in and user-defined database functions. Following is the syntax:

FUNCTION(function_name {, function_arg}*)

Example

Entity

@Entity
public class ScheduledEvent {
  @Id
  @GeneratedValue
  private long id;
  private String eventName;
  private Date date;
  private Time start;
  private Time end;
  private Timestamp eventCreated;
    .............
}

Calling database function

In this example we are using H2 database. We are going to use following H2 built-in functions:

MONTHNAME(dateTime): Returns the name of the month.

DATEADD(datetimeField, units, datetime): Adds units to a datetime value. For negative values units are subtracted. Example DATEADD('SECOND', 10, CURRENT_TIMESTAMP) will add 10 sec to current timestamp.

REPLACE(string, searchString [, replacementString]): Replaces all occurrences of a search string in a text with another string. Example: REPLACE('afternoon', 'noon', 'effect') will return 'aftereffect'.

The complete list of H2 built-in database functions is here.

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

  public static void main(String[] args) {
      try {
          persistEvents();
          findEventMonths();
          findEventCreatedWithin10Sec();
          findReplacedEventNames();
      } finally {
          entityManagerFactory.close();
      }
  }

  private static void persistEvents() {
      ScheduledEvent e1 = ScheduledEvent.create("Test event",
              LocalDate.of(2018, 4, 10),
              LocalTime.of(9, 30, 45),
              LocalTime.of(11, 45, 30),
              LocalDateTime.now().minusDays(10));

      ScheduledEvent e2 = ScheduledEvent.create("Test event2",
              LocalDate.now(),
              LocalTime.of(9, 30, 45),
              LocalTime.of(20, 45, 30),
              LocalDateTime.now().minusMonths(1));

      ScheduledEvent e3 = ScheduledEvent.create("Test event3",
              LocalDate.now(),
              LocalTime.of(18, 0, 0),
              LocalTime.of(20, 0, 0),
              LocalDateTime.now());

      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(e1);
      em.persist(e2);
      em.persist(e3);
      em.getTransaction().commit();

      System.out.println("-- all persisted entities --");
      em.createQuery("SELECT e FROM ScheduledEvent e")
        .getResultList()
        .forEach(System.out::println);
      em.close();
  }

  private static void findEventMonths() {
      System.out.println("-- event months --");
      EntityManager em = entityManagerFactory.createEntityManager();
      List<Object[]> list = em
              .createQuery("SELECT e.eventName, FUNCTION('MONTHNAME', e.date) FROM ScheduledEvent e")
              .getResultList();
      list.forEach(e -> System.out.println(Arrays.toString(e)));
      em.close();
  }


  private static void findEventCreatedWithin10Sec() {
      System.out.println("-- event created within 10 sec --");
      EntityManager em = entityManagerFactory.createEntityManager();
      List list = em
              .createQuery("SELECT e FROM ScheduledEvent e WHERE e.eventCreated BETWEEN  "
                      +" FUNCTION('DATEADD','SECOND', -10, CURRENT_TIMESTAMP) "
                      + " AND CURRENT_TIMESTAMP")

              .getResultList();
      list.forEach(System.out::println);
      em.close();
  }

  private static void findReplacedEventNames() {
      System.out.println("-- event names after replacing 'Test' with 'Scheduled' --");
      EntityManager em = entityManagerFactory.createEntityManager();
      List list = em
              .createQuery("SELECT FUNCTION('REPLACE', e.eventName, 'Test', 'Scheduled')"
                      + " FROM ScheduledEvent e")
              .getResultList();

      list.forEach(System.out::println);
      em.close();
  }
}

-- all persisted entities --
ScheduledEvent{id=1, eventName='Test event', date=2018-04-10, start=09:30:45, end=11:45:30, eventCreated=2018-06-06 00:37:01.883}
ScheduledEvent{id=2, eventName='Test event2', date=2018-06-16, start=09:30:45, end=20:45:30, eventCreated=2018-05-16 00:37:01.885}
ScheduledEvent{id=3, eventName='Test event3', date=2018-06-16, start=18:00:00, end=20:00:00, eventCreated=2018-06-16 00:37:01.885}
-- event months --
[Test event, April]
[Test event2, June]
[Test event3, June]
-- event created within 10 sec --
ScheduledEvent{id=3, eventName='Test event3', date=2018-06-16, start=18:00:00, end=20:00:00, eventCreated=2018-06-16 00:37:01.885}
-- event names after replacing 'Test' with 'Scheduled' --
Scheduled event
Scheduled event2
Scheduled event3

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