Query Entities by Dates and Times with Spring Data JPA

1. Introduction

In this quick tutorial, we’ll see how to query entities by dates with Spring Data JPA.

First, we’ll refresh our memory on how to map dates and times with JPA. Then we’ll create an entity with date and time fields, as well as a Spring Data repository to query those entities.

2. Mapping Dates and Times with JPA

To begin, we’ll review a bit of theory about mapping dates with JPA. The thing to know is that we need to decide whether we want to represent:

• A date only
• A time only
• Or both

In addition to the (optional) @Column annotation, we’ll need to add the @Temporal annotation to specify what the field represents.

This annotation takes a parameter that’s a value of TemporalType enum:

• TemporalType.DATE
• TemporalType.TIME
• TemporalType.TIMESTAMP

A detailed article about date and time mapping with JPA can be found here.

3. In Practice

In practice, once our entities are correctly set up, there’s not much work to do to query them using Spring Data JPA. We just have to use query methods, or the @Query annotation.

Every Spring Data JPA mechanism will work just fine.

Let’s see a couple examples of entities queried by dates and times with Spring Data JPA.

3.1. Set Up an Entity

For instance, let’s say we have an Article entity with a publication date, publication time, and a creation date and time:

@Entity
public class Article {

    @Id
    @GeneratedValue
    Integer id;
 
    @Temporal(TemporalType.DATE)
    Date publicationDate;
 
    @Temporal(TemporalType.TIME)
    Date publicationTime;
 
    @Temporal(TemporalType.TIMESTAMP)
    Date creationDateTime;
}

We split the publication date and time into two fields for demonstration purposes; that way we represent the three temporal types.

3.2. Query the Entities

Now that our entity is all set up, let’s create a Spring Data repository to query those articles.

We’ll create three methods using several Spring Data JPA features:

public interface ArticleRepository 
  extends JpaRepository<Article, Integer> {

    List<Article> findAllByPublicationDate(Date publicationDate);

    List<Article> findAllByPublicationTimeBetween(
      Date publicationTimeStart,
      Date publicationTimeEnd);

    @Query("select a from Article a where a.creationDateTime <= :creationDateTime")
    List<Article> findAllWithCreationDateTimeBefore(
      @Param("creationDateTime") Date creationDateTime);

}

So we defined three methods:

• findAllByPublicationDate which retrieves articles published on a given date
• findAllByPublicationTimeBetween which retrieves articles published between two given hours
• and findAllWithCreationDateTimeBefore which retrieves articles created before a given date and time

The first two methods rely on Spring Data’s query methods mechanism, and the last on the @Query annotation.

In the end, that doesn’t change the way dates will be treated. The first method will only consider the date part of the parameter.

The second will only consider the time of the parameters. And the last will use both date and time.

3.3. Test the Queries

The last thing we’ll do is set up some tests to check that these queries work as expected.

We’ll first import data into our database, and then we’ll create the test class that’ll check each method of the repository:

@RunWith(SpringRunner.class)
@DataJpaTest
public class ArticleRepositoryIntegrationTest {

    @Autowired
    private ArticleRepository repository;

    @Test
    public void whenFindByPublicationDate_thenArticles1And2Returned() {
        List<Article> result = repository.findAllByPublicationDate(
          new SimpleDateFormat("yyyy-MM-dd").parse("2018-01-01"));

        assertEquals(2, result.size());
        assertTrue(result.stream()
          .map(Article::getId)
          .allMatch(id -> Arrays.asList(1, 2).contains(id)));
    }

    @Test
    public void whenFindByPublicationTimeBetween_thenArticles2And3Returned() {
        List<Article> result = repository.findAllByPublicationTimeBetween(
          new SimpleDateFormat("HH:mm").parse("15:15"),
          new SimpleDateFormat("HH:mm").parse("16:30"));

        assertEquals(2, result.size());
        assertTrue(result.stream()
          .map(Article::getId)
          .allMatch(id -> Arrays.asList(2, 3).contains(id)));
    }

    @Test
    public void givenArticlesWhenFindWithCreationDateThenArticles2And3Returned() {
        List<Article> result = repository.findAllWithCreationDateTimeBefore(
          new SimpleDateFormat("yyyy-MM-dd HH:mm").parse("2017-12-15 10:00"));

        assertEquals(2, result.size());
        assertTrue(result.stream()
          .map(Article::getId)
          .allMatch(id -> Arrays.asList(2, 3).contains(id));
    }
}

Each test verifies that only the articles matching the conditions are retrieved.

4. Conclusion

In this brief article, we learned how to query entities by their dates and times fields with Spring Data JPA.

We discussed a bit of theory before using Spring Data mechanisms to query the entities. We saw those mechanisms work the same way with dates and times as they do with other types of data.