BLOBs with Postgres and Hibernate caused a lot of headace for me

hibernate-jpa-postgres-issues.md

The problem

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

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!

Disclaimer

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 a lot you saved my day !!!

Thanks! after finally determining the problem child, this solved it almost right away (I had to use TextType, but credit to this for being able to resolve this)

As far as I understand, a bytea is not streamed. Instead, it gets fully loaded into memory inside PostgreSQL every time the according row got selected. So be careful with big data in a bytea. And using byte[] at Hibernate causes the same at the application layer: All data is read into this byte array (= into memory) when the entity gets loaded. If you need streaming consider using java.sql.Blob in your application layer and BLOB in PostgreSQL instead.

https://stackoverflow.com/questions/4386030/how-to-use-blob-datatype-in-postgres

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.

Thank you @mark-spooner