mysql 查询结果组装sql,hibernate的sql多表查询结果组装为对象

@SuppressWarnings("unchecked")

public SimplePage listPage(WorkFlowEventDto condition, int start, int limit) {

StringBuilder selectColumnSql = new StringBuilder("SELECT a.`id` as id,g.name as resourceType,b.name as instanceName,pin.`serial_num` AS instanceNumber," +

"h.`op_name` as operation,a.status as status,a.`create_time` as createdTime,c.`vm_name` as vmName" +

",d.`vdisk_name` as vdiskName,e.`file_sys_name` as fsName");

StringBuilder selectCountSql = new StringBuilder("select count(a.id)");

StringBuilder sql = new StringBuilder(

" FROM work_flow_event a LEFT JOIN resource_instance b ON a.`resourece_instance_id`=b.`id`" +

" LEFT JOIN product_instance pin ON pin.`id`=b.`product_instance_id`" +

" LEFT JOIN vm_res_instance c ON b.id=c.`vm_id` LEFT JOIN vdisk_resource_instance d ON b.`id`=d.`vdisk_id`" +

" LEFT JOIN file_sys_resource_instance e ON b.`id`=e.`file_sys_id` LEFT JOIN resource f ON b.`resource_id`=f.`id`" +

" LEFT JOIN resource_type g ON f.`type_id`=g.`id` LEFT JOIN work_flow h ON a.event_type=h.op_type WHERE 1=1");

List params = new ArrayList();

if (condition != null) {

if (StringUtils.isNotBlank(condition.getInstanceName())) {

sql.append(" AND b.`name` LIKE ?");

params.add("%"+condition.getInstanceName()+"%");

}

if (StringUtils.isNotBlank(condition.getInstanceNumber())) {

sql.append(" AND pin.`serial_num` LIKE ?");

params.add("%"+condition.getInstanceNumber()+"%");

}

if (StringUtils.isNotBlank(condition.getCloudosName())) {

sql.append(" AND (c.`vm_name` LIKE ? OR d.`vdisk_name` LIKE ? OR e.`file_sys_name` LIKE ?)");

params.add("%"+condition.getCloudosName()+"%");

params.add("%"+condition.getCloudosName()+"%");

params.add("%"+condition.getCloudosName()+"%");

}

if (StringUtils.isNotBlank(condition.getResourceType())) {

sql.append(" AND g.`type_number`=?");

params.add(condition.getResourceType());

}

if (condition.getStatus() != null) {

if(IWorkFlowConstant.WORK_FLOW_STATUS_EXECUTING == condition.getStatus()){

sql.append(" and (a.`status` = 2 or a.`status`= 6)");

}else {

sql.append(" and a.`status`=?");

params.add(condition.getStatus());

}

}

}

selectColumnSql.append(sql).append(" ORDER BY a.`create_time` DESC");

selectCountSql.append(sql);

SQLQuery q = this.getSession().createSQLQuery(selectColumnSql.toString());

SQLQuery qc = this.getSession().createSQLQuery(selectCountSql.toString());

for (int i = 0; i < params.size(); i++) {

q.setParameter(i, params.get(i));

qc.setParameter(i, params.get(i));

}

q.addScalar("id", Hibernate.INTEGER);

q.addScalar("resourceType", Hibernate.STRING);

q.addScalar("instanceName", Hibernate.STRING);

q.addScalar("instanceNumber", Hibernate.STRING);

q.addScalar("operation", Hibernate.STRING);

q.addScalar("status", Hibernate.INTEGER);

q.addScalar("createdTime", Hibernate.TIMESTAMP);

q.addScalar("vmName", Hibernate.STRING);

q.addScalar("vdiskName", Hibernate.STRING);

q.addScalar("fsName", Hibernate.STRING);

q.setResultTransformer(Transformers.aliasToBean(WorkFlowEventDto.class));

List data = q.setMaxResults(limit).setFirstResult(start).list();

int totalCount = Integer.valueOf(qc.uniqueResult().toString());

return new SimplePage(data, totalCount);

这是一个按条件分页查询，主要就是用hibernate查询出对象，WorkFlowEventDto不是一个hibernate实体，重要的不是上边那堆，注意看下边。