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.

We’ll first refresh our memory about 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

For starters, 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 which is a value of TemporalType enum:

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

A detailed article about dates and times mapping with JPA can be found here.

3. In Practice

In practice, once our entities are correctly set up, there is not much work to do to query them using Spring Data JPA. We just have to use query methods, @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 starters, let’s say we have an Article entity, with a publication date, a 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 publication date and time into two fields for the demonstration purpose. That way the three temporal types are represented.

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 two first methods rely on Spring Data query methods mechanism and the last on @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’ve to do is to set up some tests to check that these queries work as expected.

We’ll first import a few data into our database and then we’ll create the test class which will 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 short article, we’ve seen how to query entities using their dates and times fields with Spring Data JPA.

We learned 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.

The source code for this article is available over on GitHub.

Related posts:

Login For a Spring Web App – Error Handling and Localization
MyBatis with Spring
Java 8 Stream API Analogies in Kotlin
Converting a List to String in Java
Java Program to Implement LinkedBlockingQueue API
Bootstrapping Hibernate 5 with Spring
Java Program to Implement Gabow Algorithm
Java Program to Remove the Edges in a Given Cyclic Graph such that its Linear Extension can be Found
Spring Boot - CORS Support
Registration – Activate a New Account by Email
Deque và ArrayDeque trong Java
Guide to UUID in Java
Java Program to Implement Hash Tables Chaining with List Heads
Thực thi nhiều tác vụ cùng lúc như thế nào trong Java?
Hướng dẫn Java Design Pattern – Singleton
Instance Profile Credentials using Spring Cloud
Java Program to Perform Searching Based on Locality of Reference
TreeSet và sử dụng Comparable, Comparator trong java
Java Program to Apply DFS to Perform the Topological Sorting of a Directed Acyclic Graph
Jackson Exceptions – Problems and Solutions
Java Program to Implement Doubly Linked List
Java Program to Implement Caesar Cypher
Java – Write an InputStream to a File
Spring Security – security none, filters none, access permitAll
Java Program to implement Circular Buffer
Create a Custom Auto-Configuration with Spring Boot
Properties with Spring and Spring Boot
Java Program to Implement LinkedBlockingDeque API
Guide to ThreadLocalRandom in Java
Java Program to Solve the Fractional Knapsack Problem
Java Program to Find the Nearest Neighbor Using K-D Tree Search
DistinctBy in the Java Stream API