一、嵌套查询：

　　在映射文件中resultMap标签内配置<association>/<collection>标签；

　　1）association：

　　　　1、嵌套结果映射：多表查询；

　　　　（1）实体：

public class User {
    private Long id;
    private String name;
    private Integer age;
    private UserDetail userDetail;

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public UserDetail getUserDetail() {
        return userDetail;
    }

    public void setUserDetail(UserDetail userDetail) {
        this.userDetail = userDetail;
    }

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

User

public class UserDetail {
    private Long uId;

    private Long balance;

    public Long getuId() {
        return uId;
    }

    public void setuId(Long uId) {
        this.uId = uId;
    }

    public Long getBalance() {
        return balance;
    }

    public void setBalance(Long balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "UserDetail{" +
                "uId=" + uId +
                ", balance=" + balance +
                '}';
    }
}

UserDetail

　　　　（2）映射表：

　　　　　　property：bean需要对应查询的字段；

　　　　　　resultMap：副表映射结果集；

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qf.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.qf.entity.User">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="age" jdbcType="INTEGER" property="age"/>
        <association property="userDetail" resultMap="UserDetailBaseResultMap"></association>
    </resultMap>

    <resultMap id="UserDetailBaseResultMap" type="com.qf.entity.UserDetail">
        <result column="u_id" jdbcType="BIGINT" property="uId"/>
        <result column="balance" jdbcType="DECIMAL" property="balance"/>
    </resultMap>

    <select id="selectDetailById" resultMap="BaseResultMap">
        select id,name,age,u_id,balance from user u,user_detail ud where u.id=ud.u_id;
    </select>
</mapper>

　　　　2、嵌套查询映射：子查询；

　　　　（1）实体：和上面一样；

　　　　（2）UserDetail映射文件：

<?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.qf.mapper.UserDetailMapper">
    <resultMap id="BaseResultMap" type="com.qf.entity.UserDetail">
        <result column="u_id" jdbcType="BIGINT" property="uId"/>
        <result column="balance" jdbcType="DECIMAL" property="balance"/>
    </resultMap>
    <sql id="Base_Column_List">
        u_id, balance
    </sql>
    <select id="selectById" parameterType="java.lang.Long" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user_detail
        where u_id = #{uId,jdbcType=BIGINT}
    </select>
</mapper>

UserDetailMapper.xml

　　　　（4）User映射文件：

　　　　　　property：bean需要对应查询的字段；

　　　　　　select：查询的子语句；

　　　　　　column：两表匹配的字段；

　　　　　　PS：子表需要有对应的sql语句；

<?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.qf.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.qf.entity.User">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="age" jdbcType="INTEGER" property="age"/>
        <association property="userDetail" select="com.qf.mapper.UserDetailMapper.selectById" column="id"></association>
    </resultMap>

    <select id="selectAll" resultMap="BaseResultMap">
        select * from user user;
    </select>
</mapper>

UserMapper.xml

　　　　（5）测试结果：

　　　　　　//子查询共查询了四次；

DEBUG [main] - ====>  Preparing: select u_id, balance from user_detail where u_id = ? 
DEBUG [main] - ====> Parameters: 1(Long)
DEBUG [main] - <====      Total: 1
DEBUG [main] - ====>  Preparing: select u_id, balance from user_detail where u_id = ? 
DEBUG [main] - ====> Parameters: 2(Long)
DEBUG [main] - <====      Total: 1
DEBUG [main] - ====>  Preparing: select u_id, balance from user_detail where u_id = ? 
DEBUG [main] - ====> Parameters: 3(Long)
DEBUG [main] - <====      Total: 1
DEBUG [main] - ====>  Preparing: select u_id, balance from user_detail where u_id = ? 
DEBUG [main] - ====> Parameters: 4(Long)
DEBUG [main] - <====      Total: 1
DEBUG [main] - <==      Total: 4
User{id=1, name='ww', age=100, userDetail=UserDetail{uId=1, balance=30}}
User{id=2, name='ls', age=15, userDetail=UserDetail{uId=2, balance=40}}
User{id=3, name='ws', age=18, userDetail=UserDetail{uId=3, balance=50}}
User{id=4, name='ws', age=19, userDetail=UserDetail{uId=4, balance=60}}

View Code

 

　　2）collection：嵌套集合；