Home > Back-end >  One To Many Unidirectional association insert query getting failed
One To Many Unidirectional association insert query getting failed

Time:03-31

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

  • Related