hibernte+mysql纯sql查询问题

以下代码始终报错: /** * 获得换车动态 * @return */public ListObject[] getTransDynamic(){Object result = super.commonDao.execute(new IHibernateCallback() {public Object doInHibernate(Session session) {String sql =select +(select ms.nickname

以下代码始终报错:

/**
	 * 获得换车动态
	 * @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)result;
	}</object>

错误信息如下：

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     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)result;
	}</object>