Mybatis任务二：复杂映射&配置文件深入

一 Mybatis高级查询
1.1 ResultMap属性
建立对象关系映射
resultType：如果实体的属性名与表中字段名一致，将查询结果自动封装到实体类中
resultMap：如果实体的属性名与表中字段名不一致，可以使用resultMap实现手动封装到实体类中
如果：

与表中字段不一致
（1）编写UserMapper接口

package com.lagou.mapper;

import com.lagou.domain.User;

import java.util.List;

public interface UserMapper {

    /*
     * 根据id查询用户
     * */

    public User findUserById(int id);

    /*
    * 查询所有用户
    * */
    public List<User> findAllResultMap();

}

（2）编写UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.lagou.mapper.UserMapper">

    <!--根据id查询用户-->

    <select id="findUserById" parameterType="int" resultMap="userResultMap">

        select * from USER where id = #{id}

    </select>


<!--    id:标签的唯一标识 type：封装后的实体类型-->
    <resultMap id="userResultMap" type="com.lagou.domain.User">
<!--        手动配置映射关系-->
<!--        id：用来配置主键-->
        <id property="id" column="id"></id>
<!--        result：表中普通字段封装-->
        <result property="usernameaab" column="username"></result>
        <result property="birthdayaab" column="birthday"></result>
        <result property="sexaab" column="sex"></result>
        <result property="addressaab" column="address"></result>

    </resultMap>


<!--    查询所有用户-->
<!--    手动配置实体属性与表中字段的映射关系，完成手动封装-->
    <select id="findAllResultMap" resultMap="userResultMap">

        select * from user

    </select>

</mapper>

（3）代码测试

@Test
    public void test2() throws IOException {

        InputStream resourcesAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourcesAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //当前返回的，其实是基于UserMapper所产生的代理对象：底层：JDK动态代理 实际类型：proxy
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> allResultMap = mapper.findAllResultMap();
        for (User user:allResultMap) {

            System.out.println(user);

        }

        sqlSession.close();

    }

1.2 多条件查询（三种）
需求：根据id和username查询user表
（1）方式一
使用#{arg0}=》#{arg1}或者#{param1}=》#{param2}获取参数
UserMapper接口

/*
    * 多条件查询方式一
    * */
    public List<User> findByIdAndUsername1(int id,String username);

（2）UserMapper.xml

<!--    多条件查询：方式一-->
    <select id="findByIdAndUsername1" resultMap="userResultMap">

--         select * from user where id = #{arg0} and username = #{arg1}

        select * from user where id = #{param1} and username = #{param2}

    </select>

（3）测试

/*
* 多条件查询方式一
* */
@Test
public void test3() throws IOException {

    InputStream resourcesAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourcesAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //当前返回的，其实是基于UserMapper所产生的代理对象：底层：JDK动态代理 实际类型：proxy
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    List<User> users = mapper.findByIdAndUsername1(1, "憨憨");

    for (User user:users) {

        System.out.println(user);

    }

    sqlSession.close();

}

（2）方式二
使用注解，引入@param() 注解获取参数
UserMapper接口

/*
    * 多条件查询方式二
    * */
    public List<User> findByIdAndUsername2(@Param("id") int id,@Param("username") String username);

UserMapper.xml

<!--多条件查询：方式二-->
    <select id="findByIdAndUsername2" resultMap="userResultMap">

        select * from user where id = #{id} and username = #{username}

    </select>

测试

/*
     * 多条件查询方式二
     * */
    @Test
    public void test4() throws IOException {

        InputStream resourcesAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourcesAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //当前返回的，其实是基于UserMapper所产生的代理对象：底层：JDK动态代理 实际类型：proxy
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> users = mapper.findByIdAndUsername2(1, "憨憨");

        for (User user:users) {

            System.out.println(user);

        }
        sqlSession.close();
    }

（3）方式三（最为推荐）
使用pojo对象传递参数
UserMapper接口

/*
* 多条件查询方式三
* */
public List<User> findByIdAndUsername3(User user);

UserMapper.xml

<!--多条件查询：方式三-->
<select id="findByIdAndUsername3" resultMap="userResultMap" parameterType="com.lagou.domain.User">

    select * from user where id = #{id} and username = #{usernameaab}

</select>

测试

/*
 * 多条件查询方式三
 * */
@Test
public void test5() throws IOException {

    InputStream resourcesAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourcesAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //当前返回的，其实是基于UserMapper所产生的代理对象：底层：JDK动态代理 实际类型：proxy
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    User user = new User();

    user.setId(1);

    user.setUsernameaab("憨憨");

    List<User> users = mapper.findByIdAndUsername3(user);

    for (User user1:users) {

        System.out.println(user1);

    }
    sqlSession.close();
}

1.3 模糊查询
需求：根据username模糊查询user表
（1）方式一
UserMapper接口

/*
* 模糊查询：方式一
* */
public List<User> findByUsername(String username);

UserMapper.xml

<!--    模糊查询：方式一-->
<select id="findByUsername" resultMap="userResultMap" parameterType="string">

   select * from user where username like #{username}

</select>

测试

/*
* 模糊查询：方式一
* */
@Test
public void test6() throws IOException {

	InputStream resourcesAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourcesAsStream);
	SqlSession sqlSession = sqlSessionFactory.openSession();
	
	//当前返回的，其实是基于UserMapper所产生的代理对象：底层：JDK动态代理 实际类型：proxy
	UserMapper mapper = sqlSession.getMapper(UserMapper.class);
	
	List<User> users = mapper.findByUsername("%憨%");
	
	for (User user:users) {
	
	    System.out.println(user);
	
	}
	
	sqlSession.close();

}

（2）方式二
UserMapper接口

/*
 * 模糊查询：方式二
 * */
public List<User> findby(String username);

UserMapper.xml

<!--    模糊查询：方式二-->
<!--         parameterType是基本数据类型或者String的时候,${}里面的值只能写value ${}:sql原样拼接-->
<select id="findby" parameterType="string" resultMap="userResultMap" >

    select * from user where username like '${value}'

</select>

测试

/*
* 模糊查询：方式二
* */
@Test
public void test7() throws IOException {

   InputStream resourcesAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
   SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourcesAsStream);
   SqlSession sqlSession = sqlSessionFactory.openSession();

   //当前返回的，其实是基于UserMapper所产生的代理对象：底层：JDK动态代理 实际类型：proxy
   UserMapper mapper = sqlSession.getMapper(UserMapper.class);

   List<User> list = mapper.findby("%伍%");

   for (User user:list) {

       System.out.println(user);

   }

   sqlSession.close();

}

（3）${} 与 #{}区别（笔试题）
#{}：表示一个占位符号
通过#{}可以实现preparedStatement向占位符中设置值，自动进行java类型和jdbc类型转换，#{}可以有效防止sql注入
#{}可以接收简单类型值或pojo属性值
如果parameterType传输单个简单类型值，#{}括号中名称随便写
${}：表示拼接sql串
通过 ${} 可以将parameterType传入的内容拼接在sql中且不进行jdbc类型转换，会出现sql注入问题
${} 可以接收简单类型值或pojo属性值
如果parameterType传输单个简单类型值， ${}括号中只能是value

二 Mybatis映射文件深入
2.1 返回主键
应用场景
我们很多时候有这种需求，向数据库插入一条记录后，希望能立即拿到这条记录在数据库中的主键值
2.1.1 useGeneratedKeys
mapper接口

/*
* 添加用户：获取返回主键:方式一
* */
public void saveUser(User user);

UserMapper.xml

<!--添加用户：获取返回主键：方式一-->
<!--useGeneratedKeys：声明返回主键-->
<!--keyProperty：把返回主键的值，封装到实体中的那个属性上-->
<insert id="saveUser" parameterType="user" useGeneratedKeys="true" keyProperty="id">

    insert into user(username,birthday,sex,address) values (#{usernameaab},#{birthdayaab},#{sexaab},#{addressaab})

</insert>

测试类

/*
* 添加用户：返回主键：方式一
* */
@Test
public void test8() throws IOException {

   InputStream resourcesAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
   SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourcesAsStream);
   SqlSession sqlSession = sqlSessionFactory.openSession();

   //当前返回的，其实是基于UserMapper所产生的代理对象：底层：JDK动态代理 实际类型：proxy
   UserMapper mapper = sqlSession.getMapper(UserMapper.class);

   User user = new User();

   user.setUsernameaab("库力强");

   user.setBirthdayaab(new Date());

   user.setSexaab("男");

   user.setAddressaab("杭州");

   System.out.println(user);

   mapper.saveUser(user);

   System.out.println(user);

   sqlSession.commit();

   sqlSession.close();

}

注意：只适用于主键自增的数据库，mysql和sqlserver支持，oracle不行
2.1.2 selectKey
mapper接口

/*
* 添加用户：获取返回主键：方式二
* */
public void saveUser2(User user);

UserMapper.xml

<!--添加用户：获取返回主键：方式二
selectKey：使用范围更广，支持所有数据类型的数据库
order="AFTER"：设置在sql语句执行前（后），执行此语句
keyColumn="id"：指定主键对应的列名
keyProperty="id"：把返回主键的值，
resultType="int"：指定主键类型-->
<insert id="saveUser2" parameterType="user">

    <selectKey order="AFTER" keyColumn="id" keyProperty="id" resultType="int">
        select LAST_INSERT_ID()
    </selectKey>

    insert into user(username,birthday,sex,address) values (#{usernameaab},#{birthdayaab},#{sexaab},#{addressaab})

</insert>

测试类

/*
 * 添加用户：返回主键：方式二
 * */
@Test
public void test9() throws IOException {

    InputStream resourcesAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourcesAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //当前返回的，其实是基于UserMapper所产生的代理对象：底层：JDK动态代理 实际类型：proxy
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    User user = new User();

    user.setUsernameaab("亚索");

    user.setBirthdayaab(new Date());

    user.setSexaab("男");

    user.setAddressaab("瓦罗兰");

    System.out.println(user);

    mapper.saveUser2(user);

    System.out.println(user);

    sqlSession.commit();

    sqlSession.close();

}

2.2 动态SQL
2.2.1 动态SQL之< if >
需求：根据id和username查询，但是不确定两个都有值

UserMapper接口

/*
* 动态sql的if标签：多条件查询
* */
public List<User> findByIdAndUsernameIf(User user);

UserMapper.xml

<!--动态sql之if：多条件查询
test里面写的就是表达式
<where>:想到那个鱼where 1 = 1，但是如果没有条件的话，不会拼接上where关键字
-->
<select id="findByIdAndUsernameIf" parameterType="user" resultMap="userResultMap">

    select * from user

    <where>

        <if test="id != null">

            and id = #{id}

        </if>

        <if test="usernameaab != null">

            and username = #{usernameaab}

        </if>

    </where>

</select>

测试类

/*
* 动态sql之if：多条件查询
 * */
@Test
public void test10() throws IOException {

    InputStream resourcesAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourcesAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //当前返回的，其实是基于UserMapper所产生的代理对象：底层：JDK动态代理 实际类型：proxy
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    User user = new User();

    user.setId(1) ;

    user.setUsernameaab("憨憨");

    List<User> list = mapper.findByIdAndUsernameIf(user);

    for (User user1:list) {

        System.out.println(user1);

    }

    sqlSession.close();

}

2.2.2 动态SQL之< set >
需求：动态更新user表数据，如果该属性有值就更新，没有值不做处理
UserMapper接口

/*
* 动态sql的set标签：动态更新
 * */
public void UpdateIf(User user);

UserMapper.xml映射

<!--动态SQL之set标签：动态更新
<set>:在更新的时候，会自动添加set关键字，还会去掉最后一个条件的逗号-->
<update id="UpdateIf" parameterType="user">

    update user
    <set>

        <if test="username != null">

            username = #{username},

        </if>

        <if test="birthday != null">

            birthday = #{birthday},

        </if>

        <if test="sex != null">

            sex = #{sex},

        </if>

        <if test="address != null">

            address = #{address},

        </if>

    </set>

    where id = #{id}

</update>

测试代码

/*
* 动态sql之set：动态更新
* */
@Test
public void test11() throws IOException {

   InputStream resourcesAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
   SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourcesAsStream);
   SqlSession sqlSession = sqlSessionFactory.openSession();

   //当前返回的，其实是基于UserMapper所产生的代理对象：底层：JDK动态代理 实际类型：proxy
   UserMapper mapper = sqlSession.getMapper(UserMapper.class);

   User user = new User();

   user.setId(1);

   user.setUsername("珍珍");

   user.setAddress("杭州阿里");

   mapper.UpdateIf(user);

   sqlSession.commit();

   sqlSession.close();

}

2.2.3 动态SQL之< foreach >
foreach主要是用来做数据的循环遍历
例如·，select * from user where id in (1,2,3) 在这样的语句中，传入的参数部分必须依靠foreach遍历才能实现
< foreach >标签用于遍历集合，它的属性：
collection：代表要遍历的集合元素
open：代表语句的开始部分
close：代表结束部分
item：代表遍历集合的每个元素，生成的变量名
sperator：代表分隔符
（a）集合
UserMapper接口

/*
* 动态sql的foreach标签：多值查询
* */
public List<User> findByList(List<Integer> list);

UserMapper.xml映射

<!--动态sql的foreach标签：多值查询：根据多个id值查询用户
collection:代表要遍历的集合元素，通常写collection或者list
open:代表语句的开始部分
close:代表语句的结束部分
item:代表遍历集合中的每个元素，生成的变量名
separator:分隔符
-->
<select id="findByList" parameterType="list" resultType="user">

    select * from user
    <where>
        <foreach collection="collection" open="id in (" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </where>
</select>

测试类

/*
 * 动态sql之foreach：多值查询
 * */
@Test
public void test12() throws IOException {

    InputStream resourcesAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourcesAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //当前返回的，其实是基于UserMapper所产生的代理对象：底层：JDK动态代理 实际类型：proxy
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    ArrayList<Integer> list = new ArrayList<>();

    list.add(1);

    list.add(2);

    list.add(4);

    List<User> users = mapper.findByList(list);

    for (User user:users) {

        System.out.println(user);

    }

    sqlSession.close();

}

（b）数组
UserMapper接口：

/*
 * 动态sql的foreach标签：多值查询：数组
 * */
public List<User> findByArray(Integer[] num);

UserMapper.xml映射

<select id="findByArray" parameterType="list" resultType="user">
    select * from user
    <where>
        <foreach collection="array" open="id in (" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </where>
</select>

测试代码

/*
 * 动态sql之foreach：多值查询：数组
 * */
@Test
public void test13() throws IOException {

    InputStream resourcesAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourcesAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //当前返回的，其实是基于UserMapper所产生的代理对象：底层：JDK动态代理 实际类型：proxy
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    Integer[] num = {1,2,3};

    List<User> users = mapper.findByArray(num);

    for (User user:users) {

        System.out.println(user);

    }

    sqlSession.close();

}

2.3 SQL片段
应用场景
映射文件可将重复的sql提取出来，使用时用include引用即可，最终达到sql重用的目的

2.4 知识小结
MyBatis映射文件配置
< select >：查询
< insert >：插入
< update >：修改
< delete >：删除
< selectKey >：返回主键
< where >：where条件
< if >.：if判断
< for each >：for循环
< set >：set设置
< sql >：sql片段抽取
三 Mybatis核心配置文件深入
3.1 plugins标签
Mybatis可以使用第三方的插件来对功能进行扩展，分页助手PageHelper是将分业的复杂操作进行封装，使用简单的方式即可获得分页的相关数据
开发步骤：
（1）导入通用PageHelper的坐标
（2）在mybatis核心配置文件中配置PageHelper插件
（3）测试分页数据获取

（1）pom.xml中导入通用PageHelper的坐标

<!--分页助手-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>3.7.5</version>
</dependency>

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>0.9.1</version>
</dependency>

（2）sqlMapConfig.xml在mybatis核心配置文件中配置PageHelper插件

<plugins>
    <plugin interceptor="com.github.pagehelper.PageHelper">

        <!--dialect：指定方言 -->
        <property name="dialect" value="mysql"/>
    </plugin>
</plugins>

（3）测试分页数据获取

/*
 * 核心配置文件深入：plugin标签
 * */
@Test
public void test14() throws IOException {

    InputStream resourcesAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourcesAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //当前返回的，其实是基于UserMapper所产生的代理对象：底层：JDK动态代理 实际类型：proxy
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    //设置分页参数
    //参数1：当前页
    //参数2：每页显示的条数
    PageHelper.startPage(1, 2);

    List<User> users = mapper.findAllResultMap();

    for (User user:users
         ) {

        System.out.println(user);

    }

    //获取分页相关的其他参数
    PageInfo<User> userPageInfo = new PageInfo<User>(users);
    System.out.println("总条数："+userPageInfo.getTotal());
    System.out.println("总页数："+userPageInfo.getPages());
    System.out.println("是否是第一页："+userPageInfo.isIsFirstPage());

    sqlSession.close();

}

3.2 知识小结
MyBatis核心配置文件常用标签：
1 properties标签：该标签可以加载外部的properties文件
2 typeAliases标签：设置类型别名
3 environment标签：数据源环境配置标签
4 plugins标签：配置MyBatis的插件

四 Mybatis多表查询
4.1 数据库表关系介绍
关系型数据库表关系分为：一对一 一对多 多对多
表准备：

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` ( 
`id` INT(11) NOT NULL AUTO_INCREMENT, 
`ordertime` VARCHAR(255) DEFAULT NULL, 
`total` DOUBLE DEFAULT NULL, 
`uid` INT(11) DEFAULT NULL, 
PRIMARY KEY (`id`), 
KEY `uid` (`uid`), 
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ) 
ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; 
-- ---------------------------- -- Records of orders -- ---------------------------- 
INSERT INTO `orders` VALUES ('1', '2020-12-12', '3000', '1'); 
INSERT INTO `orders` VALUES ('2', '2020-12-12', '4000', '1'); 
INSERT INTO `orders` VALUES ('3', '2020-12-12', '5000', '2'); 
-- ---------------------------- -- Table structure for sys_role -- ---------------------------- 
DROP TABLE IF EXISTS `sys_role`; 
CREATE TABLE `sys_role` ( 
`id` INT(11) NOT NULL AUTO_INCREMENT, 
`rolename` VARCHAR(255) DEFAULT NULL, 
`roleDesc` VARCHAR(255) DEFAULT NULL, PRIMARY KEY (`id`) ) 
ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; 
-- ---------------------------- -- Records of sys_role -- ---------------------------- 
INSERT INTO `sys_role` VALUES ('1', 'CTO', 'CTO'); 
INSERT INTO `sys_role` VALUES ('2', 'CEO', 'CEO'); 
-- ------------------------------ Table structure for sys_user_role -- ---------------------------- 
DROP TABLE IF EXISTS `sys_user_role`; 
CREATE TABLE `sys_user_role` ( 
`userid` INT(11) NOT NULL, 
`roleid` INT(11) NOT NULL, 
PRIMARY KEY (`userid`,`roleid`), 
KEY `roleid` (`roleid`), 
CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `sys_role` (`id`), 
CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `user` (`id`) ) 
ENGINE=INNODB DEFAULT CHARSET=utf8; 
-- ---------------------------- -- Records of sys_user_role -- ---------------------------- 
INSERT INTO `sys_user_role` VALUES ('1', '1'); 
INSERT INTO `sys_user_role` VALUES ('2', '1'); 
INSERT INTO `sys_user_role` VALUES ('1', '2'); 
INSERT INTO `sys_user_role` VALUES ('2', '2');

表之间的关系

4.2 一对一（多对一）
4.2.1 介绍
一对一查询模型
用户表和订单表的关系为：一个用户有多个订单，一个订单只从属于一个用户
一对一查询的需求：查询所有订单，与此同时查询出每个订单所属的用户
一对一查询语句：

-- 查询所有订单，与此同时查询出每个订单所属的用户
SELECT * FROM orders o LEFT JOIN USER u ON o.`uid`=u.`id`

4.2.2 代码实现

（1）Orders实体

package com.lagou.domain;

public class Orders {

    private Integer id;
    private String ordertime;
    private Double total;
    private Integer uid;

    //表示当前订单属于哪个用户
    private User user;

    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", ordertime='" + ordertime + '\'' +
                ", total=" + total +
                ", uid=" + uid +
                ", user=" + user +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getOrdertime() {
        return ordertime;
    }

    public void setOrdertime(String ordertime) {
        this.ordertime = ordertime;
    }

    public Double getTotal() {
        return total;
    }

    public void setTotal(Double total) {
        this.total = total;
    }

    public Integer getUid() {
        return uid;
    }

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

    public User getUser() {
        return user;
    }

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

（2）User实体

package com.lagou.domain;

import java.util.Date;

public class User {

    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

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

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    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 getSex() {
        return sex;
    }

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

    public String getAddress() {
        return address;
    }

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

}

（3）OrderMapper接口

package com.lagou.mapper;

import com.lagou.domain.Orders;

import java.util.List;

public interface OrderMapper {

    /*
    * 一对一关联查询：查询所有订单，与此同时还要查询出每个订单所属的用户信息
    * */
    public List<Orders> findAllWithUser();

}

（4）OrderMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lagou.mapper.OrderMapper">

    <!--一对一关联查询：查询所有订单，与此同时还要查询出每个订单所属的用户信息
    association:在进行一对一关联查询配置时，使用association标签进行关联
    property="user"：要封装实体的属性名
    javaType="com.lagou.domain.User"：要封装实体的属性类型-->

    <resultMap id="orderMap" type="com.lagou.domain.Orders">
        <id property="id" column="id"/>
        <result property="ordertime" column="ordertime"/>
        <result property="total" column="total"/>
        <result property="uid" column="uid"/>


        <association property="user" javaType="com.lagou.domain.User">

            <id property="id" column="uid"/>
            <result property="username" column="username"/>
            <result property="birthday" column="birthday"/>
            <result property="sex" column="sex"/>
            <result property="address" column="address"/>

        </association>
    </resultMap>
    <select id="findAllWithUser" resultMap="orderMap">

        select * from orders o left join user u on o.uid = u.id

    </select>

</mapper>

（5）测试类

package com.lagou.test;

import com.lagou.domain.Orders;
import com.lagou.mapper.OrderMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;


public class mybatisTest {

    /*
     * 一对一关联查询：查询所有订单，与此同时还要查询出每个订单所属的用户信息
     * */
    @Test
    public void test1() throws IOException {

        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();

        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);

        List<Orders> orders = mapper.findAllWithUser();

        for (Orders order:orders) {

            System.out.println(order);
            
        }

        sqlSession.close();

    }
    
}

4.3 一对多
4.3.1 介绍
一对多查询模型
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对多查询的需求：查询所有用户，与此同时查询出该用户具有的订单
一对多查询语句
4.3.2 代码实现
User

package com.lagou.domain;

import java.util.Date;
import java.util.List;

public class User {

    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    //表示多方关系：集合：代表了当前用户所具有的订单列表，collection
    private List<Orders> ordersList;

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

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    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 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 List<Orders> getOrdersList() {
        return ordersList;
    }

    public void setOrdersList(List<Orders> ordersList) {
        this.ordersList = ordersList;
    }
}

UserMapper

package com.lagou.mapper;

import com.lagou.domain.User;

import java.util.List;

public interface UserMapper {

    /*
     * 一对多关联查询：查询所有用户，同时还要查询出每个用户所关联的订单信息
     * */
    public List<User> findAllWithOrder();

}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lagou.mapper.UserMapper">
    <!--一对多关联查询：查询所有用户，同时还要查询出每个用户所关联的订单信息-->

    <resultMap id="userMap" type="com.lagou.domain.User">

        <id property="id" column="id"></id>
        <result property="username" column="username"/>
        <result property="birthday" column="birthday"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>

        <!--collection:一对多，使用collection标签进行关联-->
        <collection property="ordersList" ofType="com.lagou.domain.Orders">

            <id property="id" column="oid"></id>
            <result property="ordertime" column="ordertime"/>
            <result property="total" column="total"/>
            <result property="uid" column="uid"/>

        </collection>

    </resultMap>

    <select id="findAllWithOrder" resultMap="userMap">

        SELECT u.*,o.`id` oid,o.`ordertime`,o.`total`,o.`uid` FROM orders o RIGHT JOIN USER u ON o.uid = u.`id`

    </select>
</mapper>

测试类

/*
 * 一对多关联查询：查询所有用户及关联的订单信息
 * */
@Test
public void test2() throws IOException {

    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

    SqlSession sqlSession = sqlSessionFactory.openSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    List<User> users = mapper.findAllWithOrder();

    for (User user:users) {

        System.out.println(user);

    }

    sqlSession.close();

}

4.4 多对多
4.4.1 介绍
多对多查询的模型
用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询所有用户同时查询出该用户的所有角色
多对多查询语句：

SELECT u.*,r.id rid,r.rolename,r.roleDesc FROM USER u LEFT JOIN sys_user_role ur ON u.`id` = ur.`userid` LEFT JOIN sys_role r ON ur.`roleid` = r.`id`;

4.4.2 代码实现
Role实体类

package com.lagou.domain;

public class Role {

    private Integer id;
    private String rolename;
    private String roleDesc;

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", rolename='" + rolename + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getRolename() {
        return rolename;
    }

    public void setRolename(String rolename) {
        this.rolename = rolename;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }
}

User实体类

package com.lagou.domain;

import java.util.Date;
import java.util.List;

public class User {

    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    //表示多方关系：集合：代表了当前用户所具有的订单列表，collection
    private List<Orders> ordersList;

    //表示多方关系：集合：代表了当前用户所具有的角色列表，collection
    private List<Role> roleList;

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

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    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 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 List<Orders> getOrdersList() {
        return ordersList;
    }

    public void setOrdersList(List<Orders> ordersList) {
        this.ordersList = ordersList;
    }

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }
}

UserMapper接口

package com.lagou.mapper;

import com.lagou.domain.User;

import java.util.List;

public interface UserMapper {

    /*
     * 一对多关联查询：查询所有用户，同时还要查询出每个用户所关联的订单信息
     * */
    public List<User> findAllWithOrder();

    /*
     * 多对多关联查询：查询所有用户，同时还要查询出每个用户所关联的角色信息
     * */
    public List<User> findAllWithRole();

}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lagou.mapper.UserMapper">
    <!--一对多关联查询：查询所有用户，同时还要查询出每个用户所关联的订单信息-->

    <resultMap id="userMap" type="com.lagou.domain.User">

        <id property="id" column="id"></id>
        <result property="username" column="username"/>
        <result property="birthday" column="birthday"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>

        <!--collection:一对多，使用collection标签进行关联-->
        <collection property="ordersList" ofType="com.lagou.domain.Orders">

            <id property="id" column="oid"></id>
            <result property="ordertime" column="ordertime"/>
            <result property="total" column="total"/>
            <result property="uid" column="uid"/>
        </collection>

    </resultMap>

    <resultMap id="userRoleMap" type="com.lagou.domain.User">

        <id property="id" column="id"></id>
        <result property="username" column="username"/>
        <result property="birthday" column="birthday"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>

        <collection property="roleList" ofType="com.lagou.domain.Role">

            <id property="id" column="rid"></id>
            <result property="rolename" column="rolename"/>
            <result property="roleDesc" column="roleDesc"/>

        </collection>

    </resultMap>

    <select id="findAllWithOrder" resultMap="userMap">

        SELECT u.*,o.`id` oid,o.`ordertime`,o.`total`,o.`uid` FROM orders o RIGHT JOIN USER u ON o.uid = u.`id`

    </select>

    <select id="findAllWithRole" resultMap="userRoleMap">

        SELECT u.*,r.id rid,r.rolename,r.roleDesc FROM USER u LEFT JOIN sys_user_role ur ON u.`id` = ur.`userid` LEFT JOIN sys_role r ON ur.`roleid` = r.`id`

    </select>
</mapper>

测试类

/*
     * 多对多关联查询：查询所有用户及关联的角色信息
     * */
    @Test
    public void test3() throws IOException {

        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> allWithRole = mapper.findAllWithRole();

        for (User user:allWithRole
             ) {

            System.out.println(user);

        }

        sqlSession.close();

    }

结果为：

4.5 小结
MyBatis多表配置方式
多对一（一对一）配置：使用< resultMap >+< association >做配置
一对多配置：使用< resultMap >+< collection >做配置
多对多配置：使用< resultMap >+< collection >做配置
多对多的配置跟一对多很相似，难度在于SQL语句的缩写

五 MyBatis嵌套查询
5.1 什么是嵌套查询
嵌套查询就是将原来多表查询中的联合查询语句拆成单个表的查询，再使用mybatis的语法嵌套在一起
举个例子：

需求：查询一个订单，与此同时查询出该订单所属的用户
1.联合查询
select * from orders o left join user u on o.uid = u.id;
2.嵌套查询
2.1 先查询订单
select * from orders;
2.2 再根据订单uid外键，查询用户
select * from user where id = #{根据订单查询的uid}
2.3 最后使用mybatis，将以上两步嵌套起来

5.2 一对一嵌套查询
5.2.1 介绍
需求：查询订单信息，与此同时查询出该订单所属的用户
一对一查询语句

// 先查询订单
select * from orders;
// 再根据订单uid外键，查询用户
select * from user where id = #{订单的uid};

5.2.2 代码实现
（1）OrderMapper接口

/*
 * 一对一嵌套查询：查询所有订单，与此同时还要查询出每个订单所属的用户信息
 * */
public List<Orders> findAllWithUser2();

（2）OrderMapper.xml映射

<resultMap id="orderMap2" type="com.lagou.domain.Orders">

    <id property="id" column="id"></id>
    <result property="ordertime" column="ordertime"/>
    <result property="total" column="total"/>
    <result property="uid" column="uid"/>

    <!--问题：1.怎么去执行第二条sql，2.如何执行第二条sql的时候，把uid作为参数进行传递-->

    <association property="user" javaType="com.lagou.domain.User" select="com.lagou.mapper.UserMapper.findById" column="uid">

    </association>

</resultMap>

<select id="findAllWithUser2" resultMap="orderMap2">

    select * from orders

</select>

（3）UserMapper接口

/*
 * 根据id查询用户
 * */
 public User findById(Integer id);

（4）UserMapper.xml

<!--根据id查询用户-->
<select id="findById" resultType="com.lagou.domain.User" parameterType="int">

    select * from user where id = #{id};

</select>

（5）测试类

/*
 * 一对一嵌套查询：查询所有订单及关联的用户信息
 * */
@Test
public void test4() throws IOException {

    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

    SqlSession sqlSession = sqlSessionFactory.openSession();

    OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);

    List<Orders> allWithUser2 = mapper.findAllWithUser2();

    for (Orders orders:allWithUser2) {

        System.out.println(orders);

    }

    sqlSession.close();

}

5.3 一对多嵌套查询
5.3.1 介绍
需求：查询所有用户，与此同时查询出该用户具有的订单
一对多查询语句

//先查询用户
select * from user
//再根据用户id主键，查询订单列表
select * from orders where uid = #{用户id}

5.3.2 代码实现
（a）UserMapper接口

/*
 * 一对多嵌套查询：查询所有用户，同时还要查询出每个用户所关联的订单信息
 * */
 public List<User> findAllWithOrder2();

（b）UserMapper.xml映射

<resultMap id="userMap2" type="com.lagou.domain.User">

   <id property="id" column="id"></id>
   <result property="username" column="username"/>
   <result property="birthday" column="birthday"/>
   <result property="sex" column="sex"/>
   <result property="address" column="address"/>

   <collection property="ordersList" ofType="com.lagou.domain.Orders" select="com.lagou.mapper.OrderMapper.findByUid" column="id">

       <id property="id" column="oid"></id>
       <result property="ordertime" column="ordertime"/>
       <result property="total" column="total"/>
       <result property="uid" column="uid"/>

   </collection>

</resultMap>

<!--一对多嵌套查询-->
<select id="findAllWithOrder2" resultMap="userMap2">

   select * from user

</select>

（c）OrderMapper接口

/*
 * 一对多嵌套查询：查询所有用户，与此同时查询出每个用户所具有的订单信息
 * */
 public List<Orders> findByUid(Integer uid);

（d）OrderMapper.xml映射

<!--根据uid查询订单-->
<select id="findByUid" resultType="com.lagou.domain.Orders" parameterType="int">

    select * from orders where uid = #{uid}

</select>

结果：

5.4 多对多嵌套查询
5.4.1 介绍
需求：查询用户，同时查询出该用户的所有角色
多对多查询语句：

-- 先查询用户
select * from user;

-- 再根据用户id主键，查询角色列表
SELECT * FROM sys_role r INNER JOIN sys_user_role ur ON ur.roleid = r.id WHERE ur.userid = 1

5.4.2 代码实现
UserMapper接口

/*
 * 多对多嵌套查询：查询所有用户，同时还要查询出每个用户所关联的角色信息
 * */
public List<User> findAllWithRole2();

UserMapper.xml

<resultMap id="userRoleMap2" type="com.lagou.domain.User">

    <id property="id" column="id"></id>
    <result property="username" column="username"/>
    <result property="birthday" column="birthday"/>
    <result property="sex" column="sex"/>
    <result property="address" column="address"/>

    <collection property="roleList" ofType="com.lagou.domain.Role" column="id" select="com.lagou.mapper.RoleMapper.findById1">

    </collection>

</resultMap>

<!--多对多嵌套查询-->
<select id="findAllWithRole2" resultMap="userRoleMap2">

    select * from user

</select>

Role类

package com.lagou.domain;

public class Role {

    private Integer id;
    private String rolename;
    private String roleDesc;

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", rolename='" + rolename + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getRolename() {
        return rolename;
    }

    public void setRolename(String rolename) {
        this.rolename = rolename;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }
}

RoleMapper接口

package com.lagou.mapper;

import com.lagou.domain.Role;

import java.util.List;

public interface RoleMapper {

    /*
    * 根据用户id查询对应角色
    * */
    public List<Role> findById1(Integer id);

}

RoleMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lagou.mapper.RoleMapper">

    <select id="findById1" resultType="com.lagou.domain.Role" parameterType="int">

        SELECT * FROM sys_role r INNER JOIN sys_user_role ur ON ur.roleid = r.id WHERE ur.userid = #{uid}


    </select>

</mapper>

测试类

/*
 * 多对多嵌套查询：查询所有用户及关联的角色信息
 * */
@Test
public void test6() throws IOException {

    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

    SqlSession sqlSession = sqlSessionFactory.openSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    List<User> allWithRole2 = mapper.findAllWithRole2();

    for (User user: allWithRole2) {

        System.out.println(user);

    }

}

5.5 小结
一对一配置：使用< resultMap >+< association >做配置，通过column条件，执行select查询
一对多配置：使用< resultMap >+< collection >做配置，通过column条件，执行select查询
多对多配置：使用< resultMap >+< collection >做配置，通过column条件，执行select查询
优点：简化多表查询操作
缺点：执行多次sql语句，浪费数据库性能