Hibernate 执行普通的sql语句，并将结果封装成DTO对象

╰半夏微凉° 2022-06-12 03:24 175阅读 0赞

hibernate 自动映射对象有时候在用到多个表进行查询的时候根本搞不定  
如果是查出来的结果进行筛选那么需要分页功能就完蛋了，所以需要用普通的联合查询sql，  
下面上代码：

DTO对象：

package com.sc.tradmaster.service.project.impl.dto;
    
    //门店业务DTO类
    public class ShopBusinessDTO { 
    
        //门店客户名称
        private String shopCustomerName;
        //项目名称
        private String projectName;
        //项目id
        private String projectId;
        //备案状态 0 备案 1 已到访 2 成交
        private Integer applyStatus;
        //备案时间
        private String applyTime;
        //案场客户id
        private String projectCustomerId;
    
        public String getShopCustomerName() {
            return shopCustomerName;
        }
        public void setShopCustomerName(String shopCustomerName) {
            this.shopCustomerName = shopCustomerName;
        }
        public String getProjectName() {
            return projectName;
        }
        public void setProjectName(String projectName) {
            this.projectName = projectName;
        }
        public String getProjectId() {
            return projectId;
        }
        public void setProjectId(String projectId) {
            this.projectId = projectId;
        }
        public Integer getApplyStatus() {
            return applyStatus;
        }
        public void setApplyStatus(Integer applyStatus) {
            this.applyStatus = applyStatus;
        }
        public String getApplyTime() {
            return applyTime;
        }
        public void setApplyTime(String applyTime) {
            this.applyTime = applyTime;
        }
        public String getProjectCustomerId() {
            return projectCustomerId;
        }
        public void setProjectCustomerId(String projectCustomerId) {
            this.projectCustomerId = projectCustomerId;
        }
        @Override
        public String toString() {
            return "ShopBusinessDTO [shopCustomerName=" + shopCustomerName + ", projectName=" + projectName + ", projectId="
                    + projectId + ", applyStatus=" + applyStatus + ", applyTime=" + applyTime + ", projectCustomerId="
                    + projectCustomerId + "]";
        }
    
    
    
    
    }

//将查询出来的结果封装成DTO对象  
Dao层：

/** * @param sql 需要查询的sql语句 * @param clazz DTO对象 * @param colums DTO对象的属性名 * @param types DTO对象的type类型 * */
    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);
                    }
    
                }
            }
            List<Object[]> list = query.setResultTransformer(Transformers.aliasToBean(clazz)).list();
            return list;
        }

Service层：

String hql = "SELECT sc.shopCustomerName as shopCustomerName,p.projectName as projectName, " + " p.projectId as projectId,g.applyStatus as applyStatus,g.applyTime as applyTime, g.projectCustomerId as projectCustomerId "; hql += " from "; hql += " t_shopCustomers as sc,t_projects as p,t_guideRecords as g"; hql += " where "; hql += " sc.shopCustomerId = g.shopCustomerId and (g.applyStatus = 0 or g.applyStatus = 1) and p.projectId = g.projectId "; hql += "and sc.shopId =" + user.getParentId(); //hql += " and sc.shopId = 1 "; //模糊搜索某一个客户名称 if(cusOrProName != null && !"".equals(cusOrProName)){ hql += " and sc.shopCustomerName like '%" + cusOrProName + "%' "; } //进行时间的筛选 if(startTime != null && !"".equals(startTime)){ hql += " and g.applyTime >= '" + startTime + "' "; } if(endTime != null && !"".equals(endTime)){ hql += " and g.applyTime <= '" + endTime + "' "; } //进行备案状态的查询 if(status != null && !"".equals(status)){ hql += " and g.applyStatus = " + status; } //分页 hql += " limit " + page.getStart() + "," + page.getLimit(); String[] colums ={"shopCustomerName","projectName","projectId","applyStatus","applyTime","projectCustomerId"}; String[] types = {"String","String","String","Integer","String","String"}; List<ShopBusinessDTO> list = baseDao.queryDTOBySql(hql, ShopBusinessDTO.class, colums, types);

这样的查询使得hibernate可以更加灵活（为了一些特定的功能）