如何在SQL中存储值列表？账户模型关联余额列表的实现困惑

Hey there! Let's sort out your Account and Balance model setup—you're close, but there are a few tweaks needed in both your JPA annotations and SQL schema to get the association working properly.

1. Correct JPA Entity Annotations #

First, let's fix the annotation conflicts and misconfigurations in your entities. Your current setup has overlapping annotations on the Balance class, and the @OneToMany mapping in Account isn't set up to use the foreign key correctly.

Updated Account Class #

@Data
@Builder
@Table(name = "account")
@AllArgsConstructor
@NoArgsConstructor
public class Account {
    @Id
    @Column(name = "accountid")
    private Long accountId;
    
    @Column(name = "customer_id")
    private Long customerId;
    
    // Let Balance maintain the association to avoid extra join tables
    @OneToMany(mappedBy = "account", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<Balance> balances;
}

Updated Balance Class #

@Data
@Builder
@Table(name = "balance")
@AllArgsConstructor
@NoArgsConstructor
public class Balance {
    @Id
    @Column(name = "balanceid")
    private Long balanceId;
    
    // @ManyToOne is the right annotation for this side of the relationship
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "account_id", nullable = false) // Maps to account's primary key
    private Account account;
    
    @Column(name = "currency")
    private String currency;
    
    @Column(name = "amount")
    private Long amount;
}

Key Fixes Explained:

• @OneToMany(mappedBy = "account"): This tells JPA that the Balance entity's account field manages the association. It skips creating an unnecessary join table and uses the account_id column in the balance table as the foreign key.
• @ManyToOne in Balance: This is the "owning side" of the relationship (since it holds the foreign key). The @JoinColumn explicitly links to the account_id column in the balance table, which references the accountId primary key in the account table.
• Removed the conflicting @Column annotation from Balance's account field—@JoinColumn already handles the column mapping for foreign keys.

2. Proper SQL Schema with Foreign Key Constraint #

Your existing tables are nearly correct, but we should add explicit foreign key constraints to enforce data integrity at the database level, plus adjust data types to align better with Java:

CREATE TABLE account (
    accountId BIGINT PRIMARY KEY, -- Matches Java's Long (INT is too small for large IDs)
    customerId BIGINT NOT NULL
);

CREATE TABLE balance (
    balanceId BIGINT PRIMARY KEY,
    accountId BIGINT NOT NULL,
    currency VARCHAR(3) NOT NULL, -- Currency codes are 3 characters (e.g., USD, CNY)
    amount BIGINT NOT NULL,
    -- Add foreign key to link balances to their account
    FOREIGN KEY (accountId) REFERENCES account(accountId)
        ON DELETE CASCADE -- Optional: Auto-delete balances when their account is removed
);

SQL Improvements:

• BIGINT instead of INT: Java's Long maps to SQL BIGINT (INT maxes out at ~2 billion, which can be limiting for growing datasets).
• Foreign Key Constraint: Ensures every accountId in the balance table exists in the account table—prevents orphaned balance records.
• Currency Field Size: Reduced to VARCHAR(3) since standard currency codes are always 3 characters (no need for 200).

3. Quick Recap of How It Works #

• When you save an Account with a list of Balances, JPA automatically sets the account field on each Balance and inserts the correct account_id value in the balance table.
• When you fetch an Account, the balances list loads all Balance records with a matching account_id (lazy fetch means it won't load until you access the list, which is efficient for most use cases).

内容的提问来源于stack exchange，提问作者artjom prozorov