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

參考資料

• http://howtodoinjava.com/2013/08/30/hibernate-example-of-insertselect-blob-from-database/

• http://blog.csdn.net/fly0744/article/details/13623079

Hibernate ResultTransformer

在開發報表功能時我們常會遇到一張報表所要顯示的資料是來自多個table，例如我們要開發一個訂單查詢的報表，其中資料來源是來自下列2個table

訂單table，欄位如下

orderid(訂單編號), customerid(會員id), orderdate(訂單日期)

會員table，欄位如下

customerid(會員id), name(會員姓名), phone(會員電話), email(會員email)

假設報表要顯示的欄位有orderid(訂單編號), name(會員姓名), phone(會員電話), email(會員email)，通常我們會寫下列的程式碼來取得資料

1.先定義Helper Class, 此class並不會對映到一個實體的table, 僅方更讓我們將查詢結果建立成一個一個的物件

public class OrderReport {

    private String orderid;
    private String name;
    private String phone;
    private String email;

    public String getOrderid() {
        return customerid;
    }

    public void setOrderid(String orderid) {
        this.customerid = customerid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

}

2.接著寫hibernate code

List<OrderReport> list = new ArrayList<OrderReport>();

String sql = "select o.orderid, c.name, c.phone, c.email from orderform o, customer c where o.customerid = c.customerid";

SQLQuery query = session.createSQLQuery(sql);
List<Object[]> list = query.list();
for (int i = 0; i < list.size(); i++) {
    Object[] m = list.get(i);
    String orderid = m[0].toString();
    String name = m[1].toString();
    String phone = m[2].toString();
    String email = m[3].toString();

    OrderReport report = new OrderReport();
    report.setOrderid(orderid);
    report.setName(name);
    report.setPhone(phone);
    report.setEmail(email);

    list.add(report);   
}

//..other code ...

Hibernate有提供ResultTransformer可以自動的將查詢結果轉成指定的類別讓程式碼更簡短，如下

String sql = "select o.orderid, c.name, c.phone, c.email from orderform o, customer c where o.customerid = c.customerid";

SQLQuery query = session.createSQLQuery(sql);
query.addScalar("orderid", StandardBasicTypes.STRING);
query.addScalar("name", StandardBasicTypes.STRING);
query.addScalar("phone", StandardBasicTypes.STRING);
query.addScalar("email", StandardBasicTypes.STRING);

//Transformers
query.setResultTransformer(Transformers.aliasToBean(OrderReport.class));

List<OrderReport> list = query.list();

//..other code ...

Hibernate addEntity, addScalar

使用hibernate的好處是在下hql語法查詢時可以自動的將table的一個record自動轉成一個物件， 例如一個user table含有下列欄位id, name, phone, groupid

我們可以依此table schema來建立下列class

public class User {

    private String id;
    private String name;
    private String phone;
    private String groupid;

    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getGroupid() {
        return phone;
    }
    public void setGroupid(String groupid) {
        this.phone = phone;
    }
}

使用下列hql查詢我們即可得到User的物件

Query query = session.createQuery("from User");
List<User> list = query.list();

但實務上我們的的sql查詢條件可能需要join多個table，要如何使用我們較習慣的sql查詢語法且又能夠將查詢結果自動的轉成物件？我們可以使用hibernate的addEntity, addScalar來達成這個目的

假設我們有下列2個table

• user(人員) 含有下列欄位 id, name(姓名), phone(電話), address(地址), groupid(所屬單位id)

• group(單位) 含有下列欄位 groupid(單位id), groupname(單位名字)

• 註：user(人員)的groupid是關連到group(單位)的groupid

假設我們要查出電話是0912開頭的人員資料與該人所屬的單位名稱，我們可以使用下列sql

select user.*, group.groupname groupname from user, group where user.phone like '0912%' and user.groupid = group.groupid

下列為查詢結果

id    name    phone       groupid  groupname
1     james   0912345678  pg       開發部
3     wade    0912888888  sa       分析部

註：前4個欄位來自user table, 最後一個欄位來自group table

如果我們要將來自user table的欄位(id, name, phone, groupid)自動的轉成user物件的話我們可以用下列的語法

String sql = "select user.*, group.groupname groupname from user, group where user.phone like '0912%' and user.groupid = group.groupid";

SQLQuery sqlQuery = session.createSQLQuery(sql);
sqlQuery.addEntity("user", User.class); //將查詢結果的user.*轉成User物件
sqlQuery.addScalar("groupname", StandardBasicTypes.STRING); //將查詢結果的groupname轉成字串

List<Object[]> datas = sqlQuery.list();
for(Object[] objs : datas) {
    User user = (User) objs[0];
    String groupName = (String) objs[1];

    //執行您要的處理邏輯....
}