spring-data 使用时，多条件查询

在spring data 使用中

接口：
/**
* 图联币管理：通过批次号，发放时间，截止时间，查询图联币发放情况。
* @author:      LJ
* @Create at:   2011-11-1
* @param couponIssueBatchNum
* @param issueStartTime
* @param issueEndTime
* @param stopStartTime
* @param stopEndTime
* @return
*/
public List<CouponIssue> queryByMutiCondition(String couponIssueBatchNum,
Date issueStartTime, Date issueEndTime, Date stopStartTime,
Date stopEndTime);

实现：

@PersistenceContext
private EntityManager em;

@Override
public List<CouponIssue> queryByMutiCondition(String couponIssueBatchNum,
Date issueStartTime, Date issueEndTime, Date stopStartTime,
Date stopEndTime) {

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<CouponIssue> query = builder
.createQuery(CouponIssue.class);
Root<CouponIssue> couponIssue = query.from(CouponIssue.class);
query.select(couponIssue);

List<Predicate> predicateList = new ArrayList<Predicate>();

Predicate couponIssueBatchNumPredicate;

// 处理批次号不为空的条件，则表示用户通过批次号查询。
if ((couponIssueBatchNum != null) && (!(couponIssueBatchNum.isEmpty()))) {
couponIssueBatchNumPredicate = builder
.equal(couponIssue.get("couponIssueBatchNum"),
couponIssueBatchNum);
// builder.between(v, x, y)
predicateList.add(couponIssueBatchNumPredicate);
}
// 发放时间处理
Predicate issueDate;

// 因为CriteriaBuilder 的比较函数传递的是expression 所以把时间构造成date类型的表达式

// 表进行传递
Expression<Date> issueStart = builder.literal(issueStartTime);
Expression<Date> issueEnd = builder.literal(issueEndTime);
//Expression<String> issueString = couponIssue.get("issueDate"); 2011-11-03 18:00:56
//couponIssue.get("issueDate");

// 传递的时间进行转换
EntityType<CouponIssue> ci_ = couponIssue.getModel();

if (issueStartTime != null && issueEndTime != null) {
issueDate = builder
.between(couponIssue.get(ci_.getSingularAttribute("issueDate", Date.class)), issueStart, issueEnd);
predicateList.add(issueDate);
}

Predicate[] predicates = new Predicate[predicateList.size()];
predicateList.toArray(predicates);
query.where(predicates);

return em.createQuery(query).getResultList();
}

 

多条件分页查询

 

在springdata 中分页采用是返回 Page<T> 的方式：

如例子

// 接口实现 获取图联币的列表。

@Override
    public Page<CouponIssue> queryByMutiCondition(String couponIssueBatchNum,
            Date issueStartTime, Date issueEndTime, Date stopStartTime,
            Date stopEndTime, Pageable pageable) {

        Specification<CouponIssue> spec = CouponIssueSpecs
                .queryByMutiCondition(couponIssueBatchNum, issueStartTime,
                        issueEndTime, stopStartTime, stopEndTime) ;
        return couponIssueRepository.findAll(spec, pageable);
    }

 

方法调用：

// queryByMutiCondition
    public static Specification<CouponIssue> queryByMutiCondition(
            String couponIssueBatchNum, Date issueStartTime, Date issueEndTime,
            Date stopStartTime, Date stopEndTime) {

        final String cibn = couponIssueBatchNum;
        final Date ist = issueStartTime;
        final Date iet = issueEndTime;
        final Date sst = stopStartTime;
        final Date set = stopEndTime;

        return new Specification<CouponIssue>() {

            @Override
            public Predicate toPredicate(Root<CouponIssue> root,
                    CriteriaQuery<?> query, CriteriaBuilder cb) {

                Predicate pc = null;
                Root<CouponIssue> couponIssue = query.from(CouponIssue.class);
                EntityType<CouponIssue> ci_ = couponIssue.getModel();
                // query.select(couponIssue);

                // 存放多个条件
                List<Predicate> predicateList = new ArrayList<Predicate>();

                Predicate couponIssueBatchNumPredicate;

                // 处理批次号不为空的条件，则表示用户通过批次号查询。
                if ((cibn != null) && (!(cibn.isEmpty()))) {
                    couponIssueBatchNumPredicate = cb.equal(
                            couponIssue.get("couponIssueBatchNum"), cibn);
                    // builder.between(v, x, y)
                    predicateList.add(couponIssueBatchNumPredicate);
                }
                // 发放时间处理
                Predicate issueDate;

                if (ist != null && iet != null) {
                    Expression<Date> issueStart = cb.literal(ist);
                    Expression<Date> issueEnd = cb.literal(iet);
                    issueDate = cb.between(couponIssue.get(ci_
                            .getSingularAttribute("issueDate", Date.class)),
                            issueStart, issueEnd);
                    predicateList.add(issueDate);
                }

                // 截止时间处理
                Predicate stopDate;

                if (sst != null && set != null) {
                    Expression<Date> stopStart = cb.literal(sst);
                    Expression<Date> stopEnd = cb.literal(set);
                    stopDate = cb.between(
                            couponIssue.get(ci_.getSingularAttribute(
                                    "stopIssueDate", Date.class)), stopStart,
                            stopEnd);
                    predicateList.add(stopDate);
                }
                // 公共
                Predicate[] predicates = new Predicate[predicateList.size()];
                predicateList.toArray(predicates);
               
                CriteriaQuery<?> cqy = query.where(predicates);
                //Selection<?> selectionList = cqy.getSelection();
                pc = cqy.getGroupRestriction();
                return pc;
            }
        };
    }

 

评论

2 楼 henghengdh 2013-06-25  

你这个方法如果有个时间条件为空的话还能运行吗？

1 楼 mazongfei 2012-09-28  

老大，xml的sql语句怎么写？