hibernte+mysql纯sql查询问题
来源:互联网 发布:程序员开发流程图 编辑:程序博客网 时间:2024/06/14 17:35
以下代码始终报错:
/** * 获得换车动态 * @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;}
- hibernte+mysql纯sql查询问题
- hibernate关联查询纯SQL的实现(mysql)
- hibernte
- 纯SQL语句循环查询
- db2在hibernte中的统计查询
- Sql server 数据库中,纯SQL语句查询、执行 单引号问题。
- 关于 Hibernte 中*.hb.xml 引起的 “Caused by: java.sql.SQLException: ORA-02289: 序列不存在 ”问题
- 关于hibernate纯sql查询返回结果集的问题(hbm.xml中不写多表关联)
- 关于hibernate纯sql查询返回结果集的问题(hbm.xml中不写多表关联)
- 关于hibernate纯sql查询返回结果集的问题(hbm.xml中不写多表关联)
- Hibernate使用纯sql进行查询,更新
- Hibernate使用纯sql进行查询,更新
- SQL Manager for Mysql 查询中文乱码问题
- Oracle、Mysql、MS sql中关于模糊查询问题
- MYSQL /SQL 查询 日期
- SQL查询MySQL笔记
- mysql--sql查询练手
- MySQL多表SQL查询
- 以太网最大传输单元MTU
- TCP报文格式详解
- DirectShow SDK下载
- NSArray和NSMutableArray的常用方法
- linux之sed用法
- hibernte+mysql纯sql查询问题
- Visual Studio中最常用的13个快捷键
- PHP 中关于json二个函数
- linux 文件IO和标准IO
- 二维数组传参||传值
- pthread 陷阱
- vijosP1016北京2008的挂钟 BFS + 优先队列
- 在.NET开发中的单元测试工具之(1)——NUnit
- Dll 模块间(dll, exe)使用导出变量、静态变量和外部变量的试验与结论