Mybatis高级查询与映射文件深入解析-优快云博客

本文档详细介绍了Mybatis的高级查询，包括ResutlMap属性、多条件查询和模糊查询。接着深入探讨了Mybatis映射文件，讲解了返回主键、动态SQL以及SQL片段。此外，还对Mybatis核心配置文件进行了剖析，涉及typeHandlers和plugins标签。最后，详细阐述了Mybatis的多表查询，涵盖了一对一、一对多和多对多关系的查询实现，以及嵌套查询的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

复杂映射&配置文件深入

一 Mybatis高级查询
二 Mybatis映射文件深入
三 Mybatis核心配置文件深入
四 Mybatis多表查询
五 MyBatis嵌套查询

一 Mybatis高级查询

1.1 ResutlMap属性

建立对象关系映射

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

1) 编写UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

import java.util.List;

public interface UserMapper {
    /**
     * 根据id查询用户
     */
    public User findUserById(int id);

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

2) 编写UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--根据id查询用户-->
    <select id="findUserById" parameterType="int" resultMap="userResultMap">
        select * from user where id = #{id}
    </select>

    <!--id：标签的唯一标识
        type: 封装后实体类型
    -->
    <resultMap id="userResultMap" type="com.myProject.domain.User">
        <!--手动配置映射关系-->
        <!--id: 用来配置主键-->
        <id property="id" column="id"></id>
        <!--result: 表中普通字段的封装-->
        <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>

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

3）代码测试

package com.myProject.test;

import com.myProject.domain.User;
import com.myProject.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 {
    /**
     * mybatis的Dao层mapper代理方法测试
     */
    @Test
    public void test1() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 当前返回的其实就是基于UserMapper所产生的代理对象mapper；底层：JDK动态代理 实际类型：proxy
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.findUserById(1);
        System.out.println(user);

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

    @Test
    public void test2() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 当前返回的其实就是基于UserMapper所产生的代理对象mapper；底层：JDK动态代理 实际类型：proxy
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.findAllResultMap();
        users.forEach(System.out::println);

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

1.2 多条件查询（三种）

需求

根据id和username查询user表

1）方式一

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

UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

import java.util.List;

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

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--id：标签的唯一标识
        type: 封装后实体类型
    -->
    <resultMap id="userResultMap" type="com.myProject.domain.User">
        <!--手动配置映射关系-->
        <!--id: 用来配置主键-->
        <id property="id" column="id"></id>
        <!--result: 表中普通字段的封装-->
        <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="findByIdAndUsername1" resultMap="userResultMap">
        <!--使用arg作为参数传递，是从0开始-->
        <!--select * from user where id = #{arg0} and username = #{arg1}-->

        <!--使用param作为参数传递，是从1开始-->
        select * from user where id = #{param1} and username = #{param2}
    </select>
</mapper>

测试

/**
* 多条件查询：方式一
*/
@Test
public void test() throws IOException {
    // 加载核心配置文件
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

    // 获得SqlSessionFactory工厂对象
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

    // 获得SqlSession会话对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    // 当前返回的其实就是基于UserMapper所产生的代理对象mapper；底层：JDK动态代理 实际类型：proxy
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> users = mapper.findByIdAndUsername1(1, "子慕");
    users.forEach(System.out::println);

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

2）方式二

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

UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserMapper {
    /**
     * 多条件查询: 方式二
     *      @Param传入的参数要见名知意，所以要与findByIdAndUsername2()形式参数保持一致
     */
    public List<User> findByIdAndUsername2(@Param("id") int id, @Param("username") String username);
}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--id：标签的唯一标识
        type: 封装后实体类型
    -->
    <resultMap id="userResultMap" type="com.myProject.domain.User">
        <!--手动配置映射关系-->
        <!--id: 用来配置主键-->
        <id property="id" column="id"></id>
        <!--result: 表中普通字段的封装-->
        <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="findByIdAndUsername2" resultMap="userResultMap">
        select * from user where id = #{id} and username = #{username}
    </select>
</mapper>

测试

/**
* 多条件查询：方式二
*/
@Test
public void test() throws IOException {
    // 加载核心配置文件
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

    // 获得SqlSessionFactory工厂对象
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

    // 获得SqlSession会话对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    // 当前返回的其实就是基于UserMapper所产生的代理对象mapper；底层：JDK动态代理 实际类型：proxy
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> users = mapper.findByIdAndUsername2(1, "子慕");
    users.forEach(System.out::println);

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

3）方式三（推荐）

使用pojo(实体)对象传递参数

UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

import java.util.List;

public interface UserMapper {
    /**
     * 多条件查询:方式三
     */
    public List<User> findByIdAndUsername3(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="com.myProject.mapper.UserMapper">
    <!--id：标签的唯一标识
        type: 封装后实体类型
    -->
    <resultMap id="userResultMap" type="com.myProject.domain.User">
        <!--手动配置映射关系-->
        <!--id: 用来配置主键-->
        <id property="id" column="id"></id>
        <!--result: 表中普通字段的封装-->
        <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="findByIdAndUsername3" resultMap="userResultMap" parameterType="com.myProject.domain.User">
        select * from user where id = #{id} and username = #{usernameabc}
    </select>
</mapper>

测试

/**
* 多条件查询：方式三
*/
@Test
public void test() throws IOException {
    // 加载核心配置文件
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

    // 获得SqlSessionFactory工厂对象
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

    // 获得SqlSession会话对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    // 当前返回的其实就是基于UserMapper所产生的代理对象mapper；底层：JDK动态代理 实际类型：proxy
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user = new User();
    user.setId(1);
    user.setUsernameabc("子慕");
    List<User> users = mapper.findByIdAndUsername3(user);
    users.forEach(System.out::println);

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

1.3 模糊查询

需求

1）方式一

UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

import java.util.List;

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

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--id：标签的唯一标识
        type: 封装后实体类型
    -->
    <resultMap id="userResultMap" type="com.myProject.domain.User">
        <!--手动配置映射关系-->
        <!--id: 用来配置主键-->
        <id property="id" column="id"></id>
        <!--result: 表中普通字段的封装-->
        <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="findByUsername" resultMap="userResultMap" parameterType="string">
        <!--#{}：在mybatis中是占位符，引用参数值得时候会自动添加单引号
            #{}：如果只有一个参数时，参数可自定义，但要见名知意
        -->
        select * from user where username like #{username}
    </select>
</mapper>

测试

package com.myProject.test;

import com.myProject.domain.User;
import com.myProject.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 {
    /**
     * 模糊查询：方式一
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        List<User> users = mapper.findByUsername("自动提交%");
        users.forEach(System.out::println);

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

2）方式二

UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

import java.util.List;

public interface UserMapper {
    /**
     * 模糊查询：方式二
     */
    public List<User> findByUsername2(String username);
}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--id：标签的唯一标识
        type: 封装后实体类型
    -->
    <resultMap id="userResultMap" type="com.myProject.domain.User">
        <!--手动配置映射关系-->
        <!--id: 用来配置主键-->
        <id property="id" column="id"></id>
        <!--result: 表中普通字段的封装-->
        <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="findByUsername2" resultMap="userResultMap" parameterType="string">
        <!--parameterType是基本数据类型或者String的时候，${}里面的值只能写value
            ${}：sql语句原样拼接,不会自动添加单引号
        -->
        select * from user where username like '${value}'
    </select>
</mapper>

测试

package com.myProject.test;

import com.myProject.domain.User;
import com.myProject.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 {
    /**
     * 模糊查询：方式二
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        List<User> users = mapper.findByUsername2("自动提交%");
        users.forEach(System.out::println);

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

3） `${}`与 `#{}`区别

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

二 Mybatis映射文件深入

2.1 返回主键

应用场景

我们很多时候有这种需求，向数据库插入一条记录后，希望能立即拿到这条记录在数据库中的主键值。

2.1.1 useGeneratedKeys

UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

public interface UserMapper {
    /**
     * 添加用户同时获取主键：方式一
     */
    public void saveUser(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="com.myProject.mapper.UserMapper">
    <!--添加用户同时获取主键：方式一-->
    <!--useGeneratedKeys: 声明返回主键
        keyProperty：把返回主键的值，封装到实体中的那个属性上
    -->
    <insert id="saveUser" parameterType="user" useGeneratedKeys="true" keyProperty="id">
        insert into user(username, birthday, sex, address) values(#{username}, #{birthday}, #{sex}, #{address})
    </insert>
</mapper>

测试

package com.myProject.test;

import com.myProject.domain.User;
import com.myProject.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.Date;

public class MybatisTest {
    /**
     * 添加用户同时返回主键：方式一
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        User user = new User();
        user.setUsername("AA1");
        user.setBirthday(new Date());
        user.setAddress("北京昌平");
        user.setSex("女");

        System.out.println(user); // 添加用户前，id为null
        mapper.saveUser(user);
        System.out.println(user); // 添加用户后，获取主键id

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

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

2.1.2 selectKey

UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

public interface UserMapper {
    /**
     * 添加用户同时获取主键：方式二
     */
    public void saveUser(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="com.myProject.mapper.UserMapper">
    <!--添加用户同时获取主键：方式二-->
    <insert id="saveUser" parameterType="user">
        <!--selectKey: 适用范围更广，支持所有类型的数据库
                order="AFTER"：设置sql语句执行前（后），执行此语句
                keyColumn="id"：指定主键对应列名
                keyProperty="id"：把返回主键的值，封装到User实体中的那个属性上
                resultType="int"：指定主键类型
        -->
        <selectKey order="AFTER" keyColumn="id" keyProperty="id" resultType="int">
            SELECT LAST_INSERT_ID();
        </selectKey>
        insert into user(username, birthday, sex, address) values(#{username}, #{birthday}, #{sex}, #{address})
    </insert>
</mapper>

测试

package com.myProject.test;

import com.myProject.domain.User;
import com.myProject.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.Date;

public class MybatisTest {
    /**
     * 添加用户同时返回主键：方式二
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        User user = new User();
        user.setUsername("AA2");
        user.setBirthday(new Date());
        user.setAddress("北京昌平");
        user.setSex("女");

        System.out.println(user); // 添加用户前，id为null
        mapper.saveUser(user);
        System.out.println(user); // 添加用户后，获取主键id

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

2.2 动态SQL

应用场景

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

2.2.1 动态 SQL 之 `<if>`

需求

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

在这里插入图片描述

1）UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

import java.util.List;

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

2）UserMapper.xml映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--动态sql之if: 多条件查询-->
    <select id="findByIdAndUsernameIf" parameterType="user" resultType="user">
        <!--select * from user where 1 = 1-->
        <!--<where>: 相当于where 1 = 1, 但是如果没有条件的话，不会拼接上where关键字,相当智能-->
        select * from user
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="username != null">
                and username = #{username}
            </if>
        </where>
    </select>
</mapper>

3）测试代码

package com.myProject.test;

import com.myProject.domain.User;
import com.myProject.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.Date;
import java.util.List;

public class MybatisTest {
    /**
     * 动态sql之if：多条件查询
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        User user = new User();
        user.setUsername("jack");
        List<User> users = mapper.findByIdAndUsernameIf(user);
        users.forEach(System.out::println);

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

2.2.2 动态 SQL 之 `<set>`

需求

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

1）UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

import java.util.List;

public interface UserMapper {
    /**
     * 动态sql的set标签： 动态更新
     */
    public void updateSet(User user);
}

2）UserMapper.xml映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--动态sql的set标签： 动态更新-->
    <update id="updateSet" parameterType="user">
        update user
        <!--<set>: 在更新的时候，会自动添加set关键字 还会去掉最后一个条件的逗号-->
        <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>

3）测试代码

package com.myProject.test;

import com.myProject.domain.User;
import com.myProject.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;

public class MybatisTest {
    /**
     * 动态sql的set标签： 动态更新
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        User user = new User();
        user.setId(7);
        user.setUsername("lili");
        mapper.updateSet(user);

        // DML语句，要手动提交事务
        sqlSession.commit();

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

2.2.3 动态 SQL 之`<foreach>`

foreach主要是用来做数据的循环遍历

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

* <foreach>标签用于遍历集合，它的属性：
	• collection：代表要遍历的集合元素
	• open：代表语句的开始部分
	• close：代表结束部分
	• item：代表遍历集合的每个元素，生成的变量名
	• sperator：代表分隔符

a）集合

UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

import java.util.List;

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

UserMaper.xml映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--动态sql的foreach标签： 多值查询 根据多个id值查询用户-->
    <select id="findByList" parameterType="list" resultType="user">
        select * from user
        <where>
            <!--collection: 代表要遍历的集合元素，通常写collection或者list
                open: 代表语句的开始部分
                close: 代表语句的结束部分
                item: 代表遍历集合中的每个元素，生成变量名
                separator: 分隔符
            -->
            <foreach collection="collection" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>
</mapper>

测试代码

package com.myProject.test;

import com.myProject.domain.User;
import com.myProject.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.ArrayList;
import java.util.List;

public class MybatisTest {
    /**
     * 动态sql的foreach标签： 多值查询 集合
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(6);
        ids.add(2);

        List<User> users = mapper.findByList(ids);
        users.forEach(System.out::println);

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

b）数组

UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

import java.util.List;

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

UserMaper.xml映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--动态sql的foreach标签： 多值查询 根据多个id值查询用户-->
    <select id="findByArray" parameterType="int" resultType="user">
        select * from user
        <where>
            <foreach collection="array" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>
</mapper>

测试代码

package com.myProject.test;

import com.myProject.domain.User;
import com.myProject.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 {
    /**
     * 动态sql的foreach标签： 多值查询 数组
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        Integer[] ids = {6,7,8};

        List<User> users = mapper.findByArray(ids);
        users.forEach(System.out::println);

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

2.3 SQL片段

应用场景

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

<!--抽取的sql片段-->
<sql id="selectUser">
    SELECT * FROM `user`
</sql>
<select id="findByList" parameterType="list" resultType="user" >
    <!--引入sql片段-->
    <include refid="selectUser"></include>
    <where>
        <foreach collection="collection" open="id in(" close=")" item="id"
                 separator=",">
            #{id}
        </foreach>
    </where>
</select>
<select id="findByArray" parameterType="integer[]" resultType="user">
    <!--引入sql片段-->
    <include refid="selectUser"></include>
    <where>
        <foreach collection="array" open="id in(" close=")" item="id"
                 separator=",">
            #{id}
        </foreach>
    </where>
</select>

2.4 知识小结

MyBatis映射文件配置

<select>：查询
<insert>：插入
<update>：修改
<delete>：删除
<selectKey>：返回主键
<where>：where条件
<if>：if判断
<foreach>：for循环
<set>：set设置
<sql>：sql片段抽取

三 Mybatis核心配置文件深入

3.1 typeHandlers标签

无论是 MyBatis在预处理语句（ Preparedstatement）中设置个参数时，还是从结果集中取出个值时，都会用类型处理器将获取的值以合适的方式转换成Java类型。下表描述了一些默认的类型处理器（截取部分）。

类型处理器	Java类型	JDBC类型
BooleanTypeHandler	java. lang Boolean, boolean	数据库兼容的 BOOLEAN
ByteTypeHandler	java.lang.Byte,byte	数据库兼容的 NUMERIC或BYTE
Short TypeHandler	java. lang. Short, short	数据库兼容的 NUMERIC或 SHORT INTEGER
IntegerTypeHandler	java. lang. Integer, int	数据库兼容的 NUMERIC或 INTEGER
LongTypeHandler	java lang. Long, long	数据库兼容的 NUMERIC或 LONG INTEGER

你可以重写类型处理器或创建你自己的类型处理器来处理不支持的或非标准的类型。具体做法为：实现org. apache.ibatis.type.TypeHandler接口，或继承一个很便利的类 org. apache.ibatis.type.BaselypeHandler，然后可以选择性地将它映射到个JDBC类型。

例如需求：一个Java中的Date数据类型，我想将之存到数据库的时候存成个1970年至今的亳秒数，取出来时转换成java的Date，即java的Date与数据库的 varchar毫秒值之间转换。

开发步骤
①定义转换类继承类 BaseTypeHandler<T>
②覆盖4个未实现的方法，其中 setNonNullParameter为jimava程序设置数据到数据库的回调方法， getNullableResult为查询时mysq的字符串类型转换成java的Type类型的方法
③在 MyBatis核心配置文件中进行注册
④测试转换是否正确

User实体

package com.itheima.domain;

import java.util.Date;

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;

    public Date getBirthday() {
        return birthday;
    }

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

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

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

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

UserMapper

package com.itheima.mapper;

import com.itheima.domain.User;

public interface UserMapper {
    public void save(User user);
    public User findById(int id);
}

自定义处理器：DateTypeHandler

package com.itheima.handler;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

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

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

    @Override
    public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        long aLong = rs.getLong(columnIndex);
        Date date = new Date(aLong);
        return date;
    }

    @Override
    public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        long aLong = cs.getLong(columnIndex);
        Date date = new Date(aLong);
        return date;
    }
}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.UserMapper">
    <insert id="save" parameterType="user">
        insert into user values(#{id},#{username},#{password},#{birthday})
    </insert>

    <select id="findById" parameterType="int" resultType="user">
        select * from user where id = #{id}
    </select>
</mapper>

Mybatis核心配置类：sqlMapperConfig

<?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>
    <!--通过properti标签加载外部properties文件-->
    <properties resource="jdbc.properties"></properties>

    <typeAliases>
        <typeAlias type="com.itheima.domain.User" alias="user"></typeAlias>
    </typeAliases>

    <!--注册类型处理器-->
    <typeHandlers>
        <typeHandler handler="com.itheima.handler.DateTypeHandler"></typeHandler>
    </typeHandlers>
    <!--数据源环境-->
    <environments default="developement">
        <environment id="developement">
            <transactionManager type="JDBC"></transactionManager>
            <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>
        </environment>
    </environments>
    <!--加载映射配置文件-->
    <mappers>
        <mapper resource="com/itheima/mapper/UserMapper.xml"></mapper>
    </mappers>
</configuration>

jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///test
jdbc.username=root
jdbc.password=root

log4j.properties

log4j.rootLogger = debug,stdout

log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n


log4j.appender.file = org.apache.log4j.DailyRollingFileAppender
log4j.appender.file.File = C://logs/log.log
log4j.appender.file.layout = org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

测试类

package com.itheima.test;

import com.itheima.domain.User;
import com.itheima.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.Date;

public class MyBatisTest {
    @Test
    public void test1() throws IOException {
        // 获得核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        // 获取session工厂对象
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        // 获得session会话对象
        SqlSession sqlSession = sessionFactory.openSession();
        // 获取代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        // 创建user
        User user = new User();
        user.setUsername("AA");
        user.setPassword("123");
        user.setBirthday(new Date());

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

        // 提交事务
        sqlSession.commit();
        // 释放资源
        sqlSession.close();
    }

    @Test
    public void test2() throws IOException {
        // 获得核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
        // 获取session工厂对象
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        // 获得session会话对象
        SqlSession sqlSession = sessionFactory.openSession();
        // 获取代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

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

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

3.2 plugins标签

MyBatis可以使用第三方的插件来对功能进行扩展，分页助手PageHelper是将分页的复杂操作进行封装，使用简单的方式即可获得分页的相关数据

开发步骤：

①导入通用PageHelper的坐标

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

③测试分页数据获取

①导入通用PageHelper坐标

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.myProject</groupId>
    <artifactId>mybatis_dao</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!--指定编码和版本-->
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.encoding>UTF-8</maven.compiler.encoding>
        <java.version>1.8</java.version>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <!--引入相关依赖-->
    <dependencies>
        <!--引入mybatis依赖-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.4</version>
        </dependency>
        <!--引入mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!--引入junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>compile</scope>
        </dependency>

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

</project>

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

<?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>
    <!--加载properties文件-->
    <properties resource="jdbc.properties"></properties>
    <!--设置别名-->
    <typeAliases>
        <!--方式一：给单个实体起别名（类别名） 如果实体过多，不推荐使用-->
        <typeAlias type="com.myProject.domain.User" alias="user"></typeAlias>
        <!--方式二：批量起别名 (包别名) 别名就是类名且不区分大小写，推荐使用-->
        <package name="com.myProject.domain"/>
    </typeAliases>
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <!--dialect: 指定方言-->
            <property name="dialect" value="mysql"/>
        </plugin>
    </plugins>
    <!--environments: 运行环境-->
    <environments default="development">
        <environment id="development">
            <!--当前的事务管理交由JDBC管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!--数据源信息 POOLED：使用mybatis的连接池-->
            <dataSource type="POOLED">
                <!--配置数据库的属性 动态获取JDBC的属性值-->
                <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>
        </environment>
    </environments>
    <!--引入映射配置文件-->
    <mappers>
        <!--类路径的资源引用 要求：当前工程的类必须存在-->
        <!--<mapper resource="com/myProject/mapper/UserMapper.xml"></mapper>-->
        
        <!--使用映射器接口实现类的全限定类名
            使用该方式：接口和映射文件需要同包同名（目录结构需一致），不推荐使用
        -->
        <!--<mapper class="com.myProject.mapper.UserMapper"></mapper>-->

        <!--批量加载映射 推荐使用-->
        <package name="com.myProject.mapper"/>
    </mappers>
</configuration>

③测试分页代码实现

package com.myProject.test;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.myProject.domain.User;
import com.myProject.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 {
    /**
     * 核心配置文件深入：plugin标签 pageHelper
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

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

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

        // 获取分页相关的其他参数
        PageInfo<User> pageInfo = new PageInfo<>(users);
        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.3 知识小结

MyBatis核心配置文件常用标签：

1、properties标签：该标签可以加载外部的properties文件

2、typeAliases标签：设置类型别名

3、environments标签：数据源环境配置标签

4、plugins标签：配置MyBatis的插件

四 Mybatis多表查询

4.1 数据库表关系介绍

关系型数据库表关系分为

* 一对一
* 一对多
* 多对多

举例

* 人和身份证号就是一对一
	一个人只能有一个身份证号
	一个身份证号只能属于一个人
* 用户和订单就是一对多，订单和用户就是多对一
	一个用户可以下多个订单
	多个订单属于同一个用户
* 学生和课程就是多对多
	一个学生可以选修多门课程
	一个课程可以被多个学生选修
* 特例
	一个订单只从属于一个用户，所以mybatis将多对一看成了一对一

案例环境准备

相关表的创建

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`ordertime` VARCHAR(255) DEFAULT NULL,
	`total` DOUBLE DEFAULT NULL,
	`uid` INT(11) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `uid` (`uid`),
	CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '2020-12-12', '3000', '1');
INSERT INTO `orders` VALUES ('2', '2020-12-12', '4000', '1');
INSERT INTO `orders` VALUES ('3', '2020-12-12', '5000', '2');

-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`rolename` VARCHAR(255) DEFAULT NULL,
	`roleDesc` VARCHAR(255) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role` VALUES ('1', 'CTO', 'CTO');
INSERT INTO `sys_role` VALUES ('2', 'CEO', 'CEO');

-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
	`userid` INT(11) NOT NULL,
	`roleid` INT(11) NOT NULL,
	PRIMARY KEY (`userid`,`roleid`),
	KEY `roleid` (`roleid`),
	CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`roleid`) REFERENCES `sys_role`
(`id`),
	CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`userid`) REFERENCES `user`
(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
INSERT INTO `sys_user_role` VALUES ('1', '1');
INSERT INTO `sys_user_role` VALUES ('2', '1');
INSERT INTO `sys_user_role` VALUES ('1', '2');
INSERT INTO `sys_user_role` VALUES ('2', '2');

pox.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.myProject</groupId>
    <artifactId>mybatis_multitable</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!--指定编码和版本-->
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.encoding>UTF-8</maven.compiler.encoding>
        <java.version>1.8</java.version>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <!--引入相关依赖-->
    <dependencies>
        <!--引入mybatis依赖-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.4</version>
        </dependency>
        <!--引入mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!--引入junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>
</project>

jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///mybatis_db
jdbc.username=root
jdbc.password=root

4.2 一对一（多对一）

4.2.1 介绍

一对一查询模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对一查询的需求：查询所有订单，与此同时查询出每个订单所属的用户

在这里插入图片描述

一对一查询语句

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

在这里插入图片描述

4.2.2 代码实现

在这里插入图片描述

1 ）Orders实体

package com.myProject.domain;

public class Orders {
    private Integer id;
    private String ordertime;
    private Double total;
    private Integer uid;

    // 封装User
    private User user; // 表示当前订单属于哪个用户 关联对象是实体，使用association

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

    public Integer getId() {
        return id;
    }

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

    public String getOrdertime() {
        return ordertime;
    }

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

    public Double getTotal() {
        return total;
    }

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

    public Integer getUid() {
        return uid;
    }

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

2 ）User实体

package com.myProject.domain;

import java.util.Date;

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

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

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

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

    public String getAddress() {
        return address;
    }

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

3 ）OrdersMapper接口

package com.myProject.mapper;

import com.myProject.domain.Orders;

import java.util.List;

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

4 ）OrdersMapper.xml映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.OrdersMapper">
    <!--一对一关联查询：查询所有订单，与此同时查询出每个订单所属的用户-->
    <resultMap id="ordersMap" type= "orders">
        <id property="id" column="id"/>
        <result property="ordertime" column="ordertime" />
        <result property="total" column="total" />
        <result property="uid" column="uid" />

        <!--association: 在进行一对一关联查询配置时，使用association标签进行关联
            property="user": 要封装User实体的属性名
            javaType="com.myProject.domain.User"：要封装User实体的属性类型
			对方关系如果用到的是实体对象，使用association
        -->
        <association property="user" javaType="User">
            <!--由于表orders和表user都有id列名，避免冲突，列名可使用uid,因为外键和主键相同-->
            <id property="id" column="uid"></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="ordersMap">
        SELECT * FROM orders o LEFT JOIN `user` u ON o.uid = u.id
    </select>
</mapper>

5 ）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>
    <!--加载properties文件-->
    <properties resource="jdbc.properties"></properties>
    <!--设置别名-->
    <typeAliases>
        <!--方式二：批量起别名 (包别名) 别名就是类名且不区分大小写，推荐使用-->
        <package name="com.myProject.domain"/>
    </typeAliases>
    <!--environments: 运行环境-->
    <environments default="development">
        <environment id="development">
            <!--当前的事务管理交由JDBC管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!--数据源信息 POOLED：使用mybatis的连接池-->
            <dataSource type="POOLED">
                <!--配置数据库的属性 动态获取JDBC的属性值-->
                <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>
        </environment>
    </environments>
    <!--引入映射配置文件-->
    <mappers>
        <!--批量加载映射 推荐使用-->
        <package name="com.myProject.mapper"/>
    </mappers>
</configuration>

6 ）测试代码

package com.myProject.test;

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

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

public class MybatisTest {
    /*
    对一关联查询：查询所有订单，与此同时查询出每个订单所属的用户
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        List<Orders> orders = mapper.findAllWithUser();
        for (Orders order : orders) {
            System.out.println(order);
        }

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

4.3 一对多

4.3.1 介绍

一对多查询模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对多查询的需求：查询所有用户，与此同时查询出该用户具有的订单

在这里插入图片描述

一对多查询语句

-- 查询所有用户，与此同时查询出该用户具有的订单
SELECT
	u.*,
	o.id oid,
	o.ordertime,
	o.total,
	o.uid
FROM
	USER u
LEFT JOIN orders o ON
	o.uid = u.id

4.3.2 代码实现

1 ）User实体

package com.myProject.domain;

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

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    // 表示多方关系（集合）：代表当前用户所具有的订单列表
    private List<Orders> ordersList; // 关联对象是集合，使用collection

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

    public List<Orders> getOrdersList() {
        return ordersList;
    }

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

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

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

    public String getAddress() {
        return address;
    }

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

2 ）UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

import java.util.List;

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

3 ）UserMapper.xml映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--一对多关联查询： 查询所有的用户，同时还要查询出每个用户所关联的订单信息-->
    <resultMap id="userMap" type="com.myProject.domain.User">
        <!--sql查询的结果，外键和主键的id不相同且订单的id有null值，为了避免冲突，需给orders的id起别名，加以区分-->
        <id property="id" column="id"></id>
        <result property="username" column="username" />
        <result property="birthday" column="birthday" />
        <result property="sex" column="sex" />
        <result property="address" column="address" />
        <!--collection：一对多使用collection标签进行关联
            对方关系如果用到的是集合，使用collection
        -->
        <collection property="ordersList" ofType="com.myProject.domain.Orders">
            <id property="id" column="oid" />
            <result property="ordertime" column="ordertime" />
            <result property="total" column="total" />
            <result property="uid" column="uid" />
        </collection>
    </resultMap>
    <select id="findAllWithOrders" resultMap="userMap">
        SELECT u.*, o.id oid, o.ordertime,o.total, o.uid  FROM user u LEFT JOIN orders o ON o.uid = u.id
    </select>
</mapper>

4 ）测试代码

package com.myProject.test;

import com.myProject.domain.User;
import com.myProject.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 {
    /*
    一对多关联查询： 查询所有的用户，同时还要查询出每个用户所关联的订单信息
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

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

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

4.4 多对多

4.4.1 介绍

多对多查询的模型

用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询所有用户同时查询出该用户的所有角色

在这里插入图片描述

多对多查询语句

-- 多对多查询的需求：查询所有用户同时查询出该用户的所有角色
SELECT
	u.*,
	sr.id rid ,
	sr.rolename ,
	sr.roleDesc
FROM
	USER u
LEFT JOIN sys_user_role sur ON
	sur.userid = u.id
LEFT JOIN sys_role sr ON
	sur.userid = sr.id

4.4.2 代码实现

1 ）User 实体

package com.myProject.domain;

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

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    // 表示多方关系（集合）：代表当前用户所具有的角色列表
    private List<Role> roleList; // 关联对象是集合，使用collection

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

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

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

    public String getAddress() {
        return address;
    }

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

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

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

2 ）Role 实体

package com.myProject.domain;

public class Role {
    private Integer id;
    private String rolename;
    private String roleDesc;

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

    public Integer getId() {
        return id;
    }

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

    public String getRolename() {
        return rolename;
    }

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

    public String getRoleDesc() {
        return roleDesc;
    }

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

3 ）UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

import java.util.List;

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

4 ）UserMapper.xml映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--多对多关联查询： 查询所有的用户，同时还要查询出每个用户所关联的角色信息-->
    <resultMap id="userRoleMap" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username" />
        <result property="birthday" column="birthday" />
        <result property="sex" column="sex" />
        <result property="address" column="address" />
        <!--collection：一对多使用collection标签进行关联
            对方关系如果用到的是集合，使用collection
        -->
        <collection property="roleList" ofType="com.myProject.domain.Role">
            <id property="id" column="rid" />
            <result property="rolename" column="rolename" />
            <result property="roleDesc" column="roleDesc" />
        </collection>
    </resultMap>
    <select id="findAllWithRole" resultMap="userRoleMap">
        SELECT u.*, sr.id rid , sr.rolename , sr.roleDesc FROM USER u
        LEFT JOIN sys_user_role sur ON sur.userid = u.id
        LEFT JOIN sys_role sr ON sur.userid = sr.id
    </select>
</mapper>

5 ）测试代码

package com.myProject.test;

import com.myProject.domain.User;
import com.myProject.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 {
    /*
    多对多关联查询： 查询所有的用户，同时还要查询出每个用户所关联的角色信息
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

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

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

4.5 小结

* 多对一（一对一）配置：使用<resultMap>+<association>做配置
* 一对多配置：使用<resultMap>+<collection>做配置
* 多对多配置：使用<resultMap>+<collection>做配置
* 多对多的配置跟一对多很相似，难度在于SQL语句的编写。

五 MyBatis嵌套查询

5.1 什么是嵌套查询

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

举个栗子

* 需求：查询一个订单，与此同时查询出该订单所属的用户

1. 联合查询
	SELECT * FROM orders o LEFT JOIN USER u ON o.`uid`=u.`id`;
	
2. 嵌套查询
	2.1 先查询订单
		SELECT * FROM orders
		
	2.2 再根据订单uid外键，查询用户
		SELECT * FROM `user` WHERE id = #{根据订单查询的uid}
		
	2.3 最后使用mybatis，将以上二步嵌套起来
		...

5.2 一对一嵌套查询

5.2.1 介绍

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

一对一查询语句

-- 先查询订单
SELECT * FROM orders;

-- 再根据订单uid外键，查询用户
SELECT * FROM `user` WHERE id = #{订单的uid};

5.2.2 代码实现

1 ）Orders实体

package com.myProject.domain;

public class Orders {
    private Integer id;
    private String ordertime;
    private Double total;
    private Integer uid;

    // 封装User
    private User user; // 表示当前订单属于哪个用户 关联对象是实体，使用association

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

    public Integer getId() {
        return id;
    }

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

    public String getOrdertime() {
        return ordertime;
    }

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

    public Double getTotal() {
        return total;
    }

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

    public Integer getUid() {
        return uid;
    }

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

2 ）User实体

package com.myProject.domain;

import java.util.Date;

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

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

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

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

    public String getAddress() {
        return address;
    }

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

3）OrderMapper接口

package com.myProject.mapper;

import com.myProject.domain.Orders;

import java.util.List;

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

4）OrderMapper.xml映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.OrdersMapper">
    <!--一对一嵌套查询： 查询所有订单，与此同时还要查询出每个订单所属的用户信息-->
    <resultMap id="ordersMap" type= "com.myProject.domain.Orders">
        <id property="id" column="id"/>
        <result property="ordertime" column="ordertime" />
        <result property="total" column="total" />
        <result property="uid" column="uid" />

        <!--association: 在进行一对一关联查询配置时，使用association标签进行关联
            property="user": 要封装User实体的属性名
            javaType="com.myProject.domain.User"：要封装User实体的属性类型
            问题：1.在呢么去执行第二条sql, 2.如何执行第二条sql的时候，把uid作为参数进行传递
        -->
        <association property="user" javaType="user" select="com.myProject.mapper.UserMapper.findById" column="uid" />
    </resultMap>
    <select id="findAllWithUser" resultMap="ordersMap">
        SELECT * FROM orders
    </select>
</mapper>

5）UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

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

6）UserMapper.xml映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--根据id查询用户-->
    <select id="findById" resultType="com.myProject.domain.User" parameterType="int">
        select * from user where id = #{id}
    </select>
</mapper>

7）测试代码

package com.myProject.test;

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

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

public class MybatisTest {
    /*
    一对一嵌套查询： 查询所有订单，与此同时还要查询出每个订单所属的用户信息
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

        List<Orders> orders = mapper.findAllWithUser();
        for (Orders order : orders) {
            System.out.println(order);
        }

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

在这里插入图片描述

5.3 一对多嵌套查询

5.3.1 介绍

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

一对多查询语句

-- 需求：查询所有用户，与此同时查询出该用户具有的订单
	-- 先查询用户
	SELECT * FROM USER;
	
	-- 根据用户的id主键，查询订单信息
	SELECT * FROM orders o where o.uid = #{用户id};

5.3.2 代码实现

1）User实体

package com.myProject.domain;

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

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    // 表示多方关系（集合）：代表当前用户所具有的订单列表
    private List<Orders> ordersList; // 关联对象是集合，使用collection

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

    public List<Orders> getOrdersList() {
        return ordersList;
    }

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

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

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

    public String getAddress() {
        return address;
    }

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

2）Orders实体

package com.myProject.domain;

public class Orders {
    private Integer id;
    private String ordertime;
    private Double total;
    private Integer uid;

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

    public Integer getId() {
        return id;
    }

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

    public String getOrdertime() {
        return ordertime;
    }

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

    public Double getTotal() {
        return total;
    }

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

    public Integer getUid() {
        return uid;
    }

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

3）UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

import java.util.List;

public interface UserMapper {
    /*
    一对多嵌套查询 查询所有用户，与此同时查询出该用户具有的订单
     */
    public List<User> findAllWithOrder();
}

4）UserMapper.xml映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--一对多嵌套查询 查询所有用户，与此同时查询出该用户具有的订单-->
    <resultMap id="userOrderMap" type="com.myProject.domain.User">
        <id property="id" column="id" />
        <result property="username" column="username" />
        <result property="birthday" column="birthday" />
        <result property="sex" column="sex" />
        <result property="address" column="address" />
        
        <collection property="ordersList" ofType="com.myProject.domain.Orders" column="id" select="com.myProject.mapper.OrdersMapper.findByUid" />
    </resultMap>
    <select id="findAllWithOrder" resultMap="userOrderMap">
        SELECT * FROM USER
    </select>
</mapper>

5）OrderMapper接口

package com.myProject.mapper;

import com.myProject.domain.Orders;

import java.util.List;

public interface OrdersMapper {
    /*
    根据uid查询对应订单
     */
    public List<Orders> findByUid(Integer uid);
}

6）OrderMapper.xml映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.OrdersMapper">
    <!--根据uid查询对应订单-->
    <select id="findByUid" parameterType="int" resultType="com.myProject.domain.Orders">
        SELECT * FROM orders o where o.uid = #{uid}
    </select>
</mapper>

7）测试代码

package com.myProject.test;

import com.myProject.domain.Orders;
import com.myProject.domain.User;
import com.myProject.mapper.OrdersMapper;
import com.myProject.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 {
    /*
    一对多嵌套查询 查询所有用户，与此同时查询出该用户具有的订单
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

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

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

5.4 多对多嵌套查询

5.4.1 介绍

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

多对多查询语句

-- 需求：查询用户 同时查询出该用户的所有角色
	-- 先查询用户
	SELECT * FROM USER;
	
	-- 根据查询出来的用户id，查询角色信息
	SELECT
	*
	FROM
		sys_role sr
	INNER JOIN sys_user_role sur ON
		sur.roleid = sr.id
	WHERE
		sur.userid = #{用户id};

5.4.2 代码实现

1) User实体

package com.myProject.domain;

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

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    // 表示多方关系（集合）：代表当前用户所具有的角色列表
    private List<Role> roleList; // 关联对象是集合，使用collection

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

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

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

    public String getAddress() {
        return address;
    }

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

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

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

2) Role实体

package com.myProject.domain;

public class Role {
    private Integer id;
    private String rolename;
    private String roleDesc;

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

    public Integer getId() {
        return id;
    }

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

    public String getRolename() {
        return rolename;
    }

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

    public String getRoleDesc() {
        return roleDesc;
    }

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

3) UserMapper接口

package com.myProject.mapper;

import com.myProject.domain.User;

import java.util.List;

public interface UserMapper {
    /*
    多对多嵌套查询 查询用户 同时查询出该用户的所有角色
     */
    public List<User> findAllWithRole();
}

4) UserMapper.xml映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.UserMapper">
    <!--多对多嵌套查询 查询用户 同时查询出该用户的所有角色-->
    <resultMap id="userOrderMap" type="com.myProject.domain.User">
        <id property="id" column="id" />
        <result property="username" column="username" />
        <result property="birthday" column="birthday" />
        <result property="sex" column="sex" />
        <result property="address" column="address" />

        <collection property="roleList" ofType="com.myProject.domain.Role" column="id" select="com.myProject.mapper.RoleMapper.findByUid" />

    </resultMap>
    <select id="findAllWithRole" resultMap="userOrderMap">
        SELECT * FROM USER
    </select>
</mapper>

5) RoleMapper接口

package com.myProject.mapper;

import com.myProject.domain.Role;

import java.util.List;

public interface RoleMapper {
    /*
    根据用户id查询对应角色
     */
    public List<Role> findByUid(Integer uid);
}

6) RoleMapper.xml映射

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.myProject.mapper.RoleMapper">
    <select id="findByUid" resultType="com.myProject.domain.Role" parameterType="int">
        SELECT * FROM sys_role sr INNER JOIN sys_user_role sur ON sur.roleid = sr.id
        WHERE sur.userid = #{uid}
    </select>
</mapper>

7) 测试代码

package com.myProject.test;

import com.myProject.domain.Orders;
import com.myProject.domain.User;
import com.myProject.mapper.OrdersMapper;
import com.myProject.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 {
    /*
    多对多嵌套查询 查询用户 同时查询出该用户的所有角色
     */
    @Test
    public void test() throws IOException {
        // 加载核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");

        // 获得SqlSessionFactory工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);

        // 获得SqlSession会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

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

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

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

5.5 小结

一对一配置：使用<resultMap>+<association>做配置，通过column条件，执行select查询

一对多配置：使用<resultMap>+<collection>做配置，通过column条件，执行select查询

多对多配置：使用<resultMap>+<collection>做配置，通过column条件，执行select查询

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