Java 如何解决“无法执行语句；SQL [n/a]；约束 [编号]；”？

How to resolve "could not execute statement; SQL [n/a]; constraint [numbering];"?

提问by Pankaj Sharma

How to resolve "could not execute statement; SQL [n/a]; constraint [numbering];"?

如何解决“无法执行语句；SQL [n/a]；约束 [编号]；”？

org.springframework.dao.DataIntegrityViolationException:could not execute statement; SQL [n/a]; constraint [numbering]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:643) org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:106) org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517) org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755) org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724) org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:475) org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:270) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) com.sun.proxy.$Proxy132.saveListForm(null:-1) sun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethodAccessorImpl.java:-2) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:606) org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:333) org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:308) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96) org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:92) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) com.sun.proxy.$Proxy133.saveListForm(null:-1) sun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethodAccessorImpl.java:-2) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:606) org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) com.sun.proxy.$Proxy134.saveListForm(null:-1) com.qmplus.service.impl.ServiceListFormVersionImpl.save(ServiceListFormVersionImpl.java:93) sun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethodAccessorImpl.java:-2) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:606) org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) org.springframework.security.access.intercept.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:64) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) com.sun.proxy.$Proxy135.save(null:-1) com.qmplus.web.controller.ListFormController.save(ListFormController.java:158) com.qmplus.web.controller.ListFormController$$FastClassByCGLIB$$febe95eb.invoke(:-1) org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:627) com.qmplus.web.controller.ListFormController$$EnhancerByCGLIB$$5bb46747.save(:-1) sun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethodAccessorImpl.java:-2) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:606) org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219) org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686) org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80) org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925) org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936) org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838) javax.servlet.http.HttpServlet.service(HttpServlet.java:647) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812) javax.servlet.http.HttpServlet.service(HttpServlet.java:728) org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) com.github.dandelion.datatables.core.web.filter.DatatablesFilter.doFilter(DatatablesFilter.java:73) org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:164) org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) com.qmplus.web.interceptors.TenantFilter.doFilter(TenantFilter.java:60) org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118) org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) com.qmplus.web.util.AjaxTimeoutRedirectFilter.doFilter(AjaxTimeoutRedirectFilter.java:47) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:146) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:125) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87) org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192) org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160) org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) com.github.dandelion.datatables.extras.servlet2.filter.DatatablesFilter.doFilter(DatatablesFilter.java:71) org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:202) net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:180) org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953) org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023) org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312) java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) java.lang.Thread.run(Thread.java:724)

org.springframework.dao.DataIntegrityViolationException：无法执行语句；SQL [不适用]；约束[编号]；嵌套异常是 org.hibernate.exception.ConstraintViolationException：无法执行语句 org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:643) org.springframework.orm.jpa.vendor.HibernateJpaDialect.transibleExceptionHibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:643) :106) org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517) org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755) org.springframework.transaction.commitformManagerTransactionManagerAbs (AbstractPlatformTransactionManager.java:724) org.springframework.transaction。

回答by Toilal

Hibernate tries to insert data that violate underlying database integrity contraints.

Hibernate 尝试插入违反底层数据库完整性约束的数据。

There's probably misconfiguration in hibernate persistent classes and/or mapping configuration (*.hbm.xml or annotations in persitent classes).

休眠持久类和/或映射配置（*.hbm.xml 或持久类中的注释）中可能存在配置错误。

Maybe a property of the bean you want to save is not type-compatible with its related field in database (could explain the constraint [numbering]part).

也许您要保存的 bean 的属性与其数据库中的相关字段类型不兼容（可以解释该constraint [numbering]部分）。

回答by Mariuszy

In my case, this happens when I try to save an object in hibernate or other orm-mapping with null property which can not be null in database table. This happens when you try to save an object, but the save action doesn't comply with the contraints of the table.

在我的情况下，当我尝试将对象保存在休眠或其他带有 null 属性的 orm 映射中时，会发生这种情况，该属性在数据库表中不能为 null。当您尝试保存对象但保存操作不符合表的约束时会发生这种情况。

回答by Shailesh

In my case, I was fetching data from database, changing some column values and updating it in database but for updating I was using the same save query which was violating primary key constraints i.e duplicate values for primary key, so I had written a separate query for updating the columns and it solved my problem..!

就我而言，我正在从数据库中获取数据，更改一些列值并在数据库中更新它，但是为了更新，我使用了违反主键约束的相同保存查询，即主键的重复值，所以我编写了一个单独的查询用于更新列，它解决了我的问题..！

回答by Saeed

In my case, I had a OneToOne relation which I was using with @Columnby mistake. I changed it to @JoinColumnand added @OneToOneannotation and it fixed the exception.

就我而言，我@Column错误地使用了 OneToOne 关系。我将其更改为@JoinColumn并添加了@OneToOne注释并修复了异常。

回答by Georgi Peev

Overall:You persist entity that is violating database rules like as saving entity that has varchar field over 250 chars ,something like that.

总体：您坚持违反数据库规则的实体，例如保存具有超过 250 个字符的 varchar 字段的实体，诸如此类。

In my case it was a squishy problem with TestEntityManager ,because it use HSQL database /in memory database/ and it persist the user but when you try to find it ,it drops the same exception :/

在我的情况下，这是 TestEntityManager 的一个棘手问题，因为它使用 HSQL 数据库/内存数据库/并且它持久化用户，但是当您尝试找到它时，它会丢弃相同的异常：/

@RunWith(SpringRunner.class)
@ContextConfiguration(classes= Application.class)
@DataJpaTest
@ActiveProfiles("test")
public class UserServicesTests {

@Autowired
private TestEntityManager testEntityManager;

@Autowired
private UserRepository userRepository;

@Test
public void oops() {
    User user = new User();
    user.setUsername("Toshko");
    //EMAIL IS REQUIRED:
    //user.setEmail("OPS");
    this.testEntityManager.persist(user);

    //HERE COMES TE EXCEPTION BECAUSE THE EMAIL FIELD IN TE DATABASE IS REQUIRED :
    this.userRepository.findUserByUsername("Toshko");

    System.out.println();
}
}

回答by granadaCoder

Future readers.

未来的读者。

I have had the best luck figuring out these issues....using this method:

我很幸运地解决了这些问题......使用这种方法：

private static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(MyDaoObject.class);

@Transactional
public void save(MyObject item) {


    try {
         /* this is whatever code you have already...this is just an example */
        entityManager.persist(item);
        entityManager.flush();
    }
    catch(Exception ex)
    {
        /* below works in conjunction with concrete logging framework */
        logger.error(ex.getMessage(), ex);
        throw ex;
    }
}

Now, slf4j is just a fascade (interfaces, adapter)..... you need a concrete.

现在，slf4j 只是一个外观（接口、适配器）......你需要一个具体的。

When picking log4j2, you want to persist the

选择 log4j2 时，您希望持久化

%throwable

%throwable

So you are not flying blind, you can see %throwable at the below URL (on how you defined %throwable so it shows up in the logging. If you're not using log4j2 as the concrete, you'll have to figure out your logging framework's version of %throwable)

所以你不是盲目的，你可以在下面的 URL 上看到 %throwable（关于你如何定义 %throwable 以便它显示在日志中。如果你没有使用 log4j2 作为具体的，你必须弄清楚你的日志框架的 %throwable 版本）

https://www.baeldung.com/log4j2-appenders-layouts-filters

https://www.baeldung.com/log4j2-appenders-layouts-filters

That %throwable, when logged, will have the actualSQL exception in it.

该 %throwable 在记录时将包含实际的SQL 异常。

if throwable is giving you a fuss, you can do this (the below is NOT great since it does recursive log calls)

如果 throwable 让你大惊小怪，你可以这样做（下面不是很好，因为它执行递归日志调用）

@Transactional
public void save(MyObject item) {


    try {
         /* this is whatever code you have already...this is just an example */
        entityManager.persist(item);
        entityManager.flush();
    catch(Exception ex)
    {
        logger.error(ex.getMessage(), ex);
        //throw ex;
        Throwable thr = ex;
        /* recursive logging warning !!! could perform very poorly, not for production....alternate idea is to use Stringbuilder and log the stringbuilder result */
        while (null != thr) {
            logger.error(thr.getMessage(), thr);
            thr = thr.getCause();
        }
    }
}

回答by Tapan

I have got the same error but I have resolved the issue in the following ways:

我遇到了同样的错误，但我已通过以下方式解决了该问题：

1. Provide all the mandatory filed of your bean/model class
2. Don't violet the concept of the unique constraint, Provide unique value in Unique constraints.

1. 提供你的 bean/model 类的所有强制文件
2. 不要紫化唯一约束的概念，在唯一约束中提供唯一值。

回答by JAD

The solution at my end was to explicitly add a JoinColumn annotation like this:

我最后的解决方案是显式添加一个 JoinColumn 注释，如下所示：

@JoinColumn(name="mapping_type_id")

The column name is usually the table name + "_id" if there is an id field. Additionally, keep in mind which field it should be based on the relationship, OneToMany or ManyToOne.

如果有 id 字段，列名通常是表名 + "_id"。此外，请记住它应该基于关系的哪个字段，OneToMany 或 ManyToOne。

Hope this helps.

希望这可以帮助。