无法执行语句SQL约束[id]嵌套的异常是org.hibernate.exception.ConstraintViolationException [英] could not execute statement SQL constraint [id] nested exception is org.hibernate.exception.ConstraintViolationException
本文介绍了无法执行语句SQL约束[id]嵌套的异常是org.hibernate.exception.ConstraintViolationException的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有两个简单的实体:用户和帐户.帐户可以与一个或多个用户相关.例如:一个银行帐户属于一个人或一对.
I have two simple entities: user and account. Account can be related to one or many users. For instance: a bank account belongs to a single person or belong to a couple.
尝试发布帐户时，出现主题中提到的错误.我想问题是我如何在邮递员中格式化json.我看不到实体中的错误.
I am getting the error mentioned in the topic when I tried to post an account. I guess the issue is how I am formatting the json in postman. I don't see errors in my entities.
用户:
@Entity
@Table(name = "bankuser")
public class User implements java.io.Serializable {
/**
*
*/
private static final long serialVersionUID = 6447416794596398975L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", unique = true, nullable = false)
private Long id;
@Column(name = "firstname", length = 50)
private String firstname;
@Column(name = "lastname", length = 50)
private String lastname;
public User() {
}
public User(Long id) {
this.id = id;
}
public User(Long id, String firstname, String lastname, String designation, Integer salary) {
this.id = id;
this.firstname = firstname;
this.lastname = lastname;
}
public User(String firstname, String lastname, String designation, Integer salary) {
this.firstname = firstname;
this.lastname = lastname;
}
public Long getId() {
return this.id;
}
public void setId(Long id) {
this.id = id;
}
public String getFirstname() {
return this.firstname;
}
public void setFirstname(String firstname) {
this.firstname = firstname;
}
public String getLastname() {
return this.lastname;
}
public void setLastname(String lastname) {
this.lastname = lastname;
}
@Override
public String toString() {
StringBuffer sb = new StringBuffer();
sb.append("Id: ").append(this.id).append(", firstName: ").append(this.firstname).append(", lastName: ")
.append(this.lastname);
return sb.toString();
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (id == null || obj == null || getClass() != obj.getClass())
return false;
User toCompare = (User) obj;
return id.equals(toCompare.id);
}
@Override
public int hashCode() {
return id == null ? 0 : id.hashCode();
}
}
帐户:
@Entity
@Table(name = "accounts")
public class Account implements java.io.Serializable {
/**
*
*/
private static final long serialVersionUID = 2612578813518671670L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", unique = true, nullable = false)
private Long id;
@Column(name = "name", length = 50)
private String name;
@NotNull
@ManyToOne
@JoinColumn(name = "user_id")
private User user;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
StringBuffer sb = new StringBuffer();
sb.append("Id: ").append(this.id).append(", Name: ").append(this.name);
return sb.toString();
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (id == null || obj == null || getClass() != obj.getClass())
return false;
Account toCompare = (Account) obj;
return id.equals(toCompare.id);
}
@Override
public int hashCode() {
return id == null ? 0 : id.hashCode();
}
}
添加第一个用户后，我期望邮递员发布的json是正确的:
After added the first user, I was expecting this json posted by postman be correct:
{
"name": "some account purpose",
"user": 1
}
我试图帮助缩小错误原因的其他尝试是:
Other tentatives I tried just to help me narrow the error cause were:
{
"name": "some account purpose",
"user": "http://localhost:8080/basicbank/user/1"
}
结果:JsonMappingException:无法构造com.livingit.basicbank.model.User的实例
Result: JsonMappingException: Can not construct instance of com.livingit.basicbank.model.User
和
{
"name": "some account purpose",
"user": {
"id": 1,
"firstname": "joao",
"lastname": "pereira"
}
}
结果:嵌套异常是org.hibernate.exception.ConstraintViolationException:无法执行语句
Result: nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
***已编辑(启动应用程序的整个堆栈跟踪)
*** edited (whole stacktrace of starting the app)
2018-01-28 09:27:04.010 INFO 6136 --- [ main] com.mycompany.basicbank.App : Starting App on win10-cha with PID 6136 (C:\demecarv\_exercicios\wssts\basicbank\target\classes started by dca in C:\demecarv\_exercicios\wssts\basicbank)
2018-01-28 09:27:04.013 INFO 6136 --- [ main] com.mycompany.basicbank.App : No active profile set, falling back to default profiles: default
2018-01-28 09:27:04.056 INFO 6136 --- [ main] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@45dd4eda: startup date [Sun Jan 28 09:27:04 CET 2018]; root of context hierarchy
2018-01-28 09:27:05.319 INFO 6136 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [class org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration$$EnhancerBySpringCGLIB$$36aa5503] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2018-01-28 09:27:05.687 INFO 6136 --- [ main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat initialized with port(s): 8080 (http)
2018-01-28 09:27:05.695 INFO 6136 --- [ main] o.apache.catalina.core.StandardService : Starting service Tomcat
2018-01-28 09:27:05.696 INFO 6136 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet Engine: Apache Tomcat/8.5.5
2018-01-28 09:27:05.792 INFO 6136 --- [ost-startStop-1] o.a.c.c.C.[.[localhost].[/basicbank] : Initializing Spring embedded WebApplicationContext
2018-01-28 09:27:05.792 INFO 6136 --- [ost-startStop-1] o.s.web.context.ContextLoader : Root WebApplicationContext: initialization completed in 1739 ms
2018-01-28 09:27:05.958 INFO 6136 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean : Mapping servlet: 'dispatcherServlet' to [/]
2018-01-28 09:27:05.965 INFO 6136 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: 'characterEncodingFilter' to: [/*]
2018-01-28 09:27:05.966 INFO 6136 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]
2018-01-28 09:27:05.966 INFO 6136 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: 'httpPutFormContentFilter' to: [/*]
2018-01-28 09:27:05.967 INFO 6136 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: 'requestContextFilter' to: [/*]
2018-01-28 09:27:06.186 INFO 6136 --- [ main] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
2018-01-28 09:27:06.196 INFO 6136 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [
name: default
...]
2018-01-28 09:27:06.250 INFO 6136 --- [ main] org.hibernate.Version : HHH000412: Hibernate Core {5.0.11.Final}
2018-01-28 09:27:06.252 INFO 6136 --- [ main] org.hibernate.cfg.Environment : HHH000206: hibernate.properties not found
2018-01-28 09:27:06.253 INFO 6136 --- [ main] org.hibernate.cfg.Environment : HHH000021: Bytecode provider name : javassist
2018-01-28 09:27:06.293 INFO 6136 --- [ main] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
2018-01-28 09:27:06.936 INFO 6136 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.PostgreSQLDialect
2018-01-28 09:27:07.059 INFO 6136 --- [ main] o.h.e.j.e.i.LobCreatorBuilderImpl : HHH000424: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
2018-01-28 09:27:07.061 INFO 6136 --- [ main] org.hibernate.type.BasicTypeRegistry : HHH000270: Type registration [java.util.UUID] overrides previous : org.hibernate.type.UUIDBinaryType@7d42542
2018-01-28 09:27:07.177 WARN 6136 --- [ main] org.hibernate.orm.deprecation : HHH90000014: Found use of deprecated [org.hibernate.id.SequenceGenerator] sequence-based id generator; use org.hibernate.id.enhanced.SequenceStyleGenerator instead. See Hibernate Domain Model Mapping Guide for details.
2018-01-28 09:27:07.400 INFO 6136 --- [ main] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000227: Running hbm2ddl schema export
Hibernate: alter table account_sources drop constraint FKeauonjew5oxigofghc32p2qpk
Hibernate: alter table account_sources drop constraint FKa48g9fvepp3kkbvswmrhoenbo
Hibernate: alter table account_targets drop constraint FKtlh5cw6fckuj0ijvujad745si
Hibernate: alter table account_targets drop constraint FKlrql4tsjy8unw6t22lm1o6xwt
Hibernate: alter table accounts drop constraint FK4xofdnttbbgk99exx2wpccl0s
Hibernate: drop table if exists account_sources cascade
Hibernate: drop table if exists account_targets cascade
Hibernate: drop table if exists accounts cascade
Hibernate: drop table if exists bankuser cascade
Hibernate: drop table if exists transactions cascade
Hibernate: drop sequence hibernate_sequence
Hibernate: create sequence hibernate_sequence start 1 increment 1
Hibernate: create table account_sources (sources_id int8, id int8 not null, primary key (id))
Hibernate: create table account_targets (targets_id int8, id int8 not null, primary key (id))
Hibernate: create table accounts (id bigserial not null, name varchar(50), user_id int8 not null, primary key (id))
Hibernate: create table bankuser (id bigserial not null, firstname varchar(50), lastname varchar(50), primary key (id))
Hibernate: create table transactions (id int8 not null, amount numeric(19, 2), primary key (id))
Hibernate: alter table account_sources add constraint FKeauonjew5oxigofghc32p2qpk foreign key (sources_id) references accounts
Hibernate: alter table account_sources add constraint FKa48g9fvepp3kkbvswmrhoenbo foreign key (id) references transactions
Hibernate: alter table account_targets add constraint FKtlh5cw6fckuj0ijvujad745si foreign key (targets_id) references accounts
Hibernate: alter table account_targets add constraint FKlrql4tsjy8unw6t22lm1o6xwt foreign key (id) references transactions
Hibernate: alter table accounts add constraint FK4xofdnttbbgk99exx2wpccl0s foreign key (user_id) references bankuser
2018-01-28 09:27:07.580 INFO 6136 --- [ main] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000230: Schema export complete
2018-01-28 09:27:07.610 INFO 6136 --- [ main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2018-01-28 09:27:08.152 INFO 6136 --- [ main] s.w.s.m.m.a.RequestMappingHandlerAdapter : Looking for @ControllerAdvice: org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@45dd4eda: startup date [Sun Jan 28 09:27:04 CET 2018]; root of context hierarchy
2018-01-28 09:27:08.216 INFO 6136 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/account],methods=[PUT]}" onto public org.springframework.http.ResponseEntity<java.lang.Void> com.mycompany.basicbank.controller.AccountController.updateEmployee(com.mycompany.basicbank.model.Account)
2018-01-28 09:27:08.217 INFO 6136 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/account],methods=[POST]}" onto public org.springframework.http.ResponseEntity<com.mycompany.basicbank.model.Account> com.mycompany.basicbank.controller.AccountController.addEmployee(com.mycompany.basicbank.model.Account)
2018-01-28 09:27:08.217 INFO 6136 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/account/{id}],methods=[GET]}" onto public org.springframework.http.ResponseEntity<com.mycompany.basicbank.model.Account> com.mycompany.basicbank.controller.AccountController.getUser(java.lang.Long)
2018-01-28 09:27:08.218 INFO 6136 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/account],methods=[GET]}" onto public org.springframework.http.ResponseEntity<java.util.List<com.mycompany.basicbank.model.Account>> com.mycompany.basicbank.controller.AccountController.getAllEmployees()
2018-01-28 09:27:08.218 INFO 6136 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/account/{id}],methods=[DELETE]}" onto public org.springframework.http.ResponseEntity<java.lang.Void> com.mycompany.basicbank.controller.AccountController.deleteEmployee(java.lang.Long)
2018-01-28 09:27:08.219 INFO 6136 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/user],methods=[PUT]}" onto public org.springframework.http.ResponseEntity<java.lang.Void> com.mycompany.basicbank.controller.UserController.updateEmployee(com.mycompany.basicbank.model.User)
2018-01-28 09:27:08.220 INFO 6136 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/user],methods=[POST]}" onto public org.springframework.http.ResponseEntity<com.mycompany.basicbank.model.User> com.mycompany.basicbank.controller.UserController.addEmployee(com.mycompany.basicbank.model.User)
2018-01-28 09:27:08.220 INFO 6136 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/user/{id}],methods=[GET]}" onto public org.springframework.http.ResponseEntity<com.mycompany.basicbank.model.User> com.mycompany.basicbank.controller.UserController.getUser(java.lang.Long)
2018-01-28 09:27:08.220 INFO 6136 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/user],methods=[GET]}" onto public org.springframework.http.ResponseEntity<java.util.List<com.mycompany.basicbank.model.User>> com.mycompany.basicbank.controller.UserController.getAllEmployees()
2018-01-28 09:27:08.220 INFO 6136 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/user/{id}],methods=[DELETE]}" onto public org.springframework.http.ResponseEntity<java.lang.Void> com.mycompany.basicbank.controller.UserController.deleteEmployee(java.lang.Long)
2018-01-28 09:27:08.222 INFO 6136 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error]}" onto public org.springframework.http.ResponseEntity<java.util.Map<java.lang.String, java.lang.Object>> org.springframework.boot.autoconfigure.web.BasicErrorController.error(javax.servlet.http.HttpServletRequest)
2018-01-28 09:27:08.223 INFO 6136 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error],produces=[text/html]}" onto public org.springframework.web.servlet.ModelAndView org.springframework.boot.autoconfigure.web.BasicErrorController.errorHtml(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)
2018-01-28 09:27:08.250 INFO 6136 --- [ main] o.s.w.s.handler.SimpleUrlHandlerMapping : Mapped URL path [/webjars/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2018-01-28 09:27:08.250 INFO 6136 --- [ main] o.s.w.s.handler.SimpleUrlHandlerMapping : Mapped URL path [/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2018-01-28 09:27:08.283 INFO 6136 --- [ main] o.s.w.s.handler.SimpleUrlHandlerMapping : Mapped URL path [/**/favicon.ico] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2018-01-28 09:27:08.520 INFO 6136 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Registering beans for JMX exposure on startup
2018-01-28 09:27:08.572 INFO 6136 --- [ main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
2018-01-28 09:27:08.577 INFO 6136 --- [ main] com.mycompany.basicbank.App : Started App in 4.872 seconds (JVM running for 5.477)
***已编辑，尝试添加帐户时整个stacktrace
*** edited, the whole stacktrace when trying to add an account
{
"name": "some account purpose",
"user": 1
}
2018-01-28 13:14:29.047 INFO 6136 --- [nio-8080-exec-1] o.a.c.c.C.[.[localhost].[/basicbank] : Initializing Spring FrameworkServlet 'dispatcherServlet'
2018-01-28 13:14:29.047 INFO 6136 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : FrameworkServlet 'dispatcherServlet': initialization started
2018-01-28 13:14:29.121 INFO 6136 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : FrameworkServlet 'dispatcherServlet': initialization completed in 74 ms
Hibernate: insert into accounts (name, user_id) values (?, ?)
2018-01-28 13:14:29.387 WARN 6136 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 23503
2018-01-28 13:14:29.387 ERROR 6136 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: insert or update on table "accounts" violates foreign key constraint "fk4xofdnttbbgk99exx2wpccl0s"
Detail: Key (user_id)=(1) is not present in table "bankuser".
2018-01-28 13:14:29.434 ERROR 6136 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[.[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [/basicbank] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [fk4xofdnttbbgk99exx2wpccl0s]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause
org.postgresql.util.PSQLException: ERROR: insert or update on table "accounts" violates foreign key constraint "fk4xofdnttbbgk99exx2wpccl0s"
Detail: Key (user_id)=(1) is not present in table "bankuser".
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458) ~[postgresql-9.4.1211.jre7.jar:9.4.1211.jre7]
at ...
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-8.5.5.jar:8.5.5]
at java.lang.Thread.run(Unknown Source) [na:1.8.0_112]
***在Madushan Perera建议之后，我仍然看不到进展:
*** After Madushan Perera suggestion I still don't see progress:
Account.java
Account.java
...
@NotNull
@ManyToOne(fetch = FetchType.LAZY, targetEntity = User.class)
@JoinColumn(name = "user_id", insertable = false, updatable = false)
private User user;
...
我试图通过PostMan发帖
And I tried to post via PostMan
{
"name": "some account purpose",
"user_id": 1
}
结果是:
"exception": "javax.validation.ConstraintViolationException",
"message": "Validation failed for classes [com.livingit.basicbank.model.Account] during persist time for groups [javax.validation.groups.Default, ]\nList of constraint violations:[\n\tConstraintViolationImpl{interpolatedMessage='may not be null', propertyPath=user, rootBeanClass=class com.livingit.basicbank.model.Account, messageTemplate='{javax.validation.constraints.NotNull.message}'}\n]",
***已编辑
***已编辑
为了缩小我编码的错误
...
@Autowired
private UserService userService;
@Override
public Account save(Account entity) {
entity.setUser(userService.getById(entity.getUser().getId()));
return accountRepository.save(entity);
...
，然后我可以检查该实体是否已完全填充.错误始终是抱怨user_id为空
and I could check that the entity is perfectly filled in. The error is always complaining about user_id is null
org.postgresql.util.PSQLException: ERROR: null value in column "user_id" violates not-null constraint
然后我假设关于如何创建关系ManyToOne会遇到一些问题.
Then I am assuming it is some problem regard how I create the relationship ManyToOne.
推荐答案
我通过使Account和User中用户的列ID匹配来解决了我的问题.解决方法如下:
I fixed my issue by making the column id from user matches in both Account and User. Here are the solution:
User.java
User.java
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "iduser", unique = true, nullable = false)
private Long iduser;
Account.java
Account.java
@ManyToOne(fetch = FetchType.LAZY, targetEntity = User.class)
@JoinColumn(name = "iduser", insertable = false, updatable = false)
private User user;
之前的重大更改是，在帐户I中有user_id，在用户I中有ID，现在两者都具有相同的别名.
The significant change was before, in Account I had user_id and in User I had id, now both have the same alias.
这篇关于无法执行语句SQL约束[id]嵌套的异常是org.hibernate.exception.ConstraintViolationException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
