JPA, PostgreSQL and bytea vs. oid type

I started to work on a private project using JBoss Seam, Facelets, RichFaces (proven stack from Seam’s Booking demo :-)) with PostgreSQL 8.3 as a backing database – database I prefer for years for whatever reason. Hibernate is used as the JPA provider (packaged with Seam anyway) and the most recent JDBC 4 driver for PostgreSQL is used.

I added column:

picture bytea;

Somehow – after reading 8.4 Binary Data Types – I thought that bytea is the right guy for the job. Of course I had an entity with proper annotation and field:

    private byte[] picture;
    @Lob
    @Basic(fetch = FetchType.LAZY)
    public byte[] getPicture() {
        return picture;
    }

    public void setPicture(byte[] picture) {
        this.picture = picture;
    }

What was my surprise when following exception occurred:

java.sql.SQLException: ERROR: column "picture"
  is of type bytea but expression is of type oid

Now – when I know the solution – this message is pretty clear. It would be in case different types were mentioned in the message. While I understood that “he” somehow dislikes bytea, I couldn’t understand what the hell “oid” (as an “object identifier”) has to do with it?

I started my investigation with Google in one hand and PostgreSQL in the other. While 8.16 Object Identifier Types was not very helpful – as in “what does have ‘unsigned four-byte integer’ to do with my byte array?!” – some googled pages convinced me that oid indeed is that type I should use – mostly one from postgresql.com domain. Truth is that I didn’t understand why and how that oid type manages to store my byte array. But I’ve tried it, it stores it, it reads it, no exception is thrown.

Now I’ve finished second part of my investigation and the results are:

  • There is special Large Object facility in PostgreSQL and it’s based on using oid type that references actual large object stored in different table behind the scene.
  • JDBC documentation also mentions that you can use oid instead of bytea.
  • In that same chapter you can learn that bytea is supported since JDBC 7.2 version – that is not very long ago.
  • I don’t know if JPA/Hibernate can use bytea, but – honestly – I don’t care while this works fine. And it does.

So if you use PostgreSQL along with JPA and bytea type is throwing an exception, try oid. I’m not sure how well JDBC supports bytea, I don’t know why JPA wants to use oid and if other JPA providers (Toplink?) can use bytea. It’s not straightforward to use oid as a type for binary data – because you can learn this only in JDBC documentation and not in the mainstream PostgreSQL manual (I don’t count that chapter 31 because you’ll likely never use it while creating your tables). If you google this with along with “JPA” or “@Lob” your results might not lead you to proper articles quickly enough… so hopefully this post might help it a bit.

If you have some facts to add, feel free to comment.

Edit 8-Feb-2013 – warning: My colleague informed me that after removing oid row from a table, large object itself stays in the DB if you use Hibernate (can’t tell anything about other providers). Reportedly, there is hot ongoing debate between Hibernate and PostgreSQL guys. Both may be right that it’s not their job to do the stuff and they don’t have enough information to delete the object, because large object itself can be shared and referenced from more tables and in fact only you, the programmer can know if the object can go away or not. Check the PostgreSQL documentation to find out how to remove the object, also check if vacuumlo doesn’t solve your problem.

Advertisements

About virgo47
Java Developer by profession in the first place. Gamer and amateur musician. And father too. Naive believer in brighter future. Step by step.

14 Responses to JPA, PostgreSQL and bytea vs. oid type

  1. shashi says:

    I came across the same issue …I am using OID datatype to store image…

    id | branding_logo
    —+—————
    1 | 206533
    2 | 206534
    3 | 206535
    9 | 206543
    7 | 206537

    How can i identify that is store the image…

    Please help me..
    shashi

  2. virgo47 says:

    I’m not sure what you need. I don’t care what’s in that oid column – I leave it to PostgreSQL/Hibernate and when I ask for byte[] from an entity, I always get what I want.

  3. shashi says:

    I am using jboss seam …and richfaces .. for file upload..

    I used bytea for storing image into daatabase … in postgres 8.3
    I got exception

    is of type bytea but expression is of type Integer…
    Then i change to OID datatype same as bolb in oracle…. .

    This is my java class :

    private byte[] brandingLogo;

    @Column(name = “branding_logo”)
    @Lob
    public byte[] getBrandingLogo() {
    return this.brandingLogo;
    }

    public void setBrandingLogo(byte[] brandingLogo) {
    this.brandingLogo = brandingLogo;
    }

    public void fileUploadListener(UploadEvent event) throws Exception{

    upload = event.getUploadItem();
    if (upload.isTempFile()) {
    file = upload.getFile();
    } else {
    ByteArrayOutputStream b = new ByteArrayOutputStream();
    b.write(upload.getData());
    }
    }

    public UploadItem getUpload() {
    return upload;
    }

    public void setUpload(UploadItem upload) {
    this.upload = upload;
    }

    public File getFile() {
    return file;
    }

    public void setFile(File file) {
    this.file = file;
    }

    But when i store it…
    id | branding_logo
    —+—————
    1 | 206533
    2 | 206534
    3 | 206535
    9 | 206543
    7 | 206537

    How can i identify that is store the image… in database..
    I guess it store sequence number…

    Please help me…
    shashi

  4. virgo47 says:

    I guess there would be null in branding_logo if there is no image. Byte array is stored elsewhere – this is PostgreSQL’s magic. Data are stored in some “secret” Postgresql store and oid is only referencing them. Can you obtain the image back from the entity? I can’t understand your problem – you don’t mention if it works or not. It works for me. I can display the picture directly with seams tag referencing the property of the entity.

    I wrote everything I know into the blog post. Sorry.

  5. shashi says:

    In then front end this is my xhtml file:
    —————————————–

    …..
    ….. [ Other Text Fields…..]
    ……

    LOGO

    …..
    ……
    …..
    ….

    Java Entity class :
    ——————

    ….
    …..
    …..

    private byte[] brandingLogo;

    …..
    …..

    @Column(name = “branding_logo”)
    @Lob
    public byte[] getBrandingLogo() {
    return this.brandingLogo;
    }
    public void setBrandingLogo(byte[] brandingLogo) {
    this.brandingLogo = brandingLogo;
    }

    …..
    …..
    …..

    My JavaHome Class :
    ——————-

    @In(scope=ScopeType.SESSION,required=false)
    @Out(scope=ScopeType.SESSION, required=false)
    File file;

    @In(scope=ScopeType.SESSION,required=false)
    @Out(scope=ScopeType.SESSION, required=false)
    UploadItem upload;

    /* ****** File upload section ***** */

    // UploadItem upload;
    public void fileUploadListener(UploadEvent event) throws Exception{
    upload = event.getUploadItem();
    if (upload.isTempFile()) {
    file = upload.getFile();
    } else {
    ByteArrayOutputStream b = new ByteArrayOutputStream();
    b.write(upload.getData());
    }
    }
    public UploadItem getUpload() {
    return upload;
    }
    public void setUpload(UploadItem upload) {
    this.upload = upload;
    }
    public File getFile() {
    return file;
    }
    public void setFile(File file) {
    this.file = file;
    }

    ————————————————————————
    public String persist() {
    try {
    SaasTenants saasTenants = getInstance();

    /* ****** File upload section ***** */
    if(upload!=null){
    saasTenants.setBrandingLogo(file.getAbsolutePath().getBytes());
    String fileName = upload.getFileName();
    int postion = fileName.lastIndexOf(‘.’);
    saasTenants.setLogoExtension(fileName.substring(postion, fileName.length()));
    }

    saasTenants.setCreationDate(new Date());
    saasTenants.setUpdateDate(new Date());

    entityManager.persist(saasTenants);
    }

    ——————————————————————————

    Sorry for not mentioning if it works or not….

    I am using
    richfaces-ui-3.2.1.GA.jar
    richfaces-api-3.2.1.GA.jar
    richfaces-impl-3.2.1.GA.jar files

    In richfaces i set immediateUpload=”true” so when i select a image file
    I calls then fileUploadListener method in java home class….

    UploadItem upload; (for getting the extension..)
    File file; ( for getting the upload file…)

    from this object i can able to get bytes of the selected image and extension..setBrandingLogo
    I put this object in session scope…

    Then i entered all the textfield like…
    name
    Address… etc..

    When i submit the form i get the byte from the (File file;) object and set to branding..
    at the time i have byte array… with values…

    Then i call persist() in java Home class to insert image…

    Please can u suggest me is this the right way to do it….
    shashi

  6. shashi says:

    Here is xhtml file…

  7. shashi says:

    my xhtml file..

    rich:fileUpload id=”fileUploadID” immediateUpload=”true”
    fileUploadListener=”#{saasTenantsHome.fileUploadListener}”
    listWidth=”460″ listHeight=”60″
    immediate=”true”

  8. virgo47 says:

    I don’t know, I’ve never tried upload. Please try to display the image. I can’t help you. Try something like:

    <s:graphicImage value=”#{entity.picture}”…/>

    If it displays the image, it’s good. I really can’t help you more – your questions are probably better for forums: http://seamframework.org/Community/Forums

  9. vl_avan says:

    I had the same problem with
    @Lob
    @Basic(fetch = FetchType.LAZY, optional = false)
    public byte[] getContentData() {
    return contentData;
    }

    The solution is to omit @Lob annotation leaving just

    @Basic(fetch = FetchType.LAZY, optional = false)
    public byte[] getContentData() {
    return contentData;
    }

  10. Brian Krahmer says:

    I ran across this same error message. I solved it by setting my object’s byte[] field to a new byte[0] instead of null before saving it into the database.

    cheers

  11. Another Person says:

    This problem can be solved by changing the blob field datatype in the table from oid to bytea.

    alter table your_table_name drop field blob_field_name;
    alter table your_table_name add field blob_field_name bytea;

  12. Pingback: proper hibernate annotation for byte[] | Ask Programming & Technology

  13. Pingback: proper hibernate annotation for byte[] - TecHub

  14. Pingback: proper hibernate annotation for byte[] - BlogoSfera

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s