Native SQL2-使用XML設定 (Hibernate3.x 三十七)

第三十六篇是將SQL寫在程式裡，也可以寫在XML和Annotation，第六篇的將SQL或HQL分離有稍為講到

以下程式碼可對照三十六篇的例子，主要是以下三個
<return />就是SQLQuery.addEntity()
<return-join />就是SQLQuery.addJoin()
<return-scalar />就是SQLQuery.addScalar()

※Scalar查詢

Emp.hbm.xml

<class>
    <!--...-->
</class>
    
<sql-query name="xxx">
    SELECT * FROM EMP WHERE SAL >= :sal
</sql-query>
    
<sql-query name="ooo">
    <return-scalar column="DEPTNO" type="string" />
    <return-scalar column="COMM" type="integer" />
    <return-scalar column="HIREDATE" type="date" />
    SELECT * FROM EMP WHERE SAL >= :sal
</sql-query>

測試類

Session s = HibernateUtil2.getSession();
Transaction tx = s.beginTransaction();
try {
    System.out.println("方法一");
    Query q1 = s.getNamedQuery("xxx");
    q1.setInteger("sal", 3000);
    
    @SuppressWarnings("unchecked")
    List<Object[]> list1 = q1.list();
    
    for (Object[] oA : list1) {
        for (Object o : oA) {
            System.out.println(o);
        }
        System.out.println();
    }
    System.out.println("=============\n方法二");
    
    Query q2 = s.getNamedQuery("ooo");
    q2.setInteger("sal", 3200);
    
    @SuppressWarnings("unchecked")
    List<Object[][]> list2 = q2.list();
    
    for (Object[] oA : list2) {
        for (Object o : oA) {
            System.out.println(o);
        }
        System.out.println();
    }
} catch (Exception e) {
    tx.rollback();
    System.err.println("例外錯誤！");
    e.printStackTrace();
} finally {
    if (s.isOpen()) {
        s.close();
    }
}

※Entity查詢

Emp.hbm.xml

<sql-query name="xxx">
    <return class="vo.Emp" />
    SELECT * FROM EMP WHERE SAL >= :sal
</sql-query>
    
<sql-query name="ooo">
    <return alias="e" class="vo.Emp" />
    <return alias="d" class="vo.Dept" />
    SELECT e.*, d.* FROM EMP e, DEPT d WHERE e.SAL >= :sal
</sql-query>

測試類

Query q = s.getNamedQuery("xxx");
q.setInteger("sal", 3000);
    
@SuppressWarnings("unchecked")
List<Emp> list = q.list();
    
for (Emp e : list) {
    System.out.println(e.getEname());
    System.out.println(e.getDept().getDname() + "\n");
}
    
System.out.println("====================");
Query q2 = s.getNamedQuery("ooo");
q2.setInteger("sal", 3000);
    
@SuppressWarnings("unchecked")
List<Object[]> list2 = q2.list();
    
for (Object[] oA : list2) {
    for (Object o : oA) {
        if (o instanceof Emp) {
            Emp emp = (Emp) o;
            System.out.println(emp.getEname());
        } else if (o instanceof Dept) {
            Dept dept = (Dept) o;
            System.out.println(dept.getDname());
        }
    }
    System.out.println();
}

※處理關聯和集合

Emp.hbm.xml

<sql-query name="ooo">
    <return alias="e" class="vo.Emp" />
    <return-join alias="d" property="e.dept" />
    SELECT e.*, d.* FROM EMP e, DEPT d WHERE e.DEPTNO = d.DEPTNO AND e.SAL >= :sal
</sql-query>

測試類

Query q = s.getNamedQuery("ooo");
q.setInteger("sal", 3000);
    
@SuppressWarnings("unchecked")
List<Object[]> list = q.list();
    
for (Iterator<Object[]> it = list.iterator(); it.hasNext();) {
    Object[] obj = it.next();
    Emp emp = (Emp) obj[0];
    Dept dept = (Dept) obj[1];
    
    System.out.println(emp.getEname());
    System.out.println(dept.getDname() + "\n");
}

※回傳多個Entity 

Emp.hbm.xml

<sql-query name="ooo">
    <return alias="e1" class="vo.Emp" />
    <return alias="e2" class="vo.Emp" />
    SELECT {e1.*}, {e2.*} FROM EMP e1, EMP e2 WHERE e1.MGR = e2.EMPNO
</sql-query>

測試類

Query q = s.getNamedQuery("ooo");
    
@SuppressWarnings("unchecked")
List<Object[]> list = q.list();
    
for (Object[] obj : list) {
    Emp emp1 = (Emp) obj[0];
    Emp emp2 = (Emp) obj[1];
    System.out.println(emp1.getEname());
    System.out.println(emp2.getEname() + "\n");
}

※和三十六篇一樣，Alias injection names 我還是不會用

※如果資料庫的欄位名稱和java名稱不一樣，還可以這樣寫，name和column是必填

<return alias="e1" class="vo.Emp">
    <return-property name="empno" column="EMPNO" />
    <return-property name="ename" column="ENAME" />
    <return-property name="job" column="JOB" />
    <return-property name="mgr" column="MGR" />
    <return-property name="hiredate" column="HIREDATE" />
    <return-property name="sal" column="SAL" />
    <return-property name="comm" column="COMM" />
    <return-property name="dept" column="DEPTNO" />
</return>

※可是我一run就出錯了，而且有三個錯誤，如下：
Errors in named queries: ooo
Could not parse mapping document from resource vo/Dept.hbm.xml
Duplicate collection role mapping vo.Dept.setEmp
網路上也找了很久，還是找不到原因

※resultset

也就是提供一個公用的地方，讓很多的<sql-query>去映射，這個地方就是<resultset>

Emp.hbm.xml

<resultset name="aaa">
    <return alias="e" class="vo.Emp" />
    <return alias="d" class="vo.Dept" />
</resultset>
    
<sql-query name="ooo" resultset-ref="aaa">
    SELECT e.*, d.* FROM EMP e, DEPT d WHERE e.DEPTNO = d.DEPTNO
</sql-query>

測試類

Query q = s.getNamedQuery("ooo");
// SQLQuery q =
// s.createSQLQuery("SELECT e.*, d.* FROM EMP e, DEPT d WHERE e.DEPTNO = d.DEPTNO");
// q.setResultSetMapping("aaa");
    
@SuppressWarnings("unchecked")
List<Object[]> list = q.list();
    
for (Iterator<Object[]> it = list.iterator(); it.hasNext();) {
    Object[] obj = it.next();
    Emp emp = (Emp) obj[0];
    Dept dept = (Dept) obj[1];
    
    System.out.println(emp.getEname());
    System.out.println(dept.getDname() + "\n");
}

※官方提供兩個方法，註解的就是另一個方法，使用一個叫setResultSetMapping去關聯到xml的resultset的名稱




※第三十六篇還有回傳非管理的Entity，但官方沒寫，我也沒試出來，應該是沒有吧！