基于Hibernate ORM建模复合键及MySQL CSV数据插入的技术问询

Alright, let's break down how to model this with Hibernate (including composite keys) and get your CSV data into MySQL correctly. Based on your CSV structure and table relationship requirements, here's a step-by-step solution:

Step 1: Model Your Entities with Composite Keys #

First, let's map out the table relationships implied by your CSV:

• A Group can have multiple Users
• A User belongs to one Group, and has a composite primary key (groupId + username, assuming usernames are unique per group)
• A User can have multiple Interests, and an Interest can belong to multiple Users (many-to-many relationship)

1.1 Composite Key Class for User #

Since User uses a composite key, we need an embeddable class to represent it (this class must implement Serializable and override equals()/hashCode() for Hibernate to work correctly):

import jakarta.persistence.Embeddable;
import java.util.Objects;

@Embeddable
public class UserId implements Serializable {
    private String groupId;
    private String username;

    // Required no-arg constructor for Hibernate
    public UserId() {}

    public UserId(String groupId, String username) {
        this.groupId = groupId;
        this.username = username;
    }

    // Override equals and hashCode for composite key comparison
    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        UserId userId = (UserId) o;
        return Objects.equals(groupId, userId.groupId) && Objects.equals(username, userId.username);
    }

    @Override
    public int hashCode() {
        return Objects.hash(groupId, username);
    }

    // Getters and Setters
    public String getGroupId() { return groupId; }
    public void setGroupId(String groupId) { this.groupId = groupId; }
    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }
}

1.2 Group Entity #

import jakarta.persistence.*;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "groups") // "group" is a reserved MySQL keyword, so use "groups"
public class Group {
    @Id
    @Column(name = "group_id")
    private String groupId;

    // One-to-many relationship with User (cascade to auto-save users when group is saved)
    @OneToMany(mappedBy = "group", cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<User> users = new HashSet<>();

    // Constructors
    public Group() {}
    public Group(String groupId) { this.groupId = groupId; }

    // Getters and Setters
    public String getGroupId() { return groupId; }
    public void setGroupId(String groupId) { this.groupId = groupId; }
    public Set<User> getUsers() { return users; }
    public void setUsers(Set<User> users) { this.users = users; }
}

1.3 User Entity #

import jakarta.persistence.*;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "users")
public class User {
    @EmbeddedId
    private UserId id;

    // Many-to-one relationship with Group, mapped to the composite key's groupId
    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId("groupId") // Links the groupId field in UserId to Group's primary key
    @JoinColumn(name = "group_id")
    private Group group;

    @Column(name = "age")
    private Integer age;

    // Many-to-many relationship with Interest, using a join table
    @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @JoinTable(
        name = "user_interests",
        joinColumns = {
            @JoinColumn(name = "group_id", referencedColumnName = "group_id"),
            @JoinColumn(name = "username", referencedColumnName = "username")
        },
        inverseJoinColumns = @JoinColumn(name = "interest_id")
    )
    private Set<Interest> interests = new HashSet<>();

    // Constructors
    public User() {}
    public User(UserId id, Group group, Integer age) {
        this.id = id;
        this.group = group;
        this.age = age;
    }

    // Helper method to maintain bidirectional many-to-many relationship
    public void addInterest(Interest interest) {
        this.interests.add(interest);
        interest.getUsers().add(this);
    }

    // Getters and Setters
    public UserId getId() { return id; }
    public void setId(UserId id) { this.id = id; }
    public Group getGroup() { return group; }
    public void setGroup(Group group) { this.group = group; }
    public Integer getAge() { return age; }
    public void setAge(Integer age) { this.age = age; }
    public Set<Interest> getInterests() { return interests; }
    public void setInterests(Set<Interest> interests) { this.interests = interests; }
}

1.4 Interest Entity #

import jakarta.persistence.*;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "interests")
public class Interest {
    @Id
    @Column(name = "interest_id")
    private String interestId; // e.g., "Hockey", "Fishing"

    // Inverse side of the many-to-many relationship with User
    @ManyToMany(mappedBy = "interests")
    private Set<User> users = new HashSet<>();

    // Constructors
    public Interest() {}
    public Interest(String interestId) { this.interestId = interestId; }

    // Getters and Setters
    public String getInterestId() { return interestId; }
    public void setInterestId(String interestId) { this.interestId = interestId; }
    public Set<User> getUsers() { return users; }
    public void setUsers(Set<User> users) { this.users = users; }
}

Step 2: Implement CSV Data Insertion #

Assuming you've already parsed your CSV into a list of records (e.g., a custom CsvUserRecord class holding groupId, username, age, and a space-separated interests string), here's how to persist the data in a transaction:

import jakarta.persistence.EntityManager;
import jakarta.transaction.Transactional;
import java.util.Arrays;
import java.util.List;

@Transactional
public class CsvDataImporter {
    private final EntityManager em;

    public CsvDataImporter(EntityManager em) {
        this.em = em;
    }

    public void importData(List<CsvUserRecord> csvRecords) {
        for (CsvUserRecord record : csvRecords) {
            // 1. Get or create Group
            Group group = em.find(Group.class, record.getGroupId());
            if (group == null) {
                group = new Group(record.getGroupId());
                em.persist(group);
            }

            // 2. Get or create User with composite key
            UserId userId = new UserId(record.getGroupId(), record.getUsername());
            User user = em.find(User.class, userId);
            if (user == null) {
                user = new User(userId, group, record.getAge());
                em.persist(user);
            }

            // 3. Get or create Interests and link to User
            List<String> interestNames = Arrays.asList(record.getInterests().split(" "));
            for (String interestName : interestNames) {
                Interest interest = em.find(Interest.class, interestName);
                if (interest == null) {
                    interest = new Interest(interestName);
                    em.persist(interest);
                }
                user.addInterest(interest); // Maintain bidirectional relationship
            }
        }
    }

    // Custom record class for parsed CSV data
    public static class CsvUserRecord {
        private String groupId;
        private String username;
        private Integer age;
        private String interests;

        // Constructor, getters
        public CsvUserRecord(String groupId, String username, Integer age, String interests) {
            this.groupId = groupId;
            this.username = username;
            this.age = age;
            this.interests = interests;
        }

        public String getGroupId() { return groupId; }
        public String getUsername() { return username; }
        public Integer getAge() { return age; }
        public String getInterests() { return interests; }
    }
}

Key Things to Keep in Mind #

• Serializable Requirement: The composite key class (UserId) must implement Serializable—this is mandatory for Hibernate to handle composite keys across sessions.
• Equals/HashCode: Always override these methods for composite key classes to ensure Hibernate can correctly compare and cache entities.
• @MapsId: This annotation is crucial here—it tells Hibernate to reuse the groupId value from the Group entity for the composite key's groupId field, avoiding duplicate data and maintaining referential integrity.
• Transaction Management: The insertion logic must run within a transaction (@Transactional annotation) to ensure all operations are atomic and avoid partial data inserts.
• Cascade Settings: We used CascadeType.ALL for the Group-User relationship so saving a new Group automatically saves its Users. For User-Interest, we use PERSIST/MERGE to avoid accidentally deleting Interests when a User is removed.

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