无法正确配置@ElementCollection，User与Isopod实体关联问题求助

数据库结构 #

你的Postgres数据库表结构如下：

isopod=# select * from users;
 id | username | password | registered_at |       isopods        |                badges                
----+----------+----------+---------------+----------------------+--------------------------------------
  1 | mike     | mike     | now           | ISO.C.001, ISO.A.001 | badge1, badge2
(1 row)

isopod=# select * from isopods;
    id      | name  | size  | generation | type  | rarity |  icon  
----+-------+-------+------------+-----------------+--------+--------
  ISO.C.001 | AMBER | SMALL | CUBARIS    | type1 | RARE   | icon01
(1 row)

当前Java实体配置 #

User类 #

@Entity
@Table(name = "users")
public class User implements Serializable {

    @Id
    private String id;
    private String username;
    private String password;
    private String registeredAt;
    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name="isopods", joinColumns=@JoinColumn(name="id"))
    private List<String> isopods = new ArrayList<>();
    @ElementCollection
    private List<Badge> badges;
}

Isopod类 #

@Entity
@Table(name = "isopods")
public class Isopod implements Serializable {

    @Id
    private String id; //ISO.C.001
    private String name;
    private String size;
    private String generation;
    private String type;
    private String rarity;
    private String icon;
}

错误信息 #

2023-04-30 12:30:51.284 ERROR 30955 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: column isopods0_.isopods does not exist

问题根源 #

你犯了两个核心错误：

1. 混淆了映射方式：@ElementCollection + @CollectionTable是用来映射独立的集合关联表（比如多对多中间表），但你的users.isopods是单个逗号分隔的字符串字段，并非关联表。
2. 错误指定集合表：你把isopods实体表当成了User的集合表，导致Hibernate试图在isopods表中查找名为isopods的字段，而该字段实际不存在，因此报错。

修正方案 #

方案1：适配现有数据库，映射为List<String> #

针对users.isopods是逗号分隔字符串的结构，使用属性转换器实现字符串与List的双向转换：

1. 实现转换器类

@Converter
public class StringListConverter implements AttributeConverter<List<String>, String> {
    private static final String SEPARATOR = ",";

    @Override
    public String convertToDatabaseColumn(List<String> list) {
        return list != null ? String.join(SEPARATOR, list) : null;
    }

    @Override
    public List<String> convertToEntityAttribute(String s) {
        return s != null ? Arrays.asList(s.split(SEPARATOR)) : new ArrayList<>();
    }
}

2. 修改User类字段配置

@Entity
@Table(name = "users")
public class User implements Serializable {

    @Id
    private String id;
    private String username;
    private String password;
    private String registeredAt;
    
    // 用转换器映射逗号分隔字符串为List<String>
    @Convert(converter = StringListConverter.class)
    private List<String> isopods = new ArrayList<>();
    
    // badges字段同理，也需要相同的转换器
    @Convert(converter = StringListConverter.class)
    private List<String> badges;
}

方案2：升级为List<Isopod>（推荐规范设计） #

如果可以调整数据库结构，建议改为多对多关联的规范化设计：

1. 创建多对多中间表

CREATE TABLE user_isopods (
    user_id VARCHAR(255) REFERENCES users(id),
    isopod_id VARCHAR(255) REFERENCES isopods(id),
    PRIMARY KEY (user_id, isopod_id)
);

同时删除users表中的isopods字段。

2. 修改User类关联配置

@Entity
@Table(name = "users")
public class User implements Serializable {

    @Id
    private String id;
    private String username;
    private String password;
    private String registeredAt;
    
    // 多对多关联Isopod实体
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
        name = "user_isopods",
        joinColumns = @JoinColumn(name = "user_id"),
        inverseJoinColumns = @JoinColumn(name = "isopod_id")
    )
    private List<Isopod> isopods = new ArrayList<>();
}

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