PostgreSQL bytea and oid

I came across with following issue when i'm trying to save the uploaded file in to the
database as a byte array using hibernate, JPA 2.0  annotation with Postgre SQL.
even though in this stage i'm pretty much new to Postgre SQL,
It has two type of data types to represent the binary data or BLOB, since i'm using hibernate
jpa annotations use the annotation @Lob to indicate the byte[] of data to persist as a binary data.

@Lob 
 @Basic(fetch = FetchType.LAZY)
 public byte[] getPicture() {
        return picture;
 } 
 
exception : java.sql.SQLException: ERROR: column "picture"
  is of type bytea but expression is of type oid 
 
Then i understood the issue with the data types.but when the hibernate generating the table it creates the 
column with data type "oid" , after removing this annotation it creates a table column with "bytea" data type.
 and i'm in doubt which would be the most suitable data type to use.
 
 Then i found the difference between "oid" and "bytea" data types.  In "oid" it save the file in file system as 
object and saved its id here, according to the Postgre SQL people using of  "oid" is much more efficient. 
and also if the data type is "bytea" it saves the data as binary in data base it self.

I solve this issue using the following hibernate type as an annotation by explicitly specifying the hibernate type.

 @Type(type="org.hibernate.type.PrimitiveByteArrayBlobType")

    @Lob
    @Type(type="org.hibernate.type.PrimitiveByteArrayBlobType")
    @Column(name = "file_data")
    private byte[] data; 

this will help to hibernate to identify the correct data type and save the data as @Lob in underlying data base.
this would definitely help to change the databases in production without any model classes changes.


Comments

Malcolm said…
Try removing the @Lob attribute if the database type is bytea.
Unknown said…
Removing the @Lob will not solve the problem.

When you remove it, and then you try to change the underlying database for example to MySQL, it will fail for large objects, because the allocated space in that column is limited to some small value

Popular posts from this blog

MySQL as Hive metadata store

Big Memory Go.