json 无法执行语句 SQL 约束 [id] 嵌套异常是 org.hibernate.exception.ConstraintViolationException
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48484120/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-03 19:05:50 来源:igfitidea点击:
could not execute statement SQL constraint [id] nested exception is org.hibernate.exception.ConstraintViolationException
提问by Jim C
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.
我有两个简单的实体：用户和帐户。帐户可以与一个或多个用户相关。例如：一个银行账户属于一个人或属于一对夫妇。
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.
当我尝试发布帐户时，我收到了主题中提到的错误。我想问题是我如何在邮递员中格式化 json。我在我的实体中没有看到错误。
user:
用户：
@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();
}
}
account:
帐户：
@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();
}
}
After added the first user, I was expecting this json posted by postman be correct:
添加第一个用户后，我期待邮递员发布的这个json是正确的：
{
"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"
}
Result: JsonMappingException: Can not construct instance of com.livingit.basicbank.model.User
结果：JsonMappingException：无法构造 com.livingit.basicbank.model.User 的实例
and
和
{
"name": "some account purpose",
"user": {
"id": 1,
"firstname": "joao",
"lastname": "pereira"
}
}
Result: nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
结果：嵌套异常为 org.hibernate.exception.ConstraintViolationException：无法执行语句
*** 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$aa5503] 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)
*** 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]
*** After Madushan Perera suggestion I still don't see progress:
*** 在 Madushan Perera 建议之后，我仍然没有看到进展：
Account.java
账号.java
...
@NotNull
@ManyToOne(fetch = FetchType.LAZY, targetEntity = User.class)
@JoinColumn(name = "user_id", insertable = false, updatable = false)
private User user;
...
And I tried to post via PostMan
我试着通过邮递员发帖
{
"name": "some account purpose",
"user_id": 1
}
And the result is:
结果是：
"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]",
*** edited
*** 已编辑
*** edited
*** 已编辑
In order to narrow the error I coded
为了缩小我编码的错误
...
@Autowired
private UserService userService;
@Override
public Account save(Account entity) {
entity.setUser(userService.getById(entity.getUser().getId()));
return accountRepository.save(entity);
...
and I could check that the entity is perfectly filled in. The error is always complaining about user_id is null
我可以检查实体是否完全填写。错误总是抱怨 user_id 为空
org.postgresql.util.PSQLException: ERROR: null value in column "user_id" violates not-null constraint
Then I am assuming it is some problem regard how I create the relationship ManyToOne.
然后我假设在我如何创建 ManyToOne 关系方面存在一些问题。
回答by Jim C
I fixed my issue by making the column id from user matches in both Account and User. Here are the solution:
我通过在 Account 和 User 中使用户匹配的列 id 解决了我的问题。以下是解决方案：
User.java
用户.java
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "iduser", unique = true, nullable = false)
private Long iduser;
Account.java
账号.java
@ManyToOne(fetch = FetchType.LAZY, targetEntity = User.class)
@JoinColumn(name = "iduser", insertable = false, updatable = false)
private User user;
The significant change was before, in Account I had user_id and in User I had id, now both have the same alias.
之前的重大变化是，在帐户中我有 user_id，在用户中我有 id，现在两者都具有相同的别名。
回答by Mehmet Onar
you can try
你可以试试
@Query(value="select c from table c where c.name = ?1",nativeQuery = true)
for your native queries
用于您的本机查询
回答by rafiquenazir
In my case , I had a password varchar(30) field in user table in postgres sql and I was saving encrypted password into the user table in the database. e.g. using user.setPassword(passwordEncoder.encode(user.getPassword())); where after password encryption ,password exceeded the limt of 30 characters. Please make sure that you have correct data types in your model classes and in sql table schema,and sql table columns have correct length for the corresponding fields. Hope this helps.
就我而言，我在 postgres sql 的用户表中有一个密码 varchar(30) 字段，我将加密的密码保存到数据库的用户表中。例如使用 user.setPassword(passwordEncoder.encode(user.getPassword())); 其中密码加密后，密码超过了30个字符的限制。请确保您的模型类和 sql 表架构中的数据类型正确，并且 sql 表列的相应字段长度正确。希望这可以帮助。
