sql执行正常，用hibernate异常：SQLException: ORA-00918: 未明确定义列；各位大神，在线等，急，谢谢啦~~

[u011015849]

各位大神，在线等，急，谢谢啦~~
之前hibernate执行都ok，我修改了sql，然后就报异常，但是打印出来的sql在数据库工具里执行正常，也有结果，日志打印

异常信息如下：
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar [select m.id, m.sheetId, m.title, to_char(m.sheetAcceptLimit, 'yyyy-mm-dd hh24:mi:ss'),m.mainAlarmId,m.mainAlarmStaId, m.mainOriAlarmId, m.mainNetSortOne, m.mainNetSortTwo, to_char(m.sheetCompleteLimit, 'yyyy-mm-dd hh24:mi:ss'),to_char(m.mainCompleteLimitT1, 'yyyy-mm-dd hh24:mi:ss'),to_char(m.mainCompleteLimitT2, 'yyyy-mm-dd hh24:mi:ss'),to_char(m.mainFaultGenerantTime, 'yyyy-mm-dd hh24:mi:ss'),m.mainFaultGenerantCity, m.mainFaultGenerantCounty,m.mainFaultGenerantVillage, m.mainTenanceMode, m.mainNetName,m.sendTime,m.mainAlarmLevel,m.mainExtend1,m.mainAlarmSolveDate,m.mainIsPretreatment,m.mainEquipmentType, m.mainEquipmentFactory,link1.linkFaultReasonSort, link1.linkFaultReasonSubsection,link1.operateUserId,link1.linkDealStep,link1.linkAppraise,link1.activeTemplateId,link1.operateTime,link1.linkFaultDealResult,d2.operateUserId, d2.operateType from centralcommonfault_main m left join (select l.mainId,l.operateUserId,l.linkDealStep,l.operateTime,l.operateDeptId,dict1.dictname as linkFaultReasonSort, dict2.dictname as linkFaultReasonSubsection,l.linkAppraise,l.activeTemplateId,l.linkFaultDealResult from centralcommonfault_link l left join (select dict.dictname, dict.dictid from taw_system_dicttype dict) dict1 on dict1.dictid = l.linkFaultReasonSort left join (select dict.dictname, dict.dictid from taw_system_dicttype dict) dict2 on dict2.dictid = l.linkFaultReasonSubsection where l.operatetype = 46 and l.operateTime = (select max(l1.operateTime) from centralcommonfault_link l1 where l1.operatetype = 46 and l1.mainId = l.mainId)) link1 on m.id = link1.mainId left join (select l.mainId,l.operateUserId,l.operateType from centralcommonfault_link l where l.operateType in(54,55)) d2 on d2.mainId=m.id WHERE m.deleted=0 and m.sendTime >= to_date('2014-06-25 09:25:42','yyyy-MM-dd HH24:mi:ss') and m.sendTime <= to_date('2014-06-26 09:25:42','yyyy-MM-dd HH24:mi:ss') order by m.sendTime desc ]; nested exception is java.sql.SQLException: ORA-00918: 未明确定义列

java.sql.SQLException: ORA-00918: 未明确定义列

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)

java代码如下（执行别的sql都好着）：
public List getListBySql(final String countsql, final String sql, final Integer curPage, final Integer pageSize, int aTotal[])
throws HibernateException
{
HibernateCallback callbackcount = new HibernateCallback() {

public Object doInHibernate(Session session)
throws HibernateException
{
Query query = session.createSQLQuery(countsql);
return (BigDecimal)query.uniqueResult();
}

};
BigDecimal total = (BigDecimal)getHibernateTemplate().execute(callbackcount);
aTotal[0] = total.intValue();
HibernateCallback callback = new HibernateCallback() {

public Object doInHibernate(Session session)
throws HibernateException
{
Query query = session.createSQLQuery(sql);
if (pageSize.intValue() != -1)
{
query.setFirstResult(pageSize.intValue() * curPage.intValue());
query.setMaxResults(pageSize.intValue());
}
return query.list();
}

};
return getHibernateTemplate().executeFind(callback);
}

[tony4geek]

2个表里都有相同名字的字段

[u011015849]

恩，但是相同的列名前都有加表的别名，并且数据库里执行sql是没问题的

[桃子_]

会不会重复的是你java表定义的外键索引？

[么知道呀]

你用的是hibernate，写的sql里面有两个字段冲突了，如a表里面有name,b表里面有name，select a.name,b.name from a,b就会出问题，但是在sql工具里面能正常执行这个是hibernate的原因

[u011015849]

java表定义的外键索引是什么啊？不太清楚，这个sql只用到三个表，这三个表的索引是没有列名相同的；并且非常怪异的是我单在数据库里执行sql是ok的，就是用hibernate去执行sql的时候就异常了

[u011015849]

4楼，我有仔细检查，sql，每个字段前面是都有加别名的 select m.id, m.sheetId, m.title, to_char(m.sheetAcceptLimit, 'yyyy-mm-dd hh24:mi:ss'),m.mainAlarmId,m.mainAlarmStaId, m.mainOriAlarmId, m.mainNetSortOne, m.mainNetSortTwo, to_char(m.sheetCompleteLimit, 'yyyy-mm-dd hh24:mi:ss'),to_char(m.mainCompleteLimitT1, 'yyyy-mm-dd hh24:mi:ss'), to_char(m.mainCompleteLimitT2, 'yyyy-mm-dd hh24:mi:ss'),to_char(m.mainFaultGenerantTime, 'yyyy-mm-dd hh24:mi:ss'),m.mainFaultGenerantCity, m.mainFaultGenerantCounty,m.mainFaultGenerantVillage, m.mainTenanceMode, m.mainNetName,m.sendTime,m.mainAlarmLevel,m.mainExtend1,m.mainAlarmSolveDate, m.mainIsPretreatment,m.mainEquipmentType, m.mainEquipmentFactory,link1.linkFaultReasonSort, link1.linkFaultReasonSubsection,link1.operateUserId, link1.linkDealStep,link1.linkAppraise,link1.activeTemplateId,link1.operateTime,link1.linkFaultDealResult,d2.operateUserId, case d2.operateType when 54 then '质检不通过' when 55 then '质检通过' else '' end from centralcommonfault_main m left join (select l.mainId,l.operateUserId,l.linkDealStep,l.operateTime,l.operateDeptId,dict1.dictname as linkFaultReasonSort, dict2.dictname as linkFaultReasonSubsection,l.linkAppraise,l.activeTemplateId,l.linkFaultDealResult from centralcommonfault_link l left join (select dict.dictname, dict.dictid from taw_system_dicttype dict) dict1 on dict1.dictid = l.linkFaultReasonSort left join (select dict.dictname, dict.dictid from taw_system_dicttype dict) dict2 on dict2.dictid = l.linkFaultReasonSubsection where l.operatetype = 46 and l.operateTime = (select max(l1.operateTime) from centralcommonfault_link l1 where l1.operatetype = 46 and l1.mainId = l.mainId)) link1 on m.id = link1.mainId left join (select l.mainId,l.operateUserId,l.operateType from centralcommonfault_link l where l.operateType in(54,55)) d2 on d2.mainId=m.id WHERE m.deleted=0 and m.sendTime >= to_date('2014-06-01 09:14:59','yyyy-MM-dd HH24:mi:ss') and m.sendTime <= to_date('2014-06-26 09:14:59','yyyy-MM-dd HH24:mi:ss') order by m.sendTime desc

[u011015849]

4楼，刚看错了，那应该怎么办呢，肯定是有重复的列名的

[慢慢地转变]

未定义明确列，是不是查询的表别名 或者是字段写的有错误？

[u011015849]

8楼，应该不会吧，要是有错，那应该执行sql的时候就会报错吧

[花谢尊前不敢香]

在数据库里查出来，看看对应的列名是否有一样的就知道了。

[u011015849]

十楼，select 是有一样名字的，你的意思是要给一样名字的列名在起个别名吗

[花谢尊前不敢香]

嗯，是这样的。

[u011015849]

#12 恩，解决了，是这样的，select中不能有相同的列名，4楼和十二楼正解，谢谢，其他楼也谢谢啦