我一直试图理解为什么Oracle连续几天会出现此错误,但是在我阅读的所有相关主题中都找不到任何对我有帮助的解决方案。我希望有人能帮助我。我正在处理此查询:
SELECT distinct c.NAME, c.SUPERVISIONNAME, c.INTERNALADDRESS, c.IM, c.ID, c.LINK, c.IW, d.NAME, t.NAME FROM "CONCENTRATOR" c LEFT OUTER JOIN "CONCENTRATOR_GROUP" USING(CONCENTRATOR_ID) LEFT OUTER JOIN "GROUP" g USING(GROUP_ID) LEFT OUTER JOIN "TYPE" t USING(TYPE_ID) LEFT OUTER JOIN "DEPARTMENT" d USING(DEPARTMENT_ID) WHERE TRIM(UPPER(t.NAME)) = 'type' ORDER BY im DESC, id DESC, link DESC, iw DESC, TRIM(UPPER(d.name)) ASC, TRIM(UPPER(c.name)) ASC;
这在SQL Developer上可以正常工作,但是在Java中运行时会引发此错误:
java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at sun.reflect.GeneratedMethodAccessor29.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) at com.sun.proxy.$Proxy39.executeQuery(Unknown Source) at org.hibernate.loader.Loader.getResultSet(Loader.java:1978) at org.hibernate.loader.Loader.doQuery(Loader.java:829) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289) at org.hibernate.loader.Loader.doList(Loader.java:2463) at org.hibernate.loader.Loader.doList(Loader.java:2449) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279) at org.hibernate.loader.Loader.list(Loader.java:2274) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:331) at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1585) at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:224) at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:156) at com.francetelecom.visionet.server.persistance.dao.impl.TemplateDAOImpl.paginate(TemplateDAOImpl.java:282) at com.francetelecom.visionet.server.persistance.dao.impl.ConcentratorDAOImpl.findByCriteriaTest(ConcentratorDAOImpl.java:545)
由我的程序中的这一行引起的(其中querySelect是相关的SQLQuery对象):
List<T> list = (List<T>) querySelect.addEntity(referenceClass).list();
由于ORDER BY,我需要在SELECT中保留c.NAME,d.NAME和t.NAME,我不知道如何在不引发此错误的情况下执行此操作…我在SELECT中尝试使用别名,但没有要么工作。
编辑:
似乎是一个Hibernate问题,而不是SQL问题。这是在“ addEntity”行上引发错误的函数。在这种情况下,预计将返回集中器对象的列表。
@Override @SuppressWarnings("unchecked") public PaginatedList<T> paginate(SQLQuery querySelect, SQLQuery queryCount, int page, int numPerPage) throws PersistanceException { PaginatedList<T> pList = new PaginatedList<T>(); try { int offset = 0; if (numPerPage > -1) { offset = page * numPerPage; } int totalAllPages = ((BigDecimal) queryCount.uniqueResult()).intValue(); querySelect.setMaxResults(numPerPage); querySelect.setFirstResult(offset); List<T> listAll = (List<T>) querySelect.addEntity(referenceClass).list(); pList.setItems(listAll); pList.setPage(page); pList.setPageSize(numPerPage); pList.setTotal(totalAllPages); } catch (HibernateException e) { throw new PersistanceException(e); } return pList; }
这是集中器的对象字段:
private String name; private String supervisionName; private String internalAddress; private boolean activeAlarms; private int im; private int id; private int iw; private int link; private Date lastUpdate; private Type type; private Department department;
这看起来像是休眠的东西,而不是严格的SQL问题。
我认为addEntity所有列都必须具有不同的名称,这些名称必须与要添加的实体中的字段相匹配。遗憾的是,Oracle可以毫无问题地返回带有重复问题名称的结果,这就是您的查询在SQL Developer中工作的原因。
addEntity
尝试为SELECT子句中的所有列赋予不同的别名,特别是与您实体中字段成员匹配的别名。