Java ORA-00918: “列定义不明确”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24364924/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00918: "Column ambiguously defined"
提问by Flash_Back
I'va been trying to understand why Oracle is raising this error for days, but couldn't find any solution that helped me in all the related topics I read. I'm hoping that someone would help me. I'm working on this query :
我一直在试图理解为什么 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;
This works perfectly fine on SQL Developer, but raises this error when run in Java:
这在 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)
Caused by this line on my program (where querySelect is the related SQLQuery object):
由我程序中的这一行引起（其中 querySelect 是相关的 SQLQuery 对象）：
List<T> list = (List<T>) querySelect.addEntity(referenceClass).list();
I need to keep c.NAME, d.NAME and t.NAME in the SELECT due to the ORDER BY and I don't know how to do this without raising this error... I tried aliased in the SELECT but didn't work either.
由于 ORDER BY，我需要在 SELECT 中保留 c.NAME、d.NAME 和 t.NAME 并且我不知道如何在不引发此错误的情况下执行此操作...我尝试在 SELECT 中使用别名但没有工作。
EDIT:
编辑：
Seems to be an Hibernate issue , more than an SQL one. Here is the function raising the error, on the "addEntity" line. In that case, it is expected to return a list of Concentrator's object.
似乎是一个 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;
}
Here are the Concentrator's object fields :
以下是集中器的对象字段：
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;
采纳答案by Jo?o Mendes
This looks like a Hibernate thing, rather than strictly a SQL problem.
这看起来像是 Hibernate 的事情，而不是严格意义上的 SQL 问题。
I think addEntityrequires all columns to have distinct names, that match the fields in the entity being added. Oracle, alas, has no problem returning results with duplicate problem names, which is why your query works in SQL Developer.
我认为addEntity要求所有列都具有不同的名称，以匹配要添加的实体中的字段。唉，Oracle 返回具有重复问题名称的结果没有问题，这就是您的查询在 SQL Developer 中工作的原因。
Try giving all your columns in the SELECT clause distinct aliases, specifically, aliases that match the field members in your entity.
尝试为 SELECT 子句中的所有列指定不同的别名，特别是与实体中的字段成员匹配的别名。
回答by ashok_p
I think this may work for you..
我认为这可能对你有用..
Use fully qualified names to all columns in the order byclause.
对子order by句中的所有列使用完全限定的名称。
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)
AND TRIM(UPPER(t.NAME)) = 'type'
ORDER BY c.IM DESC, c.ID DESC, c.LINK DESC, c.IW DESC, TRIM(UPPER(d.name)) ASC, TRIM(UPPER(c.name)) ASC;
回答by J?cob
Try using the following query, you have three namecolumns and I have renamed it has fname, snameand tname. Rename those the they you would like to, with unique names though.
尝试使用以下查询，您有三name列，我已将其重命名为fname,sname和tname。重命名他们想要的那些，但使用唯一的名称。
Also note that you have used some of reserved keywords, so try having alias names for those as well, I have renamed it though. It is always better to avoid using reserved key words while creating database objects in order to avoid errors.
另请注意，您使用了一些保留关键字，因此也尝试为这些关键字设置别名，不过我已将其重命名。最好在创建数据库对象时避免使用保留关键字以避免错误。
SELECT DISTINCT c.NAME FNAME,
c.SUPERVISIONNAME SUPERVISIONNAME,
c.INTERNALADDRESS INTERNALADDRESS,
c.IM IM,
c.ID T_ID,
c.LINK T_LINK,
c.IW T_IW,
d.NAME SNAME,
t.NAME TNAME
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,
T_ID DESC,
T_LINK DESC,
T_IW DESC,
TRIM (UPPER (SNAME)) ASC,
TRIM (UPPER (FNAME)) ASC;
回答by Chris
Hibernate is likely wrapping the SQL within another query to use Oracle's rownum in filtering the maxresults and firstrows. This will mean that any duplicates (c.Name and d.NAME) will cause exceptions unless aliased as described here: Column ambiguously defined in subquery using rownums.
Hibernate 很可能将 SQL 包装在另一个查询中，以使用 Oracle 的 rownum 过滤 maxresults 和 firstrows。这意味着任何重复项（c.Name 和 d.NAME）都将导致异常，除非按照此处所述使用别名：列在使用 rownums 的子查询中定义含糊不清。
Other JPA providers (EclipseLink for instance) will automatically alias fields when using pagination, so you might want to check if a later Hibernate version does the same or pull the patch described here https://hibernate.atlassian.net/browse/HHH-951Another duplicate bug that describes the issue is https://hibernate.atlassian.net/browse/HHH-1436
其他 JPA 提供程序（例如 EclipseLink）在使用分页时会自动为字段设置别名，因此您可能需要检查更高的 Hibernate 版本是否也这样做或提取此处描述的补丁https://hibernate.atlassian.net/browse/HHH- 951描述该问题的另一个重复错误是https://hibernate.atlassian.net/browse/HHH-1436
回答by Flash_Back
I finally found the solution thanks to you all ! I accepted Jo?o Mendes' answer because I actually solved the problem with correct alias, but all other answers were also relevant and helped me out.
感谢大家，我终于找到了解决方案！我接受了 Jo?o Mendes 的回答，因为我实际上用正确的别名解决了这个问题，但所有其他答案也都相关并帮助了我。
Here is the final query.
In the DISTINCTI put all the mapped object's (Concentrator) fields, plus the two used in the ORDER BYusing correct alias.
Not the prettiest I guess, but worked fine !
这是最后的查询。在DISTINCTI 中放置了所有映射对象的 (Concentrator) 字段，以及ORDER BY使用正确别名中使用的两个字段。我猜不是最漂亮的，但效果很好！
SELECT DISTINCT CONCENTRATOR_ID, c.NAME, SUPERVISIONNAME, INTERNALADDRESS, ACTIVEALARMS, IM, ID, LINK, IW, LASTUPDATE, TYPE_ID, DEPARTMENT_ID, d.NAME as "department.name", t.NAME as "type.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 (g.ident = 1) OR (g.ident = 16) OR (g.ident = 44)
AND (c.iw) > 0
AND TRIM(UPPER(t.name)) = 'OTELO'
ORDER BY im DESC, id DESC, link DESC, iw DESC, TRIM(UPPER(d.name)) ASC, TRIM(UPPER(c.name)) ASC
