Hibernate，Postgresql:列“x"是 oid 类型，但表达式是 byte 类型 [英] Hibernate, Postgresql: Column "x" is of type oid but expression is of type byte

问题描述

在不同数据库之间切换时，我遇到了一个关于包含大对象 (BLOB) 的休眠映射的奇怪问题.

I have a strange problem regarding the hibernate mapping containing large objects (BLOB), when switching between different databases.

@Lob
private byte[] binaryData;

上面的字段在 MySQL 和 Oracle 中创建了一个字节数组字段，但是在 PostreSQL 中它创建了一个 oid 类型的字段.

The field above creates a byte array field in MySQL and in Oracle, however in PostreSQL it creates a field of type oid.

现在，当我尝试访问此字段时，它在其他数据库中工作正常，但在 PostgreSQL 中失败并出现以下错误

Now when I try to access this field it works fine in the other databases, but in PostgreSQL it fails with the following error

Column "binaryData" is of type oid but expression is of type bytea.

所以我尝试简单地删除@Lob"注释，这将解决 PostgreSQL 的问题，但是在没有这个注释的 MySQL 中，hibernate 创建了一个类型为tinyblob"的字段，在我们的大多数情况下它都很小.而且，由于我们想在多个环境中使用这个项目，所以要切换两种不同的映射是很烦人的.

So I tried to simply remove the "@Lob" annotation, which will solve the problem for PostgreSQL, however in MySQL without this annotation, hibernate creates a field of type "tinyblob", which is to small in most of our cases. And, as we want to use this project in more than one environment it is annoying to have two different mappings to switch.

是否有任何注释强制 postgreSQL 对使用 @Lob 注释的字段使用 bytea 而不是 oid?或者是否有可能省略@Lob并放置其他内容以强制MySQL为其分配更大的数据类型，就像使用@Lob一样?

Is there any annotation that forces postgreSQL to use bytea instead of oid for fields annotated with @Lob? Or is it somehow possible to omit the @Lob and put something else in order to force MySQL to allocate it with a larger datatype as it would using @Lob?

我什至可以想象有这样的解决方案

I could even imagine to have a solution like this

if (field is of type oid)
  store it as oid
else if (field is of type bytea)
  store it as bytea
else
  // not storable

如果有办法做到这一点，则与 getter 相同

and the same as a getter, if there exists a way to do kind of this

以下声明有效.它将列分配为 oid，但是使用它的休眠知道如何从此类字段中存储和检索数据

The following declaration is working. It allocates the column as oid, however hibernate using this knows how to store and retrieve data from such a field

@Lob
@Type(type="org.hibernate.type.PrimitiveByteArrayBlobType")
private byte[] binaryFile;

推荐答案

这个字段映射在 org.hibernate.dialect.PostgreSQLDialect 中定义，可以通过子类化和配置你的应用来改变它使用 postgres 运行时修改的方言.

This field mapping is defined in org.hibernate.dialect.PostgreSQLDialect and can be changed by subclassing this and configuring your app to use the modified dialect when running with postgres.

子类中的相关咒语大概就是放

The relevant incantation in the subclass is probably to put

    registerColumnType( Types.BLOB, "bytea" );

在你的构造函数中调用 super().

in your constructor after a call to super().

这篇关于Hibernate，Postgresql:列“x"是 oid 类型，但表达式是 byte 类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！