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 ...