2014年4月8日

hibernate 處理 blob

本篇介紹如何將檔案存入資料庫與從資料庫取出檔案

建立儲存blob資料的table

mysql要使用mediumblob
create table imagestore
(
   imagestoreseq        int(12) not null auto_increment,   
   image               mediumblob,   
   primary key (imagestoreseq)
);

postgresql要使用bytea
create table imagestore (
   imagestoreseq        SERIAL               not null,   
   image               bytea      null,
   constraint PK_IMAGESTORE primary key (imagestoreseq)
);

建立對應到此table的class

@Entity
@Table(name = "imagestore")
public class Imagestore {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(unique = true, nullable = false)
    private Long imagestoreseq;

    //檔案會存到此欄位
    private byte[] image; 

    public Imagestore() {
        super();
    }

    public Long getImagestoreseq() {
        return imagestoreseq;
    }

    public void setImagestoreseq(Long imagestoreseq) {
        this.imagestoreseq = imagestoreseq;
    }

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

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

}

檔案存入db

//將檔案轉成byte
File file = new File("/Users/james/a.jpg");
byte[] imageData = new byte[(int) file.length()];

try {
    FileInputStream fileInputStream = new FileInputStream(file);
    fileInputStream.read(imageData);
    fileInputStream.close();
} catch (Exception e) {
    e.printStackTrace();
}

//將byte存入db       
Session session = sessionFactory.openSession();
Transaction tx = null;
try {
    tx = session.beginTransaction();
    Imagestore image = new Imagestore();
    image.setImage(imageData);
    session.save(image);
    tx.commit();
} catch (Exception e) {
    if (tx != null) {
        try {
            tx.rollback();
        } catch (Exception ignore) {
        }
    }
    throw e;
} finally {
    session.close();
}

從db取出檔案

Session session = sessionFactory.openSession();
Imagestore imagestore = null;

try {
    imagestore = session.get(Imagestore.class, 1);
} catch (Exception e) {
    logger.error("error:", e);
} finally {
    session.close();
}

if (imagestore != null) {
    byte[] img1 = imagestore.getImage();
    if (img1 != null) {

        try {
            FileOutputStream fos = new FileOutputStream("/Users/james/b.jpg");
            fos.write(img1);
            fos.close();
        } catch (Exception e) {
            logger.error("e", e);
        }
    }
}

註:

如果存檔案進mysql時出現com.mysql.jdbc.PacketTooBigException: Packet for query is too large錯誤的話代表要存的檔案大小已經超過mysql的預設值,此時要做的是在mysql的設定檔my.cnf裡指定packet的大小例如max_allowed_packet = 20M

參考資料