Saving byte[] with hibernate to postgreSQL

Question

Currently, in the a DB I have the following table (PostgreSQL 9.2):

                        jackpot_image

     id       jackpot_id         mime_type        image_data
   serial      integer          varchar(32)         bytea

I have the following entity:

@Entity
@Table(name = "jackpot_image")
public class JackpotImage {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

    @Column(name="jackpot_id")
    private int jackpotId;

    @Column(name="mime_type")
    private String mimeType;

    @Column(name="image_data")
    private byte[] imageData;

    //GET, SET

}

and thw following DAO:

public class JackpotImageDao {

    public JackpotImage selectImage(int imageId){
        String hql = "FROM JackpotImage WHERE id = :imageId";
        Query query = getSession().createQuery(hql);
        query.setInteger("imageId", imageId);
        return (JackpotImage) query.uniqueResult();
    }

    public void save(JackpotImage image){
        getSession().saveOrUpdate(image);
    }
}

The thing is when I'm trying to save and then retrieve the image I get the different arrays of byte.

JackpotImage image;
JackpotImageDao imageDao;
//Getting image and ImageDAO
imageDao.save(image);
JackpotImage img = imageDao.selectImage(image.getId());

Now, the image's binary data differs from img's binary data. Why? And how can I fix that?

You say that *the `image`'s binary data differs from `img`'s binary data*, but you don't say how you determined that, or what the differences are. Please show complete samples for a small image, or at least the first and last hundred bytes of both original and result. Edit your question to do this, then comment here when done. — Craig Ringer, Apr 04 '15 at 08:03
Also, what's your PgJDBC version, and what's the output of the query `SELECT current_setting('bytea_output');`? — Craig Ringer, Apr 04 '15 at 08:04

score -2 · Answer 1 · answered Apr 03 '15 at 12:23

-2

Add annotation @Lob to the field imageData.

answered Apr 03 '15 at 12:23

Alexander Fedyukov

964
8
18

Thank you for your answer. But couldn't you expand it a little? For instance, why should I neccesary use @Lob annotation? – user3663882 Apr 03 '15 at 12:24
Because you define column image_data in table as bytea, i.e. big data type. For this case field imageData must be annotated with @Lob. Look at [link](https://docs.oracle.com/javaee/6/api/javax/persistence/Lob.html) – Alexander Fedyukov Apr 03 '15 at 12:28
I've added the annotation, but it doesn't work. I got `ERROR: column "image_data" is of type bytea but expression is of type bigint` – user3663882 Apr 03 '15 at 12:30
Now I cannot even make the entity saved. – user3663882 Apr 03 '15 at 12:31
Which hibernate version do you use? Some such troubles was in older [one](http://stackoverflow.com/questions/3677380/proper-hibernate-annotation-for-byte) – Alexander Fedyukov Apr 03 '15 at 12:47
2

This is not correct - or rather, as written, is missing critical information that makes it misleading. If you annotate with `@Lob`, Hibernate will use PostgreSQL's out-of-line large object storage, `pg_largeobject`, via *by-reference* access using large object oids. This is *completely different* to using a bytea array, `bytea`, like the user wants, and is *not compatible* at the database level. – Craig Ringer Apr 04 '15 at 08:02
@CraigRinger So the solution with changing image_data to oid and adding Lob annotation is incorrect? BTW, it works for me... So would you please provide a correct solution? – user3663882 Apr 04 '15 at 10:42

Saving byte[] with hibernate to postgreSQL

1 Answers1