Mybatis连表查询之association与collection

九转成圣

发布于 2024-04-10 09:10:20

14400

代码可运行

文章被收录于专栏：csdncsdn

运行总次数：0

代码可运行

Mybatis连表查询之association与collection

association

实体类

不用再在EmpDept中将Dept的所有属性都挨个写一遍,只需要引用Dept这个对象即可

@Setter
@Getter
public class EmpDept extends Emp {
    private Dept dept;

    @Override
    public String toString() {
        return JSON.toJSONString(this);
    }
}

Mapper

<resultMap id="EmpDeptResultMap" type="com.example.hello.entity.EmpDept" extends="com.example.hello.dao.EmpMapper.BaseResultMap">
    <association property="dept" resultMap="com.example.hello.dao.DeptMapper.BaseResultMap"></association>
</resultMap>

<select id="selectEmpDept" resultMap="EmpDeptResultMap">
    SELECT * from emp e left join dept d on d.deptno = e.deptno
</select>

xml说明

resultMap标签的extends属性可以通过继承父结果映射来复用已定义的映射规则(不在同一个命名空间下需要指定命名空间,复用代码生成器生成的结果集映射,表字段变动之后,不再需要修改此处,一处定义处处使用,你品…如果此处再写一遍,一方面是麻烦,更让人郁闷的是,如果表字段修改了,此处需要手动维护,想想就头大)。

association的resultMap应用的BaseResultMap不在当前空间下,所以要加上命名空间

测试

@SpringBootTest
class EmpDeptMapperTest {

    @Autowired
    EmpDeptMapper empDeptMapper;

    @Test
    void selectEmpDept() {
        List<EmpDept> empDepts = empDeptMapper.selectEmpDept();
        for (EmpDept empDept : empDepts) {
            System.out.println(empDept);
        }
    }
}

{"comm":20000.0,"createTime":1659164366000,"dept":{"deptno":1,"dname":"开发部","loc":"北京"},"deptno":1,"empno":6,"ename":"彭和尚","hiredate":351446400000,"job":"五散人","lDelete":0,"lVersion":1,"mgr":4}
{"comm":10000.0,"createTime":1659164366000,"dept":{"deptno":1,"dname":"开发部","loc":"北京"},"deptno":1,"empno":7,"ename":"张中","hiredate":351619200000,"job":"五散人","lDelete":0,"lVersion":1,"mgr":4}
{"comm":15000.0,"createTime":1659164366000,"dept":{"deptno":1,"dname":"开发部","loc":"北京"},"deptno":1,"empno":8,"ename":"说不得","hiredate":370454400000,"job":"五散人","lDelete":0,"lVersion":1,"mgr":4,"sal":1.0}
{"comm":10000.0,"createTime":1659164366000,"dept":{"deptno":1,"dname":"开发部","loc":"北京"},"deptno":1,"empno":9,"ename":"冷谦","hiredate":368726400000,"job":"五散人","lDelete":0,"lVersion":1,"mgr":4}
{"comm":30000.0,"createTime":1659164366000,"dept":{"deptno":1,"dname":"开发部","loc":"北京"},"deptno":1,"empno":10,"ename":"周巅","hiredate":376156800000,"job":"五散人","lDelete":0,"lVersion":1,"mgr":4}
{"createTime":1659164366000,"dept":{"deptno":2,"dname":"测试部","loc":"上海"},"deptno":2,"empno":11,"ename":"殷野王","hiredate":545760000000,"job":"教主舅舅","lDelete":0,"lVersion":1,"mgr":3}
{"createTime":1659164366000,"dept":{"deptno":2,"dname":"测试部","loc":"上海"},"deptno":2,"empno":12,"ename":"殷素素","hiredate":376156800000,"job":"教主妈妈","lDelete":0,"lVersion":1,"mgr":3}
{"createTime":1659164366000,"dept":{"deptno":2,"dname":"测试部","loc":"上海"},"deptno":2,"empno":13,"ename":"小昭","hiredate":380563200000,"job":"神秘丫头","lDelete":0,"lVersion":1,"mgr":2}
{"createTime":1659164366000,"dept":{"deptno":3,"dname":"市场部","loc":"广州"},"deptno":3,"empno":2,"ename":"紫衫龙王","hiredate":360864000000,"job":"护教法王","lDelete":0,"lVersion":1,"mgr":1}
{"createTime":1659164366000,"dept":{"deptno":3,"dname":"市场部","loc":"广州"},"deptno":3,"empno":3,"ename":"白眉鹰王","hiredate":354988800000,"job":"护教法王","lDelete":0,"lVersion":1,"mgr":1}
{"createTime":1659164366000,"dept":{"deptno":3,"dname":"市场部","loc":"广州"},"deptno":3,"empno":4,"ename":"金毛狮王","hiredate":357494400000,"job":"护教法王","lDelete":0,"lVersion":1,"mgr":1}
{"createTime":1659164366000,"dept":{"deptno":3,"dname":"市场部","loc":"广州"},"deptno":3,"empno":5,"ename":"青翼蝙王","hiredate":345830400000,"job":"护教法王","lDelete":0,"lVersion":1,"mgr":1}
{"comm":200.0,"createTime":1659164366000,"dept":{"deptno":4,"dname":"运营部","loc":"杭州"},"deptno":4,"empno":1,"ename":"张无忌","hiredate":374774400000,"job":"教主","lDelete":0,"lVersion":2,"updateTime":1659194462000}

单条数据格式

{
    "comm": 200,
    "createTime": 1659164366000,
    "deptno": 4,
    "empno": 1,
    "ename": "张无忌",
    "hiredate": 374774400000,
    "job": "教主",
    "lDelete": 0,
    "lVersion": 2,
    "updateTime": 1659194462000,
    "dept": {
        "deptno": 4,
        "dname": "运营部",
        "loc": "杭州"
    }
}

collection

实体类

@Setter
@Getter
public class DeptEmp extends Dept {
    private List<Emp> emps;

    @Override
    public String toString() {
        return JSON.toJSONString(this);
    }
}

Mapper

<resultMap id="DeptEmpResultMap" type="com.example.hello.entity.DeptEmp" extends="com.example.hello.dao.DeptMapper.BaseResultMap">
    <collection property="emps" ofType="Emp" resultMap="com.example.hello.dao.EmpMapper.BaseResultMap"></collection>
</resultMap>

注意:

resultMap标签的type只写DeptEmp会报错,collection的ofType只写Emp是可以的(猜测指定了DeptEmp,应该可以根据属性推断出来),不写也不会报错

测试

@SpringBootTest
class EmpDeptMapperTest {

    @Autowired
    EmpDeptMapper empDeptMapper;

    @Test
    void selectDeptEmp() {
        List<DeptEmp> deptEmps = empDeptMapper.selectDeptEmp();
        for (DeptEmp deptEmp : deptEmps) {
            System.out.println(deptEmp);
        }
    }
}

{"deptno":4,"dname":"运营部","emps":[{"comm":200.0,"createTime":1659164366000,"deptno":4,"empno":1,"ename":"张无忌","hiredate":374774400000,"job":"教主","lDelete":0,"lVersion":2,"updateTime":1659194462000}],"loc":"杭州"}
{"deptno":3,"dname":"市场部","emps":[{"createTime":1659164366000,"deptno":3,"empno":2,"ename":"紫衫龙王","hiredate":360864000000,"job":"护教法王","lDelete":0,"lVersion":1,"mgr":1},{"createTime":1659164366000,"deptno":3,"empno":3,"ename":"白眉鹰王","hiredate":354988800000,"job":"护教法王","lDelete":0,"lVersion":1,"mgr":1},{"createTime":1659164366000,"deptno":3,"empno":4,"ename":"金毛狮王","hiredate":357494400000,"job":"护教法王","lDelete":0,"lVersion":1,"mgr":1},{"createTime":1659164366000,"deptno":3,"empno":5,"ename":"青翼蝙王","hiredate":345830400000,"job":"护教法王","lDelete":0,"lVersion":1,"mgr":1}],"loc":"广州"}
{"deptno":1,"dname":"开发部","emps":[{"comm":20000.0,"createTime":1659164366000,"deptno":1,"empno":6,"ename":"彭和尚","hiredate":351446400000,"job":"五散人","lDelete":0,"lVersion":1,"mgr":4},{"comm":10000.0,"createTime":1659164366000,"deptno":1,"empno":7,"ename":"张中","hiredate":351619200000,"job":"五散人","lDelete":0,"lVersion":1,"mgr":4},{"comm":15000.0,"createTime":1659164366000,"deptno":1,"empno":8,"ename":"说不得","hiredate":370454400000,"job":"五散人","lDelete":0,"lVersion":1,"mgr":4,"sal":1.0},{"comm":10000.0,"createTime":1659164366000,"deptno":1,"empno":9,"ename":"冷谦","hiredate":368726400000,"job":"五散人","lDelete":0,"lVersion":1,"mgr":4},{"comm":30000.0,"createTime":1659164366000,"deptno":1,"empno":10,"ename":"周巅","hiredate":376156800000,"job":"五散人","lDelete":0,"lVersion":1,"mgr":4}],"loc":"北京"}
{"deptno":2,"dname":"测试部","emps":[{"createTime":1659164366000,"deptno":2,"empno":11,"ename":"殷野王","hiredate":545760000000,"job":"教主舅舅","lDelete":0,"lVersion":1,"mgr":3},{"createTime":1659164366000,"deptno":2,"empno":12,"ename":"殷素素","hiredate":376156800000,"job":"教主妈妈","lDelete":0,"lVersion":1,"mgr":3},{"createTime":1659164366000,"deptno":2,"empno":13,"ename":"小昭","hiredate":380563200000,"job":"神秘丫头","lDelete":0,"lVersion":1,"mgr":2}],"loc":"上海"}

单条数据格式

{
    "deptno": 4,
    "dname": "运营部",
    "loc": "杭州",
    "emps": [
        {
            "comm": 200,
            "createTime": 1659164366000,
            "deptno": 4,
            "empno": 1,
            "ename": "张无忌",
            "hiredate": 374774400000,
            "job": "教主",
            "lDelete": 0,
            "lVersion": 2,
            "updateTime": 1659194462000
        }
    ]
}