注:实际业务中可能比较复杂不得不用原生sql执行时可能会遇到该问题,为了描述问题,本例采用简单的举例
表1:
tbtask:
具有如下列:taskId,name,groupId
表2:
tbtaskGroup:
具有如下列:tbgroupId,name
使用原生sql表连接获取task表和group表的name信息
select task.name,group.name from tbtask task left join tbtaskGroup group on task.groupId=group.tbgroupId
至此阐述问题:
这个sql语句如果直接在mysql客户端执行的话没有任何问题,可以正确得到两个表的name值,但是如果使用hibernate执行原生sql的话会导致得到的结果并不是我们想要的结果,相同列直接发生了值得覆盖现象,甚至导致赋值错乱。
网上也有类似的问题,可能是我的方式不正确均未得到正确的解决办法,根据经验终于试验出了几种解决办法,现总结如下:
错误的方式1:直接命名别名
eq:String sql = select task.name taskName,group.name groupName from tbtask task left join tbtaskGroup group on task.groupId=group.tbgroupId
List list = this.getHibernateTemplate().executeFind(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
List result = session.createSQLQuery(sql).list();
return result;
}
});
return list;
debug发现问题仍然没解决,后台报错:找不到name字段
错误的方式2:使用{}
eq:String sql = select {task}.name taskName,{group}.name groupName from tbtask {task}left join tbtaskGroup {group} on {task}.groupId={group}.tbgroupId
仍然报错
正确解决方式1:
采用子查询重新给列起名
eq:String sql = select task.name taskName,group.groupName groupName from tbtask task left join (select tbgroupId,name as groupName from tbtaskGroup) group on task.groupId=group.tbgroupId
通过!!!!
正确解决方式2:
查询两次 这里只提供大概思路:首先得到task表的名称以及groupId 遍历循环根据groupId查询group表得到对应的group的name值
正确解决方式3:
给重复的列名给别名。然后通过addScalar()方式(参考:http://blog.sina.com.cn/s/blog_534f69a00101m2pp.html)
Hibernate SQL查询,相同列名,重复列名的解决方法
StringBuffer hql = new StringBuffer(" select w.id, w.bugTitle, w.projectCode, p.projectName, w.functionModel,"
+ "w.subProductSystem, w.productVersion, w.testEnvironment, w.developPerson,w.testPerson, w.createTime, w.bugState, "
+ "w.preCondition, w.operationStep,w.bugLevel, w.problemPriority, w.actResult, w.expectResult,"
+ " w.apperFrequency,w.bakOther,ee.name as developPersonName,ee2.name as testPersonName,w.reportPerson,ee3.name reportPersonName,"
+ " bh.toPerson,bh.toPersonName,bh.handleState"
+ " from bug_model w left join sys_employee ee on ee.EPid=w.developPerson "
+ " left join sys_employee ee2 on ee2.EPid=w.testPerson "
+ " left join sys_employee ee3 on ee3.EPid=w.reportPerson "
+ " left join project p on w.projectCode=p.projectCode "
+ "left join (select bhh.toPerson,bhh.handleState,bhh.bugModelId,ee4.name toPersonName from bugModelHandle bhh left join sys_employee ee4 on bhh.toPerson=ee4.EPid) bh on w.id=bh.bugModelId "
+ " where 1=1 ");
if(map!=null&&map.get("reportPerson")!=null&&!"".equals(map.get("reportPerson").toString().trim())){
hql.append(" and w.reportPerson=:reportPerson");
}
if(map!=null&&map.get("projectId")!=null&&!"".equals(map.get("projectId").toString().trim())){
hql.append(" and w.projectCode=:projectId");
}
if(map!=null&&map.get("handleState")!=null&&!"".equals(map.get("handleState").toString().trim())&&!"all".equals(map.get("handleState").toString().trim())){
hql.append(" and bh.handleState=:handleState");
}else if(map!=null&&map.get("handleState")!=null&&"".equals(map.get("handleState").toString().trim())){
hql.append(" and bh.handleState is null ");
}else if(map!=null&&map.get("handleState")!=null&&"all".equals(map.get("handleState").toString().trim())){
map.put("handleState", "");
}
Session session = (Session) entityManager.getDelegate();
org.hibernate.Query query = session.createSQLQuery(hql.toString())
.addScalar("id") .addScalar("bugTitle") .addScalar("projectCode").addScalar("projectName") .addScalar("functionModel")
.addScalar("subProductSystem") .addScalar("productVersion") .addScalar("testEnvironment") .addScalar("developPerson")
.addScalar("testPerson") .addScalar("createTime") .addScalar("bugState")
.addScalar("preCondition") .addScalar("operationStep").addScalar("bugLevel") .addScalar("problemPriority")
.addScalar("actResult") .addScalar("expectResult")
.addScalar("apperFrequency").addScalar("bakOther").addScalar("developPersonName")
.addScalar("testPersonName").addScalar("reportPerson").addScalar("reportPersonName")
.addScalar("toPerson").addScalar("toPersonName").addScalar("handleState");
query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
Set> sets=map.entrySet();
String key="";
for(Map.Entry entry:sets){
key=entry.getKey()+"";
if(entry.getValue()!=null&&!"".equals(entry.getValue().toString()))
{
query.setParameter(key+"", entry.getValue());
}
}
List res=query.list();
int total = res.size();
query.setFirstResult(pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
return new PageImpl