Criteria 三层嵌套 排序 投影 去重 查询

Criteria 复杂查询

@Override
    public Map<String, Object> blurFindOfficers(String officerName) throws Exception {

        List<Criterion> cri = new ArrayList<Criterion>();

        DetachedCriteria dOfficerCriteria = DetachedCriteria.forClass(SsOfficer.class)
                .setProjection(Property.forName("officerId"))
                .add(Restrictions.like("name", officerName, MatchMode.START));
        DetachedCriteria preCriteria = DetachedCriteria.forClass(SsClientOfficerMovement.class)
                .setProjection(Property.forName("ssClientPreOfficerMovement"))
                .add(Restrictions.isNotNull("ssClientPreOfficerMovement"));
        Criterion currenrRes = Restrictions.and(
                Restrictions.and(Restrictions.eq("status", JsonPayloadApi.CURRENT),
                        Restrictions.not(Property.forName("movementId").in(preCriteria))),
                Property.forName("ssOfficer.officerId").in(dOfficerCriteria));
        cri.add(currenrRes);

        List<Order> orderList = new ArrayList<Order>();
        Order order = Order.desc("injectedEdate");
        orderList.add(order);

        // System.out.println(lastCurrentMovementId(10));

        return universalDao.find("local.ssOfficers", SsClientOfficerMovement.class, cri, orderList, null, null, null,
                true, "ssOfficer");

        //
        // List<SsOfficer> commonOfficerList = (List<SsOfficer>)
        // universalDao.executeHqlQuery(hql, params);
        // map.put("ssOfficers", commonOfficerList);
        // return map;
    }

@Override
    public Map<String, Object> find(String key, Class<?> clz, List<Criterion> criterions, List<Order> orders,
            Integer pageSize, Integer currentPage, Map<String, String> aliasMap, boolean isDistinct, String projection)
                    throws Exception {

        Map<String, Object> map = new HashMap<String, Object>();
        Map<String, Object> metaMap = null;
        Criteria criteria = sessionFactory.getCurrentSession().createCriteria(clz);

        if (aliasMap != null) {
            Iterator<String> alIt = aliasMap.keySet().iterator();
            while (alIt.hasNext()) {
                String propertyname = alIt.next();
                String alias = aliasMap.get(propertyname);
                criteria.createAlias(propertyname, alias);// 设置别名
            }
        }
        // 查询过滤
        if (criterions != null) {
            for (int i = 0; i < criterions.size(); i++) {
                criteria.add(criterions.get(i));

            }
        }
        // 排序
        if (orders != null) {
            for (int i = 0; i < orders.size(); i++) {
                criteria.addOrder((orders.get(i)));
            }
        }
        // 分页
        if (pageSize != null && currentPage != null) {
            Criteria countCriteria = sessionFactory.getCurrentSession().createCriteria(clz);
            if (criterions != null) {
                for (int i = 0; i < criterions.size(); i++) {

                    countCriteria.add(criterions.get(i));
                }
            }
            if (aliasMap != null) {
                Iterator<String> alIt = aliasMap.keySet().iterator();
                while (alIt.hasNext()) {
                    String propertyname = alIt.next();
                    String alias = aliasMap.get(propertyname);
                    countCriteria.createAlias(propertyname, alias);// 设置别名
                }
            }
            // 得到记录的总条数 一次查询
            int totalCount = ((Number) countCriteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
            // 得到记录的总页数
            int totalPages = (totalCount % pageSize == 0) ? (totalCount / pageSize) : (totalCount / pageSize) + 1;

            criteria.setFirstResult((currentPage - 1) * pageSize);
            criteria.setMaxResults(pageSize);

            metaMap = new HashMap<String, Object>();
            metaMap.put("currentPage", currentPage);
            metaMap.put("perPage", pageSize);
            metaMap.put("totalPages", totalPages);
            metaMap.put("totalCount", totalCount);
            map.put(JsonPayloadApi.META, metaMap);

        }
        if (projection != null && isDistinct) {
            // criteria.setProjection(Property.forName(projection));

            // 一步到位
            criteria.setProjection(Projections.distinct(Property.forName(projection)));
        }
        if (isDistinct) {
            // criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        }

        // 得到列表项的查询
        List<?> results = (List<?>) criteria.list();
        map.put(key, results);

        return map;
    }

原生sql 连接 投影 聚集分组 排序 查询

SELECT c.certificateNo,s.companyName,m.shareClassType,sum(m.numberOfShare), m.amountPerShare*sum(m.numberOfShare),c.registrationId,c.clientId FROM sjerp_sec_2315ag.ss_client_share_certificate_movement m left join sjerp_sec_2315ag.ss_client_share_certificate c on m.mainId=c.certificateId left join sjerp_sec_2315ag.ss_corporate_shareholder s on c.registrationId=s.registerationId where m.shareClassType='eeeeeeeeee' and c.registrationId='hzhcor1' and c.clientId=2315 and m.currency='eeeeeeeeee' group by mainId having sum(numberOfShare)>0 order by c.certificateNo asc;