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