I have two tables, these are profile_info and user_Info. Database ER-diagram
Table creation:
CREATE TABLE `user_info` (
`id` int NOT NULL AUTO_INCREMENT,
`userLogin` varchar(20) DEFAULT NULL,
`userPassword` varchar(60) DEFAULT NULL,
`userType` enum('user','administrator') DEFAULT NULL,
`userEmail` varchar(320) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `userEmail` (`userEmail`)
);
CREATE TABLE `profile_info` (
`id` int NOT NULL AUTO_INCREMENT,
`userId` int DEFAULT NULL,
`userRegistrationDate` datetime DEFAULT NULL,
`userFirstName` varchar(25) DEFAULT NULL,
`userSurName` varchar(25) DEFAULT NULL,
`accountBalance` double DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `userUniqueId` (`userId`),
CONSTRAINT `fkUserId` FOREIGN KEY (`userId`) REFERENCES `user_info` (`id`) ON DELETE CASCADE,
CONSTRAINT `chk_accountBalance` CHECK ((`accountBalance` >= 0))
);
So I have one to one relationship(one profile is related only to one user)
Here are my entities :
User :
@Table("user_info")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User implements Persistable<Integer> {
@Id
@Column("id")
private Integer id;
@Column("userLogin")
private String userLogin;
@Column("userPassword")
private String userPassword;
@Column("userType")
private String userType;
@Column("userEmail")
private String userEmail;
@Override
public boolean isNew() {
return id==null;
}
}
Profile :
@Table("profile_info")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Profile implements Persistable<Integer> {
@Id
@Column("id")
private Integer id;
@Column("userRegistrationDate")
private LocalDateTime userRegistrationDate;
@Column("userFirstName")
private String userFirstName;
@Column("userSurName")
private String userSurName;
@Column("accountBalance")
private double accountBalance;
@MappedCollection(idColumn = "id")
private User user;
@Override
public boolean isNew() {
return id==null;
}
}
I use standard repositories as CRUDRepository UserRepository:
@Repository
public interface UserRepository extends CrudRepository<User,Long> {
}
ProfileRepository:
@Repository
public interface ProfileRepository extends CrudRepository<Profile,Integer> {
}
In user table I have only two records :
In profile table I have only one which I'm gonna update
And I'm trying to insert User entity first, and after that update Profile entity
public static void main(String[] args) {
ApplicationContext context = new AnnotationConfigApplicationContext(ApplicationConfig.class);
ProfileRepository repository = context.getBean("profileRepository",ProfileRepository.class);
UserRepository userRepository =context.getBean("userRepository",UserRepository.class);
User user = new User(null,"Login","newUserP","user","TestEmail");
userRepository.save(user);
user.setUserLogin("new bqqvnbvnvbn");
System.out.println(user.getId());
Profile profile = new Profile(34, LocalDateTime.now(),"fff","sss",250.0,user);
repository.save(profile);
}
User id is :
User was succesfully created
But When I'm trying to update Profile I'm getting error duplicate entry
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '77' for key 'user_info.PRIMARY'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1350)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
at org.springframework.jdbc.core.JdbcTemplate.lambda$update$2(JdbcTemplate.java:965)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
... 38 more
But in Spring documentation is said "If the aggregate root is not new, all referenced entities get deleted, the aggregate root gets updated, and all referenced entities get inserted again. Note that whether an instance is new is part of the instance’s state."
So it should delete user from database first and after that update profile and insert deleted user
Also, when I did this I got this exception
Exception in thread "main" org.springframework.data.relational.core.conversion.DbActionExecutionException: Failed to execute DbAction.Insert(entity=User(id=77, userLogin=new bqqvnbvnvbn, userPassword=newUserP, userType=user, userEmail=TestEmail), propertyPath=user, dependingOn=DbAction.UpdateRoot(entity=Profile(id=34, userRegistrationDate=2021-08-15T20:38:16.862, userFirstName=fff, userSurName=sss, accountBalance=250.0, user=User(id=77, userLogin=new bqqvnbvnvbn, userPassword=newUserP, userType=user, userEmail=TestEmail))), qualifiers={})
