Saving large files in h2 database

Question

I recently updated the h2 database in my springboot project to 2.1.210.

This induced some problems, most of them I was able to fix myself. The one that I can't fix is about saving large files in the database.

When I try to upload a ~3,5MB file, I get following error:

org.h2.jdbc.JdbcSQLDataException: 
Value too long for column "BINARY VARYING": "504b03040a00000000008a6bd150e8d6354d8303010083030100070000003030302e706e6789504e... (3648495)" [22001-210]

The annotation, which worked flawlessly on h2 1.4.200 looks like this

@Lob
@Type( type = "binary" )
@Column(name = "ZIP", columnDefinition="BLOB")
private byte[] zip;

I don't know if its related, but I also get an error when using

@Type( type = "text" )

The error here is

Precision ("2147483647") must be between "1" and "1048576" inclusive

because whenever I use "text", it gets translated to varchar(2147483647)

Answer 1

It looks like, since H2 version 2, the org.h2.value.Value.getBytes() method has changed significantly so as to limit the amount that can be held in memory in a byte array.

The H2 manual (data types and large objects) says that the allowed length is from 1 to 1048576 bytes for BINARY VARYING and for anything else ResultSet.getBinaryStream(...) is the preferred method.

I'm not sure how you go about controlling this in HQL or using Hibernate annotations, but changing the data type from byte[] to some kind of stream may encourage the ORM to avoid rs.getBytes(...) and call H2's favoured method instead.