Java实现Postgres JSONB转Oracle BLOB时遇列类型无效等错误求助

问题根源 #

• Postgres的JSONB通过rs.getString("jdoc")读取后转SerialBlob，会导致Oracle驱动无法正确识别Blob类型，触发Invalid column type错误
• 直接传递java.sql.Blob跨数据库存在驱动兼容性问题
• 字符串转BLOB时的ORA-01465错误，源于字符串编码与Oracle RAW类型的不匹配，utl_raw.cast_to_raw无法处理复杂编码场景

修复方案 #

1. 优化实体类（推荐）

将java.sql.Blob替换为byte[]，简化字节流处理，避免跨数据库Blob对象的兼容性问题：

import lombok.Data;

@Data
public class Employee {
    private byte[] jdoc;
}

2. 修改RowMapper：直接读取JSONB为字节流

从Postgres ResultSet中直接读取JSONB的字节数据，跳过字符串转换环节：

@Bean("employee_RowMapper")
public RowMapper employee_entityRowMapper() {
    return new RowMapper<Employee>(){
        @Override
        public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
            Employee employee = new Employee();
            byte[] jsonBytes = rs.getBytes("jdoc");
            if (jsonBytes != null && jsonBytes.length > 0) {
                employee.setJdoc(jsonBytes);
            }
            return employee;
        }  
    };
}

若必须保留Blob类型，可直接读取Postgres原生Blob：

@Bean("employee_RowMapper")
public RowMapper employee_entityRowMapper() {
    return new RowMapper<Employee>(){
        @Override
        public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
            Employee employee = new Employee();
            Blob postgresBlob = rs.getBlob("jdoc");
            if (postgresBlob != null && postgresBlob.length() > 0) {
                employee.setJdoc(postgresBlob);
            }
            return employee;
        }  
    };
}

3. 调整SQLParameterSourceProvider：适配Oracle BLOB插入

如果用byte[]类型，直接传递字节数组并指定BLOB类型：

@Bean(name = "employee_entity_TableSqlParameterSourceProvider")
public ItemSqlParameterSourceProvider employee_entityTableSqlParameterSourceProvider() {
    return new ItemSqlParameterSourceProvider<Employee>() {
        @Override
        public SqlParameterSource createSqlParameterSource(Employee item) {
            MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
            if (item.getJdoc() != null) {
                mapSqlParameterSource.addValue("jdoc", item.getJdoc(), Types.BLOB);
            }
            return mapSqlParameterSource;
        }
    };
}

若保留Blob类型，需将其转换为字节数组后传入，避免驱动类型冲突：

@Bean(name = "employee_entity_TableSqlParameterSourceProvider")
public ItemSqlParameterSourceProvider employee_entityTableSqlParameterSourceProvider() {
    return new ItemSqlParameterSourceProvider<Employee>() {
        @Override
        public SqlParameterSource createSqlParameterSource(Employee item) {
            MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
            Blob blob = item.getJdoc();
            if (blob != null && blob.length() > 0) {
                byte[] blobBytes = blob.getBytes(1, (int) blob.length());
                mapSqlParameterSource.addValue("jdoc", blobBytes, Types.BLOB);
            }
            return mapSqlParameterSource;
        }
    };
}

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