所属专栏:《Python入门到精通》

spring data jpa hql动态查询案例

温馨提示:
本文最后更新于 2022年03月31日,距今已超过 252 天。若文章内容失效或有不良信息,请举报或直接联系官方

目的:根据入参条件不同,动态组装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/构造方法

}

 

  

剩余内容,需要“订阅专栏”后继续阅读

正文结束了
本文目录