如何通过Hibernate将PostgreSQL数组映射为Java自定义对象列表？

一、现有数据库设计下的实现方式（自定义Hibernate类型） #

Hibernate默认不支持直接将PostgreSQL数值数组映射为自定义对象列表，需要通过自定义UserType实现转换逻辑：

1. 实现自定义UserType #

创建实现org.hibernate.usertype.UserType的类，负责数据库数组与Java对象列表的双向转换：

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;
import java.io.Serializable;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class WorkStationListType implements UserType {

    @Override
    public int[] sqlTypes() {
        return new int[]{Types.ARRAY};
    }

    @Override
    public Class<List<Station>> returnedClass() {
        return (Class<List<Station>>) (Class<?>) List.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        if (x == y) return true;
        if (x == null || y == null) return false;
        return x.equals(y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return x.hashCode();
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException {
        Array array = rs.getArray(names[0]);
        if (array == null) return new ArrayList<>();
        
        Long[] stationIds = (Long[]) array.getArray();
        List<Station> stations = new ArrayList<>();
        for (Long id : stationIds) {
            Station station = session.get(Station.class, id);
            if (station != null) {
                stations.add(station);
            }
        }
        return stations;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException {
        List<Station> stations = (List<Station>) value;
        if (stations == null || stations.isEmpty()) {
            st.setNull(index, Types.ARRAY);
            return;
        }
        
        Long[] stationIds = stations.stream()
                .map(Station::getStationId)
                .toArray(Long[]::new);
        Array array = session.connection().createArrayOf("bigint", stationIds);
        st.setArray(index, array);
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        if (value == null) return null;
        List<Station> original = (List<Station>) value;
        List<Station> copy = new ArrayList<>(original.size());
        copy.addAll(original);
        return copy;
    }

    @Override
    public boolean isMutable() {
        return true;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable) deepCopy(value);
    }

    @Override
    public Object assemble(Serializable cached, Object owner) throws HibernateException {
        return deepCopy(cached);
    }

    @Override
    public Object replace(Object original, Object target, Object owner) throws HibernateException {
        return deepCopy(original);
    }
}

2. 在实体类中配置自定义类型 #

修改SalEmp实体，通过@Type注解指定自定义类型，并定义数据库列的数组类型：

import org.hibernate.annotations.Type;
import javax.persistence.*;
import java.io.Serializable;
import java.util.List;

@Entity
@Table(name = "sal_emp")
public class SalEmp implements Serializable {
    @Id
    private String name;
    
    @Type(type = "com.yourpackage.WorkStationListType")
    @Column(name = "work_station", columnDefinition = "bigint[]")
    private List<Station> stations;

    // getters & setters
}

注意事项 #

• 每次查询SalEmp时会触发多次Station查询，存在性能瓶颈，可手动实现批量查询优化
• 数组中若存在无效station_id，对应Station对象会为null，需额外处理空值逻辑

二、更合理的数据库设计（符合关系型范式） #

用数值数组存储外键不符合关系型数据库设计规范，推荐使用多对多中间表维护关联关系，Hibernate可直接支持，无需自定义类型：

1. 修改数据库表结构 #

-- 删除原sal_emp表的work_station列
ALTER TABLE sal_emp DROP COLUMN work_station;

-- 创建多对多中间表
CREATE TABLE sal_emp_station (
    sal_emp_name varchar REFERENCES sal_emp(name),
    station_id bigint REFERENCES station(station_id),
    PRIMARY KEY (sal_emp_name, station_id)
);

-- 插入关联数据
INSERT INTO sal_emp_station VALUES ('one', 1), ('one', 2);
INSERT INTO sal_emp_station VALUES ('two', 3), ('two', 4);
INSERT INTO sal_emp_station VALUES ('three', 2), ('three', 4);

2. 实体类映射修改 #

// SalEmp实体
@Entity
@Table(name = "sal_emp")
public class SalEmp implements Serializable {
    @Id
    private String name;
    
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
        name = "sal_emp_station",
        joinColumns = @JoinColumn(name = "sal_emp_name"),
        inverseJoinColumns = @JoinColumn(name = "station_id")
    )
    private List<Station> stations;

    // getters & setters
}

// Station实体
@Entity
@Table(name = "station")
public class Station implements Serializable {
    @Id
    @Column(name = "station_id")
    private Long stationId;
    private String stationName;
    private String stationTimezone;

    // 可选：添加双向关联
    @ManyToMany(mappedBy = "stations")
    private List<SalEmp> salEmps;

    // getters & setters
}

优势 #

• 符合关系型数据库范式，数据一致性更易维护
• 支持Hibernate懒加载、级联操作、查询优化等特性
• 避免数组存储带来的性能问题与数据有效性校验难题

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