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

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可以更加灵活（为了一些特定的功能）