如何为JPA中的日期创建一个规范，以查找更大/更小等？

Question

我有一个Maria数据库，其中有一个表，列名为registrated（时间戳）。我想做的规范，以找到记录之前/之后的日期。我有这个：

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:00000000。如果我尝试查找记录，我会得到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;
}

null

Answer 1

您的代码有几个问题。

>

您的实体类的字段类型为java.time.ZonedDateTime。但是您正在尝试使用simpledateformat进行解析，它是为旧的java.util.date设计的。请使用DateTimeFormatter来分析注册的ZonedDateTime私有ZonedDateTime；

日期格式yyyy-mm-dd't'hh:mm:ss.sssxxx与输入日期2020-09-01 08:00000000不匹配，因此无法使用此格式分析日期。您将获得ParseException

不要隐藏异常。您在问题上发布的异常可能是由于其他错误引起的。

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