如何为Spring Data JPA仓库的所有自定义查询添加日期范围条件？

下面提供几种实用的实现方式，按需选择：

方法一：使用JPA Specifications #

这种方式灵活可控，能在调用查询时轻松组合通用的日期范围条件和自定义查询条件。

1. 让仓库继承JpaSpecificationExecutor接口：

@Repository
public interface TransactionRepository extends JpaRepository<TransactionEntity, String>, JpaSpecificationExecutor<TransactionEntity> {
    // 自定义查询方法示例
    List<TransactionEntity> findByStatus(String status);
}

2. 编写通用的日期范围Specification工具方法：

public class TransactionSpecs {
    public static Specification<TransactionEntity> dateRange(Date fromDate, Date toDate) {
        return (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            if (fromDate != null) {
                predicates.add(cb.greaterThanOrEqualTo(root.get("inputDate"), fromDate));
            }
            if (toDate != null) {
                predicates.add(cb.lessThanOrEqualTo(root.get("inputDate"), toDate));
            }
            return cb.and(predicates.toArray(new Predicate[0]));
        };
    }
}

3. 调用查询时组合条件：

• 组合自定义条件与日期范围：

Specification<TransactionEntity> spec = Specification.where(TransactionSpecs.dateRange(fromDate, toDate))
    .and((root, query, cb) -> cb.equal(root.get("status"), "SUCCESS"));

List<TransactionEntity> result = transactionRepository.findAll(spec);

• 仅使用日期范围条件：

List<TransactionEntity> result = transactionRepository.findAll(TransactionSpecs.dateRange(fromDate, toDate));

方法二：自定义仓库基类（全局自动添加条件） #

如果希望所有自定义查询自动带上日期范围，无需手动组合，可以通过自定义仓库基类实现。

1. 定义自定义仓库接口：

@NoRepositoryBean
public interface CustomJpaRepository<T, ID> extends JpaRepository<T, ID> {
    List<T> findAllWithDateRange(Date fromDate, Date toDate);
    List<T> findByStatusWithDateRange(String status, Date fromDate, Date toDate);
}

2. 实现自定义仓库基类：

public class CustomJpaRepositoryImpl<T, ID> extends SimpleJpaRepository<T, ID> implements CustomJpaRepository<T, ID> {
    private final EntityManager entityManager;

    public CustomJpaRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
        this.entityManager = entityManager;
    }

    @Override
    public List<T> findAllWithDateRange(Date fromDate, Date toDate) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<T> query = cb.createQuery(getDomainClass());
        Root<T> root = query.from(getDomainClass());
        
        List<Predicate> predicates = new ArrayList<>();
        addDateRangePredicates(cb, root, predicates, fromDate, toDate);
        
        query.where(predicates.toArray(new Predicate[0]));
        return entityManager.createQuery(query).getResultList();
    }

    @Override
    public List<T> findByStatusWithDateRange(String status, Date fromDate, Date toDate) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<T> query = cb.createQuery(getDomainClass());
        Root<T> root = query.from(getDomainClass());
        
        List<Predicate> predicates = new ArrayList<>();
        predicates.add(cb.equal(root.get("status"), status));
        addDateRangePredicates(cb, root, predicates, fromDate, toDate);
        
        query.where(predicates.toArray(new Predicate[0]));
        return entityManager.createQuery(query).getResultList();
    }

    private void addDateRangePredicates(CriteriaBuilder cb, Root<T> root, List<Predicate> predicates, Date fromDate, Date toDate) {
        if (fromDate != null) {
            predicates.add(cb.greaterThanOrEqualTo(root.get("inputDate"), fromDate));
        }
        if (toDate != null) {
            predicates.add(cb.lessThanOrEqualTo(root.get("inputDate"), toDate));
        }
    }
}

3. 让业务仓库继承自定义接口：

@Repository
public interface TransactionRepository extends CustomJpaRepository<TransactionEntity, String> {
    // 直接使用基类中的方法，或按需扩展
}

方法三：使用JPA查询拦截器（全局拦截） #

这种方式会拦截所有JPA查询，自动添加日期范围条件，适合需要强制给所有查询加条件的场景，注意控制范围避免影响其他实体。

1. 编写EntityManager拦截器：

public class DateRangeQueryInterceptor implements org.hibernate.Interceptor {
    private ThreadLocal<Date[]> dateRangeHolder = new ThreadLocal<>();

    public void setDateRange(Date fromDate, Date toDate) {
        dateRangeHolder.set(new Date[]{fromDate, toDate});
    }

    public void clearDateRange() {
        dateRangeHolder.remove();
    }

    @Override
    public String onPrepareStatement(String sql) {
        Date[] dateRange = dateRangeHolder.get();
        if (dateRange != null && sql.contains("FROM transaction_entity")) {
            Date fromDate = dateRange[0];
            Date toDate = dateRange[1];
            StringBuilder sb = new StringBuilder(sql);
            if (sql.contains("WHERE")) {
                sb.append(" AND INPUT_DT >= :fromDate AND INPUT_DT <= :toDate");
            } else {
                sb.append(" WHERE INPUT_DT >= :fromDate AND INPUT_DT <= :toDate");
            }
            return sb.toString();
        }
        return sql;
    }

    // 实现其他必要的空方法（如onLoad等）
    @Override
    public boolean onLoad(Object entity, Serializable id, Object[] state, String[] propertyNames, Type[] types) {
        return false;
    }
}

2. 配置拦截器到Spring：

@Configuration
public class JpaConfig {
    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder, DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean em = builder
                .dataSource(dataSource)
                .packages("com.yourpackage.entity")
                .persistenceUnit("transactionPU")
                .build();
        em.getJpaPropertyMap().put("hibernate.session_factory.interceptor", new DateRangeQueryInterceptor());
        return em;
    }
}

3. 使用时设置日期范围：

@Autowired
private DateRangeQueryInterceptor interceptor;

public List<TransactionEntity> getTransactions(String status) {
    try {
        interceptor.setDateRange(fromDate, toDate);
        return transactionRepository.findByStatus(status);
    } finally {
        interceptor.clearDateRange(); // 必须清理，避免线程污染
    }
}

内容的提问来源于stack exchange，提问作者Muhammed K K