多對多雙向FK關聯(關聯表有主鍵或其他欄位) (Hibernate3.x 三十)

上一篇的關聯表沒有主鍵，只有兩個欄位分別對應到兩張表，這裡多一個主鍵，當然也可以多一些其他的欄位，如新增的日期等…

※準備工作

DROP TABLE PERSON_TOILET PURGE;
DROP TABLE TOILET PURGE;
DROP TABLE PERSON PURGE;
    
CREATE TABLE PERSON(
    PID NUMBER(5),
    PNAME VARCHAR(20),
    CONSTRAINT PERSON_PK PRIMARY KEY(PID)
);
    
CREATE TABLE TOILET(
    TID NUMBER(5),
    TNAME VARCHAR(20),
    CONSTRAINT TOILET_PK PRIMARY KEY(TID)
);
    
CREATE TABLE PERSON_TOILET(
    PT_ID NUMBER(5),
    PERSON_ID NUMBER(5),
    TOILET_ID NUMBER(5),
    CONSTRAINT PERSON_TOILET_PK PRIMARY KEY(PT_ID),
    CONSTRAINT FK_PID FOREIGN KEY(PERSON_ID) REFERENCES PERSON(PID) ON DELETE CASCADE,
    CONSTRAINT FK_TID FOREIGN KEY(TOILET_ID) REFERENCES TOILET(TID) ON DELETE CASCADE
);
    
CREATE SEQUENCE TOILET_SEQ;
CREATE SEQUENCE PERSON_SEQ;
CREATE SEQUENCE PERSON_TOILET_SEQ;

※在關聯表加個主鍵

※XML設定

Person.java

public class Person {
    private Integer pid;
    private String pname;
    private Set<PersonToilet> personToilets = new HashSet<>();
    // setter/getter...
}

Toilet.java

public class Toilet {
    private Integer tid;
    private String tname;
    private Set<PersonToilet> personToilets = new HashSet<>();
    // setter/getter...
}

PersonToilet.java

public class PersonToilet {
    private Integer ptId;
    private Person centerPerson;
    private Toilet centerToilet;
    // setter/getter...
}

Person.hbm.xml

<class name="vo.Person" table="PERSON">
    <id name="pid" type="java.lang.Integer">
        <column name="PID" />
        <generator class="sequence">
            <param name="sequence">PERSON_SEQ</param>
        </generator>
    </id>
    
    <property name="pname" type="java.lang.String" column="PNAME" />
    
    <set name="personToilets" inverse="true" cascade="all" lazy="false">
        <key column="PERSON_ID" />
        <one-to-many class="vo.PersonToilet" />
    </set>
</class>

Toilet.hbm.xml

<class name="vo.Toilet" table="TOILET">
    <id name="tid" type="java.lang.Integer">
        <column name="TID" />
        <generator class="sequence">
            <param name="sequence">TOILET_SEQ</param>
        </generator>
    </id>
    
    <property name="tname" type="java.lang.String" column="TNAME" />
    
    <set name="personToilets" inverse="true" cascade="all" lazy="false">
        <key column="TOILET_ID" />
        <one-to-many class="vo.PersonToilet" />
    </set>
</class>

PersonToilet.hbm.xml

<class name="vo.PersonToilet" table="PERSON_TOILET">
    <id name="ptId" type="java.lang.Integer">
        <column name="PT_ID" />
        <generator class="sequence">
            <param name="sequence">PERSON_TOILET_SEQ</param>
        </generator>
    </id>
    
    <many-to-one name="centerPerson" column="PERSON_ID" lazy="false" not-null="true" cascade="all" class="vo.Person" />
    
    <many-to-one name="centerToilet" column="TOILET_ID" lazy="false" not-null="true" cascade="all" class="vo.Toilet" />
</class>

新增測試

Session s = HibernateUtil2.getSession();
Transaction tx = s.beginTransaction();
try {
    Toilet t1 = new Toilet();
    t1.setTname("便所1號");
    
    Toilet t2 = new Toilet();
    t2.setTname("便所2號");
    
    Toilet t3 = new Toilet();
    t3.setTname("便所3號");
    
    Person p1 = new Person();
    p1.setPname("掃便所英俠");
    
    Person p2 = new Person();
    p2.setPname("茅坑王");
    
    // 因為掃便所英俠可以上3間廁所，而茅坑王可以上2間廁所，所以要創5個實體
    PersonToilet pt1 = new PersonToilet();
    PersonToilet pt2 = new PersonToilet();
    PersonToilet pt3 = new PersonToilet();
    PersonToilet pt4 = new PersonToilet();
    PersonToilet pt5 = new PersonToilet();
    
    // -----設定互相關聯-----
    // 掃便所英俠(p1)可以上便所1號(t1)
    // 此時的中間表就代替了Toilet的位置，然後自己在去關聯Toilet
    p1.getPersonToilets().add(pt1);
    pt1.setCenterPerson(p1);
    pt1.setCenterToilet(t1);// 就是這時去關聯Toilet
    t1.getPersonToilets().add(pt1);
    
    // 掃便所英俠(p1)可以上廁所2號(t2)
    p1.getPersonToilets().add(pt2);
    pt2.setCenterPerson(p1);
    pt2.setCenterToilet(t2);
    t2.getPersonToilets().add(pt2);
    
    // 掃便所英俠(p1)可以上廁所3號(t3)
    p1.getPersonToilets().add(pt3);
    pt3.setCenterPerson(p1);
    pt3.setCenterToilet(t3);
    t3.getPersonToilets().add(pt3);
    
    // 茅坑王(p2)可以上廁所1號(t1)
    p2.getPersonToilets().add(pt4);
    pt4.setCenterPerson(p2);
    pt4.setCenterToilet(t1);
    t1.getPersonToilets().add(pt4);
    
    // 茅坑王(p2)可以上廁所3號(t3)
    p2.getPersonToilets().add(pt5);
    pt5.setCenterPerson(p2);
    pt5.setCenterToilet(t3);
    t3.getPersonToilets().add(pt5);
    
    s.save(pt1);
    s.save(pt2);
    s.save(pt3);
    s.save(pt4);
    s.save(pt5);
    tx.commit();
} catch (Exception e) {
    tx.rollback();
    System.err.println("例外錯誤！");
    e.printStackTrace();
} finally {
    if (s.isOpen()) {
        s.close();
    }
}

修改Person測試

Person person = (Person) s.get(Person.class, 114);// 掃便所英俠的ID
person.setPname("吸茅坑大王");
    
for (PersonToilet pt : person.getPersonToilets()) {
    Toilet t = pt.getCenterToilet();
    t.setTname("廁" + t.getTname().substring(1));
}
    
s.update(person);
tx.commit();

修改Toilet測試

Toilet toilet = (Toilet) s.get(Toilet.class, 184);// 便所1號的ID
toilet.setTname("廁所1號");
    
for (PersonToilet pt : toilet.getPersonToilets()) {
    Person p = pt.getCenterPerson();
    p.setPname("臭臭王");
}
    
s.update(toilet);
tx.commit();

查詢測試

// 查詢掃便所英俠能用哪些廁所
Person p = (Person) s.get(Person.class, 114);// 掃便所英俠的ID
s.close();
System.out.println(p.getPname());
    
// 必需在Person.hbm.xml和PersonToilet.hbm.xml的centerToilet設定lazy="false"
for (PersonToilet pt : p.getPersonToilets()) {
    System.out.println(pt.getCenterToilet().getTname());
}
    
// 查詢便所1號能被哪些人使用
Toilet t = (Toilet) s.get(Toilet.class, 184);// 便所1號的ID
s.close();
System.out.println(t.getTname());
    
// 必需在Toilet.hbm.xml和PersonToilet.hbm.xml的centerPerson設定lazy="false"
for (PersonToilet pt : t.getPersonToilets()) {
    System.out.println(pt.getCenterPerson().getPname());
}

刪除測試

Person person = (Person) s.get(Person.class, 114);
s.delete(person);
tx.commit();
    
Toilet toilet = (Toilet) s.get(Toilet.class, 185);
s.delete(toilet);
tx.commit();

※刪除很奇怪，我把三張表的4個cascade拿掉或設none才會成功，中間表也會刪

※結論：

關聯表有沒有主鍵最大的差異就是要不要將關聯表建立出來，但如果像現在這個例子只有新增主鍵的話，可以不用建，但因為oracle的主鍵生成方式和其他資料庫不一樣，所以只好建出來，其他資料庫有像increment的語法可用

※Annotation設定

Person.java

@Entity
@Table
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "XXX")
    @SequenceGenerator(name = "XXX", sequenceName = "PERSON_SEQ")
    private Integer pid;
    
    private String pname;
    
    @OneToMany(mappedBy = "centerPerson", cascade = CascadeType.ALL)
    @LazyCollection(value = LazyCollectionOption.FALSE)
    private Set<PersonToilet> personToilets = new HashSet<>();
    
    // setter/getter...
}

Toilet.java

@Entity
@Table
public class Toilet {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "XXX")
    @SequenceGenerator(name = "XXX", sequenceName = "TOILET_SEQ")
    private Integer tid;
    
    private String tname;
    
    @OneToMany(mappedBy = "centerToilet", cascade = CascadeType.ALL)
    @LazyCollection(value = LazyCollectionOption.FALSE)
    private Set<PersonToilet> personToilets = new HashSet<>();
    
    // setter/getter...
}

PersonToilet.java

@Entity
@Table(name = "PERSON_TOILET")
public class PersonToilet {
    @Id
    @Column(name = "PT_ID")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "XXX")
    @SequenceGenerator(name = "XXX", sequenceName = "PERSON_TOILET_SEQ")
    private Integer ptId;
    
    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "PERSON_ID", unique = true)
    private Person centerPerson;
    
    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "TOILET_ID", unique = true)
    private Toilet centerToilet;
    
    // setter/getter...
}

※查詢時，中間表不需要@LazyToOne，只要在兩張表加@LazyCollection就可以查得到了

※刪除時，在資料庫有查到資料會將三張表全刪了，所以我將4個cascade拿掉才能刪，中間表也會刪，而CascadeType.REMOVE不等於整個拿掉，會出錯