How to read bytea image data from PostgreSQL with JPA?

Question

I have PostgreSQL database and there is column 'image' with datatype 'bytea'. I cannot modify columns or database configurations. JPA annotated POJO contains followign mapping

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

The returned data is in following format (this is just a sample)

WF5ClN6RlpLZ0hJTUdNQ1FJWmkwcFVGSUdNQ0lDWUE5TUEvanRFeElwK2x0M2tBQUFBQVNVVk9SSzVDWUlJPQo=

When I write this data to file (.jpeg) photo viewer says "this is corrupted file". I also understand that actual image byte data looks different from above sample. I read some blogs which mentioned that PostgreSQL applies hexadecimal conversion to bytea data. How to restore it to original data with or without JPA ?

Database - PostgresSQL Version 9.5.1

Driver

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.4-1205-jdbc41</version>
</dependency>

Answer 1

Try to annotate you entity with @Lob

@Lob
@Column(name="image")
private byte[] image;

If you are using hibernate implementation you can add @Type(type="org.hibernate.type.BinaryType") in column too.

@Lob
@Column(name="image")
@Type(type="org.hibernate.type.BinaryType")
private byte[] image;

Answer 2

ImageEntity

package com.example;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class ImageEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public byte[] getImage() {
        return image;
    }

    public void setImage(byte[] image) {
        this.image = image;
    }
}

ImageRepository

package com.example;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface ImageRepository extends JpaRepository<ImageEntity, Long> {
}

Test

package com.example;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;

import javax.annotation.Resource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import junit.framework.TestCase;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = TestApplication.class)
public class ImageDaoTest {

    @Resource
    private ImageRepository imageRepository;

    @Test
    public void testImage() throws IOException {

        // Read an image from disk. Assume test.png exists
        ByteArrayOutputStream out = new ByteArrayOutputStream();

        try (InputStream in = getClass().getResourceAsStream("test.png")) {
            int length;
            byte[] buffer = new byte[1024];
            while ((length = in.read(buffer)) != -1) out.write(buffer, 0, length);
        }

        byte[] image = out.toByteArray();

        // Store image to DB
        ImageEntity imageEntiry = new ImageEntity();
        imageEntiry.setImage(image);
        long imageEntiryId = imageRepository.save(imageEntiry).getId();

        // Retrieve image from DB
        ImageEntity resultImageEntiry = imageRepository.findOne(imageEntiryId);
        byte[] resultImage = resultImageEntiry.getImage();

        // Compare retrieved image with source image by byte to byte comparison
        for (int i = 0; i < resultImage.length; i++) {
            TestCase.assertEquals(image[i], resultImage[i]);
        }

    }

}

It works against Postgres 9.5.0-1 with 9.4.1207.jre7 jdbc driver.

Answer 3

The returned Data looks as if it is base64 encoded. You have to decode it back to binary data before writing to file.

For further information to decode look here