How to create a specification for dates in JPA to find the greater/less etc?

Question

I have a Maria database with a table with a column named registrated what is a TIMESTAMP. I would like to make specifications to find records that are before/after a date. I have this:

public class GreaterThanDate implements Specification<User> {
    
    private transient SearchCriteria criteria;
    
    public GreaterThanDate(SearchCriteria searchCriteria) {
        criteria = searchCriteria;
    }
    
    @Override
    public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        try {
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
            return builder.greaterThanOrEqualTo(root.<Date>get(criteria.getKey()), formatter.parse(criteria.getValue().toString()));
            } catch (ParseException e) {
            System.out.println(e.getMessage());
        }
    }
}

public class SearchCriteria {
    private String key;
    private Object value;
}

This is a date that is found in my database: 2020-09-01 08:00:00.000000. If I try to find the records I get a java.lang.IllegalArgumentException. This is how I call it:

SearchCriteria criteria = new SearchCriteria("registered", "2020-09-01 08:00:00.000000");
GreaterThanDate specification = new GreaterThanDate(criteria);
userRepository.findAll(specification, pageable);

@Entity
public class User {
    private ZonedDateTime registered;
}

Exception is java.lang.IllegalArgumentException: Invalid filter parameters. at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) at javax.servlet.http.HttpServlet.service(HttpServlet.java:626) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.AbstractRequestLoggingFilter.doFilterInternal(AbstractRequestLoggingFilter.java:289) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:93) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1589) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Unknown Source)

Answer 1

There are couple of problems with your code.

1. You entity class has java.time.ZonedDateTime type for the field. But you are trying to parse using SimpleDateFormat which is designed for old java.util.Date. Please use DateTimeFormatter to parse ZonedDateTime private ZonedDateTime registered;

2. The date format yyyy-MM-dd'T'HH:mm:ss.SSSXXX doesn't match with the input date 2020-09-01 08:00:00.000000 so you cannot use this format to parse the date. You will get ParseException

3. Do not bury the exception. The exception that you posted on question might have been raised due to a different error.

 } catch (ParseException e) {
            System.out.println(e.getMessage());
        }

See this working example and do the similar to fix your problem:

package stackoverflowdatespec

import lombok.*;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.event.ContextRefreshedEvent;
import org.springframework.context.event.EventListener;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.support.JpaRepositoryImplementation;
import org.springframework.stereotype.Component;

import javax.persistence.*;
import javax.persistence.criteria.*;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;

@SpringBootApplication
public class StackOverFlowDateSpecApp {
    public static void main(String[] args) {
        SpringApplication.run(StackOverFlowDateSpecApp.class, args);
    }
}

@Entity
@Data
@ToString
class A {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Long id;

    private ZonedDateTime registered = ZonedDateTime.now();
}

interface ARepo extends JpaRepositoryImplementation<A, Long> {
}

@Component
@RequiredArgsConstructor
class Init {
    final ARepo repo;
    @EventListener
    public void init(ContextRefreshedEvent evt) {
        repo.save(new A());
        System.out.println(repo.findAll());

        SearchCriteria criteria = new SearchCriteria("registered", "2011-12-03T10:15:30+01:00");
        System.out.println(repo.findAll(new GreaterThanDate(criteria), Pageable.unpaged()).getContent());
    }
}

@RequiredArgsConstructor
class GreaterThanDate implements Specification<A> {
    private final SearchCriteria criteria;
    @Override
    public Predicate toPredicate(Root<A> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder builder) {
        ZonedDateTime before = ZonedDateTime.parse(criteria.getValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ssXXX"));
        return builder.greaterThanOrEqualTo(root.get(criteria.getKey()), before);

    }
}

@Data
@AllArgsConstructor
class SearchCriteria {
    private String key;
    private String value;
}