MyBatis——嵌套查询/延迟加载

一、嵌套查询：

　　在映射文件中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 +
                '}';
    }
}

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

　　　　（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>

　　　　（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>

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

　　2）collection：嵌套集合；

<collection property="userDetail" resultMap="UserDetailBaseResultMap" javaType="java.util.ArrayList" ofType="com.qf.entity.UserDetail"></collection>
property：bean需要对应查询的字段；
resultMap：副表结果集；
javaType：副表返回值类型；
ofType：副表entity；

　　　　（2）实体类：注意集合属性类型对应；

public class User {
    private Long id;
    private String name;
    private Integer age;
    private List<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 List<UserDetail> getUserDetail() {
        return userDetail;
    }

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

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

　　　　（2）User映射文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="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"/>
        <collection property="userDetail" resultMap="UserDetailBaseResultMap" javaType="java.util.ArrayList" ofType="com.qf.entity.UserDetail"></collection>
    </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="selectAll" resultMap="BaseResultMap">
        select id,name,age,u_id,balance from user u,user_detail ud where u.id=ud.u_id;
    </select>
</mapper>

二、延迟加载：

　　1）开启：mybatis.xml中配置：

    <settings>
        <!-- 启用延迟加载特性，不配置默认关闭该特性-->
        <setting name="lazyLoadingEnabled" value="true"></setting>
        <!-- 延迟加载: false；及时加载：true； -->
        <setting name="aggressiveLazyLoading" value="true"/>
    </settings>

　　2）使用上述第一个子查询的例子：

　　　　1、实际打印的结果：执行了四次子查询，因为有4条数据；

　　　　

　　　　2、不使用子查询中的字段，在测试类中只获取主查询字段中的Id:

　　　　　　

　　　　　　

　　　　//从日志中可以看到，子查询没有执行；

三、log4j.properties：

　　//用来打印日志，查看执行了拿些语句；

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n