jpa/eclipselink.jpa.test.jse/src/it/java/org/eclipse/persistence/jpa/test/criteria/TestDateTimeFunctions.java

/*
 * Copyright (c) 2022 Oracle and/or its affiliates. All rights reserved.
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0,
 * or the Eclipse Distribution License v. 1.0 which is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
 */

// Contributors:
//     02/01/2022: Tomas Kraus
//       - Issue 1442: Implement New JPA API 3.1.0 Features
package org.eclipse.persistence.jpa.test.criteria;

import java.time.Duration;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.Period;
import java.util.List;

import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.CriteriaUpdate;
import jakarta.persistence.criteria.Root;

import org.eclipse.persistence.jpa.test.criteria.model.DateTimeEntity;
import org.eclipse.persistence.jpa.test.framework.DDLGen;
import org.eclipse.persistence.jpa.test.framework.Emf;
import org.eclipse.persistence.jpa.test.framework.EmfRunner;
import org.eclipse.persistence.jpa.test.framework.Property;
import org.hamcrest.MatcherAssert;
import org.hamcrest.Matchers;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;

/**
 * Test {@code LocalTime}/{@code LocalDate}/{@code LocalDateTime} functions in CriteriaBuilder.
 * Added to JPA-API as PR #352
 */
@RunWith(EmfRunner.class)
public class TestDateTimeFunctions {

    @Emf(createTables = DDLGen.DROP_CREATE,
            classes = {
                    DateTimeEntity.class
            },
            properties = {
                    @Property(name = "eclipselink.cache.shared.default", value = "false"),
                    // This property remaps String from VARCHAR->NVARCHAR(or db equivalent)
                    @Property(name = "eclipselink.target-database-properties",
                            value = "UseNationalCharacterVaryingTypeForString=true"),
            })
    private EntityManagerFactory emf;

    private final LocalDateTime[] TS = {
            LocalDateTime.of(1970, 1, 1, 1, 11, 11),
            LocalDateTime.of(1970, 1, 1, 0, 0, 0, 0)
    };

    private final DateTimeEntity[] ENTITY = {
            new DateTimeEntity(1, TS[0].toLocalTime(), TS[0].toLocalDate(), TS[0]), // reserved for testCriteriaUpdateLocalTime
            new DateTimeEntity(2, TS[0].toLocalTime(), TS[0].toLocalDate(), TS[0]), // reserved for testCriteriaUpdateLocalDate
            new DateTimeEntity(3, TS[0].toLocalTime(), TS[0].toLocalDate(), TS[0]), // reserved for testCriteriaUpdateLocalDateTime
            new DateTimeEntity(4, TS[1].toLocalTime(), TS[1].toLocalDate(), TS[1])  // reserved for testCriteriaQueryWhereLocalTime
                                                                                       //              testCriteriaQueryWhereLocalTimeReturnsEmpty
                                                                                       //              testCriteriaQueryWhereLocalDate
                                                                                       //              testCriteriaQueryWhereLocalDateReturnsEmpty
                                                                                       //              testCriteriaQueryWhereLocalDateTime
                                                                                       //              testCriteriaQueryWhereLocalDateTimeReturnsEmpty
                                                                                       //              testCriteriaQuerySelectLocalTime
                                                                                       //              testCriteriaQuerySelectLocalDate
                                                                                       //              testCriteriaQuerySelectLocalDateTime
    };

    @Before
    public void setup() {
        final EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            for (DateTimeEntity e : ENTITY) {
                em.persist(e);
            }
            em.flush();
            em.getTransaction().commit();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    @After
    public void cleanup() {
        final EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            em.createQuery("DELETE FROM DateTimeEntity e").executeUpdate();
            em.flush();
            em.getTransaction().commit();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test CriteriaUpdate of localTime.
    // Strategy: Update 1:11:11 to current LocalTime and verify that time in database
    //           differs in less than 30 seconds from current time.
    // May fail when database time is not set properly.
    @Test
    public void testCriteriaUpdateLocalTime() {
        final EntityManager em = emf.createEntityManager();
        try {
            // Run the update query: Set current time
            em.getTransaction().begin();
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaUpdate<DateTimeEntity> cu = cb.createCriteriaUpdate(DateTimeEntity.class);
            Root<DateTimeEntity> entity = cu.from(DateTimeEntity.class);
            cu.set("time", cb.localTime());
            cu.where(cb.equal(entity.get("id"), 1));
            em.createQuery(cu).executeUpdate();
            em.flush();
            em.getTransaction().commit();
            // Verify updated entity
            DateTimeEntity data = em.find(DateTimeEntity.class, 1);
            long diffMilis = Duration.between(data.getTime(), LocalTime.now()).toMillis();
            // Positive value means that test did not pass midnight.
            if (diffMilis > 0) {
                MatcherAssert.assertThat(diffMilis, Matchers.lessThan(30000L));
            // Midnight pass correction.
            } else {
                MatcherAssert.assertThat(86400000L + diffMilis, Matchers.lessThan(30000L));
            }
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test CriteriaUpdate of localDate.
    // Strategy: Update 1. 1. 1970 to current LocalDate and verify that time in database
    //           differs in less than two days from current date. One day difference
    //           may be caused by midnight being just passed.
    // May fail when database time is not set properly.
    @Test
    public void testCriteriaUpdateLocalDate() {
        final EntityManager em = emf.createEntityManager();
        try {
            // Run the update query: Set current time
            em.getTransaction().begin();
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaUpdate<DateTimeEntity> cu = cb.createCriteriaUpdate(DateTimeEntity.class);
            Root<DateTimeEntity> entity = cu.from(DateTimeEntity.class);
            cu.set("date", cb.localDate());
            cu.where(cb.equal(entity.get("id"), 2));
            em.createQuery(cu).executeUpdate();
            em.flush();
            em.getTransaction().commit();
            // Verify updated entity
            DateTimeEntity data = em.find(DateTimeEntity.class, 2);
            Period diff = Period.between(data.getDate(), LocalDate.now());
            MatcherAssert.assertThat(diff.getYears(), Matchers.equalTo(0));
            MatcherAssert.assertThat(diff.getMonths(), Matchers.equalTo(0));
            MatcherAssert.assertThat(diff.getDays(), Matchers.lessThan(2));
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test CriteriaUpdate of localDateTime.
    // Strategy: Update 1. 1. 1970 1:11:11 to current LocalDateTime and verify that timestamp in database
    //           differs in less than 30 seconds from current timestamp.
    // May fail when database time is not set properly.
    @Test
    public void testCriteriaUpdateLocalDateTime() {
        final EntityManager em = emf.createEntityManager();
        try {
            // Run the update query: Set current time
            em.getTransaction().begin();
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaUpdate<DateTimeEntity> cu = cb.createCriteriaUpdate(DateTimeEntity.class);
            Root<DateTimeEntity> entity = cu.from(DateTimeEntity.class);
            cu.set("datetime", cb.localDateTime());
            cu.where(cb.equal(entity.get("id"), 3));
            em.createQuery(cu).executeUpdate();
            em.flush();
            em.getTransaction().commit();
            // Verify updated entity
            DateTimeEntity data = em.find(DateTimeEntity.class, 3);
            long diffMilis = Duration.between(data.getDatetime(), LocalDateTime.now()).toMillis();
            MatcherAssert.assertThat(diffMilis, Matchers.lessThan(30000L));
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test CriteriaQuery with localTime in WHERE condition.
    // Strategy: SELECT DateTimeEntity WHERE time column < localTime AND id = :id
    //           LocalTime in entity is set to 0:00:00.0.
    @Test
    public void testCriteriaQueryWhereLocalTime() {
        final EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<DateTimeEntity> cq = cb.createQuery(DateTimeEntity.class);
            Root<DateTimeEntity> entity = cq.from(DateTimeEntity.class);
            cq.select(entity);
            cq.where(cb.and(cb.lessThan(entity.get("time"), cb.localTime()), cb.equal(entity.get("id"), 4)));
            em.createQuery(cq).getSingleResult();
            em.getTransaction().commit();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test CriteriaQuery with localTime in WHERE condition with no record matching it.
    // Strategy: SELECT DateTimeEntity WHERE time column > localTime AND id = :id
    //           localTime in entity is set to 0:00:00.0
    // Such a record does not exist so empty result is expected.
    @Test
    public void testCriteriaQueryWhereLocalTimeReturnsEmpty() {
        final EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<DateTimeEntity> cq = cb.createQuery(DateTimeEntity.class);
            Root<DateTimeEntity> entity = cq.from(DateTimeEntity.class);
            cq.select(entity);
            cq.where(cb.and(cb.greaterThan(entity.get("time"), cb.localTime()), cb.equal(entity.get("id"), 4)));
            List<DateTimeEntity> data = em.createQuery(cq).getResultList();
            em.getTransaction().commit();
            MatcherAssert.assertThat(data.size(), Matchers.equalTo(0));
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test CriteriaQuery with localDate in WHERE condition.
    // Strategy: SELECT DateTimeEntity WHERE time column < localTime AND id = :id
    //           LocalDate in entity is set to 1.1.1970.
    @Test
    public void testCriteriaQueryWhereLocalDate() {
        final EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<DateTimeEntity> cq = cb.createQuery(DateTimeEntity.class);
            Root<DateTimeEntity> entity = cq.from(DateTimeEntity.class);
            cq.select(entity);
            cq.where(cb.and(cb.lessThan(entity.get("date"), cb.localDate()), cb.equal(entity.get("id"), 4)));
            em.createQuery(cq).getSingleResult();
            em.getTransaction().commit();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test CriteriaQuery with localDate in WHERE condition with no record matching it.
    // Strategy: SELECT DateTimeEntity WHERE time column > localTime AND id = :id
    //           LocalDate in entity is set to 1.1.1970.
    // Such a record does not exist so empty result is expected
    @Test
    public void testCriteriaQueryWhereLocalDateReturnsEmpty() {
        final EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<DateTimeEntity> cq = cb.createQuery(DateTimeEntity.class);
            Root<DateTimeEntity> entity = cq.from(DateTimeEntity.class);
            cq.select(entity);
            cq.where(cb.and(cb.greaterThan(entity.get("date"), cb.localDate()), cb.equal(entity.get("id"), 4)));
            List<DateTimeEntity> data = em.createQuery(cq).getResultList();
            em.getTransaction().commit();
            MatcherAssert.assertThat(data.size(), Matchers.equalTo(0));
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test CriteriaQuery with localDateTime in WHERE condition.
    // Strategy: SELECT DateTimeEntity WHERE time column < localDateTime AND id = :id
    //           LocalDate in entity is set to 1.1.1970 0:00:00.
    @Test
    public void testCriteriaQueryWhereLocalDateTime() {
        final EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<DateTimeEntity> cq = cb.createQuery(DateTimeEntity.class);
            Root<DateTimeEntity> entity = cq.from(DateTimeEntity.class);
            cq.select(entity);
            cq.where(cb.and(cb.lessThan(entity.get("datetime"), cb.localDateTime()), cb.equal(entity.get("id"), 4)));
            em.createQuery(cq).getSingleResult();
            em.getTransaction().commit();
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test CriteriaQuery with localDateTime in WHERE condition with no record matching it.
    // Strategy: SELECT DateTimeEntity WHERE time column > localDateTime AND id = :id
    //           LocalDate in entity is set to 1.1.1970 0:00:00.
    // Such a record does not exist so empty result is expected
    @Test
    public void testCriteriaQueryWhereLocalDateTimeReturnsEmpty() {
        final EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<DateTimeEntity> cq = cb.createQuery(DateTimeEntity.class);
            Root<DateTimeEntity> entity = cq.from(DateTimeEntity.class);
            cq.select(entity);
            cq.where(cb.and(cb.greaterThan(entity.get("datetime"), cb.localDateTime()), cb.equal(entity.get("id"), 4)));
            List<DateTimeEntity> data = em.createQuery(cq).getResultList();
            em.getTransaction().commit();
            MatcherAssert.assertThat(data.size(), Matchers.equalTo(0));
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test CriteriaQuery with localTime in SELECT column list
    @Test
    public void testCriteriaQuerySelectLocalTime() {
        final EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<LocalTime> cq = cb.createQuery(LocalTime.class);
            Root<DateTimeEntity> entity = cq.from(DateTimeEntity.class);
            cq.select(cb.localTime());
            cq.where(cb.equal(entity.get("id"), 4));
            LocalTime time = em.createQuery(cq).getSingleResult();
            em.getTransaction().commit();
            long diffMilis = Duration.between(time, LocalTime.now()).toMillis();
            // Positive value means that test did not pass midnight.
            if (diffMilis > 0) {
                MatcherAssert.assertThat(diffMilis, Matchers.lessThan(30000L));
            // Midnight pass correction.
            } else {
                MatcherAssert.assertThat(86400000L + diffMilis, Matchers.lessThan(30000L));
            }
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test CriteriaQuery with localDate in SELECT column list
    @Test
    public void testCriteriaQuerySelectLocalDate() {
        final EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<LocalDate> cq = cb.createQuery(LocalDate.class);
            Root<DateTimeEntity> entity = cq.from(DateTimeEntity.class);
            cq.select(cb.localDate());
            cq.where(cb.equal(entity.get("id"), 4));
            LocalDate date = em.createQuery(cq).getSingleResult();
            em.getTransaction().commit();
            Period diff = Period.between(date, LocalDate.now());
            MatcherAssert.assertThat(diff.getYears(), Matchers.equalTo(0));
            MatcherAssert.assertThat(diff.getMonths(), Matchers.equalTo(0));
            MatcherAssert.assertThat(diff.getDays(), Matchers.lessThan(2));
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

    // Test CriteriaQuery with localDateTime in SELECT column list
    @Test
    public void testCriteriaQuerySelectLocalDateTime() {
        final EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<LocalDateTime> cq = cb.createQuery(LocalDateTime.class);
            Root<DateTimeEntity> entity = cq.from(DateTimeEntity.class);
            cq.select(cb.localDateTime());
            cq.where(cb.equal(entity.get("id"), 4));
            LocalDateTime datetime = em.createQuery(cq).getSingleResult();
            em.getTransaction().commit();
            long diffMilis = Duration.between(datetime, LocalDateTime.now()).toMillis();
            MatcherAssert.assertThat(diffMilis, Matchers.lessThan(30000L));
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            em.close();
        }
    }

}