Spring Boot操作PostgreSQL存储bit类型报错求助

问题描述 #

执行存储操作时触发如下报错：

Caused by: org.postgresql.util.PSQLException: ERROR: column "published" is of type bit but expression is of type bytea

该问题仅在PostgreSQL环境出现，MySQL、Oracle可正常运行。已尝试两种写法均无效：

• @Column(name = "published", columnDefinition = "bit default '0") private boolean published;
• @Column(name = "published", columnDefinition = "BIT default '0'") private byte[] published = {'0'};

解决方案 #

方法1：自定义Hibernate类型映射

创建自定义类型处理PostgreSQL BIT(1)与Java boolean的转换：

import org.hibernate.type.AbstractSingleColumnStandardBasicType;
import org.hibernate.type.descriptor.java.BooleanTypeDescriptor;
import org.hibernate.type.descriptor.sql.CharTypeDescriptor;

public class PostgresBitBooleanType extends AbstractSingleColumnStandardBasicType<Boolean> {
    public static final PostgresBitBooleanType INSTANCE = new PostgresBitBooleanType();

    public PostgresBitBooleanType() {
        super(CharTypeDescriptor.INSTANCE, BooleanTypeDescriptor.INSTANCE);
    }

    @Override
    public String getName() {
        return "postgres_bit_boolean";
    }
}

实体类中引用该类型：

@Type(type = "com.yourpackage.PostgresBitBooleanType")
@Column(name = "published", columnDefinition = "BIT(1) DEFAULT '0'")
private boolean published;

方法2：使用JPA属性转换器

编写转换器实现boolean与数据库BIT类型的双向转换：

import jakarta.persistence.AttributeConverter;
import jakarta.persistence.Converter;

@Converter(autoApply = true)
public class BitBooleanConverter implements AttributeConverter<Boolean, String> {
    @Override
    public String convertToDatabaseColumn(Boolean attribute) {
        return attribute != null && attribute ? "1" : "0";
    }

    @Override
    public Boolean convertToEntityAttribute(String dbData) {
        return "1".equals(dbData);
    }
}

实体类中应用转换器：

@Convert(converter = BitBooleanConverter.class)
@Column(name = "published", columnDefinition = "BIT(1) DEFAULT '0'")
private boolean published;

方法3：SQL显式转换（临时方案）

如果不想编写自定义类，可在SQL语句中显式转换类型：

• JPQL写法：

  UPDATE YourEntity e SET e.published = CAST(:value AS BIT) WHERE e.id = :id
  

• 原生SQL写法：

  INSERT INTO your_table (published) VALUES (CAST('1' AS BIT));
  

问题原因 #

PostgreSQL对BIT类型的处理逻辑与MySQL、Oracle存在差异，Hibernate默认会将boolean或byte[]类型映射为bytea类型，导致与数据库的BIT类型不匹配，因此需要显式配置类型转换规则。

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