1. 复杂映射

1.1 结果映射

1.1.1 基础概念

• resultType : 如果实体的属性名与表中字段名一致，将查询结果自动封装到实体类中
• resutlMap : 如果实体的属性名与表中字段名不一致，可以使用ResutlMap实现手动封装到实体类中

1.1.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="cn.knightzz.mapper.UserMapper"><resultMap id="UserResultMap" type="cn.knightzz.entity.User"><!-- 封装主键字段 --><id property="uid" column="id"></id><!-- 配置普通字段 --><result property="usernameABC" column="username"></result><result property="birthdayABC" column="birthday"></result><result property="sexABC" column="sex"></result><result property="addressABC" column="address"></result></resultMap><select id="findUserById" parameterType="int" resultMap="UserResultMap">select *from userwhere id = #{id};</select>
  </mapper>
  

• 实体类

  package cn.knightzz.entity;import java.util.Date;/*** @author 王天赐* @title: User* @projectName spring-aop-01* @description:* @website http://knightzz.cn/* @github https://github.com/knightzz1998* @date 2022/1/15 22:00*/
  public class User {/*** 用户id*/private Integer uid;/*** 用户名称*/private String usernameABC;/*** 用户生日*/private Date birthdayABC;/*** 性别*/private String sexABC;/*** 地址*/private String addressABC;// ....
  }

• 测试代码

  package cn.knightzz;import cn.knightzz.entity.User;
  import cn.knightzz.mapper.UserMapper;
  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 {@Testpublic void findUserById() throws IOException {InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(stream);// 开启自动提交SqlSession sqlSession = factory.openSession(true);UserMapper mapper = sqlSession.getMapper(UserMapper.class);User user = mapper.findUserById(1);System.out.println(user);sqlSession.close();}
  }

1.2 参数注入

1.2.1 参数注入的三种方式

• 使用 #{arg0}-#{argn} 或者 #{param1}-#{paramn} 获取参数

  <mapper namespace="com.lagou.mapper.UserMapper"> <select id="findByIdAndUsername1" resultType="user"> <!-- select * from user where id = #{arg0} and username = #{arg1} --> select * from user where id = #{param1} and username = #{param2} </select> 
  </mapper>
  

• 使用注解，引入 @Param() 注解获取参数

  public interface UserMapper { public List<User> findByIdAndUsername2(@Param("id") Integer id,@Param("username") String username); }
  

• 使用pojo对象传递参数 推荐

  • User实体类

    package cn.knightzz.entity;import java.util.Date;public class User {/*** 用户id*/private Integer id;/*** 用户名称*/private String username;/*** 用户生日*/private Date birthday;/*** 性别*/private String sex;/*** 地址*/private String address;
    }

  • UserMapper接口

    public List<User> findByIdAndUsername(User user);
    

  • UserMapper.xml

    <mapper namespace="com.lagou.mapper.UserMapper"> <select id="findByIdAndUsername" parameterType="cn.knightzz.entity.User" resultType="cn.knightzz.entity.User"> select * from user where id = #{id} and username = #{username} </select> </mapper>
    

1.3 模糊查询

1.3.1 需求

• 需求 : 根据 username 查询 user 表

1.3.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="cn.knightzz.mapper.UserMapper"><resultMap id="UserResultMap" type="cn.knightzz.entity.User"><!-- 封装主键字段 --><id property="uid" column="id"></id><!-- 配置普通字段 --><result property="usernameABC" column="username"></result><result property="birthdayABC" column="birthday"></result><result property="sexABC" column="sex"></result><result property="addressABC" column="address"></result></resultMap><select id="findUserById" parameterType="int" resultMap="UserResultMap">select *from userwhere id = #{id};</select><select id="findUserByName" parameterType="string" resultMap="UserResultMap">select *from userwhere username like #{username};</select>
  </mapper>
  

• UserMapper

  package cn.knightzz.mapper;import cn.knightzz.entity.User;import java.util.List;public interface UserMapper {/*** 根据id查询用户* @param id 用户id* @return*/public User findUserById(int id);/*** 根据用户名查询用户* @param username* @return*/public User findUserByName(String username);
  }

• 测试代码

      @Testpublic void findUserByName() throws IOException {InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(stream);// 开启自动提交SqlSession sqlSession = factory.openSession(true);UserMapper mapper = sqlSession.getMapper(UserMapper.class);User user = mapper.findUserByName("%应%");System.out.println(user);sqlSession.close();}
  

1.3.3 ${} 与 #{} 区别【笔试题】

• #{} :表示一个占位符号

  • 通过 #{} 可以实现preparedStatement向占位符中设置值，自动进行java类型和jdbc类型转换，
  • #{} 可以有效防止sql注入。
  • #{} 可以接收简单类型值或pojo属性值。
  • 如果parameterType传输单个简单类型值， #{} 括号中名称随便写。

• ${} :表示拼接sql串

  • 通过 ${} 可以将parameterType 传入的内容拼接在sql中且不进行jdbc类型转换，会出现sql注入
    问题。
  • ${} 可以接收简单类型值或pojo属性值。如果parameterType传输单个简单类型值，
  • ${} 括号中只能是value

2. 映射文件

2.1 返回主键

2.1.1 基本概念

• 很多情况下, 我们有这样的需求 : 向数据库中插入一条数据后希望能够立刻拿到这条数据

2.1.2 useGeneratedKeys

• 我们可以通过设置 useGeneratedKeys 的值来设置 查询返回的主键的值

• useGeneratedKeys="true" 声明返回主键 keyProperty="id" 把返回主键的值，封装到实体的id属性中, 注意 这个实体是指的是参数的对象的 id 值, 并不是直接返回的值

• 注意：只适用于主键自增的数据库，mysql 和 sqlserver 支持，oracle不支持

• 实体类 User

  package cn.knightzz.entity;import java.util.Date;/*** @author 王天赐* @title: User* @projectName spring-aop-01* @description:* @website http://knightzz.cn/* @github https://github.com/knightzz1998* @date 2022/1/15 22:00*/
  public class User {/*** 用户id*/private Integer id;/*** 用户名称*/private String username;/*** 用户生日*/private Date birthday;/*** 性别*/private String sex;/*** 地址*/private String address;// .... get / set
  }

• 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="cn.knightzz.mapper.UserMapper"><!--useGeneratedKeys="true" 声明返回主键 keyProperty="id" 把返回主键的值，封装到实体的id属性中,注意 这个实体是指参数的对象--><insert id="saveUser" parameterType="cn.knightzz.entity.User" useGeneratedKeys="true" keyProperty="id">insert into user(username, birthday, sex, address)values (#{username}, #{birthday}, #{sex}, #{address})</insert>
  </mapper>
  

• 测试代码

  package cn.knightzz.test;import cn.knightzz.entity.User;
  import cn.knightzz.mapper.UserMapper;
  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.Before;
  import org.junit.Test;import java.io.IOException;
  import java.io.InputStream;
  import java.util.Date;public class MyBatisMapperTest {private SqlSession sqlSession;@Beforepublic void init() throws IOException {InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);this.sqlSession = build.openSession(true);}@Testpublic void saveUser() {UserMapper mapper = sqlSession.getMapper(UserMapper.class);User user = new User();user.setUsername("KKK");user.setSex("男");user.setBirthday(new Date());user.setAddress("河南");mapper.saveUser(user);System.out.println(user);sqlSession.close();}
  }

2.1.3 selectKey

• selectKey 适用范围广，支持所有类型数据库

• 原理是 : 使用 SELECT LAST_INSERT_ID(); SQL内置的函数, 可以查询最近一次插入数据的主键id

• 常用配置

  • keyColumn="id" 指定主键列名
  • keyProperty="id" 指定主键封装到实体的id属性中
  • resultType="int" 指定主键类型
  • order="AFTER" 设置在sql语句执行前（后），执行此语句

• 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="cn.knightzz.mapper.UserMapper"><!--selectKey 适用范围广，支持所有类型数据库 keyColumn="id" 指定主键列名 keyProperty="id"指定主键封装到实体的id属性中 resultType="int"指定主键类型 order="AFTER" 设置在sql语句执行前（后），执行此语句--><insert id="saveUserBySelectKey" parameterType="cn.knightzz.entity.User" useGeneratedKeys="true" keyProperty="id"><selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER">SELECT LAST_INSERT_ID();</selectKey>insert into user(username, birthday, sex, address)values (#{username}, #{birthday}, #{sex}, #{address})</insert>
  </mapper>
  

• UserMapper

  package cn.knightzz.mapper;import cn.knightzz.entity.User;import java.util.List;public interface UserMapper {/*** 插入用户数据* @param user* @return 返回主键*/public void saveUser(User user);/*** 通过 selectKey 的方式返回主键* @param user*/public void saveUserBySelectKey(User user);
  }

• 测试代码

  package cn.knightzz.test;import cn.knightzz.entity.User;
  import cn.knightzz.mapper.UserMapper;
  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.Before;
  import org.junit.Test;import java.io.IOException;
  import java.io.InputStream;
  import java.util.Date;public class MyBatisMapperTest {private User user = new User();private SqlSession sqlSession;private UserMapper mapper;@Beforepublic void init() throws IOException {InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);sqlSession = build.openSession(true);mapper = sqlSession.getMapper(UserMapper.class);}@Testpublic void saveUserBySelectKey() {user.setUsername("KKK222");user.setSex("女");user.setBirthday(new Date());user.setAddress("河南");mapper.saveUserBySelectKey(user);System.out.println(user);sqlSession.close();}
  }

2.2 动态SQL

2.2.1 基本概念

• 当我们要根据不同的条件，来执行不同的sql语句的时候，需要用到动态sql

2.2.2 动态SQL<if>

• UserMapper接口

      /*** 根据id后者* @param user* @return*/public User findUserByIdAndUsernameIf(User user);
  

• UserMapper.xml

      <select id="findUserByIdAndUsernameIf" parameterType="cn.knightzz.entity.User" resultType="cn.knightzz.entity.User">select * from user<where><if test="id != null">AND id = #{id}</if><if test="username != null">AND username = #{username}</if></where></select>
  

• 测试代码

  package cn.knightzz.test;import cn.knightzz.entity.User;
  import cn.knightzz.mapper.UserMapper;
  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.Before;
  import org.junit.Test;import java.io.IOException;
  import java.io.InputStream;
  import java.util.Date;/*** @author 王天赐* @title: MyBatisMapperTest* @projectName spring-aop-01* @description:* @website http://knightzz.cn/* @github https://github.com/knightzz1998* @date 2022/1/17 18:51*/
  public class MyBatisMapperTest {private User user = new User();private SqlSession sqlSession;private UserMapper mapper;@Beforepublic void init() throws IOException {InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);sqlSession = build.openSession(true);mapper = sqlSession.getMapper(UserMapper.class);}@Testpublic void findUserByIdAndUsernameIf() {user.setUsername("KKK222");User userByIdAndUsernameIf = mapper.findUserByIdAndUsernameIf(user);System.out.println(userByIdAndUsernameIf);sqlSession.close();}
  }

2.2.3 动态SQL<set>

• 动态更新user表数据，如果该属性有值就更新，没有值不做处理。

• UserMapper接口

  /*** 更新用户信息, 使用 set 标签* @param user*/public void updateUserBySet(User user);
  

• 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="cn.knightzz.mapper.UserMapper"><update id="updateUserBySet" parameterType="cn.knightzz.entity.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>
  </mapper>
  

• 测试代码

  package cn.knightzz.test;import cn.knightzz.entity.User;
  import cn.knightzz.mapper.UserMapper;
  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.Before;
  import org.junit.Test;import java.io.IOException;
  import java.io.InputStream;
  import java.util.Date;/*** @author 王天赐* @title: MyBatisMapperTest* @projectName spring-aop-01* @description:* @website http://knightzz.cn/* @github https://github.com/knightzz1998* @date 2022/1/17 18:51*/
  public class MyBatisMapperTest {private User user = new User();private SqlSession sqlSession;private UserMapper mapper;@Beforepublic void init() throws IOException {InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);sqlSession = build.openSession(true);mapper = sqlSession.getMapper(UserMapper.class);}@Testpublic void updateUserBySet() {user.setId(1);user.setUsername("KKK222");mapper.updateUserBySet(user);sqlSession.close();}
  }

2.2.4 动态SQL<foreach>

• foreach主要是用来做数据的循环遍历
  例如： select * from user where id in (1,2,3) 在这样的语句中，传入的参数部分必须依靠
  foreach遍历才能实现。

• <foreach>标签用于遍历集合，它的属性：

• collection：代表要遍历的集合元素

• open：代表语句的开始部分

• close：代表结束部分

• item：代表遍历集合的每个元素，生成的变量名

• sperator：代表分隔符

• UserMapper 接口

      /*** 根据id查询用户列表* @param ids* @return*/public List<User> findAllUserById(List<Integer> ids);
  

• UserMapper.xml 配置

      <select id="findAllUserById" parameterType="java.util.List" resultType="cn.knightzz.entity.User">select * from user<where>/*collection：代表要遍历的集合元素, 通常写 collection 或者 listopen：代表语句的开始部分close：代表结束部分item：代表遍历集合的每个元素，生成的变量名sperator：代表分隔符*/<foreach collection="collection" open="id in (" close=")" item="id" separator=",">#{id}</foreach></where></select>
  

• 测试代码

  package cn.knightzz.test;import cn.knightzz.entity.User;
  import cn.knightzz.mapper.UserMapper;
  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.Before;
  import org.junit.Test;import java.io.IOException;
  import java.io.InputStream;
  import java.util.ArrayList;
  import java.util.Date;
  import java.util.List;/*** @author 王天赐* @title: MyBatisMapperTest* @projectName spring-aop-01* @description:* @website http://knightzz.cn/* @github https://github.com/knightzz1998* @date 2022/1/17 18:51*/
  public class MyBatisMapperTest {private User user = new User();private SqlSession sqlSession;private UserMapper mapper;@Beforepublic void init() throws IOException {InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);sqlSession = build.openSession(true);mapper = sqlSession.getMapper(UserMapper.class);}@Testpublic void findAllUserById() {List<Integer> ids = new ArrayList<>();ids.add(1);ids.add(2);ids.add(2024);List<User> userList = mapper.findAllUserById(ids);for (User user1 : userList) {System.out.println(user1);}sqlSession.close();}
  }

2.2.5 动态SQL<sql>

• 映射文件中可将重复的 sql 提取出来，使用时用 include 引用即可，最终达到 sql 重用的目的

• 使用方法

  • 提取公用代码

        <sql id="selectUser">select * from user</sql>
    

  • 引入SQL代码

     /* 引入 SQL 片段*/
    <include refid="selectUser"></include>
    

• 代码示例

      <sql id="selectUser">select * from user</sql><select id="findAllUserById" parameterType="java.util.List" resultType="cn.knightzz.entity.User">/* 引入 SQL 片段*/<include refid="selectUser"></include><where>/*collection：代表要遍历的集合元素, 通常写 collection 或者 listopen：代表语句的开始部分close：代表结束部分item：代表遍历集合的每个元素，生成的变量名sperator：代表分隔符*/<foreach collection="collection" open="id in (" close=")" item="id" separator=",">#{id}</foreach></where></select>
  

2.3 分页插件

2.3.1 plugins插件

• MyBatis可以使用第三方的插件来对功能进行扩展，分页助手PageHelper是将分页的复杂操作进行封
  装，使用简单的方式即可获得分页的相关数据
• 开发步骤：
  1. 导入通用PageHelper的坐标
  2. 在mybatis核心配置文件中配置PageHelper插件
  3. 测试分页数据获取

2.3.2 案例实现

• 引入 Maven 依赖

          <!-- 分页助手 --><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>
  

• 在 sqlMapConfig.xml 中配置插件

• 特别注意 : 注意 <plugins> 放置的位置要在 <environment> 前面 <typeAliases> 后面

      <!-- 配置插件 --><plugins><plugin interceptor="com.github.pagehelper.PageHelper"><!--1. 指定方言, 因为不同的数据库部分分页语法不同, 需要指定数据库--><property name="dialect" value="mysql"/></plugin></plugins>
  

• UserMapper 接口

      /*** 查询所有用户信息* @return*/public List<User> findAll();
  

• 测试代码

  package cn.knightzz.test;import cn.knightzz.entity.User;
  import cn.knightzz.mapper.UserMapper;
  import com.github.pagehelper.PageHelper;
  import com.github.pagehelper.PageInfo;
  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.Before;
  import org.junit.Test;import java.io.IOException;
  import java.io.InputStream;
  import java.util.ArrayList;
  import java.util.Date;
  import java.util.List;/*** @author 王天赐* @title: MyBatisMapperTest* @projectName spring-aop-01* @description:* @website http://knightzz.cn/* @github https://github.com/knightzz1998* @date 2022/1/17 18:51*/
  public class MyBatisMapperTest {private User user = new User();private SqlSession sqlSession;private UserMapper mapper;@Beforepublic void init() throws IOException {InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);sqlSession = build.openSession(true);mapper = sqlSession.getMapper(UserMapper.class);}@Testpublic void pageHelper() {// 设置分页参数 :// pageNum : 当前页码// pageSize: 每页显示的数据条数PageHelper.startPage(2, 2);List<User> allUsers = mapper.findAll();for (User allUser : allUsers) {System.out.println(allUser);}PageInfo<User> pageInfo = new PageInfo<User>(allUsers);System.out.println("总条数：" + pageInfo.getTotal());System.out.println("总页数：" + pageInfo.getPages());System.out.println("当前页：" + pageInfo.getPageNum());System.out.println("每页显示长度：" + pageInfo.getPageSize());System.out.println("是否第一页：" + pageInfo.isIsFirstPage());System.out.println("是否最后一页：" + pageInfo.isIsLastPage());sqlSession.close();}
  }

3. MyBatis多表查询

3.1 数据库关系

3.1.1 关系表类型

• 关系型数据库表关系分为
  • 一对一
  • 一对多
  • 多对多

3.1.2 关系表案例

• 人和身份证号就是一对一

  • 一个人只能有一个身份证号
  • 一个身份证号只能属于一个人

• 用户和订单就是一对多，订单和用户就是多对一

  • 一个用户可以下多个订单
  • 多个订单属于同一个用户

• 学生和课程就是多对多

  • 一个学生可以选修多门课程
  • 一个课程可以被多个学生选修

• 特例一个订单只从属于一个用户，所以mybatis将多对一看成了一对一

3.2 案例环境

3.2.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 = INNODBAUTO_INCREMENT = 4DEFAULT 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 = INNODBAUTO_INCREMENT = 3DEFAULT 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 = INNODBDEFAULT 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');

3.3 一对一

3.3.1 数据模型

• 用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

• 一对一查询的需求：查询所有订单，与此同时查询出每个订单所属的用户

• 查询sql

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

3.3.2 代码案例

• OrderMapper 接口

  package cn.knightzz.mapper;import cn.knightzz.entity.Order;import java.util.List;/*** @author 王天赐* @title: OrderMapper* @projectName spring-aop-01* @description:* @website http://knightzz.cn/* @github https://github.com/knightzz1998* @date 2022/1/19 11:24*/
  public interface OrderMapper {/*** 查询订单信息以及订单对应的用户信息* @return*/public List<Order> findAllWithUser();
  }

• Order 实体类

  package cn.knightzz.entity;import java.util.Date;/*** @author 王天赐* @title: Oder* @projectName spring-aop-01* @description:* @website http://knightzz.cn/* @github https://github.com/knightzz1998* @date 2022/1/19 11:19*/
  public class Order {/*** 订单id*/private Integer id;/*** 订单时间*/private Date orderTime;/*** 金额*/private double money;/*** 订单所属的用户信息*/private User user;// ... get / set
  }

• OrderMapper.xml

  需要注意的是 <association property="user" 的 user 指的是 实体类Order的User对象的属性值

  映射实体类属性可以使用 <association property="user" javaType="cn.knightzz.entity.User">

  <?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="cn.knightzz.mapper.OrderMapper"><!--一对一（多对一）使用association标签关联property="user" 封装实体的属性名javaType="user" 封装实体的属性类型--><resultMap id="orderReturnMap" type="cn.knightzz.entity.Order"><!-- 定义主键 --><id property="id" column="id"></id><result property="orderTime" column="ordertime"></result><result property="money" column="total"></result><!--property : Order 表中 User 对象的属性名javaType : User 对象的属性类型全路径--><association property="user" javaType="cn.knightzz.entity.User"><!-- 封装主键 --><id property="id" column="id"></id><result property="username" column="username"></result><result property="birthday" column="birthday"></result><result property="sex" column="sex"></result><result property="address" column="address"></result></association></resultMap><select id="findAllWithUser" resultMap="orderReturnMap">select *from ordersjoin user u on u.id = orders.uid</select></mapper>
  

• 测试代码

  package cn.knightzz.test;import cn.knightzz.entity.Order;
  import cn.knightzz.entity.User;
  import cn.knightzz.mapper.OrderMapper;
  import cn.knightzz.mapper.UserMapper;
  import com.github.pagehelper.PageHelper;
  import com.github.pagehelper.PageInfo;
  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.After;
  import org.junit.Before;
  import org.junit.Test;import java.io.IOException;
  import java.io.InputStream;
  import java.util.ArrayList;
  import java.util.Date;
  import java.util.List;/*** @author 王天赐* @title: MyBatisMapperTest* @projectName spring-aop-01* @description:* @website http://knightzz.cn/* @github https://github.com/knightzz1998* @date 2022/1/17 18:51*/
  public class MyBatisMapperTest {private User user = new User();private SqlSession sqlSession;private UserMapper mapper;private OrderMapper orderMapper;@Beforepublic void init() throws IOException {InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);sqlSession = build.openSession(true);mapper = sqlSession.getMapper(UserMapper.class);orderMapper = sqlSession.getMapper(OrderMapper.class);}@Testpublic void findAllWithUser(){List<Order> allWithUsers = orderMapper.findAllWithUser();for (Order allWithUser : allWithUsers) {System.out.println(allWithUser);}}@Afterpublic void destroy(){sqlSession.close();}
  }

3.4 一对多

3.4.1 数据模型

• 用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

• 一对多查询的需求：查询所有用户，与此同时查询出该用户具有的订单

• 查询sql

  select *, o.id as oid from user left join orders o on user.id = o.uid
  

3.4.2 代码案例

• UserMapper 接口

  /*** 查询所有用户信息以及其订单信息* @return*/public List<User> findAllWithOrder();
  

• User 实体类

  package cn.knightzz.entity;import java.util.Date;
  import java.util.List;/*** @author 王天赐* @title: User* @projectName spring-aop-01* @description:* @website http://knightzz.cn/* @github https://github.com/knightzz1998* @date 2022/1/15 22:00*/
  public class User {/*** 用户id*/private Integer id;/*** 用户名称*/private String username;/*** 用户生日*/private Date birthday;/*** 性别*/private String sex;/*** 地址*/private String address;/*** 订单列表*/private List<Order> orderList;
  }

• UserMapper.xml 配置

  <resultMap id="userReturnMap" type="cn.knightzz.entity.User"><!-- 设置 用户表主键与属性映射--><id property="id" column="id"></id><result property="username" column="username"></result><result property="birthday" column="birthday"></result><result property="sex" column="sex"></result><result property="address" column="address"></result><collection property="orderList" ofType="cn.knightzz.entity.Order"><!-- 定义主键 --><id property="id" column="oid"></id><result property="orderTime" column="ordertime"></result><result property="money" column="total"></result></collection></resultMap><select id="findAllWithOrder" resultMap="userReturnMap">select *, o.id as oid from user left join orders o on user.id = o.uid</select>
  

• 测试代码

  package cn.knightzz.test;import cn.knightzz.entity.Order;
  import cn.knightzz.entity.User;
  import cn.knightzz.mapper.OrderMapper;
  import cn.knightzz.mapper.UserMapper;
  import com.github.pagehelper.PageHelper;
  import com.github.pagehelper.PageInfo;
  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.After;
  import org.junit.Before;
  import org.junit.Test;import java.io.IOException;
  import java.io.InputStream;
  import java.util.ArrayList;
  import java.util.Date;
  import java.util.List;/*** @author 王天赐* @title: MyBatisMapperTest* @projectName spring-aop-01* @description:* @website http://knightzz.cn/* @github https://github.com/knightzz1998* @date 2022/1/17 18:51*/
  public class MyBatisMapperTest {private User user = new User();private SqlSession sqlSession;private UserMapper mapper;private OrderMapper orderMapper;@Beforepublic void init() throws IOException {InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);sqlSession = build.openSession(true);mapper = sqlSession.getMapper(UserMapper.class);orderMapper = sqlSession.getMapper(OrderMapper.class);}@Testpublic void findAllWithOrder(){List<User> allWithOrders = mapper.findAllWithOrder();for (User allWithOrder : allWithOrders) {System.out.println(allWithOrder);}}@Afterpublic void destroy(){sqlSession.close();}
  }

3.5 多对多

3.5.1 数据模型

• 用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用

• 多对多查询的需求：查询所有用户同时查询出该用户的所有角色

• 查询SQL

          select *from userleft join sys_user_role sur on user.id = sur.useridinner join sys_role sr on sur.roleid = sr.id
  

3.5.2 代码案例

• User 和 SysRole实体类

package cn.knightzz.entity;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;/*** 表示的当前用户关联的角色列表*/private List<SysRole> roleList;}

package cn.knightzz.entity;public class SysRole {private Integer id;private String roleName;private String roleDesc;// get /set ...
}

• UserMapper 接口

      /*** 查找用户以及其权限列表* @return*/public List<User> findAllWithRole();
  

• UserMapper.xml 配置`

  <resultMap id="userRoleListMap" type="cn.knightzz.entity.User"><!-- 设置 用户表主键与属性映射--><id property="id" column="id"></id><result property="username" column="username"></result><result property="birthday" column="birthday"></result><result property="sex" column="sex"></result><result property="address" column="address"></result><collection property="roleList" ofType="cn.knightzz.entity.SysRole"><!-- 定义主键 --><id property="id" column="roleid"></id><result property="roleName" column="rolename"></result><result property="roleDesc" column="roleDesc"></result></collection></resultMap><select id="findAllWithRole" resultMap="userRoleListMap">select *from userleft join sys_user_role sur on user.id = sur.useridinner join sys_role sr on sur.roleid = sr.id</select>
  

• 测试代码

  package cn.knightzz.test;import cn.knightzz.entity.Order;
  import cn.knightzz.entity.User;
  import cn.knightzz.mapper.OrderMapper;
  import cn.knightzz.mapper.UserMapper;
  import com.github.pagehelper.PageHelper;
  import com.github.pagehelper.PageInfo;
  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.After;
  import org.junit.Before;
  import org.junit.Test;import java.io.IOException;
  import java.io.InputStream;
  import java.util.ArrayList;
  import java.util.Date;
  import java.util.List;/*** @author 王天赐* @title: MyBatisMapperTest* @projectName spring-aop-01* @description:* @website http://knightzz.cn/* @github https://github.com/knightzz1998* @date 2022/1/17 18:51*/
  public class MyBatisMapperTest {private User user = new User();private SqlSession sqlSession;private UserMapper mapper;private OrderMapper orderMapper;@Beforepublic void init() throws IOException {InputStream stream = Resources.getResourceAsStream("sqlMapConfig.xml");SqlSessionFactory build = new SqlSessionFactoryBuilder().build(stream);sqlSession = build.openSession(true);mapper = sqlSession.getMapper(UserMapper.class);orderMapper = sqlSession.getMapper(OrderMapper.class);}@Testpublic void findAllWithRole(){List<User> allWithRoles = mapper.findAllWithRole();for (User allWithRole : allWithRoles) {System.out.println(allWithRole);}}@Afterpublic void destroy(){sqlSession.close();}
  }

4. MyBatis嵌套查询

4.1 基本概念

• 嵌套查询就是将原来多表查询中的联合查询语句拆成单个表的查询，再使用mybatis的语法嵌套在一

  起。

4.2 优缺点

• 优点：简化多表查询操作
• 缺点：执行多次sql语句，浪费数据库性能