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;