以下代码始终报错: /** * 获得换车动态 * @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 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; }
错误信息如下：
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 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; }