It seems like mapping a LOB-type from Hibernate is not as easy as you'd think. If you're like me, you'll toss something like the following annotation to a POJO and hoping that all is fine:
@Lob
@Column(name = "picture")
private byte[] picture;
However, lo and behold - things is not as expected. Hibernate throws all kinds of fancy exceptions - such as:
org.postgresql.util.PSQLException: Bad value for type long : x
So, you'll think that something is wrong with the mapping - and thats right! It seems like Hibernate maps LOBs to OIDs unless others specified. Duh..
The solution is then to add a Hibernate-specific annotation that specifies the type, such as:
@Lob
@Type(type = "org.hibernate.type.BinaryType")
@Column(name = "picture")
private byte[] picture;
This fixes the issue and the bytestream is being feeded to the POJO attribute. Jeay!
As far as I can understand, this only happens to the following databases:
- OracleSQL
- PostgresSQL
We're using the following properties:
Properties props = new Properties();
props.put(Environment.DIALECT, org.hibernate.dialect.PostgreSQL94Dialect.class.getName());
props.put(Environment.FORMAT_SQL, "true");
props.put(Environment.USE_STREAMS_FOR_BINARY, "false");
Enjoy! :-)
Thanks, very useful. Admittedly not having the time to look into this properly to see if I'm even remotely doing anything sane, I'd just like to provide a link for anyone on hibernate 6+ where the above @type stuff doesnt work anymore:
https://stackoverflow.com/questions/75354646/using-type-for-binary-data-in-hibernate-6
From this I found that omitting the @lob (didnt make a difference) but just adding:
@JdbcTypeCode(Types.BINARY)
worked for my issue
(which was getting a DBTestContainer test to run and not fail hibernate schema validation with:
found [bytea (Types#BINARY)], but expecting [bytes (Types#VARBINARY)
after I moved a cockroach column from STRING to BYTEA and the corresponding entity field from String to byte[])
Field isn't going to hold anything very big so not worried re loading fully into memory vs streaming etc.