spring data jpa hql动态查询案例

目的：根据入参条件不同，动态组装hql里的where语句。

1. 实现代码

　　public List<WrapStatis> queryStatisCriteriaBuilder(String startTime, String endTime, String collectName,

                                                       String imei) {
        List<WrapStatis> list = new ArrayList<>();
        try {
            CriteriaBuilder cb = entityManager.getCriteriaBuilder();
            //WrapStatis指定了查询结果返回至自定义对象
            CriteriaQuery<WrapStatis> query = cb.createQuery(WrapStatis.class);
            Root<StatisEntity> root = query.from(StatisEntity.class);
            Path<Calendar> timePath = root.get("createTime");
            Path<String> statisName = root.get("statisName");
            Path<String> statisNum = root.get("statisNum");
            Path<Double> statisRate = root.get("statisRate");
            List<Predicate> predicateList = new ArrayList<Predicate>();
            Date startDate = DateUtils.parse(startTime,DateUtils.YMD_DASH_WITH_TIME);
            Date endDate = DateUtils.parse(endTime,DateUtils.YMD_DASH_WITH_TIME);
            if (startTime != null) {
                predicateList.add(cb.between(root.get("createTime"),startDate,endDate));
            }
            if(StringUtils.isNotEmpty(collectName)){
                predicateList.add(cb.equal(root.get("collectName"),collectName));
            }

            if(StringUtils.isNotEmpty(imei) && !imei.equals("all")){
                predicateList.add(cb.equal(root.get("imei"),imei));
            }
            Predicate[] predicates = new Predicate[predicateList.size()];
            predicates = predicateList.toArray(predicates);
            //加上where条件
            query.where(predicates);
            //指定查询项，select后面的东西
            Expression<String> timeStr = cb.function("DATE_FORMAT", String.class, timePath, cb.parameter(String.class, "formatStr"));//格式化日期
            query.multiselect(timeStr,statisName,statisNum, statisRate);//返回列
//            query.groupBy(root.get(conditionName),timeStr);//分组
//            query.orderBy(cb.asc(timeStr));//排序
            TypedQuery<WrapStatis> typedQuery = entityManager.createQuery(query);

            typedQuery.setParameter("formatStr", Constant.STATIS_DAY);
            list = typedQuery.getResultList();
        } catch (ParseException e) {
            log.error("call StatisService queryStatisCriteriaBuilder is error", e);
        }
        return list;
    }

Constant.STATIS_DAY值为：%Y-%m-%d按天格式化，执行完成打印的sql为：

select date_format(statisenti0_.create_time, ?) as col_0_0_, 
    statisenti0_.statis_name as col_1_0_, 
    statisenti0_.statis_num as col_2_0_, 
    statisenti0_.statis_rate as col_3_0_ 
        from statis statisenti0_ 
        where (statisenti0_.create_time between ? and ?) 
        and statisenti0_.collect_name=? 
        and statisenti0_.kepler_version=? 
        and statisenti0_.device_brand=? 
        and statisenti0_.rom_version=? 
        and statisenti0_.alipay_version=?

2. 包装类wrapStatis.class为

　　

public class WrapStatis {

    private String date;
    private String statisName;
    private String statisNum;
    private Double statisRate;

    //省略get/set/构造方法

}