Mybatis 学习日志4.复杂查询结果映射+延迟加载（使用IDEA）

最新推荐文章于 2023-04-04 15:49:12 发布

Qingo呀

最新推荐文章于 2023-04-04 15:49:12 发布

阅读量146

点赞数

本文链接：https://blog.csdn.net/birdmanqin/article/details/115065661

版权

1、嵌套查询-联合查询- - - 多对一

使用嵌套结果（将结果对象嵌套）实现联合查询（多表连接查询）---多对一

<association 实现”多对一“中的一
property 指定对象中嵌套对象的属性

    <resultMap id="MultiTableQuery_map1" type="Emp">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <association property="dept">
            <id column="did" property="id"/>
            <result column="dept_name" property="dept_name"/>
        </association>
    </resultMap>
    <select id="selectMultiTableQuery1" resultMap="MultiTableQuery_map1">
        SELECT t1.id as uid ,t1.username,t2.id as did,t2.dept_name
        FROM emp t1
        INNER JOIN dept t2 ON t1.dept_id = t2.id
        where t1.id=#{id}
    </select>

2、嵌套查询（分步查询）- - - 多对一

联合查询和分步查询区别：1.性能区别不大
                    2.分步查询支持懒加载（延迟加载）
需要设置懒加载，一定要使用嵌套查询的方式。
是否启动懒加载可以在全局文件中设置：<setting name="lazyLoadingEnabled" value="true"/>
还可以单独为某个分步查询设置立即加载<association fetchType="eager"

EmpMapper.xml

    <resultMap id="MultiTableQuery_map2" type="Emp">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <!--
            返回一个结果,fetchType="eager"单独设置为立即加载延迟查询
            <association 实现”多对一“中的一
            property 指定对象中嵌套对象的属性
            column 指定将查到的哪个字段传到下一步查询中
            select 下一步查询的命名空间+id
            以上三个属性是实现分步查询的必须属性
            fetchType 可选项，fetchType="eager" ：立即加载； "lazy"由全局配置中的lazyLoadingEnabled属性决定
        -->
        <association property="dept" fetchType="eager" column="dept_id" select="cn.qqa.mapper.DeptMapper.selectDept">
        </association>
    </resultMap>

    <select id="selectMultiTableQuery2" resultMap="MultiTableQuery_map2">
        SELECT * FROM emp WHERE id = #{id}
    </select>

DeptMapper.xml

    <select id="selectDept" resultType="Dept">
        SELECT * FROM Dept WHERE id = #{id}
    </select>

3、基于嵌套查询的延迟查询

    <resultMap id="MultiTableQuery_map3" type="Emp">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <!--   返回一个结果 -->
        <association property="dept" column="dept_id" select="cn.qqa.mapper.DeptMapper.selectDept">
        </association>
    </resultMap>

    <select id="selectMultiTableQuery3" resultMap="MultiTableQuery_map3">
        SELECT * FROM emp WHERE id = #{id}
    </select>

4、嵌套查询(联合查询)：一对多

<collection 映射一对多中的多
property 指定需要与多进行映射的属性，一般声明为List
ofType   需要指定List的泛型类型

    <resultMap id="selectDeptAndEmpsMap" type="Dept">
        <id column="did" property="id"/>
        <result column="dept_name" property="deptname"/>
        <!--
           <collection 映射一对多中的多
           property 指定需要与多进行映射的属性，一般声明为List
           ofType   需要指定List的泛型类型
        -->
        <collection property="emps" ofType="Emp">
            <id column="eid" property="id"></id>
            <result column="username" property="username"></result>
            <result column="createdate" property="createdate"></result>
        </collection>
    </resultMap>
    <select id="selectDeptAndEmps" resultMap="selectDeptAndEmpsMap">
        SELECT t1.id as did ,t1.dept_name,t2.id as eid ,t2.username,t2.createdate
        FROM dept t1
        LEFT JOIN emp t2
        ON t1.id = t2.dept_id
        WHERE t1.id = 2
    </select>

5、嵌套查询（分步查询）：一对多查询部门及所有员工

DeptMapper.xml

    <!--嵌套查询（分步查询）：一对多 查询部门及所有员工-->
    <resultMap id="selectDeptAndEmpsMap1" type="Dept">
        <id column="did" property="id"/>
        <result column="dept_name" property="deptname"/>
        <!--
           <collection 映射一对多中的多
           property 指定需要与多进行映射的属性，一般声明为List
           ofType   需要指定List的泛型类型
           column  需要传递到下一步查询的参数
           select 命名空间+id的形式
        -->

        <collection property="emps" ofType="Emp" column="id" select="cn.qqa.mapper.EmpMapper.selectEmpByDeptid3">
        </collection>
    </resultMap>

EmpMapper.xml

    <!--根据部门id查询所有员工-->
    <select id="selectEmpByDeptid3" resultType="emp">
        SELECT * FROM emp WHERE dept_id = #{id}
    </select>

6.1、DeptMapper.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="cn.qqa.mapper.DeptMapper">

    <!--根据id查询部门-->
    <select id="selectDept" resultType="Dept">
        SELECT * FROM Dept WHERE id = #{id}
    </select>


    <!--嵌套结果：一对多 查询部门及所有员工-->
    <resultMap id="selectDeptAndEmpsMap" type="Dept">
        <id column="did" property="id"/>
        <result column="dept_name" property="deptname"/>
        <!--
           <collection 映射一对多中的多
           property 指定需要与多进行映射的属性，一般声明为List
           ofType   需要指定List的泛型类型
        -->
        <collection property="emps" ofType="Emp">
            <id column="eid" property="id"></id>
            <result column="username" property="username"></result>
            <result column="createdate" property="createdate"></result>
        </collection>
    </resultMap>



    <select id="selectDeptAndEmps" resultMap="selectDeptAndEmpsMap">
        SELECT t1.id as did ,t1.dept_name,t2.id as eid ,t2.username,t2.createdate
        FROM dept t1
        LEFT JOIN emp t2
        ON t1.id = t2.dept_id
        WHERE t1.id = 2
    </select>


    <!--嵌套查询（分步查询）：一对多 查询部门及所有员工-->
    <resultMap id="selectDeptAndEmpsMap1" type="Dept">
        <id column="did" property="id"/>
        <result column="dept_name" property="deptname"/>
        <!--
           <collection 映射一对多中的多
           property 指定需要与多进行映射的属性，一般声明为List
           ofType   需要指定List的泛型类型
           column  需要传递到下一步查询的参数
           select 命名空间+id的形式
        -->

        <collection property="emps" ofType="Emp" column="id" select="cn.qqa.mapper.EmpMapper.selectEmpByDeptid3">
        </collection>
    </resultMap>

    <select id="selectDeptAndEmps1"  resultMap="selectDeptAndEmpsMap1">
          SELECT * FROM Dept WHERE id = #{id}
    </select>


</mapper>

6.2、EmpMapper.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="cn.qqa.mapper.EmpMapper">

    <!--
        更重用，更规范
        实现表连接查询，可以映射为Map或者xxxDTO
    -->
    <resultMap id="MultiTableQuery_map" type="MultiTableQueryDTO">
        <id column="uid" property="id"/>
        <result column="dept_name" property="deptName"/>
        <result column="did" property="deptId"/>
        <result column="username" property="userName"/>
    </resultMap>
    <select id="selectMultiTableQuery" resultMap="MultiTableQuery_map">
        SELECT t1.id as uid ,t1.username,t2.id as did,t2.dept_name
        FROM emp t1
        INNER JOIN dept t2 ON t1.dept_id = t2.id
        where t1.id=#{id}
    </select>

    <!--嵌套结果（嵌套对象）- - - 多对一
            <association 实现”多对一“中的一
            property 指定对象中嵌套对象的属性
    -->
    <resultMap id="MultiTableQuery_map1" type="Emp">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <association property="dept">
            <id column="did" property="id"/>
            <result column="dept_name" property="dept_name"/>
        </association>
    </resultMap>

    <select id="selectMultiTableQuery1" resultMap="MultiTableQuery_map1">
        SELECT t1.id as uid ,t1.username,t2.id as did,t2.dept_name
        FROM emp t1
        INNER JOIN dept t2 ON t1.dept_id = t2.id
        where t1.id=#{id}
    </select>

    <!--
        嵌套查询（分步查询）- - - 多对一
        联合查询和分步查询区别：1.性能区别不大
                            2.分步查询支持懒加载（延迟加载）
        需要设置懒加载，一定要使用嵌套查询的方式。
        是否启动懒加载可以在全局文件中设置：<setting name="lazyLoadingEnabled" value="true"/>
        还可以单独为某个分步查询设置立即加载<association fetchType="eager"
    -->
    <resultMap id="MultiTableQuery_map2" type="Emp">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <!--
            返回一个结果,fetchType="eager"单独设置为立即加载延迟查询
            <association 实现”多对一“中的一
            property 指定对象中嵌套对象的属性
            column 指定将查到的哪个字段传到下一步查询中
            select 下一步查询的命名空间+id
            以上三个属性是实现分步查询的必须属性
            fetchType 可选项，fetchType="eager" ：立即加载； "lazy"由全局配置中的lazyLoadingEnabled属性决定
        -->
        <association property="dept" fetchType="eager" column="dept_id" select="cn.qqa.mapper.DeptMapper.selectDept">
        </association>
    </resultMap>

    <select id="selectMultiTableQuery2" resultMap="MultiTableQuery_map2">
        SELECT * FROM emp WHERE id = #{id}
    </select>

    <!--
        基于嵌套查询的延迟查询

    -->
    <resultMap id="MultiTableQuery_map3" type="Emp">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <!--   返回一个结果 -->
        <association property="dept" column="dept_id" select="cn.qqa.mapper.DeptMapper.selectDept">
        </association>
    </resultMap>

    <select id="selectMultiTableQuery3" resultMap="MultiTableQuery_map3">
        SELECT * FROM emp WHERE id = #{id}
    </select>

    <!--根据部门id查询所有员工-->
    <select id="selectEmpByDeptid3" resultType="emp">
        SELECT * FROM emp WHERE dept_id = #{id}
    </select>

</mapper>

6.3、

package cn.qqa.mapper;

import cn.qqa.pojo.Emp;
import cn.qqa.pojo.MultiTableQueryDTO;

import java.util.Map;


public interface EmpMapper {

    //实际开发
    MultiTableQueryDTO selectMultiTableQuery(Integer id);
    //使用嵌套结果（将结果对象嵌套）实现联合查询（多表连接查询）---多对一
    Emp selectMultiTableQuery1(Integer id);

    //使用嵌套查询（分步查询）实现联合查询（多表连接查询）---多对一
    Emp selectMultiTableQuery2(Integer id);

    Emp selectEmpByDeptid3(Integer deptid);


}

package cn.qqa.pojo;

public class MultiTableQueryDTO {
    private  Integer id;
    private String userName;
    private Integer deptId;

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    private  String deptName;

    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 Integer getDeptId() {
        return deptId;
    }

    public void setDeptId(Integer deptId) {
        this.deptId = deptId;
    }


    @Override
    public String toString() {
        return "MultiTableQueryDTO{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", deptId=" + deptId +
                ", deptName='" + deptName + '\'' +
                '}';
    }
}

package cn.qqa.mapper;


import cn.qqa.pojo.Dept;

import java.util.Deque;

public interface DeptMapper {

    Dept selectDept(Integer id);
    //嵌套查询：一对多
    Dept selectDeptAndEmps(Integer id);
    //嵌套查询（分步查询）：一对多 查询部门及所有员工
    Dept selectDeptAndEmps1(Integer id);
}

package cn.qqa.tests;

import cn.qqa.mapper.DeptMapper;
import cn.qqa.mapper.EmpMapper;
import cn.qqa.pojo.Dept;
import cn.qqa.pojo.Emp;
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.Before;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sound.midi.Soundbank;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;


public class MybatisTest {
    Logger logger = LoggerFactory.getLogger(this.getClass());
    SqlSessionFactory sqlSessionFactory;
    @Before
    public void before(){
        //从XML中构建SqlSeesionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        }catch (IOException e){
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    /**
     *使用嵌套结果（将结果对象嵌套）实现联合查询（多表连接查询）---多对一
     */
    @Test
    public void test01() throws IOException, SQLException {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
        try {
            System.out.println(mapper.selectMultiTableQuery1(1));
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }

    /**
     * 使用嵌套查询（分步查询）实现联合查询（多表连接查询）---多对一
     */
    @Test
    public void test02() throws IOException, SQLException {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
        try {

            Emp emp = mapper.selectMultiTableQuery2(1);
            //System.out.println();
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }

    @Test
    public void test03() throws IOException, SQLException {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        try {
            System.out.println(mapper.selectDept(1));
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }
    /*
        嵌套查询(联合查询)：一对多
    */
    @Test
    public void test04() throws IOException, SQLException {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        try {
            System.out.println(mapper.selectDeptAndEmps(2));
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }

    /*
        嵌套查询(分步查询)：一对多
    */
    @Test
    public void test05() throws IOException, SQLException {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        try {
            Dept dept = mapper.selectDeptAndEmps1(2);
            System.out.println(dept);
            System.out.println(dept.getEmps());
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }
}

Qingo呀

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mybatis 学习日志4.复杂查询结果映射+延迟加载（使用IDEA）

1、嵌套查询-联合查询- - - 多对一使用嵌套结果（将结果对象嵌套）实现联合查询（多表连接查询）---多对一<association 实现”多对一“中的一property 指定对象中嵌套对象的属性 <resultMap id="MultiTableQuery_map1" type="Emp"> <id column="uid" property="id"/> <result column="username" p
复制链接

扫一扫