MyBatis之多表查询

此篇为个人学习笔记 （在编程中遇到的错误）

1.一对一

需要的有关SQL语句如下：
一张身份证ID只属于一人，一人只能有一个身份证ID

 CREATE TABLE IDCARD(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    CODE VARCHAR(18)
 );

 INSERT INTO IDCARD(CODE) VALUES('152221198711020624');
 INSERT INTO IDCARD(CODE) VALUES('152201199008150317');

 CREATE TABLE PERSON(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(32),
    AGE INT,
    SEX VARCHAR(8),
    CARD_ID INT UNIQUE,
    FOREIGN KEY(CARD_ID) REFERENCES IDCARD(ID)
 );

 INSERT INTO PERSON(NAME,AGE,SEX,CARD_ID) VALUES('Rose',29,'女',1);
 INSERT INTO PERSON(NAME,AGE,SEX,CARD_ID) VALUES('tom',27,'男',2);

(1) 嵌套查询

文件PersonMapper.xml中
<select id="findPersonById" parameterType="int" resultMap="person">
    SELECT * FROM PERSON WHERE ID = #{id}
</select>

<resultMap id="person" type="com.it.pojo.Person">
    <id property="id" column="id"/>
    <result property="name" column="name"></result>
    <result property="age" column="age"></result>
    <result property="sex" column="sex"></result>
    <association property="card" column="card_id" javaType="com.it.pojo.IdCard" select="com.it.mapper.IdCardMapper.findCodeById"></association>
</resultMap>
-------------------------------------------------------------------------------------------------------------------------------------------
文件IdCardMapper.xml中
<select id="findCodeById" resultType="com.it.pojo.IdCard" parameterType="int">
    SELECT * FROM IDCARD WHERE ID = #{id};
</select>

此方式下需注意：
1.在<association>标签中的属性column对应的值 作为参数 去 执行子查询 ，此处刚好是外键
2.在<association>标签中的属性javaType对应的是子查询的查询结果POJO类型，而后将其set给card属性

(2) 嵌套结果

<select id="findPersonById2" parameterType="int" resultMap="person2">
    SELECT P.*,I.CODE FROM PERSON P , IDCARD I WHERE P.ID = #{id} AND P.ID = I.ID
</select>

<resultMap id="person2" type="com.it.pojo.Person">
    <id property="id" column="id"/>
    <result property="name" column="name"></result>
    <result property="age" column="age"></result>
    <result property="sex" column="sex"></result>
    <association property="card" javaType="com.it.pojo.IdCard">
        <id property="id" column="id"></id>
        <result property="code" column="code"></result>
    </association>
</resultMap>

此方式下需注意：
1.在<association>子标签中的属性column对应的值 是 子查询 结果的列名
2.在<association>标签中的属性javaType对应的是子查询的查询结果POJO类型，而后将其set给card属性

2. 一对多

需要的有关SQL语句如下：
一个用户可以拥有多个订单，一个订单却只属于一人

 CREATE TABLE USER(
 ID INT(32) PRIMARY KEY AUTO_INCREMENT,
 USERNAME VARCHAR(32),
 ADDRESS VARCHAR(256)
 );

 INSERT INTO USER(ID,USERNAME,ADDRESS) VALUES('1','詹姆斯','克利夫兰');
 INSERT INTO USER(ID,USERNAME,ADDRESS) VALUES('2','科比','洛杉矶');
 INSERT INTO USER(ID,USERNAME,ADDRESS) VALUES('3','保罗','洛杉矶');

 CREATE TABLE ORDERS(
 ID INT(32) PRIMARY KEY AUTO_INCREMENT,
 NUMBER VARCHAR(32) NOT NULL,
 USERID INT(32) NOT NULL REFERENCES USER(ID)
 );

 INSERT INTO ORDERS VALUES('1','1000011','1');
 INSERT INTO ORDERS VALUES('2','1000012','1');
 INSERT INTO ORDERS VALUES('3','1000013','2');

(1)在SQL控制台实现查询

SELECT U.*,O.ID,O.NUMBER FROM ORDERS O , USER U WHERE U.ID = O.USERID AND U.ID = 1;

结果为

(2) 使用嵌套查询

在文件UserMapper.xml中
<select id="findUserById" resultMap="userResult" parameterType="int">
    SELECT * FROM USER WHERE ID = #{id}
</select>
<resultMap id="userResult" type="com.it.pojo.User">
    <id property="id" column="id"></id>
    <result property="userName" column="userName"></result>
    <result property="address" column="address"></result>
    <collection property="ordersList" ofType="com.it.pojo.Orders" column="ID" select="com.it.mapper.OrdersMapper.findOrdersById">
    </collection>
</resultMap>
在文件OrdersMapper.xml中
<select id="findOrdersById" parameterType="int" resultType="com.it.pojo.Orders">
    SELECT * FROM ORDERS WHERE USERID = #{id}
</select>

此方式下需注意：
1.在<collection>标签中的属性column的为表User的主键ID值，表Orders中的USERID 是参照表User的主键，此处可理解为拿 表User的主键 去 查询表Orders
2.在OrdersMapper.xml中查询表User时 WHERE 条件是 自己的外键 去和 传来的 参数(表User的主键) 比较

(3) 使用嵌套结果

在文件UserMapper.xml中
<select id="findUserById" resultMap="userResult" parameterType="int">
    SELECT U.*,o.id as oid,O.NUMBER FROM USER U,ORDERS O WHERE U.ID = #{id} AND U.ID = O.USERID
</select>
<resultMap id="userResult" type="com.it.pojo.User">
    <id property="id" column="id"></id>
    <result property="userName" column="userName"></result>
    <result property="address" column="address"></result>
    <collection property="ordersList" ofType="com.it.pojo.Orders">
        <id property="id" column="oid"></id>
        <result property="number" column="number"></result>
    </collection>
</resultMap>

此方式下需注意：
1.首先只需写一个xml文件即可
2.在<collection>标签中的属性ofType是集合类的泛型
3.在<collection>子标签 中需要写的是
在上述2中泛型POJO类的属性，而column指的是该属性对应表的列名
此处因为 表User的主键id 和 表Orders的主键 同名，故须为 其中一个表的主键取别名，否则有如下错误

未取别名 造成的错误结果(找到两条结果也只显示一条)
DEBUG - ==>  Preparing: SELECT U.*,o.id,O.NUMBER FROM USER U,ORDERS O WHERE U.ID = ? AND U.ID = O.USERID
DEBUG - ==> Parameters: 1(Integer)
DEBUG - <==      Total: 2
User{id=1, userName='詹姆斯', address='克利夫兰', ordersList=[Orders{id=1, number='1000011'}]}

取别名后
DEBUG - ==>  Preparing: SELECT U.*,o.id as oid,O.NUMBER FROM USER U,ORDERS O WHERE U.ID = ? AND U.ID = O.USERID
DEBUG - ==> Parameters: 1(Integer)
DEBUG - <==      Total: 2
User{id=1, userName='詹姆斯', address='克利夫兰', ordersList=[Orders{id=1, number='1000011'}, Orders{id=2, number='1000012'}]}

3.多对多

需要的有关SQL语句如下：
一个订单可以拥有多种产品，一种产品可属于多个订单

 CREATE TABLE ORDERS(
 ID INT(32) PRIMARY KEY AUTO_INCREMENT,
 NUMBER VARCHAR(32) NOT NULL,
 USERID INT(32) NOT NULL REFERENCES USER(ID)
 );

 INSERT INTO ORDERS VALUES('1','1000011','1');
 INSERT INTO ORDERS VALUES('2','1000012','1');
 INSERT INTO ORDERS VALUES('3','1000013','2');

 CREATE TABLE PRODUCT(
 ID INT(32) PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(32),
 PRICE DOUBLE
 );

 INSERT INTO PRODUCT VALUES ('1','JAVA基础入门','44.5');
 INSERT INTO PRODUCT VALUES ('2','JAVA WEB 程序开发入门','38.5');
 INSERT INTO PRODUCT VALUES ('3','SSM框架整合实战','50');

 CREATE TABLE ORDERSITEM(
 ID INT(32) PRIMARY KEY AUTO_INCREMENT,
 ORDERS_ID INT(32) REFERENCES ORDERS(ID),
 PRODUCT_ID INT(32) REFERENCES PRODUCT(ID)
 );

 INSERT INTO ORDERSITEM VALUES (1,1,1);
 INSERT INTO ORDERSITEM VALUES (2,1,3);
 INSERT INTO ORDERSITEM VALUES (3,3,3);

需求 ： 查询订单(包含订单中的商品)

(1) 嵌套查询

在OrdersMapper.xml中
<select id="findOrdersById" resultMap="ordersRes" parameterType="int">
    SELECT * FROM ORDERS WHERE ID = #{id};
</select>
<resultMap id="ordersRes" type="com.it.pojo.Orders">
    <id property="id" column="id"></id>
    <result column="number" property="number"></result>
    <collection property="productList" column="id" ofType="com.it.pojo.Product" select="com.it.mapper.ProductMapper.findProductById"></collection>
</resultMap>

-------------------------------------------------------------------------------------------------------------------------------------------------
在ProductMapper.xml中
<select id="findProductById" resultMap="proRes">
<!--
    SELECT * FROM PRODUCT P JOIN ORDERSITEM OI ON OI.PRODUCT_ID = P.ID WHERE OI.ORDERS_ID = #{id};
-->
SELECT * FROM PRODUCT P WHERE P.ID IN (
    SELECT OI.PRODUCT_ID FROM ORDERSITEM OI WHERE OI.ORDERS_ID = #{id}
    )
</select>
<resultMap id="proRes" type="com.it.pojo.Product">
    <id column="id" property="id"></id>
    <result property="name" column="name"></result>
    <result property="price" column="price"></result>
</resultMap>

(2) 嵌套结果(注释的SQL语句可达到相同效果)

<select id="findOrdersById" resultMap="ordersResult" parameterType="int">
<!--
    SELECT O.*,P.ID AS PID,P.NAME,P.PRICE FROM ORDERS O,PRODUCT P,ORDERSITEM OI  WHERE O.ID = #{id} AND O.ID = OI.ORDERS_ID AND OI.PRODUCT_ID = P.ID ;
-->
<!--
   SELECT O.*,P.ID AS PID,P.NAME,P.PRICE FROM (ORDERS O JOIN ORDERSITEM OI ON O.ID = OI.ORDERS_ID) JOIN PRODUCT P ON OI.PRODUCT_ID = P.ID WHERE O.ID = #{id};
-->
    SELECT O.*,P.ID AS PID,P.NAME,P.PRICE FROM (ORDERS O JOIN ORDERSITEM OI ON O.ID = OI.ORDERS_ID) JOIN PRODUCT P ON OI.PRODUCT_ID = P.ID
    <where>
        <if test="id != null and id != '' ">
            AND O.ID = #{id}
        </if>
    </where>
</select>

<resultMap id="ordersResult" type="com.it.pojo.Orders">
    <id property="id" column="id"></id>
    <result property="number" column="number"></result>
    <collection property="productList" ofType="com.it.pojo.Product">
        <id property="id" column="PID"></id>
        <result property="name" column="name"></result>
        <result property="price" column="price"></result>
    </collection>
</resultMap>

此方式下需注意：
多表查询的各种方式

FROM [table1] JOIN [table2] ON [ table1.column = table2.column ]
	JOIN [table3] ON [ table1.column = table3.column ]
	----
	WHERE [ table[n].column = #{}]
--------------------------------------------------------------------

FROM [table,table,table] WHERE [] AND [] AND []