mybatis 和spring 数据库连接池 mybatis数据库连接池有几种

文章目录

• 1. Mybatis 连接池简单介绍
• 2. Mybatis 的动态 SQL 语句

• 2.1 if 标签的使用
• 2.2 where 标签的使用
• 2.3 foreach 标签的使用
• 2.4 定义 SQL 片段

• 3. Mybatis 多表查询

• 3.1 多表查询之多对一（一对多）
• 3.1 多表查询之多对多

---

1. Mybatis 连接池简单介绍

• 在 Mybatis 中，数据源 dataSource 共有三类，分别是：

• UNPOOLED ： 不使用连接池的数据源。采用传统的 javax.sql.DataSource 规范中的连接池，Mybatis 中有针对规范的实现
• POOLED ： 使用连接池的数据源。采用池的思想
• JNDI ： 使用 JNDI 实现的数据源，采用服务器提供的 JNDI 技术实现，来获取 DataSource 对象，不同的服务器所能拿到的 DataSource 是不一样的。

• 如果想要修改 Mybatis 使用的数据源，那么就可以在 Mybatis 配置文件中修改：

<!-- 配置数据源（连接池） -->
<dataSource type="POOLED">
    <property name="driver" value="${jdbc.driver}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</dataSource>

• 这里 type 属性的取值就是为POOLED、UNPOOLED、JNDI 。
• 注意，如果不是 Web 或者 Maven 的war工程，是不能使用 JNDI 的。

• 查看 POOLED 的实现 PooledDataSource ，可以看出获取连接时采用了池的思想，大概流程如下图（只是一个简单介绍，不全面）
• 查看 UNPOOLED 的实现 UnpooledDataSource ，可以看出每次获取连接时都会注册驱动并创建新连接，大概流程如下图（只是一个简单介绍，不全面）

---

2. Mybatis 的动态 SQL 语句

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

2.1 if 标签的使用

• 如果这个时候我们有一个需求，就是根据传入的实体类，判断哪些属性不为空，并以此作为查询条件，那么我们就可以用到了 <if></if> 标签：
• 首先定义接口方法：

/**
 * 根据传入的查询条件，进行多条件查询
 *
 * @param user 包含查询的条件，可能包含 username、sex、birthday、address等条件
 * @return
 */
List<User> listUsersByCondition(User user);

• 接着配置映射文件：

<!-- 根据查询条件进行复合查询 -->
<select id="listUsersByCondition" parameterType="cn.ykf.pojo.User" resultType="cn.ykf.pojo.User">
    SELECT * FROM user WHERE 1 = 1
    <if test="username != null and username != ''">
        AND username LIKE CONCAT('%',#{username},'%')
    </if>
    <if test="sex != null and sex != ''">
        AND sex = #{sex}
    </if>
    <if test="address != null and address != ''">
        AND address = #{address}
    </if>
    <if test="birthday != null">
        AND birthday = #{birthday}
    </if>
</select>

• 这里加上 WHERE 1 =1 是防止所有条件都为空时拼接 SQL 语句出错。因为不加上 1 = 1 这个恒等条件的话，如果后面查询条件都没拼接成功，那么 SQL 语句最后会带有一个 WHERE 关键字而没有条件，不符合 SQL 语法。
• <if></if> 标签中 test 属性是必须的，表示判断的条件。其中有几点需要注意：

• 如果 test 有多个条件，那么必须使用 and 进行连接，而不能使用 Java 中的 && 运算符。
• test 中的参数名称必须与实体类的属性保持一致，也就是和 #{参数符号} 保持一致。
• 如果判断条件为字符串，那么除了判断是否为 null 外，最好也判断一下是否为空字符串，'' ，防止 SQL语句将其作为条件查询。

• 测试代码及运行结果如下

/**
     * 测试多条件查询
     */
    @Test
    public void testListUsersByCondition() {
        User user = new User();
        // 加入查询条件
        user.setUsername("王");
        user.setSex("男");
        // 查询并展示
        List<User> users = mapper.listUsersByCondition(user);
        users.forEach(System.out::println);
    }

• 大家可以修改测试代码中的查询条件，观察 SQL 语句的变化。

2.2 where 标签的使用

• 虽然上面的例子并没有问题，但是我们还可以使用 <where></where> 进一步优化 SQL 语句。

<select id="listUsersByCondition" parameterType="cn.ykf.pojo.User" resultType="cn.ykf.pojo.User">
    SELECT * FROM user
    <where>
        <if test="username != null and username != ''">
            AND username LIKE CONCAT('%',#{username},'%')
        </if>
        <if test="sex != null and sex != ''">
            AND sex = #{sex}
        </if>
        <if test="address != null and address != ''">
            AND address = #{address}
        </if>
        <if test="birthday != null">
            AND birthday = #{birthday}
        </if>
    </where>
</select>

• 可以发现，相比之前的 SQL 语句，我们少写了 WHERE 1 = 1，而是使用 <where></where> 标签来代替它。
• <where></where> 标签只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入 WHERE 子句。而且，若语句的开头为 AND 或 OR，<where></where> 标签也会将它们去除。
• 简单来说，就是该标签可以动态添加 WHERE 关键字，并且剔除掉 SQL 语句中多余的 AND 或者 OR。

• 修改测试代码，并观察拼接后的 SQL 语句

@Test
public void testListUsersByCondition() {
    User user = new User();
    // 加入查询条件
    user.setSex("男");
    user.setAddress("北京");
    // 查询并展示
    List<User> users = mapper.listUsersByCondition(user);
    users.forEach(System.out::println);
}

• 可以发现，即便在 SQL 语句中，我们没有添加 WHERE 关键字，并且以 sex 为查询条件的语句中含有多余的 AND ，但是最终 <where></where> 标签都帮我们处理好了。
• 为了避免 Mybatis 在动态拼接 SQL 语句的时候发生错误，建议在编写 SQL 语句时不要添加分号 ;

2.3 foreach 标签的使用

• 假如我们现在有一个新的需求，就是根据一个 id 集合，来查询出 id 在该集合中的所有用户，那么又该怎么实现呢？
• 如果使用普通 SQL 语句的话，那么查询语句应该这样写：SELECT * FROM user WHERE id IN(41,42,43);
• 因此，如果想使用动态 SQL 来完成的话，那么我们就应该考虑如何拼接上 id IN(41,42,43) 这一串内容，这时候，我们的 <foreach></foreach> 标签就出场了。
• 首先修改 QueryVo 类，增加一个成员变量用于存放 id 集合，并增加其 getter()/setter()

public class QueryVo implements Serializable {
    private User user;
    // 如果还有其他的查询条件，就可以一并封装进来
    
    private List<Integer> ids;
    
    public User getUser() {
        return user;
    }

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

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }
}

• 接下来添加接口方法，并配置映射文件

/**
 * 根据id集合，查询用户
 *
 * @param vo
 * @return
 */
List<User> listUsersByIds(QueryVo vo);

<!-- 根据id集合查询用户 -->
<select id="listUsersByIds" parameterType="cn.ykf.pojo.QueryVo" resultType="cn.ykf.pojo.User">
    SELECT * FROM user
    <where>
        <if test="ids != null and ids.size > 0">
            <foreach collection="ids" open="AND id IN (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </if>
    </where>
</select>

• <foreach></foreach> 标签用于遍历集合，每个属性的作用如下所示：

• collection ： 代表要遍历的集合或数组，这个属性是必须的。如果是遍历数组，那么该值只能为 array
• open ： 代表语句的开始部份。
• close ： 代表语句的结束部份。
• item ： 代表遍历集合时的每个元素，相当于一个临时变量。
• separator ： 代表拼接每个元素之间的分隔符。

• 你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象传递给 foreach 作为集合参数。当使用可迭代对象或者数组时，index 是当前迭代的次数，item 的值是本次迭代获取的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。
• 注意，SQL 语句中的参数符号 #{id} 应该与 item="id" 保持一致，也就是说，item 属性如果把临时变量声明为 uid 的话，那么使用时就必须写成 #{uid}。

• 测试代码及运行结果如下

/**
 * 测试根据id集合查询
 */
@Test
public void testListUsersByIds(){
    QueryVo vo = new QueryVo();
    List<Integer> ids = new ArrayList<>();
    ids.add(41);
    ids.add(42);
    ids.add(46);
    vo.setIds(ids);

    List<User> users = mapper.listUsersByIds(vo);
    users.forEach(System.out::println);
}

2.4 定义 SQL 片段

• 在上面的例子中，我们在每条 SQL 中都用到了 SELECT * FROM user ，因此，我们可以把该语句定义为 SQL 片段，以供复用，减少工作量。
• 首先在映射文件中定义代码片段

<!-- 定义代码片段 -->
<sql id="defaultSql">
    SELECT * FROM user
</sql>

• 接着就可以在需要的地方引用该片段了

<!-- 根据id集合查询用户 -->
<select id="listUsersByIds" parameterType="cn.ykf.pojo.QueryVo" resultMap="userMap">
    <!-- 引用代码片段 -->
    <include refid="defaultSql"></include>
    <where>
        <if test="ids != null and ids.size > 0">
            <foreach collection="ids" open="AND id IN (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </if>
    </where>
</select>

---

3. Mybatis 多表查询

• 在说多表查询之前，首先要了解表之间的关系共有以下几种：

• 一对一 ： 人和身份证就是一对一的关系。一个人只能有一个身份证号，而一个身份证号只能属于一个人。
• 多对一（一对多）：学生和班级就是多对一（一对多）的关系。一个班级有多个学生，而一个学生只属于一个班级。
• 多对多 ： 学生和任课老师就是多对多的关系。一个学生可以有多个任课老师，而一个任课老师可以有多个学生。

3.1 多表查询之多对一（一对多）

• 现在以用户和账户为例子，一个用户可以有多个账户，一个账户只能属于一个用户。
• 先搭建相关环境，导入所需数据库表：

# 创建用户表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` datetime default NULL COMMENT '生日',
  `sex` char(1) default NULL COMMENT '性别',
  `address` varchar(256) default NULL COMMENT '地址',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 导入用户数据
insert  into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (41,'老王','2018-02-27 17:47:08','男','北京'),(42,'小二王','2018-03-02 15:09:37','女','北京金燕龙'),(43,'小二王','2018-03-04 11:34:34','女','北京金燕龙'),(45,'传智播客','2018-03-04 12:04:06','男','北京金燕龙'),(46,'老王','2018-03-07 17:37:26','男','北京'),(48,'小马宝莉','2018-03-08 11:44:00','女','北京修正');
# 创建账户表，外键为uid，关联用户表的id
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int(11) NOT NULL COMMENT '编号',
  `uid` int(11) default NULL COMMENT '用户编号',
  `money` double default NULL COMMENT '金额',
  PRIMARY KEY  (`id`),
  KEY `FK_Reference_8` (`uid`),
  CONSTRAINT `FK_Reference_8` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 导入账户数据
insert  into `account`(`id`,`uid`,`money`) values (1,46,1000),(2,45,1000),(3,46,2000);

• 添加账户实体类 Account

/**
 * 账户实体类
 *
 * @author yukaifan
 * @ClassName Account
 * @date 2020/2/4 10:42
 */
public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;

    public Integer getId() {
        return id;
    }

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

    public Integer getUid() {
        return uid;
    }

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

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                '}';
    }
}

• 添加账户实体类对应的 Mapper 接口 AccountMapper 以及映射文件 AccountMapper.xml

/**
 * @author yukaifan
 * @InterfaceName AccountMapper
 * @date 2020/2/4 10:49
 */
public interface AccountMapper {
    /**
     * 查询所有账户，带有用户信息
     *
     * @return
     */
    List<Account> listAllAcounts();
}

<?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.ykf.mapper.AccountMapper">
    <!-- 配置查询所有账户 -->
    <select id="listAllAccounts" resultType="cn.ykf.pojo.Account">
        SELECT * FROM account
    </select>
</mapper>

记得在 Mybatis 配置文件中加入新的映射文件：<mapper resource="AccountMapper.xml"/> 或者直接配置整个包：<package name="cn.ykf.mapper"/>

• 接下来测试一下查询账户

public class AccountMapperTest {

    private InputStream is;
    private SqlSession sqlSession;
    private AccountMapper mapper;

    /**
     * 测试之前执行，用于初始化
     */
    @Before
    public void init() throws Exception {
        // 1. 读取配置文件
        is = Resources.getResourceAsStream("mybatis-config.xml");
        // 2. 创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(is);
        // 3. 获取SqlSession对象
        sqlSession = factory.openSession();
        // 4. 使用SqlSession创建Mapper的代理对象
        mapper = sqlSession.getMapper(AccountMapper.class);
    }

    /**
     * 测试结束执行，用于提交事务和释放资源
     */
    @After
    public void destroy() throws Exception {
        // 6. 提交事务
        sqlSession.commit();
        // 7. 释放资源
        sqlSession.close();
        is.close();
    }

    /**
     * 测试查询所有账户
     */
    @Test
    public void testListAllAccounts() {
        // 5. 使用代理对象执行查询
        List<Account> accounts = mapper.listAllAccounts();
        accounts.forEach(System.out::println);
    }
}

• 运行到这里，我们可以发现，虽然可以查询出账户信息，但是这个结果并没有和用户产生关联，而我们想要的效果是查询出账户信息的同时，把所属用户信息也一并展示。那么我们该如何修改呢？

• 首先编写 SQL 语句，如果想要达到上述效果，那么 SQL 语句可以这样写：

SELECT U.*, a.id AS aid, a.uid, a.money from account a, user u WHERE a.uid = u.id;

• 为了可以接收这条语句的查询结果，那么我们应该对账户实体类 Account 进行修改，为其增加成员变量 User 来接收查询出的用户信息（从表实体应该包含一个主表实体的对象引用）：

public class Account implements Serializable {
    // 其他成员不展示...
    
    /**
     * 账户对应的用户信息,从表实体应该包含一个主表实体的对象引用
     */
    private User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
    
    // 其他setter()/getter()不展示...
    
    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                ", user=" + user +
                '}';
    }

• 因为此时账户实体类 Account 已经发生了变化，所以我们应该在映射文件定义用于封装带有 User 信息的 Account 的映射集合resultMap

<?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.ykf.mapper.AccountMapper">
    <!-- 定义可以封装带有User的Account的 resultMap -->
    <resultMap id="AccountWithUserMap" type="cn.ykf.pojo.Account">
        <id property="id" column="aid"/>
        <result property="uid" column="uid"/>
        <result property="money" column="money"/>
        <!-- 关联 User 对象 -->
        <association property="user" javaType="cn.ykf.pojo.User">
            <id property="id" column="id"/>
            <result property="username" column="username"/>
            <result property="sex" column="sex"/>
            <result property="birthday" column="birthday"/>
            <result property="address" column="address"/>
        </association>
    </resultMap>
    <!-- 配置查询所有账户，带有用户信息 -->
    <select id="listAllAccounts" resultMap="AccountWithUserMap">
        SELECT U.*, a.id AS aid, a.uid, a.money from account a, user u WHERE a.uid = u.id
    </select>
</mapper>

• <association></association> 用于一对一映射，其中的 property 属性表示要关联的属性，javaType 表示待关联的实体类的全限定类名。
• 注意，因为 SQL 语句中为 account 表的 id 字段起了别名 aid ，所以在定义 resultMap 的时候，记得主字段写 column="aid"，而不是 column="id"。

• 此处重新运行测试代码，可以发现查询的账户已经带有了用户信息
• 假如现在又有了一个新的需求，就是查询用户的时候，如果用户拥有账户，那么也要把账户信息展示出来。那么又该如何实现？
• 首先还是思考 SQL 语句如何编写，由于有的用户并没有账户，所以如果采用内连接的方式查询（也就是上面的查询方式），那么会把没有账户的用户筛选掉，像下面这样：
• 因此，我们应该采用外连接的方式，使得没有账户的用户也可以查询出来：

SELECT u.*, a.id AS aid, a.uid, a.money FROM user u LEFT OUTER JOIN account a ON u.id = a.uid;

• 为了可以接收这条语句的查询结果，那么我们应该对用户实体类 User 进行修改，为其增加成员变量 accounts 来接收查询出的账户信息（主表实体应该包含从表实体的集合引用）：

public class User implements Serializable {
    // 其他成员省略不展示...

    /**
     * 一对多关系映射：主表实体应该包含从表实体的集合引用
     */
    private List<Account> accounts;
    
    public List<Account> getAccounts() {
        return accounts;
    }

    public void setAccounts(List<Account> accounts) {
        this.accounts = accounts;
    }

	// 其他setter()/getter()省略不展示...

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

• 修改映射文件

<mapper namespace="cn.ykf.mapper.UserMapper">
    <!-- 配置 resultMap ，完成实体类与数据库表的映射 -->
    <resultMap id="UserWithAccountsMap" type="cn.ykf.pojo.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="sex" column="sex"/>
        <result property="birthday" column="birthday"/>
        <result property="address" column="address"/>
        <!-- 配置user对象中accounts集合的映射 -->
        <collection property="accounts" ofType="cn.ykf.pojo.Account">
            <id property="id" column="aid"/>
            <result property="uid" column="uid"/>
            <result property="money" column="money"/>
        </collection>
    </resultMap>
    <!-- 配置查询所有用户，并且带有账户信息 -->
    <select id="listAllUsers" resultMap="UserWithAccountsMap">
        SELECT u.*, a.id AS aid, a.uid, a.money FROM user u LEFT OUTER JOIN account a ON u.id = a.uid;
    </select>
</mapper>

• <collection ></collection > 用于一对多映射，其中的 property 属性表示要关联的集合，ofType 表示集合中的实体类的全限定类名。

• 测试代码及运行结果如下

@Test
public void testListAllUsers() {
    // 5. 使用代理对象执行查询
    List<User> users = mapper.listAllUsers();
    users.forEach(System.out::println);
}

3.1 多表查询之多对多

• 现在以用户和角色为例子，一个用户可以有多个角色，一个角色可以赋予多个用户。
• 还是一样的需求：

• 查询用户的时候可以把用户所拥有的角色信息展示出来。
• 查询角色的时候可以把角色所赋予的用户信息展示出来。

• 先搭建相关环境，由于是多对多关系，所以需要借助第三张表，导入所需数据库表：

# 创建角色表
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int(11) NOT NULL COMMENT '编号',
  `role_name` varchar(30) default NULL COMMENT '角色名称',
  `role_desc` varchar(60) default NULL COMMENT '角色描述',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 添加角色数据
insert  into `role`(`ID`,`ROLE_NAME`,`ROLE_DESC`) values (1,'院长','管理整个学院'),(2,'总裁','管理整个公司'),(3,'校长','管理整个学校');
# 创建用户角色表，也就是中间表
# uid 和 rid是复合主键，同时也是外键
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
  `uid` int(11) NOT NULL COMMENT '用户编号',
  `rid` int(11) NOT NULL COMMENT '角色编号',
  PRIMARY KEY  (`uid`,`rid`),
  KEY `FK_Reference_10` (`rid`),
  CONSTRAINT `FK_Reference_10` FOREIGN KEY (`rid`) REFERENCES `role` (`id`),
  CONSTRAINT `FK_Reference_9` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 添加用户角色数据
insert  into `user_role`(`uid`,`rid`) values (41,1),(45,1),(41,2);

• 修改用户实体类 User ，并添加角色实体类 Role，为了能体现出多对多的关系，两个实体类都必须包含对方的一个集合引用（多对多关系其实我们看成是双向的一对多关系）

public class User implements Serializable {
    /**
     * 多对多关系映射：包含对方的集合引用
     */
    private List<Role> roles;

    public List<Role> getRoles() {
        return roles;
    }

    public void setRoles(List<Role> roles) {
        this.roles = roles;
    }
    
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                ", accounts=" + accounts +
                ", roles=" + roles +
                '}';
    }

public class Role implements Serializable {
    private Integer roleId;
    private String roleName;
    private String roleDesc;
    /**
     * 多对多关系映射：持有对方集合引用
     */
    private List<User> users;

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }

    public Integer getRoleId() {
        return roleId;
    }

    public void setRoleId(Integer roleId) {
        this.roleId = roleId;
    }

    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;
    }

    @Override
    public String toString() {
        return "Role{" +
                "roleId=" + roleId +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                ", users=" + users +
                '}';
    }
}

• 接下来先完成第一个需求：查询用户的时候可以把用户所拥有的角色信息展示出来，那么这样子的话 SQL 语句该如何编写呢？
• 为了确保所有用户（无论是否具有角色）都可以被查询出来，应该使用左连接

SELECT u.*, r.id as rid, r.role_name, r.role_desc FROM user u
	LEFT OUTER JOIN user_role ur ON u.id = ur.uid
	LEFT OUTER JOIN role r ON ur.rid = r.id;

• 开始编写接口层和映射文件

/**
 * 查询所有用户信息，包含用户所拥有的角色信息
 * @return
 */
List<User> listUsersWithRoles();

<mapper namespace="cn.ykf.mapper.UserMapper">
    <resultMap id="UserWithRolesMap" type="cn.ykf.pojo.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="sex" column="sex"/>
        <result property="birthday" column="birthday"/>
        <result property="address" column="address"/>
        <!-- 配置user对象中roles集合的映射 -->
        <collection property="roles" ofType="cn.ykf.pojo.Role">
            <id property="roleId" column="rid"/>
            <result property="roleName" column="role_name"/>
            <result property="roleDesc" column="role_desc"/>
        </collection>
    </resultMap>
    <!-- 查询所有用户，并且带有角色信息 -->
    <select id="listUsersWithRoles" resultMap="UserWithRolesMap">
        SELECT u.*, r.id as rid, r.role_name, r.role_desc FROM user u
            LEFT OUTER JOIN user_role ur ON u.id = ur.uid
            LEFT OUTER JOIN role r ON ur.rid = r.id
    </select>
</mapper>

• 测试代码和运行结果如下

/**
 * 测试查询带有角色的用户信息
 */
@Test
public void testListUsersWithRoles(){
    List<User> users = mapper.listUsersWithRoles();
    users.forEach(System.out::println);
}

• 第二个需求是查询角色的时候可以把角色所赋予的用户信息展示出来，其实一样的操作，只不过是 SQL 语句发生了变化，上个需求是确保用户信息全部展示，而这个需求是确保所有角色全部展示，所以这里应该用 role 表作为左表来连接另外两张表

SELECT u.*, r.id as rid, r.role_name, r.role_desc FROM role r 
	LEFT OUTER JOIN user_role ur ON r.id = ur.rid 
	LEFT OUTER JOIN user u ON ur.uid = u.id;

• 接口层代码及映射文件如下

public interface RoleMapper {
    /**
     * 查询所有角色，带有用户信息
     *
     * @return
     */
    List<Role> listAllRoles();
}

<mapper namespace="cn.ykf.mapper.RoleMapper">

    <resultMap id="RoleWithUsersMap" type="cn.ykf.pojo.Role">
        <id property="roleId" column="rid"/>
        <result property="roleName" column="role_name"/>
        <result property="roleDesc" column="role_desc"/>
        <!-- 配置role对象中users集合的映射 -->
        <collection property="users" ofType="cn.ykf.pojo.User">
            <id property="id" column="id"/>
            <result property="username" column="username"/>
            <result property="sex" column="sex"/>
            <result property="birthday" column="birthday"/>
            <result property="address" column="address"/>
        </collection>
    </resultMap>

    <!-- 查询所有角色，并且带有用户信息 -->
    <select id="listAllRoles" resultMap="RoleWithUsersMap">
        SELECT u.*, r.id as rid, r.role_name, r.role_desc FROM role r
            LEFT OUTER JOIN user_role ur ON r.id = ur.rid
            LEFT OUTER JOIN user u ON ur.uid = u.id
    </select>
</mapper>

• 运行结果