How to make related entity's id(auto-incremented) to be inserted when using one to one relationship in spring-data-jdbc?

Question

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> {
}

And I have that spring config:

@Configuration
@EnableJdbcRepositories("com.example.testrepositories.repository")
public class ApplicationConfig extends AbstractJdbcConfiguration {
    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource managerDataSource = new DriverManagerDataSource();
        managerDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        managerDataSource.setUrl("jdbc:mysql://localhost:3306/testdatabase");
        managerDataSource.setUsername("Bruce");
        managerDataSource.setPassword("givanchy");
        return managerDataSource;
    }
    @Bean
    public NamedParameterJdbcTemplate namedParameterJdbcTemplate(DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }
}

Main method where I test my repositories:

 public static void main(String[] args) {
        ApplicationContext context = new AnnotationConfigApplicationContext(ApplicationConfig.class);
        ProfileRepository repository = context.getBean("profileRepository",ProfileRepository.class);
        User user = new User(null,"TestLogin","testPassword","user","eeed");
        Profile profile = new Profile(null, LocalDateTime.now(),"fff","sss",250.0,user);
        Profile savedProfile = repository.save(profile);
        System.out.println(savedProfile.getId());
        System.out.println(savedProfile.getUser().getId());
    }

output is :

In database after these actions I have following:

User info was inserted as it should

But in profile userId wasn't inserted, should it work like that?

EDIT

I changed root entity from Profile to User and it has worked well

User Entity:

@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;
    @MappedCollection(keyColumn = "id",idColumn = "userId")
    private Profile profile;
    @Override
    public boolean isNew() {
        return id==null;
    }
}

Profile Enity:

@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;
    @Override
    public boolean isNew() {
        return id==null;
    }
}

have you tried using JPA annotations instead of `@MappedCollection`? — Govinda Sakhare, Aug 15 '21 at 11:33

score 0 · Accepted Answer · answered Aug 16 '21 at 14:56

It seems that the use of a one-to-one relationship is wrong. According to your ER diagram there can be multiple Profile rows for each User. If that is the case and Profile is an aggregate (root), user_info is an aggregate root as well. The same is suggested by having a repository for both entities.

We therefore deal with references between aggregates which should be represented by the id only, not object references. That means you should make the Profile.user and Integer or AggregateReference<Integer> and set it to the value of User.id after the User has been saved.

Some other notes: MappedCollection is not relevant for simple references. When A reference B the foreign key in the database goes in the opposite direction. So if Profile and User actually do have a one to one relationship, User should be the aggregate root.

For more background on all this see https://spring.io/blog/2018/09/24/spring-data-jdbc-references-and-aggregates

I set User as aggregate root as you said and It worked – DozezQuest Aug 16 '21 at 15:10 — DozezQuest, Aug 16 '21 at 15:10
does JdbcRepositoryFactory support Query by example? – DozezQuest Aug 16 '21 at 20:33 — DozezQuest, Aug 16 '21 at 20:33

How to make related entity's id(auto-incremented) to be inserted when using one to one relationship in spring-data-jdbc?

1 Answers1