如何在JPA中创建日期规范以查找较大/较小等

Question

我有一个Maria数据库，其中有一个名为registrated的表，它是TIMESTAMP。我想制定规范，以查找日期之前/之后的记录。我有这个：

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;
}

这是在我的数据库中找到的日期：2020-09-01 08:00:00.000000。如果我试图找到这些记录，我会得到一个java.lang.IllegalArgumentException。我这样称呼它：

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;
}

异常为java.lang.IollegalArgumentException：无效筛选器参数。在sun.reflect.NativeMethodAccessorImpl.invoke0(Native方法)在sun.reflect.NativeMethodAccessorImpl.invoke(未知Source)在sun.reflect.DelegatingMethodAccessorImpl.invoke(未知Source)，位于java.lang.reflect.Method.ioke(未知源)org.springframework.web.method.support.IInvocableHandlerMethod.doInvoke(InvocableHandler method.java:190)在org.springframework.web.method.support.InvocaleHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)在org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)在org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)在org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandledAdapter.java:792)在org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethod Adapter.java:87)在org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java：1040)在org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)在org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java：1006)在org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java：898)位于org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java：883)位于org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)在org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)在org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)在org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)在org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)在org.springframework.web.filter.AbstractRequestLoggingFilter.doFilterInternal(AbstractRequest LoggingFilter.java:289)在org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequest filter.java:119)在org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)在org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)在org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)在org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequest filter.java:119)在org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)在org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)在org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)在org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequest filter.java:119)在org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)在org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)在org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:93)在org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequest filter.java:119)在org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)在org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)在org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncoding filter.java:201)在org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequest filter.java:119)在org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)在org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)在org.apache.catalina.core.StandardWrapperValv.invoke(StandardWrapperValve.java:202)在org.apache.catalina.core.StandardContextValv.invoke(StandardContextValv.java:96)在org.apache.catalina.authenticator.AuthenticatorBase.ioke(AuthenticatorBase.java:541)在org.apache.catalina.core.StandardHostValv.invoke(StandardHostValve.java:139)在org.apache.catalina.vals.ErrorReportValve.ioke(ErrorReportValve.java:92)在org.apache.catalina.core.StandardEngineValv.invoke(StandardEngineValv.java:74)在org.apache.catalina.connecter.CoyoteAdapter.service(Coyotedapter.java:343)在org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)在org.apache.coyote.AbstractProcessorLight.prrocess(AbstractProcessorLight.java:65)在org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)在org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1589)在org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)位于java.util.concurrent.ThreadPoolExecutor.runWorker(未知源)位于java.util.concurrent.ThreadPoolExecutor$Worker.run(未知源)在org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)在java.lang.Thread.run(未知源)

Answer 1

您的代码有几个问题。

1. 实体类的字段具有java.time.ZonedDateTime类型。但是您正在尝试使用为旧java.util.Date设计的SimpleDateFormat进行解析。请使用DateTimeFormatter解析ZonedDateTimeprivate ZonedDateTime已注册；

2. 日期格式yyyy-MM-dd'T'HH:mm:ss.SSSXXX与输入日期2020-09-01 08:00:00.000000不匹配，因此无法使用此格式解析日期。您将获得ParseException

3. 不要掩盖例外。您发布的有关问题的异常可能是由于其他错误引起的。

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

看看这个工作示例，并做类似的事情来解决你的问题：

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;
}