在Oracle中给出唯一约束冲突的SELECT查询

4dbbbstv  于 2023-05-06  发布在  Oracle
关注(0)|答案(4)|浏览(171)

我在下面的Hibernate Select查询中获取了具有给定名称的结果：

Query selectQuery = entityManager.createQuery("select aw from TableName aw WHERE lower(name)='"+"NAME".toLowerCase()+"'",TableName.class);

List entityList = selectQuery.getResultList();

但是这个查询给了我：ORA-00001：唯一约束（Constraint_Name）违反了Exception。
javax.persistence.PersistenceException:org.hibernate.exception.ConstraintViolationException：无法执行语句
SELECT查询如何给予Unique约束异常？是因为表上的唯一索引吗？下面是完整的堆栈跟踪：

javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1419)
    at org.hibernate.Query.getResultList(Query.java:427)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.security.access.intercept.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:69)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)     at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:116)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)     at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:474)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:783)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:798)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1434)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748) 
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:207)
    at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3001)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3501)
    at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:586)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:460)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)
    at org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:50)
    at org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1389)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1474)   at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1441)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1410)
    ... 105 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (CRESTPRD.NAME) violated
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)     at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)     at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)   at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4875)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1361)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
    ... 117 more

有什么想法吗

oracle

4条答案

按热度按时间
chhqkbe1

chhqkbe11#

我很确定这个异常不是由于你的select语句。我不确定select是否会导致这个异常。相反，我认为您正在插入表中已经存在的行。在你的堆栈跟踪中有这样一段：

at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:89)

也许这是由Hibernate完成的？我对Hibernate一无所知，真的。文档表明EntityInsertAction接口是用来插入记录的，这一点也不奇怪。

赞(0）分享  回复(0）举报  2023-05-06
ql3eal8s

ql3eal8s2#

列CRESTPRD.NAME定义了唯一的约束，这意味着每个值在该列中只能出现一次。如果你试图插入一个新的条目，而该条目已经存在于该列中，你会得到一个ConstraintViolationException。
所以这似乎是您的数据模型中的一个问题。要么从该列中删除该唯一约束，以便能够在该列中多次使用某个值，要么将每个值只添加一次，以防止出现该错误。

赞(0）分享  回复(0）举报  2023-05-06
ddhy6vgd

ddhy6vgd3#

短版
约束冲突异常属于您代码中较早执行的insert语句！但是**Hibernate缓存了插入！**它没有将其转发到数据库-但它在选择之前执行缓存的插入！因此失败了-看起来选择有问题。

因此，之前的插入是根本原因，并且包含了损害该约束的值。

长版本：

我偶然发现了这篇关于Hibernate，JavaEE的类似问题的文章-我（还）不是Maven：

  • 我有一个向表中插入行的循环。
  • 循环中的第一次迭代是成功的。
  • 但是在第二个例子中，我得到了一个ConstraintViolation异常（在我的例子中，它是两列上的唯一索引）。

在调试的时候我发现，它发生在执行这样一个简单的JQPL“select count(*) from ... where ...”！在这个查询中没有触发器，什么都没有（在我的例子中，它甚至与循环中的insert语句所涉及的表相同）。
我确信Hibernate所做的是--我在名为application.properties的文件中启用了将SQL记录到控制台的spring.jpa.show-sql=true，以查看Hibernate创建了什么SQL。
在执行select查询时执行下一个调试步骤：
1.发生约束执行
1.日志只显示了一条insert语句！因为之前在控制台日志中缺少它！我用表内容检查了第一次循环迭代的参数，是的，它会破坏这个约束！万岁。我找到了。
您的Hibernate配置/设置是这样的（就像我的情况一样），您在代码中的select语句之前执行了insert语句。但是Hibernate缓存了插入的执行--它还没有对数据库执行任何插入！
最后，在执行select语句时，Hibernate不知何故看到需要在数据库中执行缓存的insert语句-这会因ConstraintException而失败，返回给您的是（令人兴奋的）错误。
就像下面这个伪代码，在执行select查询时调用：

private ResultSet executeQuery(String sql = "select ...") {
       if (areThereCachedHiberateQueries()) { // yes, an insert
           if (isItNowNecessaryToExecuteTheseCachedQueriesWithDatabase()) { // yes says Hibernate
               executeAllCachedQueries(); // here the cached insert produces the constraint violation error
           }
       }
       if isSelectStatement(sql) {
           return executeQuery(sql); // we do not even reach this code here!
       } else {
           cacheQuery(sql); // insert, delete, update
           return null;
       }
   }

一些成员指出，堆栈跟踪显示插入语句失败。但是他们假设，select语句以某种方式包含了一个trigger/...@user3262365否认了什么。
在这里，我展示了选择并不导致插入。insert命令已经被Hibernate缓存了--最后作为对数据库的选择的“准备”执行--但是insert失败了，因为违反了约束。

赞(0）分享  回复(0）举报  2023-05-06
xdnvmnnf

xdnvmnnf4#

感谢您的回复。我通过从表中删除Unique约束和索引解决了这个问题。我怀疑这是由于lower（）函数。在lower（）之后，这两行将具有相同的名称。删除Unique约束后，现在我在Application级别进行检查，以避免重复的名称。再次感谢。

赞(0）分享  回复(0）举报  2023-05-06
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com