Hibernate 自定义查询sql 并使用自定义对象接收查询结果

ゝ一纸荒年。 2022-06-03 05:23 499阅读 0赞

> 在很多的生产中，hibernate并不能满足我们所有的开发需求，比如，很多表的联合查询，并且查询之后的各种结果封装在自定义的dto对象中，那么我们就需要使用自定义的sql进行查询了，好了，开始我们新的旅程吧。

### 需求： ###

*  n张表进行联合查询
 *  将结果封装在一个DTO的对象中

### 代码： ###

**本文中使用了一个很复杂的联合查询的sql，所以大家并不需要了解详细，只需要只是我们进行一个很复杂的多张表进行联合查询的操作，最后将结果使用自定义的dto对象接收即可**

#### SQL ####

SELECT p9.projectId, p9.fenxiaoMoney, p9.shopCount FROM ( SELECT p8.projectId, p8.fenxiaoMoney, count(ic.inviteByPerson) shopCount FROM ( SELECT p7.projectId, p7.fenxiaoMoney FROM ( SELECT pp.projectId, g.fenxiaoMoney FROM ( SELECT p6.projectId FROM ( SELECT p5.projectId FROM ( SELECT p4.projectId FROM ( SELECT p2.projectId FROM ( SELECT p1.projectId FROM ( SELECT p.projectId FROM t_projects p WHERE 1 = 1 AND p.averagePrice >= 1.0 AND p.averagePrice <= 10000000000.0 AND p.saleLongitude >= 1.0 AND p.saleLongitude <= 10000.00 AND p.saleLatitude >= 1.0 AND p.saleLatitude <= 10000.0 AND p.rightsYears = 70 AND p.city LIKE '%330000%' #AND p.isOpenStatus = 1 AND p.buildArea >= 1.0 AND p.buildArea <= 10000000.0 #AND p.afforestationRatio = 1 AND p.projectName LIKE '%%' #AND p.projectId = '1515269bc87448b4927fa676c624c8f6' ) p1 INNER JOIN ( SELECT targetId FROM t_tagsrelation WHERE 1 = 1 AND originalTags LIKE '%4507%' AND originalTags LIKE '%757%' ) t1 ON p1.projectId = t1.targetId ) p2 INNER JOIN ( SELECT h.projectId FROM t_projecthouses h WHERE h.houseNum IN ( SELECT targetId FROM t_tagsrelation WHERE 1 = 1 AND originalTags LIKE '%%' ) AND h.houseStatus = 1 AND isOpen = 1 ) p3 ON p2.projectId = p3.projectId ) p4 INNER JOIN ( SELECT projectId FROM t_projecthousetypes pht WHERE 1 = 1 AND pht.housType = '三房两厅一卫' ) pht1 ON p4.projectId = pht1.projectId GROUP BY p4.projectId ) p5 INNER JOIN ( SELECT ph.projectId FROM t_projecthouses ph WHERE 1 = 1 AND ( ph.houseKind = '10' OR ph.houseKind = '0' ) ) hk ON p5.projectId = hk.projectId GROUP BY p5.projectId ) p6 INNER JOIN ( SELECT applyForPerson FROM t_applychart WHERE applyByPerson = '860635' AND applyStatus = 2 ) ap ON p6.projectId = ap.applyForPerson ) pp LEFT JOIN t_projectguide g ON pp.projectId = g.projectId ) p7 ) p8 LEFT JOIN (SELECT inviteByPerson FROM t_invitechart WHERE inviteStatus = 2) ic ON p8.projectId = ic.inviteByPerson GROUP BY p8.projectId ) p9 LEFT JOIN ( SELECT beCollectId FROM t_newcollectrecord WHERE userId = '4e0b0089-410a-497e-8056-6190e2a183d4' ) nc ON p9.projectId = nc.beCollectId GROUP BY p9.projectId ORDER BY shopCount DESC LIMIT 0, 10

从sql中可以看出我们需要查的三个参数：

*  项目的Id
 *  钱的数量
 *  合作店铺的数量

需要的额外功能：

*  分页
 *  根据上面的参数进行排序

#### SQL拼接方法 ####

**顾名思义就是将上述的sql使用java进行拼接，因为这样便于查看和bug查找(这段并不重要)**

/** * 拼接Hql中的p段 * @param param * @param user * @param sb */
        private void jointHqlTheP(ShopSearchParam param, User user, StringBuilder sb){
            /** * SELECT p.projectId FROM t_projects p WHERE 1 = 1 AND p.averagePrice >= 1.0 AND p.averagePrice <= 10000000000.0 AND p.saleLongitude >= 1.0 AND p.saleLongitude <= 10000.00 AND p.saleLatitude >= 1.0 AND p.saleLatitude <= 10000.0 AND p.rightsYears = 70 AND p.city LIKE '%330000%' #AND p.isOpenStatus = 1 AND p.buildArea >= 1.0 AND p.buildArea <= 10000000.0 #AND p.afforestationRatio = 1 AND p.projectName LIKE '%%' #AND p.projectId = '1515269bc87448b4927fa676c624c8f6' */
    
            sb.append(" SELECT ");
            sb.append(" p.projectId ");
            sb.append(" FROM ");
            sb.append(" t_projects p ");
            sb.append(" WHERE ");
            sb.append(" 1 = 1 ");
            if(param.getMinAveragePrice() != null){
                sb.append(" AND p.averagePrice >= " + param.getMinAveragePrice());
            }
            if(param.getMaxAveragePrice() != null){
                sb.append(" AND p.averagePrice <= " + param.getMaxAveragePrice());
            }
            if(!isEmpty(param.getMinLongitudes())){
                sb.append(" AND p.saleLongitude >= " + param.getMinLongitudes());
            }
            if(!isEmpty(param.getMaxLongitudes())){
                sb.append(" AND p.saleLongitude <= " + param.getMaxLongitudes());
            }
            if(!isEmpty(param.getMinLatitudes())){
                sb.append(" AND p.saleLatitude >= " + param.getMinLatitudes());
            }
            if(!isEmpty(param.getMaxLatitudes())){
                sb.append(" AND p.saleLatitude <= " + param.getMaxLatitudes());
            }
            if(param.getRightsYears() != null){
                sb.append(" AND p.rightsYears = " + param.getRightsYears());
            }
            if(!isEmpty(param.getCityId())){
                sb.append(" AND p.city LIKE '%" + param.getCityId() + "%'");
            }else if(!isEmpty(param.getCityName())){
                String hql = "from CountryProvinceInfo where cityName = '" + param.getCityName() + "' and cityLevel = '市' ";
                CountryProvinceInfo cp = (CountryProvinceInfo) baseDao.loadObject(hql);
                String cityId = cp.getUpCityId();
                sb.append(" AND p.city LIKE '%" + cityId + "%'");
            }
            //项目状态 在售、等待售、全部
            if(param.getProjectStatus() != null){
    
                sb.append(" AND p.isOpenStatus = " + param.getProjectStatus());
            }
            if(param.getMinBulidArea() != null){
                sb.append(" AND p.buildArea >= " + param.getMinBulidArea());
            }
            if(param.getMaxBuildArea() != null){
                sb.append(" AND p.buildArea <= " + param.getMaxBuildArea());
            }
            if(!isEmpty(param.getProjectName())){
                sb.append(" AND p.projectName LIKE '%" + param.getProjectName() + "%' ");
            }
            if(!isEmpty(param.getProjectId())){
                sb.append(" AND p.projectId = '" + param.getProjectId() + "' ");
            }
    
        }
    
        /** * 拼接Hql的p1段 -- 项目标签过滤 * @param param * @param user * @param sb */
        private void jointHqlTheP1(ShopSearchParam param, User user, StringBuilder sb){
            /** * SELECT p1.projectId FROM ( {p}//这个是p段 ) p1 INNER JOIN ( SELECT targetId FROM t_tagsrelation WHERE 1 = 1 AND originalTags LIKE '%4507%' AND originalTags LIKE '%757%' ) t1 ON p1.projectId = t1.targetId */
            sb.append(" SELECT ");
            sb.append(" p1.projectId ");
            sb.append(" FROM ");
            sb.append(" ( ");
    
            this.jointHqlTheP(param, user, sb);//将p加入
    
            sb.append(" ) p1 ");
            if(!isEmpty(param.getpTags())){
                sb.append(" INNER JOIN ( ");
                sb.append(" SELECT ");
                sb.append(" targetId ");
                sb.append(" FROM ");
                sb.append(" t_tagsrelation ");
                sb.append(" WHERE ");
                sb.append(" 1 = 1 ");
                String[] pTags = this.stringToArray(param.getpTags());
                for (int i = 0; i < pTags.length; i++) {
                    sb.append(" AND originalTags LIKE '%" + pTags[i] + "%' ");
                }
                sb.append(" ) t1 ON p1.projectId = t1.targetId ");
            }
    
        }
    
        /** * 拼接Hql的p2段 -- 房源标签过滤 * @param param * @param user * @param sb */
        private void jointHqlTheP2(ShopSearchParam param, User user, StringBuilder sb){
            /** * SELECT p2.projectId FROM ( {p1}//这个是p1段 ) p2 INNER JOIN ( SELECT h.projectId FROM t_projecthouses h WHERE h.houseNum IN ( SELECT targetId FROM t_tagsrelation WHERE 1 = 1 AND originalTags LIKE '%%' ) AND h.houseStatus = 1 AND isOpen = 1 ) p3 ON p2.projectId = p3.projectId */
    
            sb.append(" SELECT ");
            sb.append(" p2.projectId ");
            sb.append(" FROM ");
            sb.append(" ( ");
    
            this.jointHqlTheP1(param, user, sb);//这个拼接p1
    
            sb.append(" ) p2 ");
    
            if(!isEmpty(param.gethTags())){
                sb.append(" INNER JOIN ( ");
                sb.append(" SELECT ");
                sb.append(" h.projectId ");
                sb.append(" FROM ");
                sb.append(" t_projecthouses h ");
                sb.append(" WHERE ");
                sb.append(" h.houseNum IN ( ");
                sb.append(" SELECT ");
                sb.append(" targetId ");
                sb.append(" FROM ");
                sb.append(" t_tagsrelation ");
                sb.append(" WHERE ");
                sb.append(" 1=1 ");
                String[] hTags = this.stringToArray(param.gethTags());
                for (int i = 0; i < hTags.length; i++) {
                    sb.append(" AND originalTags LIKE '%" + hTags[i] + "%' ");
                }
                sb.append(" ) ");
                sb.append(" AND h.houseStatus = 1 ");
                sb.append(" AND isOpen = 1 ");
                sb.append(" ) p3 ON p2.projectId = p3.projectId ");
    
            }
        }
    
        /** * 拼接Hql的p4段 -- 房源户型 * @param param * @param user * @param sb */
        private void jointHqlTheP4(ShopSearchParam param, User user, StringBuilder sb){
            /** * SELECT p4.projectId FROM ( {p2}//这个是p2段 ) p4 INNER JOIN ( SELECT projectId FROM t_projecthousetypes pht WHERE 1 = 1 AND pht.housType = '三房两厅一卫' ) pht1 ON p4.projectId = pht1.projectId GROUP BY p4.projectId */
            sb.append(" SELECT ");
            sb.append(" p4.projectId ");
            sb.append(" FROM ");
            sb.append(" ( ");
    
            this.jointHqlTheP2(param, user, sb);
    
            sb.append(" ) p4 ");
    
            if(!isEmpty(param.getHouseType())){
                sb.append(" INNER JOIN ( ");
                sb.append(" SELECT ");
                sb.append(" projectId ");
                sb.append(" FROM ");
                sb.append(" t_projecthousetypes pht ");
                sb.append(" WHERE ");
                sb.append(" 1 = 1 ");
                sb.append(" AND pht.housType = '" + param.getHouseType() + "' ");
                sb.append(" ) pht1 ON p4.projectId = pht1.projectId ");
                sb.append(" GROUP BY ");
                sb.append(" p4.projectId ");
            }
    
    
    
        }
    
        /** * 拼接Hql的p5段 -- 房源类型过滤 * @param param * @param user * @param sb */
        private void jointHqlTheP5(ShopSearchParam param, User user, StringBuilder sb){
            /** * SELECT p5.projectId FROM ( {p4}//拼接p4段 ) p5 INNER JOIN ( SELECT ph.projectId FROM t_projecthouses ph WHERE 1 = 1 AND ( ph.houseKind = '10' OR ph.houseKind = '0' ) ) hk ON p5.projectId = hk.projectId GROUP BY p5.projectId */
            sb.append(" SELECT ");
            sb.append(" p5.projectId ");
            sb.append(" FROM ");
            sb.append(" ( ");
    
            this.jointHqlTheP4(param, user, sb);
    
            sb.append(" ) p5 ");
    
            if(!isEmpty(param.getHouseKinds())){
                sb.append(" INNER JOIN ( ");
                sb.append(" SELECT ");
                sb.append(" ph.projectId ");
                sb.append(" FROM ");
                sb.append(" t_projecthouses ph ");
                sb.append(" WHERE ");
                sb.append(" 1 = 1 ");
                sb.append(" AND ( ");
                String[] kinds = this.stringToArray(param.getHouseKinds());
                for (int i = 0; i < kinds.length; i++) {
                    if(i == 0){
                        sb.append(" ph.houseKind = '" + kinds[i] + "' ");
                    }else{
                        sb.append(" OR ph.houseKind = '" + kinds[i] + "' ");
                    }
                }
                sb.append(" ) ");
                sb.append(" ) hk ON p5.projectId = hk.projectId ");
                sb.append(" GROUP BY p5.projectId ");
            }
    
        }
    
        /** * 拼接Hql的p5段 -- 房源类型过滤 * @param param * @param user * @param sb */
        private void jointHqlTheP6(ShopSearchParam param, User user, StringBuilder sb){
            /** * SELECT p6.projectId FROM ( {p5}//拼接p5段 ) p6 INNER JOIN ( SELECT applyForPerson FROM t_applychart WHERE applyByPerson = '860635' AND applyStatus = 2 ) ap ON p6.projectId = ap.applyForPerson */
            sb.append(" SELECT ");
            sb.append(" p6.projectId ");
            sb.append(" FROM ");
            sb.append(" ( ");
            this.jointHqlTheP5(param, user, sb);
            sb.append(" ) p6 ");
    
            if(param.getInviteStatus() != null){
                sb.append(" INNER JOIN ( ");
                sb.append(" SELECT ");
                sb.append(" applyForPerson ");
                sb.append(" FROM ");
                sb.append(" t_applychart ");
                sb.append(" WHERE ");
                sb.append(" applyByPerson = '" + user.getParentId() + "' ");
                sb.append(" AND applyStatus = " + param.getInviteStatus());
                sb.append(" ) ap ON p6.projectId = ap.applyForPerson ");
    
            }
    
        }
    
        /** * 拼接Hql的PP段 -- 查看带看业务定义中的佣金 * @param param * @param user * @param sb */
        private void jointHqlThePP(ShopSearchParam param, User user, StringBuilder sb){
            /** * SELECT pp.projectId, g.fenxiaoMoney FROM ( {p6}//拼接p6段 ) pp LEFT JOIN t_projectguide g ON pp.projectId = g.projectId */
    
            sb.append(" SELECT ");
            sb.append(" pp.projectId, ");
            sb.append(" g.fenxiaoMoney ");
            sb.append(" FROM ");
            sb.append(" ( ");
    
            this.jointHqlTheP6(param, user, sb);
    
            sb.append(" ) pp ");
            sb.append(" LEFT JOIN t_projectguide g ON pp.projectId = g.projectId ");
    
        }
    
    
        /** * 拼接Hql的P7段 * @param param * @param user * @param sb */
        private void jointHqlTheP7(ShopSearchParam param, User user, StringBuilder sb){
            /** * SELECT p7.projectId, p7.fenxiaoMoney FROM ( {pp}//拼接pp段 ) p7 */
    
            sb.append(" SELECT ");
            sb.append(" p7.projectId, ");
            sb.append(" p7.fenxiaoMoney ");
            sb.append(" FROM ");
            sb.append(" ( ");
    
            this.jointHqlThePP(param, user, sb);
    
            sb.append(" ) p7 ");
        }
    
    
        /** * 拼接Hql的P8段 -- 项目的合作门店数量 * @param param * @param user * @param sb */
        private void jointHqlTheP8(ShopSearchParam param, User user, StringBuilder sb){
            /** * SELECT p8.projectId, p8.fenxiaoMoney, count(ic.inviteByPerson) shopCount FROM ( {p7} //拼接P7段 ) p8 LEFT JOIN t_invitechart ic ON p8.projectId = ic.inviteByPerson GROUP BY p8.projectId */
            sb.append(" SELECT ");
            sb.append(" p8.projectId, ");
            sb.append(" p8.fenxiaoMoney, ");
            sb.append(" count(ic.inviteByPerson) shopCount ");
            sb.append(" FROM ");
            sb.append(" ( ");
    
            this.jointHqlTheP7(param, user, sb);
    
            sb.append(" ) p8 ");
            sb.append(" LEFT JOIN (SELECT inviteByPerson FROM t_invitechart WHERE inviteStatus = 2) ic ON p8.projectId = ic.inviteByPerson ");
            sb.append(" GROUP BY p8.projectId ");
        }
    
        /** * 拼接Hql的P9段 -- 个人是否收藏 * @param param * @param user * @param sb */
        private void jointHqlTheP9(ShopSearchParam param, User user, StringBuilder sb, Page page){
    
            /** * SELECT * FROM ( {p8}//拼接p8段 ) p9 LEFT JOIN ( SELECT beCollectId FROM t_newcollectrecord WHERE userId = '4e0b0089-410a-497e-8056-6190e2a183d4' ) nc ON p9.projectId = nc.beCollectId GROUP BY p9.projectId ORDER BY shopCount DESC LIMIT 0, 10 */
    
            sb.append(" SELECT ");
            sb.append(" p9.projectId, p9.fenxiaoMoney, p9.shopCount ");
            sb.append(" FROM ");
            sb.append(" ( ");
    
            this.jointHqlTheP8(param, user, sb);
    
            sb.append(" ) p9 ");
            if(param.getIsClikeLike() != null){
                sb.append(" LEFT JOIN ( ");
                sb.append(" SELECT ");
                sb.append(" beCollectId ");
                sb.append(" FROM ");
                sb.append(" t_newcollectrecord ");
                sb.append(" WHERE ");
                sb.append(" userId = '" + user.getUserId() + "' ");
                sb.append(" ) nc ON p9.projectId ");
                if(param.getIsClikeLike() == 1){
       //收藏
                    sb.append(" = ");
                }else{
       //未收藏
                    sb.append(" != ");
                }
                sb.append(" nc.beCollectId ");
    
                sb.append(" GROUP BY ");
                sb.append(" p9.projectId ");
    
            }
    
            if(!isEmpty(param.getOrderType()) && param.getOrder() != null){
    
                sb.append(" ORDER BY ");
                if(param.getOrderType().equals(ShopSearchConstant.ORDER_TYPE_SHOP_COUNT)){
       //合作门店数量
                    sb.append(" p9.shopCount ");
                }else if(param.getOrderType().equals(ShopSearchConstant.ORDER_TYPE_MONEY)){
       //及时结款率 - 暂无
                    sb.append(" p9.shopCount ");//暂无，暂时用这个替代
                }else if(param.getOrderType().equals(ShopSearchConstant.ORDER_TYPE_COMMISSION)){
       //佣金高低
                    sb.append(" p9.fenxiaoMoney ");
                }
                sb.append(param.getOrderType());
                if(param.getOrder() == 0){
                    sb.append(" DESC ");
                }else{
                    sb.append(" ASC ");
                }
            }
    
            if(page != null){
                sb.append(" LIMIT " + page.getStart() + "," + page.getLimit());
            }
    
    
        }

### Service 层 ###

//这个方法是将反射之后得到的字段类型和字段名称进行拆分
    private void invokeDtoToFieldsToArray(Field[] fields, String[] colums, String[] types){
            for (int i = 0; i < fields.length; i++) {
                types[i] = fields[i].getType().toString().replace("class java.lang.", "");
                colums[i] = fields[i].getName();
            }
        }

**下面的方法就是整个Service中的核心方法了，将自定义sql交给hibernate进行处理，并且使用自定义的dto对象进行封装**

//这个方法就是将sql进行查询
    @Override
        public void findProjectListByShopSearchParam(ShopSearchParam shopSearchParam, Page page, User user) {
            StringBuilder sb = new StringBuilder();
    
            // 利用反射获取Project组装类的所有的字段和类型
            Field[] fields = ProjectMapListDTO.class.getDeclaredFields();
            String[] colums = new String[fields.length];
            String[] types = new String[fields.length];
            this.invokeDtoToFieldsToArray(fields, colums, types);
            this.jointHqlTheP9(shopSearchParam, user, sb, page);
            //返回的结果
            List<ProjectMapTotalDTO> rsList = new LinkedList<>();
            //当前用户所有收藏的项目
            List<NewCollectRecord> collList =  this.findNewCollectRecordByUserId(user.getUserId(), ShopSearchConstant.COLLECT_TYPE_PROJECT,null);
            //当前门店所有的申请
            List<ApplyChart> appList = this.findApplyChartsByShopId(user.getParentId(), null);
            //---------重要---------这个就是关键方法
            List<ProjectMapListDTO> list = baseDao.queryDTOBySql(sb.toString(), ProjectMapListDTO.class, colums, types);
    
            //这里是工作需要，将查询出来的结果进行再次封装--看需要吧
            //需要进行判断是否收藏和申请状态的判断
            for (ProjectMapListDTO pd : list) {
    
                ProjectMapTotalDTO ptd = new ProjectMapTotalDTO();
                Project project = (Project) baseDao.loadById(Project.class, pd.getProjectId());
                ptd.setProject(project);
                ptd.setFenxiaoMoney(pd.getFenxiaoMoney());
                ptd.setShopCount(pd.getShopCount());
                //判断是否已经被收藏
                boolean flag = false;
                for(NewCollectRecord nr : collList){
                    if(nr.getBeCollectId().equals(pd.getProjectId())){
                        flag = true;
                    }
                }
    
                //查看合作关系
                for(ApplyChart ac : appList){
                    if(pd.getProjectId().equals(ac.getApplyForPerson())){
                        ptd.setApplyStatus(ac.getApplyStatus());
                    }
                }
    
                ptd.setIsLike(flag ? 1 : 0);
                rsList.add(ptd);
            }
            page.setRoot(rsList);
    
            List<ProjectMapTotalDTO> lz = this.findProjectListForMapNew(shopSearchParam, user);
            page.setTotal(lz.size());
    
        }

看看DTO对象

package com.sc.tradmaster.service.shop.impl.dto;
    
    public class ProjectMapListDTO { 
    
        private String projectId;
        private Double fenxiaoMoney;
        private Integer shopCount;
    
        //省略set和get方法
    
    
    }

#### DAO核心方法 ####

public class BaseDaoImpl extends HibernateDaoSupport implements BaseDao{ 
    
        @Resource
        public void mySessionFactory(SessionFactory sf){
            super.setSessionFactory(sf);
        }
    
    /** * 执行sql组装DTO对象集合 * @param sql * @param clazz * @return */
        public List queryDTOBySql(String sql,Class clazz,String[] colums,String[] types) {    
            Session session = super.getSessionFactory().getCurrentSession();
            SQLQuery query = session.createSQLQuery(sql);
            if(colums!=null && types!=null && colums.length==types.length){
                for(int i=0;i<colums.length;i++){
                    if(types[i].equals("Integer")){
                        query.addScalar(colums[i],StandardBasicTypes.INTEGER);
                    }else if(types[i].equals("String")){
                        query.addScalar(colums[i],StandardBasicTypes.STRING);
                    }else if(types[i].equals("Double")){
                        query.addScalar(colums[i],StandardBasicTypes.DOUBLE);
                    }else if(types[i].equals("Long")){
                        query.addScalar(colums[i],StandardBasicTypes.LONG);
                    }
    
                }
            }
            List<Object[]> list = query.setResultTransformer(Transformers.aliasToBean(clazz)).list();
            return list;
        }
       }

> 结语：由于是在工作中用到的代码段，所以显得有些复杂化了，以后有时间的话，使用通俗易懂的方法讲解出来，先留个坑，如果大家遇到这种使用场景无法解决的话，欢迎打扰：1085143002@qq.com