Blog of Bruce: 一對多雙向FK關聯 (Hibernate3.x 二十六)

※一對多只有FK關聯，因為PK不能重覆
※而因為是雙向，所以一對多也是多對一，看你站在哪個角度想而已

※準備工作

DROP TABLE STUDENT PURGE;
DROP TABLE CLAZZ PURGE;
    
CREATE TABLE CLAZZ(
    CID NUMBER(5),
    CNAME VARCHAR(15),
    CONSTRAINT CLAZZ_PK PRIMARY KEY(CID)
);
    
CREATE TABLE STUDENT(
    SID NUMBER(5),
    SNAME VARCHAR(15),
    CLAZZ_ID NUMBER(5),
    CONSTRAINT STUDENT_PK PRIMARY KEY(SID),
    CONSTRAINT STUDENT_FK FOREIGN KEY(CLAZZ_ID) REFERENCES CLAZZ(CID) ON DELETE CASCADE
);
    
CREATE SEQUENCE CLAZZ_SEQ;
CREATE SEQUENCE STUDENT_SEQ;

※一個班級有很多學生，在多那邊多一個FK欄位與一那邊的PK相關聯

※XML設定

hibernate.cfg.xml

<hibernate-configuration>
    <session-factory>
        <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.OracleDialect</property>
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.format_sql">true</property>
        <!-- <property name="hibernate.use_sql_comments">true</property> -->
    
        <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
        <property name="hibernate.current_session_context_class">thread</property>
    
        <mapping resource="vo/Clazz.hbm.xml" />
        <mapping resource="vo/Student.hbm.xml" />
    </session-factory>
</hibernate-configuration>

clazz.hbm.xml

<hibernate-mapping>
    <class name="vo.Clazz" table="CLAZZ">
        <id name="cid" type="java.lang.Integer">
            <column name="CID" />
            <generator class="sequence">
                <param name="sequence">CLAZZ_SEQ</param>
            </generator>
        </id>
    
        <property name="cname" type="java.lang.String">
            <column name="CNAME" />
        </property>
    
        <set name="setStudent" inverse="true" cascade="all" lazy="false" fetch="join">
            <key column="CLAZZ_ID" />
            <one-to-many class="vo.Student" />
        </set>
    </class>
</hibernate-mapping>

student.hbm.xml

<class name="vo.Student" table="STUDENT">
    <id name="sid" type="java.lang.Integer">
        <column name="SID" />
        <generator class="sequence">
            <param name="sequence">STUDENT_SEQ</param>
        </generator>
    </id>
    
    <property name="sname" type="string">
        <column name="SNAME" />
    </property>
    
    <many-to-one name="clazz" column="CLAZZ_ID" not-null="true" lazy="false" fetch="join" />
</class>

Clazz.java

public class Clazz {
    private Integer cid;
    private String cname;
    private Set<Student> setStudent = new HashSet<>();
    // setter/getter...
}

Student.java

public class Student {
    private Integer sid;
    private String sname;
    private Clazz clazz;
    // setter/getter...
}

新增測試

Session s = HibernateUtil2.getSession();
Transaction tx = s.beginTransaction();
try {
    Clazz clazz = new Clazz();
    clazz.setCname("三年丁班");
    
    for (int i = 1; i <= 5; i++) {
        Student stu = new Student();
        stu.setSname("Mary" + i + "號");
        stu.setClazz(clazz);
        clazz.getSetStudent().add(stu);
    }
    
    s.save(clazz);
    tx.commit();
} catch (Exception e) {
    tx.rollback();
    System.err.println("例外錯誤！");
    e.printStackTrace();
} finally {
    if (s.isOpen()) {
        s.close();
    }
}

※只有新增Clazz也可以，這就是網路上所謂的快速註冊與詳細註冊

※如果要新增Student，要設定cascade

修改測試

Clazz clazz = new Clazz();
clazz.setCid(16);
clazz.setCname("九年戊班");
    
String sql = " SELECT * FROM STUDENT WHERE CLAZZ_ID = :id ";
SQLQuery query = s.createSQLQuery(sql)
        .addScalar("SID", StandardBasicTypes.INTEGER)
        .addScalar("SNAME", StandardBasicTypes.STRING);
query.setParameter("id", clazz.getCid());
    
@SuppressWarnings("unchecked")
List<Object> list = query.list();
for (int i = 0; i < list.size(); i++) {
    Object[] o = (Object[]) list.get(i);
    
    Student stu = new Student();
    stu.setSid((Integer) o[0]);
    stu.setSname("第" + i + "號");
    stu.setClazz(clazz);
    clazz.getSetStudent().add(stu);
}
    
s.update(clazz);
tx.commit();

※也可以只修改Clazz

查詢Clazz

Clazz clazz = (Clazz) s.get(Clazz.class, 6);
s.close();
System.out.println(clazz.getCname());
    
for (Student stu : clazz.getSetStudent()) {
    System.out.println(stu.getSname());
}

※查詢到多的那一方會出Session is closed的錯(查不到資料也會)，所以要在Clazz設定lazy="false"

※fetch預設為select

查詢Student

Student stu = (Student) s.get(Student.class, 13);
s.close();
System.out.println(stu.getSname());
System.out.println(stu.getClazz().getCname());

※查詢到一的那一方會出Session is closed的錯(查不到資料也會)，所以要在Student設定lazy="false"

※fetch預設為select

※fetch="select"或不設定的結果：
Hibernate:
select
student0_.SID as SID1_0_,
student0_.SNAME as SNAME1_0_,
student0_.cid as cid1_0_
from
STUDENT student0_
where
student0_.SID=?

Hibernate:
select
clazz0_.CID as CID0_0_,
clazz0_.CNAME as CNAME0_0_
from
CLAZZ clazz0_
where
clazz0_.CID=?

Hibernate:
select
setstudent0_.cid as cid0_1_,
setstudent0_.SID as SID1_,
setstudent0_.SID as SID1_0_,
setstudent0_.SNAME as SNAME1_0_,
setstudent0_.cid as cid1_0_
from
STUDENT setstudent0_
where
setstudent0_.cid=?
Maza1號
六年甲班

※fetch="join"的結果：
Hibernate:
select
student0_.SID as SID1_1_,
student0_.SNAME as SNAME1_1_,
student0_.cid as cid1_1_,
clazz1_.CID as CID0_0_,
clazz1_.CNAME as CNAME0_0_
from
STUDENT student0_,
CLAZZ clazz1_
where
student0_.cid=clazz1_.CID
and student0_.SID=?

Hibernate:
select
setstudent0_.cid as cid0_1_,
setstudent0_.SID as SID1_,
setstudent0_.SID as SID1_0_,
setstudent0_.SNAME as SNAME1_0_,
setstudent0_.cid as cid1_0_
from
STUDENT setstudent0_
where
setstudent0_.cid=?
Maza1號
六年甲班

※因為是對Student查詢，所以會有查Student的語句，然後執行到要關聯時，又會發出語句，這時就看是select或join了

刪除測試

Clazz clazz = (Clazz) s.get(Clazz.class, 6);
s.delete(clazz);
tx.commit();

※刪除Clazz就會幫我們把多的一方全刪了，當然資料庫要on delete cascade

※刪除Student(就是刪除多的一方)，當然只會刪Student的一筆紀錄而已

※如果資料庫沒有可以刪除的資料就會出「java.lang.IllegalArgumentException: attempt to create delete event with null entity」的錯

※Annotation設定

hibernate.cfg.xml

<mapping class="vo.Clazz" />
<mapping class="vo.Student" />

Clazz.java

package vo;
    
import java.util.HashSet;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import org.hibernate.annotations.LazyCollection;
import org.hibernate.annotations.LazyCollectionOption;
    
@Entity
@Table
public class Clazz {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "XXX")
    @SequenceGenerator(name = "XXX", sequenceName = "CLAZZ_SEQ")
    private Integer cid;
    
    @Column
    private String cname;
    
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "clazz" )
    @LazyCollection(value = LazyCollectionOption.FALSE)
    private Set<Student> setStudent = new HashSet<>();
    
    // setter/getter...
}

※mappedBy裡的字串要對應Student裡的屬性名稱

※lazy="false"，在Annotation有兩種一種就是上面針對集合用的，一種就是下面Student.java的，針對單一使用，如果設定錯誤，也不會拋Exception，只是沒作用而已

Student.java

package vo;
    
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import org.hibernate.annotations.LazyToOne;
import org.hibernate.annotations.LazyToOneOption;
    
@Entity
@Table
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "OOO")
    @SequenceGenerator(name = "OOO", sequenceName = "STUDENT_SEQ")
    private Integer sid;

    @Column
    private String sname;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "CLAZZ_ID")
    @LazyToOne(value = LazyToOneOption.FALSE)
    private Clazz clazz;
    
    // setter/getter...
}

※測試的結果和XML設定有一點小小的不同，就是在查詢多方(Student)，而且又設定fetch=join或@ManyToOne(fetch = FetchType.EAGER)時
用Annotation設定是外連接，就是有(+)；而XML設定是內連接，沒有(+)

※其實搜尋的欄位順序和欄位的數目也不太一樣，可以自行試試

※Inverse

※在一方(Clazz)時，inverse預設為false；在多方(Student)無此設定
※查詢不會影響
※inverse就是逆、反的意思，在一方設false，當然就是自己(一方)維護關係；
設成true就是Student(多方)維護關係

※設定為true時控制台輸出語句如下：

新增：

Hibernate:
select
CLAZZ_SEQ.nextval
from
dual

Hibernate:
select
STUDENT_SEQ.nextval
from
dual

Hibernate:
select
STUDENT_SEQ.nextval
from
dual

Hibernate:
insert
into
CLAZZ
(CNAME, CID)
values
(?, ?)

Hibernate:
insert
into
STUDENT
(SNAME, CLAZZ_ID, SID)
values
(?, ?, ?)

Hibernate:
insert
into
STUDENT
(SNAME, CLAZZ_ID, SID)
values
(?, ?, ?)

修改：

Hibernate:
SELECT
*
FROM
STUDENT
WHERE
CLAZZ_ID = ?

Hibernate:
update
CLAZZ
set
CNAME=?
where
CID=?

Hibernate:
update
STUDENT
set
SNAME=?,
CLAZZ_ID=?
where
SID=?

Hibernate:
update
STUDENT
set
SNAME=?,
CLAZZ_ID=?
where
SID=?

刪除：

Hibernate:
select
clazz0_.CID as CID0_0_,
clazz0_.CNAME as CNAME0_0_
from
CLAZZ clazz0_
where
clazz0_.CID=?

Hibernate:
select
setstudent0_.CLAZZ_ID as CLAZZ3_0_1_,
setstudent0_.SID as SID1_,
setstudent0_.SID as SID1_0_,
setstudent0_.SNAME as SNAME1_0_,
setstudent0_.CLAZZ_ID as CLAZZ3_1_0_
from
STUDENT setstudent0_
where
setstudent0_.CLAZZ_ID=?

Hibernate:
delete
from
STUDENT
where
SID=?

Hibernate:
delete
from
STUDENT
where
SID=?

Hibernate:
delete
from
CLAZZ
where
CID=?

※設定為false或不設定時控制台輸出語句如下：

新增：

結果為設定true的語句加上下面兩條SQL
Hibernate:
update
STUDENT
set
CLAZZ_ID=?
where
SID=?

Hibernate:
update
STUDENT
set
CLAZZ_ID=?
where
SID=?

修改：

結果為設定true的語句加上下面三條SQL
Hibernate:
update
STUDENT
set
CLAZZ_ID=null
where
CLAZZ_ID=?

Hibernate:
update
STUDENT
set
CLAZZ_ID=?
where
SID=?

Hibernate:
update
STUDENT
set
CLAZZ_ID=?
where
SID=?

刪除：

結果為設定true的語句加上下面一條SQL
Hibernate:
update
STUDENT
set
CLAZZ_ID=null
where
CLAZZ_ID=?

※可以發現設定為false或不設多出來的語句都是針對多方的Student做處理，且都是update
不過多出來的語句似乎沒什麼用，所以在一對多大部分都是設成true，讓多方去維護關係，這樣才不會出現多的語句，影響效能

Blog of Bruce

2015年12月19日星期六

一對多雙向FK關聯 (Hibernate3.x 二十六)

※準備工作

※XML設定

hibernate.cfg.xml

clazz.hbm.xml

student.hbm.xml

Clazz.java

Student.java

新增測試

修改測試

查詢Clazz

查詢Student

刪除測試

※Annotation設定

hibernate.cfg.xml

Clazz.java

Student.java

※Inverse

※設定為true時控制台輸出語句如下：

新增：

修改：

刪除：

※設定為false或不設定時控制台輸出語句如下：

新增：

修改：

刪除：

沒有留言:

張貼留言

關於我自己

網誌存檔

2015年12月19日 星期六

一對多雙向FK關聯 (Hibernate3.x 二十六)

※準備工作

※XML設定

hibernate.cfg.xml

clazz.hbm.xml

student.hbm.xml

Clazz.java

Student.java

新增測試

修改測試

查詢Clazz

查詢Student

刪除測試

※Annotation設定

hibernate.cfg.xml

Clazz.java

Student.java

※Inverse

※設定為true時 控制台輸出語句如下：

新增：

修改：

刪除：

※設定為false或不設定時 控制台輸出語句如下：

新增：

修改：

刪除：

沒有留言:

張貼留言

2015年12月19日星期六

※設定為true時控制台輸出語句如下：

※設定為false或不設定時控制台輸出語句如下：