Blog of Bruce: HQL (Hibernate3.x 三十四)

準備工作

DROP TABLE EMP PURGE;
DROP TABLE DEPT PURGE;
    
CREATE TABLE DEPT (
    DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME VARCHAR2(14) ,
    LOC VARCHAR2(13) 
) ;
    
CREATE TABLE EMP (
    EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
    
INSERT INTO DEPT VALUES    (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES    (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES    (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES    (40,'OPERATIONS','BOSTON');
    
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-12-1980','DD-MM-YYYY'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('20-2-1981','DD-MM-YYYY'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('22-2-1981','DD-MM-YYYY'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,TO_DATE('2-4-1981','DD-MM-YYYY'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('28-9-1981','DD-MM-YYYY'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('1-5-1981','DD-MM-YYYY'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('9-6-1981','DD-MM-YYYY'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('19-04-1987','DD-MM-YYYY')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('17-11-1981','DD-MM-YYYY'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('8-9-1981','DD-MM-YYYY'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('23-05-1987','DD-MM-YYYY')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,TO_DATE('3-12-1981','DD-MM-YYYY'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,TO_DATE('3-12-1981','DD-MM-YYYY'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,TO_DATE('23-1-1982','DD-MM-YYYY'),1300,NULL,10);
    
CREATE SEQUENCE DEPT_SEQ;
CREATE SEQUENCE EMP_SEQ;

hibernate.cfg.xml

<session-factory name="">
    <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
    <property name="hibernate.connection.url">jdbc:oracle:thin:@127.0.0.1:1521:orcl</property>
    <property name="hibernate.connection.username">username</property>
    <property name="hibernate.connection.password">password</property>
    <property name="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</property>
    <property name="hibernate.show_sql">true</property>
    <property name="hibernate.format_sql">true</property>
    <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
    <property name="hibernate.current_session_context_class">thread</property>
    <property name="hibernate.default_schema">your schema</property>
    
    <!-- <mapping resource="vo/Dept.hbm.xml"/> -->
    <!-- <mapping resource="vo/Emp.hbm.xml"/> -->
    
    <mapping class="vo.Dept" />
    <mapping class="vo.Emp" />
</session-factory>

Dept.hbm.xml

<class name="vo.Dept" table="DEPT">
    <id name="deptno" type="int">
        <column name="DEPTNO" />
        <generator class="sequence">
            <param name="sequence">DEPT_SEQ</param>
        </generator>
    </id>
    
    <property name="dname" type="java.lang.String">
        <column name="DNAME" />
    </property>
    
    <property name="loc" type="java.lang.String">
        <column name="LOC" />
    </property>
    
    <set name="setEmp" inverse="true" cascade="all" lazy="false" fetch="join">
        <key column="DEPTNO" />
        <one-to-many class="vo.Emp" />
    </set>
</class>

Emp.hbm.xml

<class name="vo.Emp" table="Emp">
    <id name="empno" type="int">
        <column name="EMPNO" />
        <generator class="sequence">
            <param name="sequence">EMP_SEQ</param>
        </generator>
    </id>
    
    <property name="ename" type="java.lang.String" length="4">
        <column name="ENAME" length="4" />
    </property>
    
    <property name="job" type="java.lang.String">
        <column name="JOB" />
    </property>
    
    <property name="mgr" type="java.lang.Integer">
        <column name="MGR" />
    </property>
    
    <property name="hiredate" type="java.util.Date">
        <column name="HIREDATE" />
    </property>
    
    <property name="sal" type="java.lang.Double">
        <column name="SAL" />
    </property>
    
    <property name="comm" type="java.lang.Double">
        <column name="COMM" />
    </property>
    
    <!-- <property name="deptno" type="java.lang.Integer"> -->
    <!--     <column name="DEPTNO" /> -->
    <!-- </property> -->
    
    <many-to-one name="dept" column="DEPTNO" not-null="true" class="vo.Dept" lazy="false" fetch="join" />
</class>

Dept.java

@Entity
@Table
public class Dept {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "OOO")
    @SequenceGenerator(name = "OOO", sequenceName = "DEPT_SEQ")
    private int deptno;
    
    private String dname;
    private String loc;
    
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, mappedBy = "dept")
    @LazyCollection(value = LazyCollectionOption.FALSE)
    private Set<Emp> setEmp = new HashSet<>();
    // setter/getter...
}

Emp.java

@Entity
@Table
public class Emp {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "XXX")
    @SequenceGenerator(name = "XXX", sequenceName = "EMP_SEQ")
    private int empno;
    
    private String ename;
    private String job;
    private Integer mgr;
    
    @Temporal(TemporalType.TIMESTAMP)
    private Date hiredate;
    
    private Double sal;
    private Double comm;
    // private Integer deptno;
    
    @ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name = "DEPTNO")
    @LazyToOne(value = LazyToOneOption.FALSE)
    private Dept dept;
    // setter/getter...
}

※XML和Annotation設定都有，看要使用哪一種都可以

※這是一對多雙向，尤於關聯欄位為DEPTNO，所以多方的deptno已經關聯好了，不能設定，否則會出錯

測試類

Session s = HibernateUtil2.getSession();
Transaction tx = s.beginTransaction();
try {
    String hql = " select e.ename, d.deptno from vo.Emp e right join e.dept d ";
    
    Query hqlQuery = s.createQuery(hql);
    @SuppressWarnings("unchecked")
    List<Object[]> list = hqlQuery.list();
    System.out.println(list.size());// 有15筆
    
    for (Object[] oArray : list) {
        for (Object o : oArray) {
            System.out.print(o + " ");
        }
        System.out.println();
    }
} catch (Exception e) {
    tx.rollback();
    System.err.println("例外錯誤！");
    e.printStackTrace();
} finally {
    if (s.isOpen()) {
        s.close();
    }
}

※JOIN

※如果只能left join請參考第三十三篇

※Dept裡面有Emp，或者Emp裡面有Dept(因為是雙向)，所以要下
select 欄位 from vo.Emp e join e.dept d(e裡面的dept，不能下join Dept)
這就等同於一般SQL的select * from emp e inner join dept d on(e.deptno = d.deptno)，HQL不用關聯是因為一對多已經設定好DEPTNO為關聯字段了，如果要增加也可以，如
select e.ename, d.deptno from vo.Emp e right join e.dept d where e.job = 'CLERK'

※hql沒有on這種東西，但有with，但我下如
from vo.Emp e join e.dept d with d.dname = 'SALES'是OK的

但如果下
from Emp e left join e.dept d with d.dname = e.sal，with後面有兩張表的欄位，就會出「
org.hibernate.hql.ast.QuerySyntaxException: with-clause referenced two different from-clause elements」的錯，看錯誤訊息，看起來應該不能關聯兩張表吧！

但 from vo.Emp e join e.dept d with e.empno in(7369, 7499)
就會出「org.hibernate.hql.ast.InvalidWithClauseException: with-clause expressions did not reference from-clause element to which the with-clause was associated」的錯，看來with只能為關聯的欄位使用而已，但因為Emp和Dept的關係是雙向，所以如果想為Emp使用with，那就下
from Dept d left join d.setEmp e with e.empno = 7369，這樣子就OK了

※如果不想用原本的關聯字段，可以這樣下from vo.Emp e, vo.Dept d where e.job = d.loc，也就是cross join，但打cross join是會錯的

※from vo.Dept, vo.Emp就是cross join

※inner join、left outer join、right outer join的inner和outer可以省略；只有full join，沒有什麼full outer join，不知道差別的可參考這篇

※full join 不曉得是不是我版本問題，反正只要打類似如下：
from vo.Emp e full join e.dept d
就一定會出「org.hibernate.AssertionFailure: undefined join type 23」的錯
我用的是Oracle12c+Hibernate3.6.10，知道的回應一下吧！

※欄位和table(class)大小寫以java為主，其他大小寫都ok

※table(class)是package.className，但如果className在所有的package中只有一個，那package可省略

※Dept as d，as也可省略

※select * from vo.Dept等同from vo.Dept，*不能打

※select aaa from xxx，只查一個欄位回傳List<Object>； select aaa, bbb from xxx，查二個欄位以上回傳List<Object[]>

※fetch

fetch語法用在(inner)join fetch、left (outer) join等…，會自動幫我們把值放在對應的物件上，所以回傳值就不是List<Object[]>了

String hql = " from vo.Emp e join fetch e.dept d ";
    
Query hqlQuery = s.createQuery(hql);
    
@SuppressWarnings("unchecked")
List<Emp> list = hqlQuery.list();
System.out.println(list.size());
// s.close();
    
for (Emp e : list) {
    System.out.println(e.getEname());
    System.out.println(e.getDept().getDeptno() + "\n");
}

※right會出NullPointException，因為有一筆是有人沒部門的，所以e是null，無法再「.」下去

※如果把s.close()打開，而且不要fetch，就會出錯，這時有兩種解法，一種就是現在用的fetch，還有一種就是之前說的設定lazy="false"

※還有一種叫fetch all properties
from vo.Emp e fetch all properties where e.sal > 1500
可以強制抓取原本應該延遲加載的屬性，但我試的結果，lazy="true"還是抓不到

※隱式連接

因為之前的join都會寫出來，又叫顯式連接，以下的語法不寫join，但確已經join了，所以叫隱式連接，但我試的結果，速度好慢啊！

String hql1 = " from vo.Emp e right join fetch e.dept d where d.dname = 'SALES' ";
String hql2 = " from vo.Emp e where e.dept.dname = 'SALES' ";
    
Query hqlQuery = s.createQuery(hql2);
@SuppressWarnings("unchecked")
List<Emp> list = hqlQuery.list();
System.out.println(list.size());
    
for (Emp e : list) {
    System.out.println(e.getEname());
    System.out.println(e.getDept().getDeptno() + "\n");
}

※hql1為顯式，hql2為隱式

※建構子

在Emp.java增加建構子，增加以後，預設建構子也會不見，所以也要將預設建構子打出來

public Emp(){}
public Emp(String ename, Double sal, Dept dept){
    this.ename = ename;
    this.sal = sal;
    this.dept = dept;
}

測試類

String hql = " select new vo.Emp(ename, sal, dept) from vo.Emp e where e.job = 'SALESMAN' ";
Query hqlQuery = s.createQuery(hql);
@SuppressWarnings("unchecked")
List<Emp> list = hqlQuery.list();
System.out.println(list.size());
    
for (Emp e : list) {
    System.out.println(e.getEname());
    System.out.println(e.getSal());
    System.out.println(e.getJob());
    System.out.println(e.getDept().getDeptno() + "\n");
}

※除了job是null以外，其他都撈的到，包括Dept也可以

※List

String hql = " select new list(ename, sal, dept) from vo.Emp e where e.job = 'SALESMAN' ";
Query hqlQuery = s.createQuery(hql);
@SuppressWarnings("unchecked")
List<Object> list = hqlQuery.list();
System.out.println(list.size());
    
for (Object o : list) {
    System.out.println(o);
}

※結果：
4
[ALLEN, 1600.0, vo.Dept@7a934832]
[WARD, 1250.0, vo.Dept@7a934832]
[MARTIN, 1250.0, vo.Dept@7a934832]
[TURNER, 1500.0, vo.Dept@7a934832]

※沒有set這種東西，可以用distinct

※Map

String hql = " select new map(ename, sal, dept) from vo.Emp e where e.job = 'SALESMAN' ";
Query hqlQuery = s.createQuery(hql);
@SuppressWarnings("unchecked")
List<Map<Integer, Object>> list = hqlQuery.list();
System.out.println(list.size());
    
for (Map<Integer, Object> m : list) {
    System.out.println(m);
    System.out.println(m.get("1"));
}

※結果：
4
{2=vo.Dept@4ceddac6, 1=1600.0, 0=ALLEN}
1600.0
{2=vo.Dept@4ceddac6, 1=1250.0, 0=WARD}
1250.0
{2=vo.Dept@4ceddac6, 1=1250.0, 0=MARTIN}
1250.0
{2=vo.Dept@4ceddac6, 1=1500.0, 0=TURNER}
1500.0

※回傳全部mapping的物件

String hql = " from java.lang.Object ";
Query hqlQuery = s.createQuery(hql);
System.out.println(hqlQuery.list().size());

※一定要寫java.lang

※其他方法

select e.ename || '-' || e.sal from vo.Emp e
select concat(e.ename, '-', e.sal) from vo.Emp e
    
select current_date(), current_time(), current_timestamp() from vo.Emp e
    
select coalesce(e.comm, null, '0', null) from vo.Emp e

※前兩行一樣的效果

※還可以回傳目前日期、時間、日期時間，印出來的效果如下：
2016-01-02
19:42:04
2016-01-02 19:42:04.727

※coalesce第一個參數放想判斷的欄位，後面的欄位可以很多，如果第二個參數不是null就顯示，如果是null，就再繼續判斷第三個參數是不是null，以此類推

※nullif
select nullif(e.comm, e.comm) from vo.Emp e
兩值相等回傳null
select nullif(e.comm, e.mgr) from vo.Emp e
兩值不相等，以第一個參數為主

※extract：可針對欄位或sql的日期時間方法取值

String hql = " select extract(year from e.hiredate), extract(year from current_timestamp()) from vo.Emp e ";
Query hqlQuery = s.createQuery(hql);
    
@SuppressWarnings("unchecked")
List<Object[]> list = hqlQuery.list();
System.out.println(list.size());
    
for (Object[] oa : list) {
    for (Object o : oa) {
        System.out.println(o);
    }
}

※因為官網寫的不清楚，我參考這個網站，我沒有每個都試過

※cast(e.hiredate as date)，as後面接什麼要上網找了，官網寫得不清楚

※hour等：年月日時分秒，官網都有寫，但我hour要加8才是現在時間
select year(current_timestamp()), hour(current_timestamp()) + 8 from vo.Emp e
但對欄位使用，要注意資料庫的值有沒有時分秒，如果沒有，而又使用如second()，會出「ORA-30076: 擷取來源的擷取欄位無效」的錯

※Formula

Formula對HQL和Criteria(包括Example和DetachedCriteria)有效，但對Native SQL沒有作用
這個功能可以將原本的值改掉，或像如下的薪水*2蓋掉原來的值

Emp.java

@NamedQuery(name = "xxx", query = "from Emp")
@Entity
@Table
public class Emp {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "XXX")
    @SequenceGenerator(name = "XXX", sequenceName = "EMP_SEQ")
    private int empno;
    
    @Formula("ename || '-' || deptno")
    private String ename;
    
    @Formula("sal * 2")
    private Double sal;
    //...
}

測試類

Query q1 = s.getNamedQuery("xxx");
    
@SuppressWarnings("unchecked")
List<Emp> list = q1.list();
    
for (Emp e : list) {
    System.out.println(e.getEname());
    System.out.println(e.getSal());
}

※另外一張表的欄位dept，或另一張表Dept的欄位，我設會錯，但我設deptno竟然可以，不知道為什麼

※formula裡面的欄位以java類為主，而如果想用xml設定可如下設定

※不一定要包括原本的值，如sal不一定要sal，如打empno就會變成empno的值

※如果想顯示寫死的字串或數字，要用「'」包起來，「"」或「\"」都不行

Emp.hbm.xml

<class>
    <id>
        <!-- ... -->
    </id>
    
    <property name="ename" type="java.lang.String" length="4">
        <!-- <column name="ENAME" length="4" /> -->
        <formula>ename || '-' || deptno </formula>
    </property>
    
    <property name="sal" type="java.lang.Double" formula="sal * 2" />
    
    <!-- ... -->
</class>
    
<query name="xxx">from Emp</query>

※和column一樣有兩種方式，column可不用打
我試過寫一行的方式，有column和fomula，可以正常執行，但寫多行就會報錯，所以乾脆不打

※還可以下條件，如 formula="case when sal > 1500 then 5000 else 8000 end"

※或是下formula="(select count(*) from Emp)"，前後一定要打括號，看控制台就知道為什麼了，不過這時我發現大小寫居然都可以，而且只要一打欄位，就莫明奇妙的失效了，會變成原來的值，也就是和只設column一樣

Blog of Bruce

2016年1月2日星期六

HQL (Hibernate3.x 三十四)

準備工作

hibernate.cfg.xml

Dept.hbm.xml

Emp.hbm.xml

Dept.java

Emp.java

測試類

※JOIN

※fetch

※隱式連接

※建構子

測試類

※List

※Map

※回傳全部mapping的物件

※其他方法

※Formula

Emp.java

測試類

Emp.hbm.xml

沒有留言:

張貼留言

關於我自己

網誌存檔

2016年1月2日 星期六

HQL (Hibernate3.x 三十四)

準備工作

hibernate.cfg.xml

Dept.hbm.xml

Emp.hbm.xml

Dept.java

Emp.java

測試類

※JOIN

※fetch

※隱式連接

※建構子

測試類

※List

※Map

※回傳全部mapping的物件

※其他方法

※Formula

Emp.java

測試類

Emp.hbm.xml

沒有留言:

張貼留言

2016年1月2日星期六