Native SQL4-call stored procedure、將資料庫的資料變成 Map (Hibernate3.x 三十九)

※查詢stored procedure

增加一支function

CREATE OR REPLACE
FUNCTION HELLO_EMP(pk IN EMP.EMPNO%TYPE) RETURN SYS_REFCURSOR AS
    ST_CURSOR SYS_REFCURSOR;
BEGIN
    OPEN ST_CURSOR FOR
    SELECT * FROM EMP WHERE EMPNO = pk;
    RETURN ST_CURSOR;
END;

※官方說要用Oracle9或10的SYS_REFCURSOR，所以我將方言改成9i或10g，只打9(沒有i)不行，否則會有兩個錯誤
Problem while trying to load or access OracleTypes.CURSOR value
Class org.hibernate.dialect.Oracle9Dialect can not access a member of class oracle.jdbc.driver.OracleTypes with modifiers ""

增加一支procedure

CREATE OR REPLACE PROCEDURE SELECT_EMP (rtn OUT SYS_REFCURSOR, empno_pk IN EMP.EMPNO%TYPE) AS
BEGIN
    OPEN rtn FOR
    SELECT * FROM EMP WHERE EMPNO = empno_pk;
END SELECT_EMP;

※官方說第一個參數一定是OUT

Emp.hbm.xml

<class>
    <!--...-->
</class>
<sql-query name="hw" callable="true">
    <return 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>
    { ? = call hello_emp(:pk) }
    <!-- { call SELECT_EMP(?, :pk) } -->
</sql-query>

※callable="true"一定要打，不然會出「遺失 IN 或 OUT 參數」的錯

※每個欄位都要打，和addEntity一樣

測試類

Query q = s.getNamedQuery("hw");
q.setParameter("pk", 7369);
    
Emp e = (Emp) q.uniqueResult();
System.out.println(e.getDept().getDname() + "\n");

※官方還說Native call syntax is not supported.，感覺是說createSQLQuery不支援call，所以我不用NamedQuery改下SQLQuery q = s.createSQLQuery("{ ? = call hello_emp(:pk) }");
這時不管有沒有加addEntity都會出錯
而官方也沒有寫annotation的方式，所以我寫了也是出錯，應該是Hibernate3沒支援吧！

改下SQLQuery q = s.createSQLQuery("select hello_emp(7369) from dual");
出現一個錯誤：No Dialect mapping for JDBC type: -10
解決方法如下：
寫一個class繼承資料庫的Dialect，可看API的org.hibernate.dialect

public class Dialect extends Oracle8iDialect {
    public Dialect() {
        super();
        super.registerHibernateType(Types.DECIMAL, StandardBasicTypes.BIG_DECIMAL.getName());
        super.registerHibernateType(-10, StandardBasicTypes.STRING.getName());
    }
}

※因為錯誤訊息顯示「-10」，所以打-10

※hibernate.cfg.xml也要改自己的Dialect
<property name="hibernate.dialect">util.Dialect</property>

這時雖然不會錯了，但撈出來永遠是null，我在資料庫的軟體使用這個語法是有值的，不知道為什麼？

※將資料庫的資料變成Map

如果想偷懶，不想用hbm可用這招

return (Map<String, Object>) super.getSession()
.createSQLQuery(" SELECT * FROM TABLE WHERE XXX = :xxx").setParameter("xxx", xxx)
.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).uniqueResult();

※也可以.list()，回傳值改成List<Map<String, Object>