Proper Hibernate Annotation for Byte[]

proper hibernate annotation for byte[]

What is the portable way to annotate a byte[] property?

It depends on what you want. JPA can persist a non annotated byte[]. From the JPA 2.0 spec:

11.1.6 Basic Annotation

The Basic annotation is the simplest
type of mapping to a database column.
The Basic annotation can be applied
to a persistent property or instance
variable of any of the following
types: Java primitive, types, wrappers
of the primitive types,
java.lang.String,
java.math.BigInteger,
java.math.BigDecimal,
java.util.Date,
java.util.Calendar, java.sql.Date,
java.sql.Time, java.sql.Timestamp,
byte[], Byte[], char[], Character[], enums, and any other
type that implements Serializable.
As described in Section 2.8, the use
of the Basic annotation is optional
for persistent fields and properties
of these types. If the Basic
annotation is not specified for such a
field or property, the default values
of the Basic annotation will apply.

And Hibernate will map a it "by default" to a SQL VARBINARY (or a SQL LONGVARBINARY depending on the Column size?) that PostgreSQL handles with a bytea.

But if you want the byte[] to be stored in a Large Object, you should use a @Lob. From the spec:

11.1.24 Lob Annotation

A Lob annotation specifies that a
persistent property or field should be
persisted as a large object to a
database-supported large object type.
Portable applications should use the
Lob annotation when mapping to a
database Lob type. The Lob annotation
may be used in conjunction with the
Basic annotation or with the
ElementCollection annotation when the
element collection value is of basic
type. A Lob may be either a binary or
character type. The Lob type is
inferred from the type of the
persistent field or property and,
except for string and character types,
defaults to Blob.

And Hibernate will map it to a SQL BLOB that PostgreSQL handles with a oid
.

Is this fixed in some recent version of hibernate?

Well, the problem is that I don't know what the problem is exactly. But I can at least say that nothing has changed since 3.5.0-Beta-2 (which is where a changed has been introduced)in the 3.5.x branch.

But my understanding of issues like HHH-4876, HHH-4617 and of PostgreSQL and BLOBs (mentioned in the javadoc of the PostgreSQLDialect) is that you are supposed to set the following property

hibernate.jdbc.use_streams_for_binary=false

if you want to use oid i.e. byte[] with @Lob (which is my understanding since VARBINARY is not what you want with Oracle). Did you try this?

As an alternative, HHH-4876 suggests using the deprecated PrimitiveByteArrayBlobType to get the old behavior (pre Hibernate 3.5).

References

• JPA 2.0 Specification
  
  
  • Section 2.8 "Mapping Defaults for Non-Relationship Fields or Properties"
  • Section 11.1.6 "Basic Annotation"
  • Section 11.1.24 "Lob Annotation"

Resources

• http://opensource.atlassian.com/projects/hibernate/browse/HHH-4876
• http://opensource.atlassian.com/projects/hibernate/browse/HHH-4617
• http://relation.to/Bloggers/PostgreSQLAndBLOBs

Store byte array in MySQL with Hibernate

Well,

I have tried your suggestions @Ernusc. The first one:

@Lob(type = LobType.BLOB)
private byte[] blobImg;

it is no compiling for me. It may be my Hibernate version is different as yours.
About the second option:

@Type(type = "org.hibernate.type.BlobType")
@Lob
private byte[] blobImg;

It does compile, but it fails at runtime. It was showing the following error:

2015-09-14 23:21:01.324  WARN 7436 --- [bTaskExecutor-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
2015-09-14 23:21:01.324 ERROR 7436 --- [bTaskExecutor-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'blob blob0_ where blob0_.idBlobPersistence=326' at line 1
2015-09-14 23:21:01.337  INFO 7436 --- [bTaskExecutor-3] o.h.e.internal.DefaultLoadEventListener  : HHH000327: Error performing load command : org.hibernate.exception.SQLGrammarException: could not extract ResultSet   

Also, when I tried to follow the trace, I found it was actually throwing the following exception:

java.lang.ClassCastException: [B cannot be cast to java.sql.Blob

Then I realized the first of the error messages was actually caused by my stupid idea of naming my entity as "blob", which is probably a reserved keyword in Hibernate. So I changed the entity declaration from:

@Entity(name = "blob")
public class Blob {
   ...
}

to:

@Entity(name = "blobframe")
public class Blob {
   ...
}

Then, your second suggestion was only throwing that ClassCastException exception.
However, some of the approaches I tried before do work now. For instance:

@Column(name = "blobImg", nullable = false, columnDefinition = "BINARY(256)", length = 256)
private byte[] blobImg;

UPDATE:
This approach is NOT working for me. It fails on blobframe table creation with the following field definition (don't know the reason, though):

@Lob
@Column(name="blobImg", columnDefinition="bytea")
private byte[] blobImg;

Thank you Ernusc for your quick response.

Hibernate @Lob on byte[] causes Bad value for type long

The bytea type is inlined into the table whereas other types are chunked into a separate table which is called TOAST on PostgreSQL. To access these values, database have a concept often referred to as a LOB locator which essentially is just an id for doing the lookup. Some drivers/databases just work either way but others might need to match the actual physical representation. In your case, using @Lob is just wrong because AFAIK bytea is inlined up to a certain size and de-TOASTed i.e. materialized automatically behind the scenes if necessary. If you were using the varbinary/blob type or something like that, you would have to use @Lob as in that case, the main table only contains this LOB locator which is a long. The driver then knows when you ask for the value by using getBlob that it has to execute some select get_lob(?) query to retrieve the actual contents.

---

Related Topics