Mybatis

一、Mybatis简介

1、 原始jdbc操作

1）查询数据

2）插入数据

2、 原始jdbc操作的分析

原始jdbc开发存在的问题如下：

① 数据库连接创建、释放频繁造成系统资源浪费从而影响系统性能；
② sql语句在代码中硬编码，造成代码不易维护，实际应用sql变化的可能较大，sql变动需要改变java代码；
③ 查询操作时，需要手动将结果集中的数据手动封装到实体中。插入操作式，需要手动将实体的数据设置到sql语句的占位符位置

应对上述问题给出的解决方案：

① 使用数据库连接池初始化连接资源
② 将sql语句抽取到xml配置文件中
③ 使用反射、内省等底层技术，自动将实体与表进行属性与字段的自动映射

3、 什么式Mybatis

1）mybatis是一个基于java的持久层(Dao)框架，内部封装了jdbc，使开发者只需要关注sql语句。

2）mybatis通过xml或注解的方式将要执行的各种statement配置起来， 并通过java对象和statement中sql的动态参数进行映射最终执行的sql语句。

3）mybatis框架执行sql并将结果映射为java对象并返回，采用ORM思想解决了实体和数据库映射的问题，对jdbc进行了封装，屏蔽了jdbc api底层访问细节。

二、Mybatis的快速入门

1、 简单案例演示

Mybatis的开发步骤：

① 添加Mybatis坐标

<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.11</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.6</version>
    </dependency>

② 创建user数据表

③ 编写User实体类

public class User {

    private int id;
    private String name;
    private  String password;
    private String address;
    private String phone;

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getAddress() {
        return address;
    }

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

    public String getPhone() {
        return phone;
    }

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

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", address='" + address + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }
}

④ 编写映射文件UserMapper.xml(主要是sql语句)

resultType的值查询结果对应的实体类型，查询结果让Mybatis进行实体封装，要告诉实体的权限名。

<?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="userMapper" resultType="com.chu.domain.User">
    <select id="findAll">
        select * from user
    </select>
</mapper>

⑤ 编写核心文件SqlMapConfig.xml(主要是Mybatis的一些配置)

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

<configuration>
    <!--数据源(连接池)的环境-->
    <environments default="developement">
        <environment id="developement">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test?serverTimezone=GMT"/>
                <property name="username" value="root"/>
                <property name="password" value="4680"/>
            </dataSource>
        </environment>
    </environments>

    <!--加载映射文件-->
    <mappers>
        <mapper resource="com/chu/mapper/UserMapper.xml"></mapper>
    </mappers>
</configuration>

⑥ 编写测试类

	@Test
    public void test1() throws IOException {
        //获得核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        //获得session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获得session回话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行操作 参数：namespace+id
        List<User> userList = sqlSession.selectList("userMapper.findAll");
        //打印数据
        System.out.println(userList);
        //释放资源
        sqlSession.close();
    }

2、 Mybatis的映射文件（xxxMapper.xml）概述

2.1 Mybatis的增删改查操作（Mybatis默认事务是不提交的）

1）插入操作

插入操作注意事项：

① 插入语句使用insert标签；
② 在映射文件中使用parameterType属性指定要插入的数据类型；
③ sql语句中使用#{实体属性名}方式引用实体中的属性值；
④ 插入操作使用的API是sqlSession.insert("命名空间.id",实体对象)；
⑤ 插入操作涉及数据库数据变化，所以要使用sqlSession对象显示的提交事务，即sqlSession.commit()

映射文件：

	<!--插入操作 parameterType传入参数的类型，
            #{参数类型的属性名称,不是表的字段名}-->
    <insert id="save" parameterType="com.chu.domain.User">
        insert into user values (#{id},#{name},#{password},#{address},#{phone})
    </insert>

测试代码：

    @Test
    public void test2() throws IOException {

        //模拟user对象
        User user = new User();
        user.setName("tom");
        user.setPassword("999999");
        user.setAddress("luoyang");
        user.setPhone("12222222233");

        //获得核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        //获得session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获得session回话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行操作 参数：namespace+id
        sqlSession.insert("userMapper.save", user);

        //mybatis执行更新操作 提交事务
        sqlSession.commit();

        //释放资源
        sqlSession.close();
    }

2）修改操作 - - -注意事项同插入类似

映射文件：

    <!--修改操作-->
    <update id="update" parameterType="com.chu.domain.User">
        update user set name=#{name},password=#{password},address=#{address},phone=#{phone} where id=#{id}
    </update>

测试代码：

// 修改操作
    @Test
    public void test3() throws IOException {

        //模拟user对象
        User user = new User();
        user.setId(7);
        user.setName("lucy");
        user.setPassword("666666");
        user.setAddress("luoyang");
        user.setPhone("13333332233");

        //获得核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        //获得session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获得session回话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行操作 参数：namespace+id
        sqlSession.update("userMapper.update", user);

        //mybatis执行更新操作 提交事务
        sqlSession.commit();

        //释放资源
        sqlSession.close();
    }

3）删除操作 - - -注意事项同插入类似

补：

sql语句中使用 #{任意字符串} 方式引用传递的单个参数

映射文件：

    <!--删除操作-->
    <delete id="delete" parameterType="java.lang.Integer">
        delete from user  where id=#{id}
    </delete>

测试代码：

    // 删除操作
    @Test
    public void test4() throws IOException {

        //获得核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        //获得session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获得session回话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行操作 参数：namespace+id
        sqlSession.update("userMapper.delete", 7);

        //mybatis执行更新操作 提交事务
        sqlSession.commit();

        //释放资源
        sqlSession.close();
    }

2.2 映射配置与其API的知识要点

注：

① 查询数据时，如果根据实体条件查询，还需要加入paramterType="实体全限定类名"

举例查询一个selectOne：

    <!--根据id进行查询一个数据-->
    <select id="findById" resultType="user" parameterType="int">
        select * from user where id=#{id}
    </select>

    //根据id查询一个操作
    @Test
    public void test5() throws IOException {
        //获得核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        //获得session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获得session回话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行操作 参数：namespace+id
        User user = sqlSession.selectOne("userMapper.findById", 1);
        //打印数据
        System.out.println(user);
        //释放资源
        sqlSession.close();
    }

② 删除数据时，如果根据实体条件删除，就需要改成paramterType="实体全限定类名"

3、 MyBatis核心配置文件概述

3.1 Mybatis核心配置文件层级关系

3.2 Mybatis常用配置解析

1）environments标签 - - -数据库环境的配置，支持多环境配置

其中，事务管理器(transactionManager)类型有两种：

JDBC：这个配置就是直接使用JDBC的提交和回滚设置，它依赖于从数据源得到的连接来管理事务作用域。
MANAGED：这个配置几乎没做什么，它从来不提交或回滚一个链接，而是让容器来管理事务的整个生命周期(比如JEE应用服务器的上下文)。默认情况下他会关闭连接，然而一些容器并不希望这样，因此需要将closeConnection属性设置为false来阻止它默认的关闭行为。

其中，数据源(dataSource)类型有三种：

UNPOOLED：这个数据源的实现知识每次被请求时打开和关闭连接。
POOLED：这种数据源的实现利用"池"的概念将JDBC连接对象组织起来。
JNDI：这个数据源的实现是为了能在如EJB或应用服务器这类容器中使用，容器可以几种或在外部配置数据源，然后放置一个JNDI上下文的引用。

2）mappers标签 - - - 加载映射，第一种用的最多

3）properties标签 - - -与spring中类似

    <!--通过properties标签加载外部properties文件-->
    <properties resource="jdbc.properties"></properties>

    <!--数据源(连接池)的环境-->
    <environments default="developement">
        <environment id="developement">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

4）typeAliases标签 - - -类型别名是为java类型设置一个短的名字

核心配置文件中给全限定类起别名： - - -顺序要在properties之后，mapping之前

	<!--自定义别名,顺序要在properties之后，mapping之前-->
    <typeAliases>
        <typeAlias type="com.chu.domain.User" alias="user"></typeAlias>
    </typeAliases>

在映射文件中使用：

	<select id="findAll" resultType="user">
        select * from user
    </select>

3.3 Mybatis核心配置文件的知识要点

4、 MyBatis相应的API

4.1 SqlSession工厂构建起SqlSessionFactoryBuilder

注：resource是类加载路径，如果是maven工程，则是resources下。

4.2 SqlSession工厂对象SqlSessionFactory

4.3 SqlSession会话对象

三、Mybatis的Dao层实现

1、传统开发方式

1）编写UserMapper接口：

public interface UserMapper {

    public List<User> findAll() throws IOException;
}

2）编写实现接口类：

public class UserMapperImpl implements UserMapper {
    @Override
    public List<User> findAll() throws IOException{
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<User> userList = sqlSession.selectList("userMapper.findAll");

        return userList;
    }
}

3）service简化测试：

public class ServiceDemo {
    public static void main(String[] args) throws IOException {

		//传统方式：
        //创建dao层对象，当前dao层实现是手动编写的
        UserMapperImpl userMapper = new UserMapperImpl();
        List<User> all = userMapper.findAll();

        System.out.println(all);
    }
}

2、代理开发方式

1）代理开发方式介绍

Mapper接口开发需要遵循以下规范：

① Mapper.xml文件中的namespace与mapper接口的全限定名相同
② Mapper接口方法名和Mapper.xml中定义的每个statement的id相同
③ Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql的parameterType的类型相同
④ Mapper接口方法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同

2）编写映射配置文件时与UserMapper接口的对应关系：

3）测试代理方式

关键在于(获取框架生成的接口实现类的代理对象)：

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

4）案例说明：

① UserMapper接口：

public interface UserMapper {

    public List<User> findAll() throws IOException;

    public  User findById(int id);
}

② 编写映射配置文件时与UserMapper接口的对应关系

<mapper namespace="com.chu.dao.UserMapper" >
    <!--resultType的值查询结果对应的实体类型，
        查询结果让Mybatis进行实体封装，要告诉实体的全限定类名-->
    <select id="findAll" resultType="user">
        select * from user
    </select>

    <!--根据id进行查询一个数据-->
    <select id="findById" resultType="user" parameterType="int">
        select * from user where id=#{id}
    </select>

</mapper>

③ 测试代理方式的dao

public class ServiceDemo {
    public static void main(String[] args) throws IOException {
    
        //Mybatis代理方式
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> all = mapper.findAll();
        System.out.println(all);

        User user = mapper.findById(1);
        System.out.println(user);
    }
}

四、Mybatis映射文件的深入分析

1、动态sql语句

1）动态sql语句概述

Mybatis的映射文件中，前面的sql都是比较简单的，有些时候业务逻辑复杂时，sql是动态变化的，此时就需要动态sql语句。

举例：

<mapper namespace="com.chu.mapper.UserMapper" >
    <!--resultType的值查询结果对应的实体类型，
        查询结果让Mybatis进行实体封装，要告诉实体的全限定类名-->
    <select id="findByCondition" resultType="user" parameterType="user">
        select * from user where id=#{id} and name=#{name} and password=#{password} and address=#{address} and phone=#{phone}
    </select>
</mapper>

查询代码：

    @Test
    public void test1() throws IOException {
        //Mybatis代理方式
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 获取框架生成的接口实现类的代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        //模拟条件user
        User condition = new User();
        condition.setId(10);
        condition.setName("hello");
        condition.setPassword("java");
        condition.setAddress("diqiu");
        condition.setPhone("8208208820");

        List<User> userList = mapper.findByCondition(condition);

        System.out.println(userList);
    }
}

注：当模拟条件缺少一个或改变时，sql语句就要发生变化，因此动态sql十分重要

2、动态SQL之 <if>

根据if判断是否满足来决定对应的where条件是否参与查询

    <select id="findByCondition" resultType="user" parameterType="user">
        select * from user
        <where>
            <if test="id!=0">
                and id=#{id}
            </if>
            <if test="name!=null">
                and name=#{name}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
            <if test="address!=null">
                and address=#{address}
            </if>
            <if test="phone!=null">
                and phone=#{phone}
            </if>
        </where>
    </select>

3、动态SQL之 <foreach>

collection是传给查询条件in中的类型；
open是开始的格式；
close是结束的格式；
item是遍历后赋值的变量名；
sparator是以什么分隔

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

查询代码如下：

	@Test
    public void test2() throws IOException {
        //Mybatis代理方式
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 获取框架生成的接口实现类的代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        //模拟条件ids数据
        ArrayList<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        ids.add(4);

        List<User> userList = mapper.findByIds(ids);
        System.out.println(userList);
    }

4、动态sql片段的抽取

使用include标签，refid是抽取出的sql语句的id

	<!--sql语句抽取-->
    <sql id="selectUser">select * from user</sql>

    <select id="findByIds" resultType="user" parameterType="list">
        /*select * from user where id in(1,2,3)*/
        <include refid="selectUser"></include>
        <where>
            <foreach collection="list" open="id in(" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

5、MyBatis映射文件配置总结

五、Mybatis核心配置文件的深入分析

1、自定义类型处理器用到的 - - - typeHandlers标签

开发步骤：

① 定义转换类继承类BaseTypeHandler

② 覆盖4个未实现的方法，其中setNonNullParameter为java程序设置数据到数据库的回调方法，getNullableResult为查询时mysql的字符串类型转换成java的Type类型的方法

③ 在Mybatis核心配置文件中进行注册

④ 测试转换是否正确

举例： 将一个java中的Date数据类型，存到数据库时存成以毫秒数，取出来时转换成java的Date。

没有自定义类型处理器时：

org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: java.sql.SQLException: Data truncated for column 'birthday' at row 1
### The error may involve com.chu.mapper.UserMapper.save-Inline
### The error occurred while setting parameters
### SQL: insert into user        values (?,?,?,?,?,?);
### Cause: java.sql.SQLException: Data truncated for column 'birthday' at row 1

①-②：

public class DateTypeHandler extends BaseTypeHandler<Date> {
    //将java类型转换成数据库需要的类型 i是字段的位置
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        long time = date.getTime();
        preparedStatement.setLong(i,time);

    }

    //将数据库中的类型转换成java类型
    // string参数 要转换的字段名称，resultSet查询出的结果集
    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        // 获得结果集中需要的数据（long）转换成Date类型 返回
        long aLong = resultSet.getLong(s);
        Date date = new Date(aLong);
        return date;
    }

    //将数据库中的类型转换成java类型
    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        // 获得结果集中需要的数据（long）转换成Date类型 返回
        long aLong = resultSet.getLong(i);
        Date date = new Date(aLong);
        return date;
    }

    //将数据库中的类型转换成java类型
    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        // 获得需要的数据（long）转换成Date类型 返回
        long aLong = callableStatement.getLong(i);
        Date date = new Date(aLong);
        return date;
    }
}

③： sqlMapperConfig.xml核心配置文件中

    <!--注册类型处理器-->
    <typeHandlers>
        <typeHandler handler="com.chu.handler.DateTypeHandler"></typeHandler>
    </typeHandlers>

④：测试存入毫秒数数据：

    @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(); //参数true默认提交事务
        //获取框架生成的接口实现类的代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        //创建user
        User user = new User();
        user.setName("li");
        user.setPassword("si");
        user.setAddress("huoxing");
        user.setPhone("1231231223");
        user.setBirthday(new Date());

        //执行保存操作
        mapper.save(user);

        sqlSession.commit();
        sqlSession.close();
    }

取出Date类型数据：

    @Test
    public void test2() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(); //参数true默认提交事务
        //获取框架生成的接口实现类的代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user = mapper.findById(11);
        System.out.println("user中的birthday："+user.getBirthday());

        sqlSession.commit();
        sqlSession.close();
    }

控制台输出结果：

user中的birthday：Sun Jul 18 09:58:17 CST 2021

2、插件用到的 - - - plugins标签 - 例如：分页插件

① 导入坐标

    <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>5.2.0</version>
    </dependency>

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

② 在核心配置文件中配置插件

    <!--配置分页助手插件，注意：配置在mapper之前-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <!--指定方言为mysql-->
            <property name="dialect" value="mysql"/>
        </plugin>
    </plugins>

③ 测试分页数据获取

    @Test
    public void test3() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(); //参数true默认提交事务
        //获取框架生成的接口实现类的代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        //设置分页相关参数  当前页+每页显示的条数
        PageHelper.startPage(1,3);

        List<User> userList = mapper.findAll();
        for (User user:userList){
            System.out.println(user);
        }

		//获得与分页相关的参数
        PageInfo<User> pageInfo = new PageInfo<>(userList);
        System.out.println("当前页:"+pageInfo.getPageNum());
        System.out.println("每页显示条数:"+pageInfo.getPageSize());
        System.out.println("总条数:"+pageInfo.getTotal());
        System.out.println("总页数:"+pageInfo.getPages());
        System.out.println("上一页:"+pageInfo.getPrePage());
        System.out.println("下一页:"+pageInfo.getNextPage());
        System.out.println("是否是第一页:"+pageInfo.isIsFirstPage());
        System.out.println("是否是最后一页:"+pageInfo.isIsLastPage());

        sqlSession.commit();
        sqlSession.close();
    }

每页显示3条数据，控制台输出第1页：

User{id=1, name='章子怡', password='qwerty', address='Beijing', phone='13788658672', birthday=null}
User{id=2, name='郭富城', password='abc123', address='HongKong', phone='15678909898', birthday=null}
User{id=3, name='林志颖', password='654321', address='Taiwan', phone='18612124565', birthday=null}

当前页:1
每页显示条数:3
总条数:7
总页数:3
上一页:0
下一页:2
是否是第一页:true
是否是最后一页:false

3、MyBatis核心配置文件配置总结

六、Mybatis的多表操作

1、一对一查询（一个订单一个用户）

一对一查询的模型：

orders表：

user表：

需求： 要运用Mybatis实现查询：

SELECT * FROM orders o,USER u WHERE o.`uid`=u.`id`;

实现过程如下：

① order类中加入user属性，可把mysql中获取到信息赋给user属性，可知当前订单属于哪个用户的操作：

public class Order {

    private int id;
    private Date ordertime;
    private double total;

    //当前订单属于哪一个用户 即在数据库中主键和外键的联系 uid
    private User user;

get、set、toString... 
}

② 在OrderMapper映射配置文件中：

方式一：将查询出user表的数据往order内部属性的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">

<!--使用代理方式Dao层实现-->
<mapper namespace="com.chu.mapper.OrderMapper" >

    <resultMap id="orderMap" type="order">
        <!--手动指定字段与实体属性的映射关系
            column：数据表字段名称
            property：实体的属性名称
        -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <result column="uid" property="user.id"></result>
        <result column="name" property="user.name"></result>
        <result column="password" property="user.password"></result>
        <result column="address" property="user.address"></result>
        <result column="phone" property="user.phone"></result>
        <result column="birthday" property="user.birthday"></result>
    </resultMap>

    <select id="findAll" resultMap="orderMap" >
        SELECT *,o.id oid FROM orders o,USER u WHERE o.`uid`=u.`id`;
    </select>

</mapper>

方式二：property与当前实体(order)中的user属性匹配，javaType当前实体中的属性类型是user

对象属性user，用到association标签

<?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">

<!--使用代理方式Dao层实现-->
<mapper namespace="com.chu.mapper.OrderMapper" >

    <resultMap id="orderMap" type="order">
        <!--手动指定字段与实体属性的映射关系
            column：数据表字段名称
            property：实体的属性名称
        -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
       
        <!--方式二：property与当前实体(order)中的user属性匹配
                javaType当前实体中的属性类型是user-->
        <association property="user" javaType="user">
            <!--property对应的是user中的属性名-->
            <id column="uid" property="id"></id>
            <result column="name" property="name"></result>
            <result column="password" property="password"></result>
            <result column="address" property="address"></result>
            <result column="phone" property="phone"></result>
            <result column="birthday" property="birthday"></result>
        </association>
    </resultMap>

    <select id="findAll" resultMap="orderMap" >
        SELECT *,o.id oid FROM orders o,USER u WHERE o.`uid`=u.`id`;
    </select>

</mapper>

③ 测试：

    @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(); //参数true默认提交事务
        //获取框架生成的接口实现类的代理对象
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);

        List<Order> orderList = mapper.findAll();
        for (Order order:orderList){
            System.out.println(order);
        }
        
        sqlSession.close();
    }

控制台输出：

Order{id=1, ordertime=Sun Jul 18 10:50:17 CST 2021, total=3000.0, user=User{id=1, name='章子怡', password='qwerty', address='Beijing', phone='13788658672', birthday=null}}
Order{id=2, ordertime=Wed Jul 14 10:50:27 CST 2021, total=5800.0, user=User{id=1, name='章子怡', password='qwerty', address='Beijing', phone='13788658672', birthday=null}}
Order{id=3, ordertime=Thu Jul 22 10:50:48 CST 2021, total=323.0, user=User{id=2, name='郭富城', password='abc123', address='HongKong', phone='15678909898', birthday=null}}
Order{id=4, ordertime=Mon Jul 05 10:50:51 CST 2021, total=2345.0, user=User{id=1, name='章子怡', password='qwerty', address='Beijing', phone='13788658672', birthday=null}}
Order{id=5, ordertime=Fri Jul 09 10:51:05 CST 2021, total=100.0, user=User{id=2, name='郭富城', password='abc123', address='HongKong', phone='15678909898', birthday=null}}
Order{id=6, ordertime=Thu Jul 01 10:51:02 CST 2021, total=2009.0, user=User{id=3, name='林志颖', password='654321', address='Taiwan', phone='18612124565', birthday=null}}

2、一对多查询（一个用户多个订单）

一对多查询的模型：

需求：要运用Mybatis实现查询：

SELECT * FROM orders o,USER u WHERE o.`uid`=u.`id`;

实现过程如下：

① user类中加入泛型为order（订单）的集合属性orderList，可把mysql中获取到信息赋给orderList属性，可知当前用户有多少个订单：

public class User {

    private int id;
    private String name;
    private String password;
    private String address;
    private String phone;
    private Date birthday;

    //描述的是当前用户存在哪些订单
    private List<Order> orderList;

get、set、toString...   
}

② 在UserMapper映射配置文件中：

泛型集合属性orderList用到collection标签

<?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">

<!--使用代理方式Dao层实现-->
<mapper namespace="com.chu.mapper.UserMapper" >

    <!--指定字段与实体属性的关系-->
    <resultMap id="userMap" type="user">
        <id column="uid" property="id"></id>
        <result column="name" property="name"></result>
        <result column="password" property="password"></result>
        <result column="address" property="address"></result>
        <result column="phone" property="phone"></result>
        <result column="birthday" property="birthday"></result>
        <!--配置集合信息
            property：实体对应的属性名称，即集合orderList
            ofType：当前集合中的数据类型
        -->
        <collection property="orderList" ofType="order">
            <!--封装order的数据 property是对应order的属性值-->
            <id column="oid" property="id"></id>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
        </collection>

    </resultMap>

    <select id="findAll"  resultMap="userMap">
        SELECT *,o.id oid FROM orders o,USER u WHERE 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(); //参数true默认提交事务
        //获取框架生成的接口实现类的代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> userList = mapper.findAll();
        for (User user:userList){
            System.out.println(user);
        }

        sqlSession.close();
    }

控制台输出：

User{id=1, name='章子怡', password='qwerty', address='Beijing', phone='13788658672', birthday=null, orderList=[Order{id=1, ordertime=Thu Jan 01 08:00:02 CST 1970, total=3000.0, user=null}, Order{id=2, ordertime=Thu Jan 01 08:00:02 CST 1970, total=5800.0, user=null}, Order{id=4, ordertime=Thu Jan 01 08:00:02 CST 1970, total=2345.0, user=null}]}
User{id=2, name='郭富城', password='abc123', address='HongKong', phone='15678909898', birthday=null, orderList=[Order{id=3, ordertime=Thu Jan 01 08:00:02 CST 1970, total=323.0, user=null}, Order{id=5, ordertime=Thu Jan 01 08:00:02 CST 1970, total=100.0, user=null}]}
User{id=3, name='林志颖', password='654321', address='Taiwan', phone='18612124565', birthday=null, orderList=[Order{id=6, ordertime=Thu Jan 01 08:00:02 CST 1970, total=2009.0, user=null}]}

3、多对多查询（一个用户多个角色，一个角色对应多个用户）需要中间表作为第三张表。

多对多查询的模型：

user表：

sys_user_role表：

sys_role表：

public class Role {

    private int id;
    private String roleName;
    private String roleDesc;
    
get、set、toString...
}

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

SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.`id`=ur.`userId` AND ur.`roleId`=r.`id`

实现过程如下：

① user类中加入泛型为Role（角色）的集合属性roleList，可把mysql中获取到信息赋给roleList属性，可知当前用户有多少个角色：

public class User {

    private int id;
    private String name;
    private String password;
    private String address;
    private String phone;
    private Date birthday;

    //描述的是当前用户具备哪些角色
    private List<Role> roleList;

get、set、toString...   
}

② 在UserMapper映射配置文件中：

泛型集合属性roleList用到collection标签

    <!--指定与实体属性的关系-->
    <resultMap id="userRoleMap" type="user">
        <!--user的信息-->
        <id column="userId" property="id"></id>
        <result column="name" property="name"></result>
        <result column="password" property="password"></result>
        <result column="address" property="address"></result>
        <result column="phone" property="phone"></result>
        <result column="birthday" property="birthday"></result>
        <!--配置user内部集合roleList信息
            property：实体对应的属性名称，即集合orderList
            ofType：当前集合中的数据类型
        -->
        <collection property="roleList" ofType="role">
            <id column="roleId" property="id"></id>
            <result column="roleName" property="roleName"></result>
            <result column="roleDesc" property="roleDesc"></result>
        </collection>

    </resultMap>

    <select id="findUserAndRoleAll"  resultMap="userRoleMap">
        SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.`id`=ur.`userId` AND ur.`roleId`=r.`id`
    </select>

③ 测试：

    @Test
    public void test3() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(); //参数true默认提交事务
        //获取框架生成的接口实现类的代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> userAndRoleAll = mapper.findUserAndRoleAll();
        for (User user:userAndRoleAll){
            System.out.println(user);
        }

        sqlSession.close();
    }

控制台输出：

User{id=1, name='章子怡', password='qwerty', address='Beijing', phone='13788658672', birthday=null, roleList=[Role{id=1, roleName='院长', roleDesc='负责全面工作'}, Role{id=2, roleName='研究员', roleDesc='课程研发工作'}]}
User{id=2, name='郭富城', password='abc123', address='HongKong', phone='15678909898', birthday=null, roleList=[Role{id=2, roleName='研究员', roleDesc='课程研发工作'}, Role{id=3, roleName='讲师', roleDesc='授课工作'}]}

4、多表查询的总结

MyBatis多表配置方式：

① 一对一配置：使用 <resultMap>+<association> 做配置；

② 一对多配置：使用 <resultMap>+<association> 做配置；

③ 多对多配置：使用 <resultMap>+<association> 做配置，与一对多的主要区别在于多一张关系表，在查询是要用到查询语句中。

七、Mybatis的注解开发

1、MyBatis的常用注解

2、MyBatis的增删改查

1）普通xml配置文件的增删改查：

① 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">

<!--使用代理方式Dao层实现-->
<mapper namespace="com.chu.mapper.UserMapper" >

    <select id="findAll" resultType="user">
        select * from user
    </select>

    <!--根据id进行查询一个数据-->
    <select id="findById" resultType="user" parameterType="int">
        select * from user where id=#{id}
    </select>

    <!--插入操作 parameterType传入参数的类型，
            #{参数类型的属性名称,不是表的字段名}-->
    <insert id="save" parameterType="user">
        insert into user values (#{id},#{name},#{password},#{address},#{phone},#{birthday})
    </insert>

    <!--修改操作-->
    <update id="update" parameterType="user">
        update user set name=#{name},password=#{password},address=#{address},phone=#{phone},birthday=#{birthday} where id=#{id}
    </update>

    <!--删除操作-->
    <delete id="delete" parameterType="java.lang.Integer">
        delete from user  where id=#{id}
    </delete>
    
</mapper>

② 测试：

public class MybatisTest {

    private UserMapper mapper;

    //抽取共有代码
    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true); //参数true默认提交事务
        //获取框架生成的接口实现类的代理对象
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void testSave(){
        User user = new User();
        user.setName("good");
        user.setPassword("ooo");
        user.setAddress("zhongguo");
        user.setPhone("66612366666");
        user.setBirthday(new Date());
        mapper.save(user);
    }

    @Test
    public void testUpdate(){
        User user = new User();
        user.setId(11);
        user.setName("ni");
        user.setPassword("hao");
        user.setAddress("zhongguo");
        user.setPhone("2323222333");
        user.setBirthday(new Date());
        mapper.update(user);
    }

    @Test
    public void testDelete(){

        mapper.delete(12);
    }

    @Test
    public void testFindById(){

        User user = mapper.findById(2);
        System.out.println(user);
    }

    @Test
    public void testFindAll(){

        List<User> userList = mapper.findAll(2);
        for (User user:userList){
            System.out.println(user);
        }
    }
}

2）注解方式的增删改查：

① 在UserMapper接口中加入注解，取代UserMapper.xml映射配置文件：

public interface UserMapper {

    @Insert("insert into user values (#{id},#{name},#{password},#{address},#{phone},#{birthday})")
    public void save(User user);

    @Update("update user set name=#{name},password=#{password},address=#{address},phone=#{phone},birthday=#{birthday} where id=#{id}")
    public void update(User user);

    @Delete("delete from user  where id=#{id}")
    public void delete(int id);

    @Select("select * from user where id=#{id}")
    public User findById(int id);

    @Select("select * from user")
    public List<User> findAll(int i);
}

② 在sqlMapConfig核心配置文件中，加载映射关系。

	<!--使用注解方式的增删改查，不是加载映射文件，
            而是加载映射关系 TODO-->
    <mappers>
        <!--指定接口所在的包-->
        <package name="com.chu.mapper"></package>
    </mappers>

③ 测试：

public class MybatisTest {

    private UserMapper mapper;

    //抽取共有代码
    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true); //参数true默认提交事务
        //获取框架生成的接口实现类的代理对象
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void testSave(){
        User user = new User();
        user.setName("good");
        user.setPassword("ooo");
        user.setAddress("zhongguo");
        user.setPhone("66612366666");
        user.setBirthday(new Date());
        mapper.save(user);
    }

    @Test
    public void testUpdate(){
        User user = new User();
        user.setId(11);
        user.setName("ni");
        user.setPassword("hao");
        user.setAddress("zhongguo");
        user.setPhone("2323222333");
        user.setBirthday(new Date());
        mapper.update(user);
    }

    @Test
    public void testDelete(){

        mapper.delete(12);
    }

    @Test
    public void testFindById(){

        User user = mapper.findById(2);
        System.out.println(user);
    }

    @Test
    public void testFindAll(){

        List<User> userList = mapper.findAll(2);
        for (User user:userList){
            System.out.println(user);
        }
    }
}

3、MyBatis的注解实现复杂映射开发

1）一对一查询-注解方式:

实现过程如下：

① order类中加入user属性，可把mysql中获取到信息赋给user属性，可知当前订单属于哪个用户的操作：

public class Order {

    private int id;
    private Date ordertime;
    private double total;

    //当前订单属于哪一个用户 即在数据库中主键和外键的联系 uid
    private User user;

get、set、toString... 
}

② 利用注解在接口OrderMapper中将查询到的结果集封装：

方式一：

public interface OrderMapper {

    @Select("select *,o.id oid from orders o,user u where o.uid=u.id")
    @Results({
            @Result(column = "oid",property = "id"),
            @Result(column = "ordertime",property = "ordertime"),
            @Result(column = "total",property = "total"),
            @Result(column = "uid",property = "user.id"),
            @Result(column = "name",property = "user.name"),
            @Result(column = "password",property = "user.password"),
            @Result(column = "address",property = "user.address"),
            @Result(column = "phone",property = "user.phone"),
            @Result(column = "birthday",property = "user.birthday")
    })
    
    public List<Order> findAll() throws IOException;
    
}

方式二： - - -查询方式是分两步走，前提在OrderMapper注解方法后，UserMapper要有对应的方法供第二步查询

SELECT * FROM orders
SELECT * FROM USER WHERE id=1		UserMapper的findById方法

public interface OrderMapper {

    //方式二：
    @Select("select * from orders")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "ordertime",property = "ordertime"),
            @Result(column = "total",property = "total"),
            @Result(
                    property = "user",          //要封装的属性名称
                    column = "uid",             //根据哪个字段去查询user表的数据
                    javaType = User.class,    //要封装的实体类型
                    //select属性 代表查询哪个接口的方法获取数据
                    one=@One(select = "com.chu.mapper.UserMapper.findById")
            )
    })
    
    public List<Order> findAll() throws IOException;

}

③ 测试

public class MybatisTest2 {

    private OrderMapper mapper;

    //抽取共有代码
    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true); //参数true默认提交事务
        //获取框架生成的接口实现类的代理对象
        mapper = sqlSession.getMapper(OrderMapper.class);
    }

    @Test
    public void testFindAll() throws IOException{

        List<Order> orderList = mapper.findAll();
        for (Order order:orderList){
            System.out.println(order);
        }
    }
}

控制台输出：

Order{id=1, ordertime=Thu Jan 01 08:00:02 CST 1970, total=3000.0, user=User{id=1, name='章子怡', password='qwerty', address='Beijing', phone='13788658672', birthday=null}}
Order{id=2, ordertime=Thu Jan 01 08:00:02 CST 1970, total=5800.0, user=User{id=1, name='章子怡', password='qwerty', address='Beijing', phone='13788658672', birthday=null}}
Order{id=3, ordertime=Thu Jan 01 08:00:02 CST 1970, total=323.0, user=User{id=2, name='郭富城', password='abc123', address='HongKong', phone='15678909898', birthday=null}}
Order{id=4, ordertime=Thu Jan 01 08:00:02 CST 1970, total=2345.0, user=User{id=1, name='章子怡', password='qwerty', address='Beijing', phone='13788658672', birthday=null}}
Order{id=5, ordertime=Thu Jan 01 08:00:02 CST 1970, total=100.0, user=User{id=2, name='郭富城', password='abc123', address='HongKong', phone='15678909898', birthday=null}}
Order{id=6, ordertime=Thu Jan 01 08:00:02 CST 1970, total=2009.0, user=User{id=3, name='林志颖', password='654321', address='Taiwan', phone='18612124565', birthday=null}}

2）一对多查询-注解方式:

实现过程如下：

① user类中加入泛型为order（订单）的集合属性orderList，可把mysql中获取到信息赋给orderList属性，可知当前用户有多少个订单：

public class User {

    private int id;
    private String name;
    private String password;
    private String address;
    private String phone;
    private Date birthday;

    //描述的是当前用户存在哪些订单
    private List<Order> orderList;

get、set、toString...   
}

② 利用注解在接口UserMapper中将查询到的结果集封装：

方式二：两步走，OrederMapper要有第二步的查询方法。

SELECT * FROM USER
SELECT * FROM orders WHERE uid=#{uid}		---OrderMapper的findByUid方法

public interface UserMapper {

    @Select("select * from user")
    @Results({  //id=true表示主键
            @Result(id = true,column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "password",property = "password"),
            @Result(column = "address",property = "address"),
            @Result(column = "phone",property = "phone"),
            @Result(column = "birthday",property = "birthday"),
            @Result(
                    property = "orderList",
                    column = "id",
                    javaType = List.class,
                    many =@Many(select = "com.chu.mapper.OrderMapper.findByUid")
            )
    })
    public List<User> findUserAndOrderAll();

}

③ 测试

public class MybatisTest3 {

    private UserMapper mapper;

    //抽取共有代码
    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true); //参数true默认提交事务
        //获取框架生成的接口实现类的代理对象
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void testFindAll() throws IOException{
        List<User> userList = mapper.findUserAndOrderAll();

        for (User user:userList){
            System.out.println(user);
        }
    }
    
}

控制台输出：

User{id=1, name='章子怡', password='qwerty', address='Beijing', phone='13788658672', birthday=null, orderList=[Order{id=1, ordertime=Thu Jan 01 08:00:02 CST 1970, total=3000.0, user=null}, Order{id=2, ordertime=Thu Jan 01 08:00:02 CST 1970, total=5800.0, user=null}, Order{id=4, ordertime=Thu Jan 01 08:00:02 CST 1970, total=2345.0, user=null}]}
User{id=2, name='郭富城', password='abc123', address='HongKong', phone='15678909898', birthday=null, orderList=[Order{id=3, ordertime=Thu Jan 01 08:00:02 CST 1970, total=323.0, user=null}, Order{id=5, ordertime=Thu Jan 01 08:00:02 CST 1970, total=100.0, user=null}]}
User{id=3, name='林志颖', password='654321', address='Taiwan', phone='18612124565', birthday=null, orderList=[Order{id=6, ordertime=Thu Jan 01 08:00:02 CST 	1970, total=2009.0, user=null}]}
User{id=4, name='梁静茹', password='987654367', address='malaixiya', phone='18912340998', birthday=null, orderList=[]}
User{id=5, name='LadyGaGa', password='123456', address='America', phone='13012386565', birthday=null, orderList=[]}
User{id=10, name='hello', password='java', address='diqiu', phone='8208208820', birthday=null, orderList=[]}
User{id=11, name='ni', password='hao', address='zhongguo', phone='2323222333', birthday=Sun Jul 18 19:37:17 CST 2021, orderList=[]}

3）多对多查询-注解方式:

多一张表，查询语句中就要加入。

实现过程如下：

① user类中加入泛型为order（订单）的集合属性orderList，可把mysql中获取到信息赋给orderList属性，可知当前用户有多少个订单：

public class User {

    private int id;
    private String name;
    private String password;
    private String address;
    private String phone;
    private Date birthday;

    // 描述当前用户具备哪些角色
    private List<Role> roleList;

get、set、toString...   
}

② 利用注解在接口UserMapper中将查询到的结果集封装：

方式二：两步走，RoleMapper要有第二步的查询方法。

SELECT * FROM USER
SELECT * FROM sys_user_role ur,sys_role r WHERE ur.roleId=r.id AND ur.userId=#{uid}上一句sql结果的id值		---RoleMapper的findByUid方法

③ 测试

public class MybatisTest4 {

    private UserMapper mapper;

    //抽取共有代码
    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true); //参数true默认提交事务
        //获取框架生成的接口实现类的代理对象
        mapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void testFindAll() throws IOException{
        List<User> userAndRoleALL = mapper.findUserAndRoleALL();

        for (User user:userAndRoleALL){
            System.out.println(user);
        }
    }
}

控制台输出：

User{id=1, name='章子怡', password='qwerty', address='Beijing', phone='13788658672', birthday=null, orderList=null, rolelist=[Role{id=1, roleName='院长', roleDesc='负责全面工作'}, Role{id=2, roleName='研究员', roleDesc='课程研发工作'}]}
User{id=2, name='郭富城', password='abc123', address='HongKong', phone='15678909898', birthday=null, orderList=null, rolelist=[Role{id=2, roleName='研究员', roleDesc='课程研发工作'}, Role{id=3, roleName='讲师', roleDesc='授课工作'}]}
User{id=3, name='林志颖', password='654321', address='Taiwan', phone='18612124565', birthday=null, orderList=null, rolelist=[]}
User{id=4, name='梁静茹', password='987654367', address='malaixiya', phone='18912340998', birthday=null, orderList=null, rolelist=[]}
User{id=5, name='LadyGaGa', password='123456', address='America', phone='13012386565', birthday=null, orderList=null, rolelist=[]}
User{id=10, name='hello', password='java', address='diqiu', phone='8208208820', birthday=null, orderList=null, rolelist=[]}
User{id=11, name='ni', password='hao', address='zhongguo', phone='2323222333', birthday=Sun Jul 18 19:37:17 CST 2021, orderList=null, rolelist=[]}