How to change database type for @Lob property with Hibernate-CodePudding

I have a requirement to make our application to work with Postgresql, Oracle and SQL Server. All is working fine in all 3 database except the @Lob mapping in a string property. For Oracle and SQLServer is working correctly (varchar(max) and clob) but for Postgresql what I want is the text type but it's being mapping as oid.

What i've done is create a dialect for Postgresql and overwrite getTypeName and remapSqlTypeDescriptor

@Override
public String getTypeName(int code, long length, int precision, int scale) throws HibernateException {
    if (code == Types.CLOB) {
        return super.getTypeName(Types.VARCHAR, length, precision, scale);
    }
    return super.getTypeName(code, length, precision, scale);

}

@Override
public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor) {
    if (sqlTypeDescriptor.getSqlType() == Types.CLOB) {
        return VarcharTypeDescriptor.INSTANCE;
    }
    return super.remapSqlTypeDescriptor(sqlTypeDescriptor);
}

Are there any better way for doing this?

Property in entity

@Size(max = 300000)
@Column(name = "value")
@Lob
private String value;

and with this mapping i get the same result

@Size(max = 300000)
@Column(name = "value")
@Type(type = "org.hibernate.type.MaterializedClobType")
@Lob
private String value;

Create sentence from Hibernate: org.hibernate.SQL : create table table (id int4 not null, value oid, primary key (id))

CodePudding user response：

As I have answered on several other questions almost just like this one, don't use @Lob for this. Just use @Column(length=SOME_BIG_NUMBER).

@Lob means: use JDBC Clob APIs to read/write the string.

But you don't need that. You're (so I suppose) just abusing @Lob to make Hibernate's schema exporter generate the column type you want. That's not the semantic of @Lob. And anyway, Hibernate (6, and I believe 5 as well) will automatically pick a sensible column type if you just specify the length of the column, so that's unnecessary.

What i've done is create a dialect for Postgresql and overwrite getTypeName and remapSqlTypeDescriptor

Do not do this please; this is just a silly thing to do. You're just redefining @Lob to mean "regular string handling". Just get rid of the @Lob annotation instead.

UPDATE: So it appears the actual real problem is that Hibernate 5 used the deprecated long data type for long strings on Oracle. That is to say, the problem was not with the dialect for Postgres, but with the dialect for Oracle. This was actually something I fixed in Hibernate 6 almost exactly three years ago to the day, but for reasons of backward compatibility cannot be changed in H5.

But no matter, it should be very easy to change the type mapping for long strings in a custom subclass of OracleXyDialect. Just write something like:

registerColumnType( Types.VARCHAR, "clob" );

This is much more straightforward than trying to make @Lob mean not-a-lob on Postgres.

(I have not tested this.)

Or, you could simply migrate to Hibernate 6 where this and many other issues with the dialects have already been sorted out.