NonUniqueDiscoveredSqlAliasException：在自动发现native-sql查询期间遇到重复的sql别名[ASC_ID] [英] NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [ASC_ID] during auto-discovery of a native-sql query

问题描述

我有三张表 CREDENTIAL_USER ， CREDENTIAL_EXPIRATION 和 ASC_DETAILS - 看起来像：

CREDENTIAL_USER {ASC_ID，name，ITEM} ， CREDENTIAL_USER {MONTH，YEAR，ASC_ID} 和 ASC_DETAILS {ASCID，NAME，ROLE}。

我执行了一个简单的本地SQL连接，以仅选择CREDENTIAL_USER.ASC_ID和ASC_DETAAILS.ASCID为：

 查询查询= 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> =+ yea r +AND（ce.IS_LICENSE_YES = 1或ce.IS_LICENSE_YES = 3）AND cu.ASC_ID = ce.ASC_ID AND+ 
cu.ID = ce.EMPLOYEE_ID）; 
 List< Object []> list = query.getResultList（）; 
  

它在本地运行良好，但获得 org.hibernate.loader.custom .NonUniqueDiscoveredSqlAliasException：在自动发现native-sql查询时遇到重复的sql别名[ASC_ID]

在 JBoss 服务器

堆栈跟踪：

<$错误[stderr]（pool-10-thread-1）javax.persistence.PersistenceException：org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException：遇到重复的sql别名[p $ p> 23：30：00,300错误[stderr] ASC_ID]在自动发现native-sql查询期间
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错误[stderr]（pool-10-thread-1）at org.hibernate.ejb.QueryImpl.getResultLi st（QueryImpl.java:261）
23：30：00,303错误[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错误[stderr]（pool-10-thread-1）在su n.reflect.NativeMethodAccessorImpl.invoke0（本地方法）
23：30：00,308错误[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在org.springframework上的java.lang.reflect.Method.invoke（Method.java:616）
23：30：00,309 ERROR [stderr]（pool-10-thread-1） .aop.support.AopUtils.invokeJoinpointUsingReflection（AopUtils.java:309）
23：30：00,310 ERROR [stderr]（pool-10-thread-1）at org.springframework.aop.framework.JdkDynamicAopProxy.invoke $ d $ b 23：30：00,311错误[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错误[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错误[stderr]（pool-10-thread-1）at com.asc.service.UserService $$ EnhancerByCGLIB $$ d2e7fa8d.getPendingNotificationCredEmployee（< generated>）
23：30：00,314错误[stderr]（pool-10-thread-1）at com.asc.service。 ExpirationNotificationService.sendAlertToAdmin（ExpirationNotificationService.java:47）
23：30：00,315在sun.reflect.NativeMethodAccessorImpl.invoke0（本机方法）时出错ERROR [stderr]（pool-10-thread-1）
23： 30：00,316错误[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错误[stderr]（pool-10-thread-1）at org.springframework.scheduling.support.MethodInvokingRunnable.run（MethodInvokingRunnable.java:65）
23：30：00,318错误[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] thread-1）在java.util.concurrent.Executors $ RunnableAdapter.call（Executors.java:471）
23：30：00,320错误[std err]（pool-10-thread-1）at java.util.concurrent.FutureTask $ Sync.innerRun（FutureTask.java:334）
23：30：00,321 ERROR [stderr]（pool-10-thread- 1）在java.util.concurrent.FutureTask.run（FutureTask.java:166）
23：30：00,321在java.util.concurrent.ScheduledThreadPoolExecutor上发生错误[stderr]（pool-10-thread-1）$ ScheduledFutureTask.access $ 101（ScheduledThreadPoolExecutor.java:165）
23：30：00,322 java.util.concurrent.ScheduledThreadPoolExecutor上的ERROR [stderr]（pool-10-thread-1）$ ScheduledFutureTask.run（ScheduledThreadPoolExecutor.java：
23：30：00,323错误[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] （线程1）在java.lang.Thread.run（Thread.java:679）
23：30：00,325错误[stderr]（pool-10-thread-1）引起：org.hibernate.loader。 C ustom.NonUniqueDiscoveredSqlAliasException：在自动发现本机sql查询期间遇到重复的sql别名[ASC_ID]
23：30：00,326错误[stderr]（pool-10-thread-1）at org.hibernate.loader .custom.CustomLoader.autoDiscoverTypes（CustomLoader.java:594）


解决方案

< CREDENTIAL_USER {ASC_ID，name，ITEM}，CREDENTIAL_USER {MONTH，YEAR，ASC_ID}
都包含ASC_ID，因此ASC_ID在选择查询中变得不明确。
明智地选择您的数据列并为每列赋予单独的别名

I have three tables CREDENTIAL_USER, CREDENTIAL_EXPIRATION and ASC_DETAILS - whose looks like :

CREDENTIAL_USER { ASC_ID, name, ITEM}, CREDENTIAL_USER { MONTH, YEAR, ASC_ID} and ASC_DETAILS {ASCID, NAME, ROLE}.

I executed a simple native SQL join to select only CREDENTIAL_USER.ASC_ID and ASC_DETAAILS.ASCID as :

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

exception on JBoss server

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.access$101(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)

解决方案

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

这篇关于NonUniqueDiscoveredSqlAliasException：在自动发现native-sql查询期间遇到重复的sql别名[ASC_ID]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！