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:

Java Program to implement Array Deque
Spring Boot - Tomcat Deployment
Java Program to Generate Randomized Sequence of Given Range of Numbers
Hướng dẫn Java Design Pattern – Flyweight
Java Program to Implement Selection Sort
Java 8 Streams peek() API
Java Program to Represent Linear Equations in Matrix Form
Java Program to Implement Find all Forward Edges in a Graph
Introduction to Spring Data MongoDB
Tính đóng gói (Encapsulation) trong java
Thao tác với tập tin và thư mục trong Java
Add Multiple Items to an Java ArrayList
Hướng dẫn Java Design Pattern – Interpreter
4 tính chất của lập trình hướng đối tượng trong Java
Java Program to Implement Nth Root Algorithm
Java Program to Implement LinkedHashSet API
Java Program to Implement Traveling Salesman Problem using Nearest neighbour Algorithm
Kết hợp Java Reflection và Java Annotations
Java Program to Implement Treap
Java Program to Perform Naive String Matching
Spring NoSuchBeanDefinitionException
Java Program to Perform Quick Sort on Large Number of Elements
Vector trong Java
Java Program to Perform Searching Using Self-Organizing Lists
Java Program to Construct an Expression Tree for an Prefix Expression
Notify User of Login From New Device or Location
Java Program to Find Transitive Closure of a Graph
Introduction to Project Reactor Bus
Introduction to Spring Cloud OpenFeign
Hướng dẫn Java Design Pattern – Chain of Responsibility
Changing Annotation Parameters At Runtime
Java Program to Generate Random Numbers Using Probability Distribution Function