java - how to not have null in foreign key oneToOne Spring Boot?

Question

I have to classes with one to one relationship. I'm creating database using flywaydb. I want to be able to get wallet from db by userId.

User class

    @Data
    @Entity
    @Table(name = "users")
    public class User {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "id", unique = true, nullable = false)
        private Integer id;
    
        @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
        @JoinColumn(name = "wallet_id", referencedColumnName = "id")
        private UserWallet userWallet;

...
}

Wallet class

@Entity
@Data
@Table(name = "users_wallet")
public class UserWallet {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    private Integer id;

    @OneToOne(mappedBy = "userWallet")
    @JoinColumn(name = "user_id", referencedColumnName = "id")
    private User userId;
...
}

CREATE TABLE users_wallet
(
    id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id  INT UNSIGNED,
    currency VARCHAR(3),
    value    DOUBLE,

    PRIMARY KEY (id)
);
CREATE TABLE users
(
    id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    wallet_id        INT UNSIGNED,

    PRIMARY KEY (id)
    FOREIGN KEY (wallet_id) REFERENCES users_wallet (id)
);

Methods to save to the database

@Override
    public User addUser(UserUpdateRequest userUpdateRequest) {
        User newUser = new User();
        UserWallet newWallet = createWallet();
        this.saveUser(newUser, userUpdateRequest, newWallet);
        return newUser;
    }
private UserWallet createWallet() {
        UserWallet userWallet = new UserWallet();
        userWallet.setValue(0.0);
        userWallet.setCurrency("PLN");
        return userWallet;
    }
private void saveUser(User user, UserUpdateRequest updateRequest, UserWallet userWallet) {
        user.setUserWallet(userWallet);
        setUserDetails(user, updateRequest);
        userRepository.save(user);
    }

After save User to database I got:

User(id=3, userWallet=UserWallet(id=3, userId=null, currency=PLN, value=0.0),

I want a value in variable userId. How it should be done to be able to get wallet by userId?

question from:https://stackoverflow.com/questions/65892943/how-to-not-have-null-in-foreign-key-onetoone-spring-boot

Answer 1

Your DB schema is a bit unusual. With your schema design, you'll always need two queries to add wallet to user:

-- let's assume user's id is 1
insert into users_wallet(id, user_id, currency, value) values (1, 1, 'EUR', 2.0);
update user set wallet_id = 1 where id = 1;

Common approach is to have only one foreign key, let's say in child entity. For example, a user can have a wallet. In that case, you can add the foreign key user_id in users_wallet table. That way when you want to add wallet to a user, you just add wallet to a user:

-- again let's assume user's id is 1
insert into users_wallet(user_id, currency, value) values (1, 'EUR', 2.0); 

So, your DB schema could then look like:

CREATE TABLE users_wallet
(
    id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id  INT UNSIGNED,
    currency VARCHAR(3),
    value    DOUBLE,

    PRIMARY KEY (id)
);
CREATE TABLE users
(
    id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
    FOREIGN KEY (wallet_id) REFERENCES users_wallet (id)
);

With such schema, you would have following mappings:

class User {
    // ...

    @OneToOne(mappedBy = "user", cascade = CascadeType.ALL,
              fetch = FetchType.LAZY, optional = false)
    private UserWallet userWallet;
}

class UserWallet {
    // ...

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id")
    private User user;
}

But you can improve your DB design even further. Since there can only be one wallet per user, you don't really need both id as primary key and user_id as foreign key. You could just use user_id as primary key, which would mirror primary key of user table. In that case mapping for UserWallet class is following:

class UserWallet {
    // ...

    @OneToOne(fetch = FetchType.LAZY)
    @MapsId
    private User user;
}