org.springframework.dao.DuplicateKeyException ERROR Mysql库

最新推荐文章于 2024-07-17 23:05:36 发布

haoshuai2015

最新推荐文章于 2024-07-17 23:05:36 发布

阅读量9k

点赞数 1

分类专栏：项目实践文章标签： exception mybatis mysql 建表 DuplicateKeyException

本文链接：https://blog.csdn.net/haoshuai2015/article/details/80323425

版权

项目实践专栏收录该内容

9 篇文章 0 订阅

订阅专栏

进行数据写入的时候，当线上调用接口，同时写入大量数据（会有重复数据写入），报以下错误：

org.springframework.dao.DuplicateKeyException: 
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0---2018-05-15 14:46:52.988' for key 'unq_tap' 
### The error may involve com.*****.PCBillAuthLogMapper.insertSelective-Inline 
### The error occurred while setting parameters 
### SQL: insert into PCBillAuthLog ( userId, phone, appKey, eventTime, statusCode, token, type ) values ( ?, ?, ?, ?, ?, ?, ? ) 
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0---2018-05-15 14:46:52.988' for key 'unq_tap' 
; SQL []; Duplicate entry '0---2018-05-15 14:46:52.988' for key 'unq_tap'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0---2018-05-15 14:46:52.988' for key 'unq_tap' 
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239) 
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) 
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75) 
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447) 
at com.sun.proxy.$Proxy56.insert(Unknown Source) 
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:279) 
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:56) 
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53) 
at com.sun.proxy.$Proxy68.insertSelective(Unknown Source) 
**********（私密信息，不影响）
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) 
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720) 
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) 
at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52) 
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) 
at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:52) 
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) 
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) 
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) 
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655) 
at com.***$$EnhancerBySpringCGLIB$$b65a430.BillAuthentication(<generated>) 
at sun.reflect.GeneratedMethodAccessor360.invoke(Unknown Source) 
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:221) 
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) 
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114) 
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:650) 
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) 
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) 
at ***doFilter(CatServletFilter.java:43) 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) 
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:241) 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) 
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220) 
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) 
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170) 
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) 
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) 
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423) 
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079) 
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:620) 
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316) 
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) 
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) 
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) 
at java.lang.Thread.run(Thread.java:745) 
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0---2018-05-15 14:46:52.988' for key 'unq_tap' 
at sun.reflect.GeneratedConstructorAccessor94.newInstance(Unknown Source) 
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) 
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) 
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) 
at com.mysql.jdbc.Util.getInstance(Util.java:387) 
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:932) 
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) 
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) 
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) 
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) 
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) 
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) 
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192) 
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) 
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) 
at sun.reflect.GeneratedMethodAccessor143.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
at java.lang.reflect.Method.invoke(Method.java:498) 
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) 
at com.sun.proxy.$Proxy109.execute(Unknown Source) 
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46) 
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) 
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) 
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) 
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) 
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) 
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185) 
at sun.reflect.GeneratedMethodAccessor199.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
at java.lang.reflect.Method.invoke(Method.java:498) 
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)

分析发现，主要错误是文中红色字体部分，是mysql表建表时建了唯一主键约束，建表语句中有

UNIQUE KEY `unq_tap` (`userId`,`phone`,`inserttime`),,，这是唯一主键约束。也就是说，当userId，phone和inserttime相同时，不能重复插入。

考虑到项目实际需求中，这样的数据重复多次插入其实对业务没有影响，因此可以去除掉唯一主键约束，修改为普通索引即可。

违反唯一约束索引报错信息：org.springframework.dao.DuplicateKeyException: