Java NonUniqueDiscoveredSqlAliasException：在本地 sql 查询的自动发现期间遇到重复的 sql 别名 [ASC_ID]
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21348177/
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
NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [ASC_ID] during auto-discovery of a native-sql query
提问by Avinash
I have three tables CREDENTIAL_USER, CREDENTIAL_EXPIRATIONand ASC_DETAILS- whose looks like :
我有三个表CREDENTIAL_USER，CREDENTIAL_EXPIRATION并且ASC_DETAILS- 看起来像：
CREDENTIAL_USER { ASC_ID, name, ITEM}, CREDENTIAL_USER { MONTH, YEAR, ASC_ID}and ASC_DETAILS {ASCID, NAME, ROLE}.
CREDENTIAL_USER { ASC_ID, name, ITEM}，CREDENTIAL_USER { MONTH, YEAR, ASC_ID}和 ASC_DETAILS {ASCID, NAME, ROLE}.
I executed a simple native SQL join to select only CREDENTIAL_USER.ASC_ID and ASC_DETAAILS.ASCID as :
我执行了一个简单的本机 SQL 连接以仅选择 CREDENTIAL_USER.ASC_ID 和 ASC_DETAAILS.ASCID 作为：
Query query = entityManager.createNativeQuery("SELECT ad.ASCID,ad.NAME,ad.CRED_EMPLOYEE_FNAME,ad.CRED_EMPLOYEE_LNAME,ad.CRED_EMPLOYEE_EMAIL,ad.CRED_EMPLOYEE_REMINDER,"+
" cu.*, ce.*, cl.* FROM ASC_DETAILS ad,CREDENTIAL_USER cu, CREDENTIAL_EXPIRATION ce, CREDENTIAL_LICENSE cl WHERE"+
" cu.IS_CRD_EMPLOYEE = true AND cl.IS_EMPLOYEE = true AND ce.LICENSE_ID = cl.LICENSE_ID AND cu.ASC_ID=ad.ASCID"+
" AND ce.LICENSE_EXP_YR >= "+year+" AND (ce.IS_LICENSE_YES=1 OR ce.IS_LICENSE_YES=3) AND cu.ASC_ID=ce.ASC_ID AND"+
" cu.ID=ce.EMPLOYEE_ID");
List<Object[]> list = query.getResultList();
Its running fine on local, But getting org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [ASC_ID] during auto-discovery of a native-sql query
它在本地运行良好，但越来越 org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [ASC_ID] during auto-discovery of a native-sql query
exception on JBossserver
JBoss服务器异常
Stack-trace :
堆栈跟踪 ：
23:30:00,300 ERROR [stderr] (pool-10-thread-1) javax.persistence.PersistenceException: org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [ASC_ID] during auto-discovery of a native-sql query
23:30:00,301 ERROR [stderr] (pool-10-thread-1) at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1361)
23:30:00,302 ERROR [stderr] (pool-10-thread-1) at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1289)
23:30:00,303 ERROR [stderr] (pool-10-thread-1) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:261)
23:30:00,303 ERROR [stderr] (pool-10-thread-1) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
23:30:00,304 ERROR [stderr] (pool-10-thread-1) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
23:30:00,304 ERROR [stderr] (pool-10-thread-1) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
23:30:00,305 ERROR [stderr] (pool-10-thread-1) at java.lang.reflect.Method.invoke(Method.java:616)
23:30:00,305 ERROR [stderr] (pool-10-thread-1) at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:310)
23:30:00,306 ERROR [stderr] (pool-10-thread-1) at sun.proxy.$Proxy42.getResultList(Unknown Source)
23:30:00,307 ERROR [stderr] (pool-10-thread-1) at com.asc.dao.UserDAO.getPendingNotificationCredEmployee(UserDAO.java:991)
23:30:00,307 ERROR [stderr] (pool-10-thread-1) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
23:30:00,308 ERROR [stderr] (pool-10-thread-1) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
23:30:00,308 ERROR [stderr] (pool-10-thread-1) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
23:30:00,309 ERROR [stderr] (pool-10-thread-1) at java.lang.reflect.Method.invoke(Method.java:616)
23:30:00,309 ERROR [stderr] (pool-10-thread-1) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
23:30:00,310 ERROR [stderr] (pool-10-thread-1) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:196)
23:30:00,311 ERROR [stderr] (pool-10-thread-1) at sun.proxy.$Proxy34.getPendingNotificationCredEmployee(Unknown Source)
23:30:00,311 ERROR [stderr] (pool-10-thread-1) at com.asc.service.UserService.getPendingNotificationCredEmployee(UserService.java:2661)
23:30:00,312 ERROR [stderr] (pool-10-thread-1) at com.asc.service.UserService$$FastClassByCGLIB$$de8fbe27.invoke(<generated>)
23:30:00,312 ERROR [stderr] (pool-10-thread-1) at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
23:30:00,313 ERROR [stderr] (pool-10-thread-1) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:617)
23:30:00,314 ERROR [stderr] (pool-10-thread-1) at com.asc.service.UserService$$EnhancerByCGLIB$$d2e7fa8d.getPendingNotificationCredEmployee(<generated>)
23:30:00,314 ERROR [stderr] (pool-10-thread-1) at com.asc.service.ExpirationNotificationService.sendAlertToAdmin(ExpirationNotificationService.java:47)
23:30:00,315 ERROR [stderr] (pool-10-thread-1) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
23:30:00,316 ERROR [stderr] (pool-10-thread-1) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
23:30:00,316 ERROR [stderr] (pool-10-thread-1) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
23:30:00,317 ERROR [stderr] (pool-10-thread-1) at java.lang.reflect.Method.invoke(Method.java:616)
23:30:00,317 ERROR [stderr] (pool-10-thread-1) at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:273)
23:30:00,318 ERROR [stderr] (pool-10-thread-1) at org.springframework.scheduling.support.MethodInvokingRunnable.run(MethodInvokingRunnable.java:65)
23:30:00,318 ERROR [stderr] (pool-10-thread-1) at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:51)
23:30:00,319 ERROR [stderr] (pool-10-thread-1) at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
23:30:00,320 ERROR [stderr] (pool-10-thread-1) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
23:30:00,320 ERROR [stderr] (pool-10-thread-1) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
23:30:00,321 ERROR [stderr] (pool-10-thread-1) at java.util.concurrent.FutureTask.run(FutureTask.java:166)
23:30:00,321 ERROR [stderr] (pool-10-thread-1) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access1(ScheduledThreadPoolExecutor.java:165)
23:30:00,322 ERROR [stderr] (pool-10-thread-1) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:266)
23:30:00,323 ERROR [stderr] (pool-10-thread-1) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1146)
23:30:00,323 ERROR [stderr] (pool-10-thread-1) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
23:30:00,324 ERROR [stderr] (pool-10-thread-1) at java.lang.Thread.run(Thread.java:679)
23:30:00,325 ERROR [stderr] (pool-10-thread-1) Caused by: org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [ASC_ID] during auto-discovery of a native-sql query
23:30:00,326 ERROR [stderr] (pool-10-thread-1) at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:594)
采纳答案by Ashish Mohan
CREDENTIAL_USER { ASC_ID, name, ITEM}, CREDENTIAL_USER { MONTH, YEAR, ASC_ID} both contain ASC_ID so ASC_ID becomes ambiguous in select query. Select your data column wise and give separate alias for each column
CREDENTIAL_USER { ASC_ID, name, ITEM}, CREDENTIAL_USER { MONTH, YEAR, ASC_ID} 都包含 ASC_ID，因此 ASC_ID 在选择查询中变得不明确。明智地选择您的数据列并为每列提供单独的别名
回答by Khasan 24-7
When using coalesce for the first time, I also run into this problem. I have solved it by writing:
第一次使用coalesce时，我也遇到了这个问题。我已经通过写作解决了它：
...
coalesce(column_name, 'default_value')as key_word,
...
in my query. Adding just coalesce(column_name, 'default_value'), creates several values with name coalesceand this error occurrs. hope this helps for someone like me.
在我的查询中。仅添加coalesce(column_name, 'default_value')，会创建多个具有名称的值，coalesce并且会发生此错误。希望这对像我这样的人有所帮助。
