以下代码始终报错:
/**
* 获得换车动态
* @return
*/
public List<Object[]> getTransDynamic(){
Object result = super.commonDao.execute(new IHibernateCallback() {
public Object doInHibernate(Session session) {
String sql =
"select "+
"(select ms.nickname from Memberstatus as ms where ms.MemberId=t.FPMemberId) as fpname, "+
"(select ms.nickname from Memberstatus as ms where ms.MemberId=t.SPMemberId) as spname, "+
"(case t.Status "+
" when '1' then '正在换车' "+
" when '2' then '换车完成' "+
"end) as st, "+
"t.StartTime, "+
"t.`FPMemberId` as fpid, "+
"t.`SPMemberId` as spid "+
"from `Transfer` as t "+
"where t.Status in ('1','2') and t.EndTime<=sysdate() "+
"order by t.StartTime "+
"limit 0,7 ";
return session.createSQLQuery(sql).list();
}
});
return result==null?null:(List<Object[]>)result;
}
错误信息如下:
root cause
java.sql.SQLException: Column 'FPMemberId' not found. com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926) com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1093) com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5488) com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3342) org.hibernate.type.StringType.get(StringType.java:18) org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163) org.hibernate.type.NullableType.nullSafeGet(NullableType.java:189) org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:474) org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:420) org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:317) org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:606) org.hibernate.loader.Loader.doQuery(Loader.java:701) org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) org.hibernate.loader.Loader.doList(Loader.java:2217) org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2108) org.hibernate.loader.Loader.list(Loader.java:2103) org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289) org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1696) org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142) org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152) com.cdaccp.hc.biz.impl.TransferInfoService$5.doInHibernate(TransferInfoService.java:386) com.jiang.foundation.dao.CommonDao$1.doInHibernate(CommonDao.java:562) org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419) org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339) com.jiang.foundation.dao.CommonDao.execute(CommonDao.java:558) com.cdaccp.hc.biz.impl.TransferInfoService.getTransDynamic(TransferInfoService.java:367)
观察控制台的输出:
Hibernate:
select
(select
ms.nickname
from
Memberstatus as ms
where
ms.MemberId=t.FPMemberId) as fpname,
(select
ms.nickname
from
Memberstatus as ms
where
ms.MemberId=t.SPMemberId) as spname,
(case t.Status
when '1' then '正在换车'
when '2' then '换车完成'
end) as st,
t.StartTime,
t.`FPMemberId` as fpid,
t.`SPMemberId` as spid
from
`Transfer` as t
where
t.Status in (
'1','2'
)
and t.EndTime<=sysdate()
order by
t.StartTime limit 0,
7
2013-59-03 10:59:26could not read column value from result set: FPMemberId; Column 'FPMemberId' not found.
2013-59-03 10:59:26SQL Error: 0, SQLState: S0022
2013-59-03 10:59:26Column 'FPMemberId' not found.
将以上sql粘贴到mysql工具中执行完全没有任何问题,这就怪了。
最后折腾处理如下:
select * from (原来的查询sql) as 别名
最后行了!
完整如下:
/**
* 获得换车动态
* @return
*/
public List<Object[]> getTransDynamic(){
Object result = super.commonDao.execute(new IHibernateCallback() {
public Object doInHibernate(Session session) {
String sql ="select * from (" +
"select "+
"(select ms.nickname from Memberstatus as ms where ms.MemberId=t.FPMemberId) as fpname, "+
"(select ms.nickname from Memberstatus as ms where ms.MemberId=t.SPMemberId) as spname, "+
"(case t.Status "+
" when '1' then '正在换车' "+
" when '2' then '换车完成' "+
"end) as st, "+
"t.StartTime, "+
"t.`FPMemberId` as fpid, "+
"t.`SPMemberId` as spid "+
"from `Transfer` as t "+
"where t.Status in ('1','2') and t.EndTime<=sysdate() "+
"order by t.StartTime "+
"limit 0,7 "+
") as model";
return session.createSQLQuery(sql).list();
}
});
return result==null?null:(List<Object[]>)result;
}