多对一就是指，一个实体类中还包含另一个实体类，需要通过这个实体类的某个字段作为另一个查询语句的参数，查询出另一个实体类，具体到 查询语句上就是，一个查询语句需要指定resultMap，resuleMap内部需要指定一个id标签，其余都是result属性，也可以指定association属性，用于多对一，需要添加属性，会多出一个select属性并指定查询语句的id，就可以实现嵌套查询，多对多同样如此，这就是xml的最高应用方式了

特殊符号问题

假如你在 XML 文档中放置了一个类似 < > 字符，那么这个文档会产生一个错误，这是因为解析器会把它解释为新元素的开始。因此你不能这样写：

  1. AND birthday < #{birthday}

AND birthday < #{birthday}

为了避免此类错误，需要把字符 < 替换为实体引用，就像这样：

  1. AND birthday \< #{birthday} (去掉空格)

&lt; <小于

&gt; >大于

&amp; &和号

&apos; ‘省略号

&quot; “引号

但是，在 XML 中仅有字符 “<”和”&” 是非法的。省略号、引号和大于号是合法的

或者使用CDATA：

CDATA 指的是不应由 XML 解析器进行解析的文本数据（Unparsed CHARACTER DATA）。

在 XML 元素中，”<” 和 “&” 是非法的。

“<” 会产生错误，因为解析器会把该字符解释为新元素的开始。

“&” 也会产生错误，因为解析器会把该字符解释为字符实体的开始。

  • CDATA 部分中的所有内容都会被解析器忽略。
  • CDATA 用法
  • 等这些标签都不要放入中，否则也不会解析，们只把有特殊字符的语句放进去即可， 尽量缩小 的范围
1
AND birthday <![CDATA[  < ]]> #{birthday}

用了它你就该写啥写啥，用这个cdata一包就行了

多查询结果封装

多查询分为 多对一 = 一对一 ， 一对多= 多对多

样例类：

orders类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
package com.ls.mybatis.model;

import java.util.Date;

public class Orders {

    private Long orderId;    //订单ID
    private Integer userId;     //用户ID
    private Double orderPrice;  //订单金额
    private String payment;     //支付方式
    private String state;          //订单状态
    private Date orderCreatetime; //下单时间
    private String note;        //备注

    private User users;          //一个订单只对应一个用户

    @Override
    public String toString() {
        return "Orders{" +
                "orderId=" + orderId +
                ", userId=" + userId +
                ", orderPrice=" + orderPrice +
                ", payment='" + payment + '\'' +
                ", state='" + state + '\'' +
                ", orderCreatetime=" + orderCreatetime +
                ", note='" + note + '\'' +
                ", user=" + users +
                '}';
    }

    public String getPayment() {
        return payment;
    }

    public void setPayment(String payment) {
        this.payment = payment;
    }

    public Long getOrderId() {
        return orderId;
    }

    public void setOrderId(Long orderId) {
        this.orderId = orderId;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public Double getOrderPrice() {
        return orderPrice;
    }

    public void setOrderPrice(Double orderPrice) {
        this.orderPrice = orderPrice;
    }

    public String getState() {
        return state;
    }

    public void setState(String state) {
        this.state = state;
    }

    public Date getOrderCreatetime() {
        return orderCreatetime;
    }

    public void setOrderCreatetime(Date orderCreatetime) {
        this.orderCreatetime = orderCreatetime;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    public User getUser() {
        return users;
    }

    public void setUser(User user) {
        this.users = user;
    }
}

user类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
package com.ls.mybatis.model;

import java.util.ArrayList;
import java.util.Date;

public class User {

    private Integer uid;    //用户ID
    private String username;    //用户名
    private Date birthday;      //生日
    private String phone;      //手机号
    private String sex;         //性别
    private String address;     //地址

    //一个用户拥有多个订单
    private ArrayList<Orders> orders = new ArrayList<Orders>();

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", phone=" + phone +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public ArrayList<Orders> getOrders() {
        return orders;
    }

    public void setOrders(ArrayList<Orders> orders) {
        this.orders = orders;
    }
}

多对一、一对一

1、resultmap

一、写好sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 <select id="listOrderAndUser" resultMap="listOU">
            SELECT
            o.order_id,
            o.user_id,
            o.order_price,
            o.payment,
            o.state,
            o.order_createtime,
            o.note,
            u.username,
            u.phone
        FROM orders o,`user` u
        WHERE o.user_id = u.uid
</select>

id 记得与 map中的mapper方法名一致， resultmap选择自定义映射的id

二、返回类型映射编写

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<resultMap id="listOU" type="orders">
    <id property="orderId" column="order_id"></id>
    <result property="userId" column="user_id" javaType="int" jdbcType="INTEGER"></result>
    <result property="orderPrice" column="order_price"></result>
    <result property="payment" column="payment"></result>
    <result property="state" column="state"></result>
    <result property="orderCreatetime" column="order_createtime"></result>
    <result property="note" column="note"></result>
    <result property="" column=""></result>
    <!--对应一个-->
    <association property="users">
        <!--定义user类属性和user表中的列之间的关系-->
        <id property="uid" column="uid"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="phone" column="phone"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
    </association>
</resultMap>

user 相对于 订单只有一个，一个用户一个订单，所以订单样例类中有一个user样例类

association的 property 填写 在orders样例类中对应 1的 样例类类型，也就是user

javatype 指的是 java传入数据类型

jdbcType指的是mysql传入数据类型，这个需要查询资料

typeHandler 用来将 jdbctype 传入 javatype的，但是已经写好了，不用写

三、接口、测试类

1
public List<Orders> listOrderAndUser();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Test
public void manyToOne() {
    //1. 获取连接
    SqlSession session = MyBatisUtils.getSession();
    //2. 获取到mapper接口
    OrderMapper mapper = session.getMapper(OrderMapper.class);
    //3. 访问接口方法，完成查询
    List<Orders> orders = mapper.listOrderAndUser();
    for (Orders order : orders) {
        System.out.println(order.toString());
    }
    // 关闭连接
    MyBatisUtils.closeSession();
}

2、嵌套select查询

一、查询语句

1
2
3
4
5
6
7
8
9
10
11
<select id="listOrderUser02" resultMap="listOUS">
    SELECT
        order_id,
        user_id,
        order_price,
        payment,
        state,
        order_createtime,
        note
      FROM orders
</select>

因为是嵌套，会通过map去查询user的值，不需要sql关联

二、map映射

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<!--多对一查询 2-->
<resultMap id="listOUS" type="orders">
    <id property="orderId" column="order_id"></id>
    <result property="userId" column="user_id" javaType="int" jdbcType="INTEGER"></result>
    <result property="orderPrice" column="order_price"></result>
    <result property="payment" column="payment"></result>
    <result property="state" column="state"></result>
    <result property="orderCreatetime" column="order_createtime"></result>
    <result property="note" column="note"></result>
    <association property="users"
                 column="user_id"
                 select="SelectU">
    </association>
</resultMap>

<select id="SelectU" resultType="user">
    SELECT
        uid,
        username,
        phone
    FROM `user` WHERE uid= #{uid}
</select>

还是引入 多对一，association的参数会选择一个新的查询语句，将与这一条数据 符合条件的数据用另一个 select 查询出来并封装

三、接口、测试类

1
public List<Orders> listOrderUser02();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Test
public void manyToOne02() {
    //1. 获取连接
    SqlSession session = MyBatisUtils.getSession();
    //2. 获取到mapper接口
    OrderMapper mapper = session.getMapper(OrderMapper.class);
    //3. 访问接口方法，完成查询
    List<Orders> orders = mapper.listOrderUser02();
    for (Orders order : orders) {
        System.out.println(order.toString());
    }
    // 关闭连接
    MyBatisUtils.closeSession();
}

3、vo类方式

直接强行定义一个类来接收所有参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
package com.ls.mybatis.vo;


import com.ls.mybatis.model.Orders;

public class OrderVo extends Orders {
    private String username;
    private String phone;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
    
}

二、map查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="listOrdersAndUserByVO" resultType="com.ls.mybatis.vo.OrderVo">
    SELECT
        o.order_id,
        o.user_id,
        o.order_price,
        o.payment,
        o.state,
        o.order_createtime,
        o.note,
        u.username,
        u.phone
    FROM orders o,`user` u
    WHERE o.user_id = u.uid
</select>

三、样例类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Test
public void listOrdersAndUserByVO() {
    //1. 获取连接
    SqlSession session = MyBatisUtils.getSession();
    //2. 获取到mapper接口
    OrderMapper mapper = session.getMapper(OrderMapper.class);
    //3. 访问接口方法，完成查询
    List<OrderVo> orders = mapper.listOrdersAndUserByVO();
    for (OrderVo order : orders) {
        System.out.println(order.toString());
    }
    // 关闭连接
    MyBatisUtils.closeSession();

}

4、resultmap的 抽取

对于很多 resultmap 都会查询到的公共参数映射可以抽取出来成为一个独立的resultmap

1
2
3
4
5
6
7
8
9
<resultMap id="commenmap" type="orders">
    <id property="orderId" column="order_id"></id>
    <result property="userId" column="user_id" javaType="int" jdbcType="INTEGER"></result>
    <result property="orderPrice" column="order_price"></result>
    <result property="payment" column="payment"></result>
    <result property="state" column="state"></result>
    <result property="orderCreatetime" column="order_createtime"></result>
    <result property="note" column="note"></result>
</resultMap>

使用的时候通过 resultmap的继承方法 + id

1
<resultMap id="listOU" type="orders" extends="commenmap">

最后可以看出多对一，其实就是一对一，但是这个一中的数据不全，所以需要其他数据来弥补，并且需要的数据只有一条就足够了。

多对多、一对多

1、resultmap

一、编写sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
    <select id="BandP01" resultMap="bandpMap">
        select b.brand_id,
       b.brand_name,
       b.brand_img,
       p.product_id,
       p.product_img,
       p.product_name,
       p.product_sale,
       p.stock,
       p.product_price,
       p.brandid
from brands b,
     product p
where b.brand_id = p.brandid
  and brand_id = 2;
    </select>

二、编写map映射

1
2
3
4
5
6
7
8
9
10
11
12
13
<resultMap id="bandpMap" type="brands">
    <id property="brandId" column="brand_id"></id>
    <result property="brandName" column="brand_name"></result>
    <result property="brandImg" column="brand_img"></result>

    <collection property="listProduct" ofType="product">
        <id property="productId" column="product_id"></id>
        <id property="productImg" column="product_img"></id>
        <id property="productName" column="product_name"></id>
        <id property="productSale" column="product_sale"></id>
        <id property="productPrice" column="product_price"></id>
    </collection>
</resultMap>

这里的一个对应多个，所以使用 collection 集合装，并且必须指定 oftype属性

三、测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//        @Test
    public void sBP() {
        SqlSession session = MyBatisUtils.getSession();

        BrandsMapper mapper = session.getMapper(BrandsMapper.class);


        Brands brands = mapper.BandP01(2);
        ArrayList<Product> listProduct = brands.getListProduct();
        System.out.println(brands);
        for (Product product : listProduct) {
            System.out.println(product.toString());
        }

        MyBatisUtils.closeSession();
    }

2、嵌套

一、sql

1
2
3
4
5
6
7
    <select id="BandP02" resultMap="bandpMap2">
        select b.brand_id,
       b.brand_name,
       b.brand_img
from brands b
where brand_id = #{enen};
    </select>

二、map

1
2
3
4
5
6
7
8
9
10
11
12
13
14
    <resultMap id="bandpMap2" type="brands">
        <id property="brandId" column="brand_id"></id>
        <result property="brandName" column="brand_name"></result>
        <result property="brandImg" column="brand_img"></result>

        <collection property="listProduct"
                    column="brand_id" select="toselectByBid">
        </collection>
    </resultMap>

    <select id="toselectByBid" resultType="product">
select product_id,product_img,product_name,product_sale,product_price
from product where brandid=#{brand_id};
    </select>

三、抽象类、样例类

1
public Brands BandP02(Integer brand);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Test
public void sBP2() {
    SqlSession session = MyBatisUtils.getSession();

    BrandsMapper mapper = session.getMapper(BrandsMapper.class);


    Brands brands = mapper.BandP02(2);
    ArrayList<Product> listProduct = brands.getListProduct();
    System.out.println(brands);
    for (Product product : listProduct) {
        System.out.println(product.toString());
    }

    MyBatisUtils.closeSession();
}

3、一对多式

一、编写sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
    <select id="selectStudentAndTeachers" resultMap="studentBaseMap">
    SELECT
    s.sid,
    s.student_name,
    s.student_birthday,
    s.student_height,
    s.sex,
    t.tid,
    t.tname,
    t.tsex
    FROM tab_student s,tab_student_teacher st,tab_teacher t
    WHERE s.sid = st.sid
    AND st.tid = t.tid
    AND s.sid = #{sid}
</select>

二、编写

1
2
3
4
5
6
7
8
9
10
11
12
<resultMap id="studentBaseMap" type="student">
    <id property="sid" column="sid"/>
    <result property="studentName" column="student_name"/>
    <result property="studentBirthday" column="student_birthday"/>
    <result property="studentHeight" column="student_height"/>
    <result property="sex" column="sex"/>
    <collection property="teacherList" ofType="teacher">
        <id property="tid" column="tid"/>
        <result property="tname" column="tname"/>
        <result property="tsex" column="tsex"/>
    </collection>
</resultMap>

三、测试类

1
public List<Student> selectStudentAndTeachers(Integer sid);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Test
public void selectStudentById(){
    //获取sqlSession链接
    SqlSession sqlSession = MyBatisUtils.getSession();
    //获取mapper接口
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> student = mapper.selectStudentAndTeachers(2);


    for (Student student1 : student) {
        System.out.println(student1.toString());
    }

    sqlSession.close();
}