雙向多對多關聯 Mybatis3.x(八)

多對多關聯可以用一張中介的table做為兩個一對多的關聯來完成
譬如一個人可以住很多房子，房子也可以很多人住

※事前規劃

DROP TABLE PERSON_HOME;
DROP TABLE PERSON;
DROP TABLE HOME;

CREATE TABLE PERSON(
 PID  NUMBER,
 NAME  VARCHAR(30) NOT NULL ,
 CONSTRAINT PERSON_PK PRIMARY KEY(PID)
);
COMMENT ON TABLE PERSON IS '人表';
COMMENT ON COLUMN PERSON.PID IS '人id';
COMMENT ON COLUMN PERSON.NAME IS '姓名';

CREATE TABLE HOME(
 HID  NUMBER,
 ADDRESS_NO NUMBER NOT NULL ,
 CONSTRAINT HOME_PK PRIMARY KEY(HID)
);
COMMENT ON TABLE HOME IS '房子表';
COMMENT ON COLUMN HOME.HID IS '房子id';
COMMENT ON COLUMN HOME.ADDRESS_NO IS '房子號碼';

CREATE TABLE PERSON_HOME(
 PID NUMBER,
 HID NUMBER,
 CONSTRAINT PERSON_HOME_PID_FK FOREIGN KEY(PID) REFERENCES PERSON(PID) ON DELETE CASCADE,
 CONSTRAINT PERSON_HOME_HID_FK FOREIGN KEY(HID) REFERENCES HOME(HID) ON DELETE CASCADE 
);
COMMENT ON TABLE PERSON_HOME IS '人和房子的中介表';
COMMENT ON COLUMN PERSON_HOME.PID IS '人的id';
COMMENT ON COLUMN PERSON_HOME.HID IS '房子的id';

INSERT INTO PERSON(PID, NAME) VALUES (111, 'john');
INSERT INTO PERSON(PID, NAME) VALUES (222, 'apple');
INSERT INTO PERSON(PID, NAME) VALUES (333, 'tom');

INSERT INTO HOME(HID, ADDRESS_NO) VALUES(999, 555);
INSERT INTO HOME(HID, ADDRESS_NO) VALUES(998, 556);
INSERT INTO HOME(HID, ADDRESS_NO) VALUES(997, 557);
INSERT INTO HOME(HID, ADDRESS_NO) VALUES(996, 558);
INSERT INTO HOME(HID, ADDRESS_NO) VALUES(995, 559);
INSERT INTO HOME(HID, ADDRESS_NO) VALUES(994, 560);
INSERT INTO HOME(HID, ADDRESS_NO) VALUES(993, 561);
INSERT INTO HOME(HID, ADDRESS_NO) VALUES(992, 562);

INSERT INTO PERSON_HOME(PID, HID) VALUES(111, 999);
INSERT INTO PERSON_HOME(PID, HID) VALUES(111, 998);
INSERT INTO PERSON_HOME(PID, HID) VALUES(111, 997);
INSERT INTO PERSON_HOME(PID, HID) VALUES(111, 996);
INSERT INTO PERSON_HOME(PID, HID) VALUES(111, 995);
INSERT INTO PERSON_HOME(PID, HID) VALUES(111, 994);
INSERT INTO PERSON_HOME(PID, HID) VALUES(111, 993);
INSERT INTO PERSON_HOME(PID, HID) VALUES(111, 992);
INSERT INTO PERSON_HOME(PID, HID) VALUES(222, 997);
INSERT INTO PERSON_HOME(PID, HID) VALUES(222, 996);
INSERT INTO PERSON_HOME(PID, HID) VALUES(222, 995);
INSERT INTO PERSON_HOME(PID, HID) VALUES(222, 994);
INSERT INTO PERSON_HOME(PID, HID) VALUES(333, 995);
INSERT INTO PERSON_HOME(PID, HID) VALUES(333, 994);
INSERT INTO PERSON_HOME(PID, HID) VALUES(333, 993);
INSERT INTO PERSON_HOME(PID, HID) VALUES(333, 992);

※java檔

兩隻java分別對應到對方，想成一對多，中介的就基本的而已：

public class Person {
    private Integer pid;
    private String name;
    private List<Home> homes;
    //setter/getter...
}

public class Home {
    private Integer hid;
    private Integer addressNo;
    private List<Person> persons;
    //setter/getter...
}

public class PersonHome {
    private Integer pid;
    private Integer hid;
    //setter/getter...
}

※xml設定

Person.xml

<mapper namespace="ooo.Person">
    <sql id="column">
        PID, NAME
    </sql>

    <resultMap type="Person" id="PersonInterface">
        <id property="pid" column="PID" />
        <result property="name" column="NAME" />
    </resultMap>

    <resultMap type="Person" id="PersonHomeInterface" extends="PersonInterface">
        <collection property="homes" javaType="java.util.List"
            ofType="Home" resultMap="xxx.Home.HomeInterface" />
    </resultMap>

    <select id="findPersonHome" parameterType="java.lang.Integer"
        resultMap="PersonHomeInterface">
        SELECT
            P.PID, P.NAME, H.HID, H.ADDRESS_NO
        FROM
            PERSON P, HOME H, PERSON_HOME PH
        <where>
                PH.PID = P.PID
            AND PH.HID = H.HID
            AND P.PID = #{xxx}
        </where>
    </select>
</mapper>

Home.xml

<mapper namespace="xxx.Home">
    <sql id="column">
        HID, ADDRESS_NO
    </sql>
    <resultMap type="Home" id="HomeInterface">
        <id property="hid" column="HID" />
        <result property="addressNo" column="ADDRESS_NO" />
    </resultMap>

    <resultMap type="Home" id="HomePersonInterface" extends="HomeInterface">
        <collection property="persons" javaType="java.util.List"
            ofType="Person" resultMap="ooo.Person.PersonInterface" />
    </resultMap>

    <select id="findHomePerson" parameterType="java.lang.Integer"
        resultMap="HomePersonInterface">
        SELECT
            P.PID, P.NAME, H.HID, H.ADDRESS_NO
        FROM
            PERSON P, HOME H, PERSON_HOME PH
        <where>
                PH.PID = P.PID
            AND PH.HID = H.HID
            AND H.HID = #{ooo}
        </where>
    </select>
</mapper>

※java檔測試：

SqlSession sqlSession = MyBatisSessionFactory.getSession();
System.out.println("*****一個人住很多房子*****");
Person person = sqlSession.selectOne("ooo.Person.findPersonHome", 222);
System.out.println(person.getPid());
System.out.println(person.getName());
System.out.println("------");

List<Home> lh = person.getHomes();
for(Home h : lh){
    System.out.println(h.getHid());
    System.out.println(h.getAddressNo());
    System.out.println("======");
}

System.out.println("*****一間房子有很多人住*****");
Home home = sqlSession.selectOne("xxx.Home.findHomePerson", 996);
System.out.println(home.getHid());
System.out.println(home.getAddressNo());
System.out.println("------");

List<Person> lp = home.getPersons();
for(Person p : lp){
    System.out.println(p.getPid());
    System.out.println(p.getName());
    System.out.println("======");
}
MyBatisSessionFactory.closeSession();

結果：
*****一個人住很多房子*****
222
apple
------
997
557
======
996
558
======
995
559
======
994
560
======
*****一間房子有很多人住*****
996
558
------
111
john
======
222
apple
======

apple住了557、558、559、560號，四間房子

住址558號的房子住了john和apple