I am having User
and Address
entity with one-to-many
Unidirectional relationship.
When I am trying to insert User
with Address
details, it is getting failed with exception "Referential integrity constraint violation" , as I checked Address
is not having inserted userId
it has 0
I am not getting what is wrong with this.
My User entity:
@Entity
@Table(name = "users")
public class User{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private long id;
@OneToMany(orphanRemoval = true,cascade = CascadeType.ALL, fetch=FetchType.EAGER)
@JoinColumn(name = "USER_ID", referencedColumnName = "ID")
private List<Address> address;
// other table columns
}
My Address entity:
@Entity
@Table(name = "address")
public class Address{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private long id;
@Column(name = "USER_ID")
private long userId;
//other table columns
}
my controller:
@PostMapping("/")
public ResponseEntity<UserDTO> saveUser(@RequestBody UserRequestDTO userRequestDTO){
try {
if (userRequestDTO != null) {
return new ResponseEntity<>(userService.saveUser(userRequestDTO), HttpStatus.CREATED);
}
} catch (Exception ex) {
return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
}
return null;
}
My Service layer:
@Override
public UserDTO saveUser(UserRequestDTO userDto) {
User obj = modelMapper.map(userDto, User.class);
System.out.println(obj.toString());
obj = userRepository.save(obj);
return modelMapper.map(obj, UserDTO.class);
}
Here is my UserRequestDTO in this I have all property
public class UserRequestDTO {
private long id;
private long clientId;
private String clientName;
private String email;
private String firstName;
private String lastName;
private String employeeNo;
private String designation;
private String status;
private String phoneNumber;
private String employeeType;
private String reportingTo;
private String department;
private String division;
private String password;
private String gender;
private String bloodGroup;
private String maritalStatus;
private String spouseName;
private String noOfChildren;
private Date dateOfBirth;
private Date hiredDate;
private LocalDate createdDate;
private String createdBy;
private LocalDate updatedDate;
private String updatedBy;
private List<Address> address;
}
Checked many online tutorials but not able to get the root cause Error message from console:
User(id=0, clientId=1, [email protected], firstName=user 10, lastName=K, password=password@123, employeeNo=EH5213, phoneNumber=9999999990, status=A, designation=Developer, employeeType=FullTime, reportingTo=Roshan, department=Information Technology, division=division, locationId=null, gender=null, bloodGroup=null, maritalStatus=null, spouseName=null, noOfChildren=null, dateOfBirth=null, hiredDate=null, createdDate=2022-03-25, createdBy=admin, updatedDate=2022-03-25, updatedBy=admin, organization=null, address=[Address(id=0, clientId=1, userId=0, address1=add1Sample, address2=add2Sample, state=sample, addressType=P, country=sample, city=sample, zipCode=000002, primaryPhone=1111111111, secondaryPhone=2222222222, active=true, createdDate=2022-03-25, createdBy=admin, updatedDate=2022-03-25, updatedBy=admin), Address(id=0, clientId=1, userId=0, address1=sample2, address2=add2sample2 , state=sample2, addressType=T, country=sample2, city=sample2, zipCode=000008, primaryPhone=4444444444, secondaryPhone=666666666, active=true, createdDate=2022-03-25, createdBy=admin, updatedDate=2022-03-25, updatedBy=admin)], employeeGroups=null)
Hibernate: insert into users (id, blood_group, client_id, created_by, created_date, date_of_birth, department, designation, division, email, employee_no, employee_type, first_name, gender, hired_date, last_name, location_id, marital_status, no_of_children, organization_id, password, phone_number, reporting_to, spouse_name, status, updated_by, updated_date) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into address (id, active, address_line_1, address_line_2, address_type, city, client_id, country, created_by, created_date, primary_phone, secondary_phone, state, updated_by, updated_date, user_id, zip_code) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[2m2022-03-30 12:41:25.689[0;39m [33m WARN[0;39m [35m13308[0;39m [2m---[0;39m [2m[nio-8085-exec-2][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper [0;39m [2m:[0;39m SQL Error: 23506, SQLState: 23506
[2m2022-03-30 12:41:25.702[0;39m [31mERROR[0;39m [35m13308[0;39m [2m---[0;39m [2m[nio-8085-exec-2][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper [0;39m [2m:[0;39m Referential integrity constraint violation: "FK6I66IJB8TWGCQTETL8EEEED6V: PUBLIC.ADDRESS FOREIGN KEY(USER_ID) REFERENCES PUBLIC.USERS(ID) (0)"; SQL statement:
insert into address (id, active, address_line_1, address_line_2, address_type, city, client_id, country, created_by, created_date, primary_phone, secondary_phone, state, updated_by, updated_date, user_id, zip_code) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23506-200]
CodePudding user response:
This can be fixed by some small changes at Entity level:
Change-1:
Add nullable=false at your User entity, something like this:
@Entity
@Table(name = "users")`
public class User{`
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private long id;
@OneToMany(orphanRemoval = true,cascade = CascadeType.ALL, fetch=FetchType.EAGER)
@JoinColumn(name = "USER_ID", referencedColumnName = "ID", nullable = false)
private List<Address> address;
// other table columns
}
Change-2: Add insertable = false and updatable = false at your Address entity, something like this
@Entity
@Table(name = "address")
public class Address{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private long id;
@Column(name = "USER_ID", insertable = false, updatable = false)
private long userId;
//other table columns
}
Use of nullable=false : It applies the not null constraint to the particular database column
To to know uses of insertable = false, updatable = false please refer this :Please explain about insertable=false and updatable=false in reference to the JPA @Column annotation
CodePudding user response:
You have to insert User
first then after insert Address
means you have to get reference of User
before inserting Address
. But in Unidirectional Mapping
you don't need to put code for insert Address
because it automatically get reference from User
. You don't need DTO
because this makes your code more complex.
Remove this column from Address.java
because it is bad pratice to insert foreign key manually:
@Column(name = "USER_ID", insertable = false, updatable = false)
private long userId;
Here down is modified code:
Controller
@RequestMapping(value = "/")
@ResponseBody
public ResponseEntity<User> addUser(@RequestBody User user)
{
userRepository.save(user);
return new ResponseEntity<>(user, HttpStatus.CREATED);
}
You don't want to make DTO using this technique