Test of LocalDate interoperability with JPA and JDBC

Experiment.java

package edu.princeton.gfts.experimental;

import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDate;
import java.time.temporal.ChronoUnit;
import java.util.List;

@Component
public class Experiment {

    private final ExperimentalRepository repository;

    public Experiment(final ExperimentalRepository repository) {
        this.repository = repository;
    }

    @Transactional
    public ExperimentalEntity storeExperimentalEntityWithDate(String name, LocalDate startDate, LocalDate endDate) {
        ExperimentalEntity entity = new ExperimentalEntity(name, startDate, endDate);
        return repository.saveAndFlush(entity);
    }

    public ExperimentalEntity getExperimentalEntityByName(String name) {
        return repository.findByName(name);
    }

    public List<ExperimentalEntity> getActiveOn(LocalDate activeDate) {
        return repository.findAllByStartDateBeforeAndEndDateAfter(activeDate.plus(1L, ChronoUnit.DAYS), activeDate.minus(1, ChronoUnit.DAYS));
    }
}

ExperimentalEntity.java

package edu.princeton.gfts.experimental;

import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.GenericGenerator;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.time.LocalDate;

@Entity
@Table(name = "experiment")
@Data
@NoArgsConstructor
@EqualsAndHashCode
public class ExperimentalEntity implements Serializable {

    public ExperimentalEntity(String name, LocalDate startDate, LocalDate endDate) {
        this.name = name;
        this.startDate = startDate;
        this.endDate = endDate;
    }

    @Id
    @GeneratedValue(generator="system-uuid")
    @GenericGenerator(name="system-uuid", strategy = "uuid")
    @Column(name = "id")
    private String id;

    @Column(name="name", unique = true)
    private String name;

    @Column(name="start_date")
    private LocalDate startDate;

    @Column(name="end_date")
    private LocalDate endDate;
}

ExperimentalRepository.java

package edu.princeton.gfts.experimental;

import org.springframework.data.jpa.repository.JpaRepository;

import java.time.LocalDate;
import java.util.List;

public interface ExperimentalRepository extends JpaRepository<ExperimentalEntity, String> {
    ExperimentalEntity findByName(String name);
    List<ExperimentalEntity> findAllByStartDateBeforeAndEndDateAfter(LocalDate startDate, LocalDate endDate);
}

ExperimentalTest.java

package edu.princeton.gfts.experimental;

import edu.princeton.gfts.GftsApplication;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.IntegrationTest;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.context.annotation.Import;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.transaction.Transactional;
import java.time.LocalDate;
import java.util.List;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = GftsApplication.class)
@IntegrationTest("server.port:0")
@Import(Experiment.class)
public class ExperimentalTest {

    @Autowired
    private Experiment experiment;

    @Autowired
    EntityManager entityManager;

    @Before
    public void setup() {
        entityManager.createNativeQuery(
                "create table EXPERIMENT (id varchar2(32) primary key, name varchar2(255), start_date DATE, end_date DATE);"
        ).executeUpdate();
    }

    @After
    public void tearDown() {
        entityManager.createNativeQuery(
                "drop table EXPERIMENT;"
        ).executeUpdate();
    }

    @Test
    @Transactional
    public void testSave() {
        final LocalDate startDate = LocalDate.parse("2018-08-20");
        final LocalDate endDate = LocalDate.parse("2019-08-20");
        ExperimentalEntity savedEntity = experiment.storeExperimentalEntityWithDate("fred", startDate, endDate);
        assertNotNull(savedEntity);
        assertNotNull(savedEntity.getId());
        assertEquals("fred", savedEntity.getName());
        assertEquals(startDate, savedEntity.getStartDate());
        assertEquals(endDate, savedEntity.getEndDate());
        entityManager.detach(savedEntity);

        savedEntity = null;

        savedEntity = experiment.getExperimentalEntityByName("fred");
        assertNotNull(savedEntity);
        assertEquals("fred", savedEntity.getName());
        assertEquals(startDate, savedEntity.getStartDate());
        assertEquals(endDate, savedEntity.getEndDate());

        savedEntity = null;

        // active in the middle of the date range
        List<ExperimentalEntity> list = experiment.getActiveOn(LocalDate.parse("2019-01-01"));
        assertNotNull(list);
        assertEquals(1, list.size());
        savedEntity = list.get(0);
        assertEquals("fred", savedEntity.getName());
        assertEquals(startDate, savedEntity.getStartDate());
        assertEquals(endDate, savedEntity.getEndDate());


        savedEntity = null;

        // not active on the day before the first day
        list = experiment.getActiveOn(LocalDate.parse("2018-08-19"));
        assertNotNull(list);
        assertEquals(0, list.size());

        // active on the first day
        list = experiment.getActiveOn(LocalDate.parse("2018-08-20"));
        assertNotNull(list);
        assertEquals(1, list.size());
        savedEntity = list.get(0);
        assertEquals("fred", savedEntity.getName());
        assertEquals(startDate, savedEntity.getStartDate());
        assertEquals(endDate, savedEntity.getEndDate());

        // active on the last day
        list = experiment.getActiveOn(LocalDate.parse("2019-08-20"));
        assertNotNull(list);
        assertEquals(1, list.size());
        savedEntity = list.get(0);
        assertEquals("fred", savedEntity.getName());
        assertEquals(startDate, savedEntity.getStartDate());
        assertEquals(endDate, savedEntity.getEndDate());

        // not active on the day after the last day
        list = experiment.getActiveOn(LocalDate.parse("2019-08-21"));
        assertNotNull(list);
        assertEquals(0, list.size());
    }

    @Test
    @Transactional
    public void testNativeSql() {
        Query query = entityManager.createNativeQuery("insert into EXPERIMENT(id, name, start_date, end_date) values('123', 'bob', :startDate, :endDate)");
        query.setParameter("startDate", LocalDate.parse("2018-08-20"));
        query.setParameter("endDate", LocalDate.parse("2019-08-20"));
        int rowCount = query.executeUpdate();
        assertEquals(1, rowCount);

        Query selectQuery = entityManager.createNativeQuery("select * from Experiment");
        List experiments = selectQuery.getResultList();
        assertEquals(1, experiments.size());

        Object[] row = (Object[]) experiments.get(0);
        assertNotNull(row);
        assertEquals("123", row[0]);
        assertEquals("bob", row[1]);
        assertEquals(LocalDate.parse("2018-08-20"), ((java.sql.Date)row[2]).toLocalDate());
        assertEquals(LocalDate.parse("2019-08-20"), ((java.sql.Date)row[3]).toLocalDate());
    }
}