Hibernate，Postgresql:列“x"是 oid 类型，但表达式是字节类型 [英] 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.

对于用@Lob 注释的字段，是否有任何注释强制 postgreSQL 使用 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 类型，但表达式是字节类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！