PostgreSQL类型long值错误：Java+Hibernate查询Blob字段失败

我有一个Java应用，对应的POJO类为ArtifactsBlobs：

public class ArtifactBlobs{
    private static final long serialVersionUID = 1L;
    private String guid;
    private String workspaceId;
    private Blob artifactBlobValue;
    private Date createdAt;
    private Long createdBy;
    private Date updatedAt;
    private Long updatedBy;
    private Set waasArtifactsBlobsMetas = new HashSet(0);

    public ArtifactsBlobs() {
    }

    public ArtifactsBlobs(String guid, Date createdAt, Date updatedAt) {
        this.guid = guid;
        this.createdAt = createdAt;
        this.updatedAt = updatedAt;
    }

    public ArtifactsBlobs(String guid, String workspaceId,
            Blob artifactBlobValue, Date createdAt, Long createdBy,
            Date updatedAt, Long updatedBy) {
        this.guid = guid;
        this.workspaceId = workspaceId;
        this.artifactBlobValue = artifactBlobValue;
        this.createdAt = createdAt;
        this.createdBy = createdBy;
        this.updatedAt = updatedAt;
        this.updatedBy = updatedBy;
    }

    // 所有getter和setter方法省略
}

调用以下方法查询数据时失败：

private ArtifactsBlobs 
getArtifactBlobById(
    String workspaceId,
    String blobId) 
{
    public static final String GET_ARTIFACT_BLOB_BY_ID = "from ArtifactsBlobs A where A.guid = :blobId and A.workspaceId = :workspaceId";
    Map<String, Object> valuesMap = new HashMap<>();
    valuesMap.put("workspaceId", workspaceId);
    valuesMap.put("blobId", blobId);
    ArtifactsBlobs artifactBlob = 
        iGenericDAO.getEntity(
            SqlQuery.GET_ARTIFACT_BLOB_BY_ID,
            valuesMap);
    
    if (artifactBlob == null) 
    {
        throw new DataIntegrityViolationException(
            "An unexpected error has occured while processing the request. Please try again later.");
    }
    
    return artifactBlob;
}

对应的Hibernate映射配置：

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="com.db.entity.autogen.ArtifactsBlobs" table="artifacts_blobs">
        <id name="guid" type="string">
            <column name="id" length="36" />
            <generator class="assigned" />
        </id>
        <property name="workspaceId" type="string">
            <column name="workspace_id" length="36" />
        </property>
        <property name="artifactBlobValue" type="blob">
            <column name="artifact_blob_value" not-null="true"/>
        </property>
        <property name="createdAt" type="timestamp">
            <column name="created_at" length="19" not-null="true" />
        </property>
        <property name="createdBy" type="java.lang.Long">
            <column name="created_by" />
        </property>
        <property name="updatedAt" type="timestamp">
            <column name="updated_at" length="19" not-null="true" />
        </property>
        <property name="updatedBy" type="java.lang.Long">
            <column name="updated_by" />
        </property>
        <set name="waasArtifactsBlobsMetas" table="waas_artifacts_blobs_meta" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="blob_id" length="36" not-null="true" />
            </key>
            <one-to-many class="com.kony.waas.db.entity.autogen.WaasArtifactsBlobsMeta" />
        </set>
    </class>
</hibernate-mapping>

数据库表结构：

CREATE TABLE artifacts_blobs (
  id varchar(36) NOT NULL,
  workspace_id varchar(36) NOT NULL,
  artifact_blob_value bytea NOT NULL,
  created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by bigint NOT NULL,
  updated_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_by bigint NOT NULL,
  PRIMARY KEY (id)
) ;

POJO保存到数据库正常，但查询时抛出异常：

javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not execute query
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154) ~[hibernate-core-5.3.20.Final.jar:5.3.20.Final]
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1575) ~[hibernate-core-5.3.20.Final.jar:5.3.20.Final]
at org.hibernate.query.internal.AbstractProducedQuery.uniqueResult(AbstractProducedQuery.java:1608) ~[hibernate-core-5.3.20.Final.jar:5.3.20.Final]

Caused by: org.postgresql.util.PSQLException: Bad value for type long : \x35313162373838396435613932383937613638303266336663303236633964366332393838393638356663613039323163623538383562666164366163643039306633656335613162333734313638353539303335343662306336666164343232353362306261306
at org.postgresql.jdbc.PgResultSet.toLong(PgResultSet.java:3253) ~[postgresql-42.5.0.jar:42.5.0]
at org.postgresql.jdbc.PgResultSet.getLong(PgResultSet.java:2436) ~[postgresql-42.5.0.jar:42.5.0]
at org.postgresql.jdbc.PgResultSet.getBlob(PgResultSet.java:456) ~[postgresql-42.5.0.jar:42.5.0]
at org.postgresql.jdbc.PgResultSet.getBlob(PgResultSet.java:442) ~[postgresql-42.5.0.jar:42.5.0]

核心错误为PostgreSQL驱动尝试将Blob字段转换为long类型时失败。

PostgreSQL的bytea类型是直接存储字节数据的字段，而Hibernate配置的blob类型默认对应PostgreSQL的oid类型——oid存储的是指向大对象的引用ID（long类型）。当Hibernate用blob映射bytea字段时，驱动会错误地把bytea的字节内容当成OID的long值解析，导致类型转换失败。

方式一：修改Hibernate映射适配bytea #

将Hibernate映射中artifactBlobValue的类型从blob改为binary，这是Hibernate对应字节数组的标准类型，完美匹配PostgreSQL的bytea：

<property name="artifactBlobValue" type="binary">
    <column name="artifact_blob_value" not-null="true"/>
</property>

同时推荐将POJO中的Blob类型改为byte[]，减少JDBC Blob对象的不必要开销：

// 修改POJO字段
private byte[] artifactBlobValue;

// 对应的getter和setter
public byte[] getArtifactBlobValue() {
    return this.artifactBlobValue;
}

public void setArtifactBlobValue(byte[] artifactBlobValue) {
    this.artifactBlobValue = artifactBlobValue;
}

方式二：使用Hibernate特定BinaryType #

如果需要更明确的类型指定，可以使用Hibernate的org.hibernate.type.BinaryType：

<property name="artifactBlobValue" type="org.hibernate.type.BinaryType">
    <column name="artifact_blob_value" not-null="true"/>
</property>

同样建议将POJO中的Blob改为byte[]。

方式三：临时兼容配置（不推荐） #

可以在JDBC URL中添加参数强制驱动兼容旧Blob处理逻辑，但这只是临时 workaround，可能引入其他兼容性问题：

jdbc:postgresql://host:port/dbname?compatible=postgresql_9_6

修改完成后重新部署应用，执行以下验证：

• 确认查询不再抛出类型转换异常
• 验证保存的字节数据可以正确读取
• 检查created_by、updated_by等long类型字段正常读写

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