Mapping binary blob to string

Hibernate ORM
1

Hi,

We are using Firebird database, and some of the columns for text are blob subtype binary. Now we have a problem with how to say this to Hibernate. I tried:

@Lob
@Column(length=Integer.MAX_VALUE-1)
private String note;

This code throws Schema-validation: wrong column type encountered in column [note] in table [Entity]; found [blob sub_type binary (Types#LONGVARBINARY)], but expecting [blob sub_type text (Types#CLOB)]

Then I tried:

@Lob
@Column(length=Integer.MAX_VALUE-1)
@JdbcTypeCode(Types.LONGVARBINARY)
private String note;

This throws HibernateException : Could not convert '[B' to 'java.lang.String' using 'org.hibernate.type.descriptor.java.StringJavaType' to wrap.

Finally this:

@Lob
@Column(length=Integer.MAX_VALUE-1, columnDefinition = "blob sub_type binary")
private String note;

This solution works, but there is a problem with unit tests when we use an HSQL database; the HSQL database does not have this type of column.

What is the correct solution to handle this?

Currently, we are using Hibernate 6.4.9.Final.

2

First of all, Firebird is not officially supported, so the Hibernate team might not be able to help you in this case. Try reaching out to the maintainer of the dialect if nothing else helps.

AFAICT, your original mapping is fine, but the Dialect doesn’t implement #equivalentTypes properly.

3

Thanks for your reply and advice. I added to the Firebird dialect:

@Override
    public boolean equivalentTypes(int typeCode1, int typeCode2) {
        if(isStringType(typeCode1) && isStringType(typeCode2))
            return true;

        return super.equivalentTypes(typeCode1, typeCode2);
    }

    private boolean isStringType(int typeCode){
        return Types.LONGVARBINARY == typeCode || Types.CLOB == typeCode;
    }

And now mapping works with the original (first) code.

4

If you think that this is a good change, you can discuss with e.g. @mrotteveel and propose a PR.

5

The correct solution is to declare these columns as BLOB SUB_TYPE TEXT (a.k.a BLOB SUB_TYPE 1), not as BLOB SUB_TYPE BINARY (a.k.a. BLOB SUB_TYPE 0 or just BLOB).

I don’t see how making the dialect consider BLOB SUB_TYPE BINARY a string type is a good idea, because it’s not actually a string type.

6

In any case, I’ll take a look at equivalentTypes and see if it needs tweaking (I don’t have time to check the semantics right now, so maybe my previous comment is a bit too firm). I’ve created [Hibernate dialect] Investigate type mapping/equivalentTypes · Issue #872 · FirebirdSQL/jaybird · GitHub in Jaybird’s tracker as a reminder to myself.