当前位置： 代码网 > it编程>编程语言>Java > MyBatis中高级多表查询(ResultMap、association、collection)详解

MyBatis中高级多表查询(ResultMap、association、collection)详解

2024年11月28日 • Java •我要评论
一、resultmap的使用resulttype：适用于装配数据时需要字段与属性一一对应(包含驼峰)resultmap：适用于复杂数据结构。如果不指定里面的配置，其功能与resulttype一致(自动

一、resultmap的使用

  • resulttype：适用于装配数据时需要字段与属性一一对应(包含驼峰)
  • resultmap：适用于复杂数据结构。如果不指定里面的配置，其功能与resulttype一致(自动对应)；如果指定了里面的配置，会根据手动指定的列与属性的对应关系装配数据

(一)定义接口的抽象方法 

/**
* 查询每个员工的总工资
*/
list<emp> empwithtotal();

(二)编写mapper.xml 

<!--高级查询-->
<resultmap id="empbasemap" type="com.javatest.entity.emp">
    <id property="empno" column="empno"></id>
    <result property="ename" column="ename"></result>
    <result property="job" column="job"></result>
    <result property="mgr" column="mgr"></result>
    <result property="hiredate" column="hiredate"></result>
    <result property="sal" column="sal"></result>
    <result property="comm" column="comm"></result>
    <result property="deptno" column="deptno"></result>
</resultmap>
<resultmap id="empwithtotal" extends="empbasemap" type="com.javatest.entity.emp">
    <result property="sal" column="totalsal"></result>
</resultmap>
<select id="empwithtotal" resultmap="empwithtotal">
    select *, (sal + ifnull(comm, 0)) totalsal
    from emp;
</select>

(三)测试类

@test
public void testempwithtotal() {
    sqlsession sqlsession = mybatishelper.getsqlsession();
    empdao mapper = sqlsession.getmapper(empdao.class);
    list<emp> emplist = mapper.empwithtotal();
    system.out.println(json.tojsonstring(emplist));
    mybatishelper.backandsavesqlsession(sqlsession);
}
[{"deptno":20,"empno":7369,"ename":"smith","hiredate":"1980-12-17","job":"clerk","mgr":7902,"sal":800.0},
{"comm":300.0,"deptno":30,"empno":7499,"ename":"allen","hiredate":"1981-02-20","job":"salesman","mgr":7698,"sal":1900.0},
{"comm":500.0,"deptno":30,"empno":7521,"ename":"ward","hiredate":"1981-02-22","job":"salesman","mgr":7698,"sal":1750.0},
{"deptno":20,"empno":7566,"ename":"jones","hiredate":"1981-04-02","job":"manager","mgr":7839,"sal":2975.0},
{"comm":1400.0,"deptno":30,"empno":7654,"ename":"martin","hiredate":"1981-09-28","job":"salesman","mgr":7698,"sal":2650.0},
{"deptno":30,"empno":7698,"ename":"blake","hiredate":"1981-05-01","job":"manager","mgr":7839,"sal":2850.0},
{"deptno":10,"empno":7782,"ename":"clark","hiredate":"1981-06-09","job":"manager","mgr":7839,"sal":2450.0},
{"deptno":20,"empno":7788,"ename":"scott","hiredate":"1987-04-19","job":"analyst","mgr":7566,"sal":3000.0},
{"deptno":10,"empno":7839,"ename":"king","hiredate":"1981-11-17","job":"president","sal":5000.0},
{"comm":0.0,"deptno":30,"empno":7844,"ename":"turner","hiredate":"1981-09-08","job":"salesman","mgr":7698,"sal":1500.0},
{"deptno":20,"empno":7876,"ename":"adams","hiredate":"1987-05-23","job":"clerk","mgr":7788,"sal":1100.0},
{"deptno":30,"empno":7900,"ename":"james","hiredate":"1981-12-03","job":"clerk","mgr":7698,"sal":950.0},
{"deptno":20,"empno":7902,"ename":"ford","hiredate":"1981-12-03","job":"analyst","mgr":7566,"sal":3000.0},
{"deptno":10,"empno":7934,"ename":"miller","hiredate":"1982-01-23","job":"clerk","mgr":7782,"sal":1300.0}]

        注意：这种方式改变了原有的sal属性的意思，开发中还是要使用dto和vo来装配，这里简写。 

二、多表一对一查询——association

(一)定义接口的抽象方法 

/**
 * 查询每个员工对应的部门
 */
list<emp> listempwithdept();

(二)编写mapper.xml 

<!--高级查询-->
<resultmap id="empbasemap" type="com.javatest.entity.emp">
    <id property="empno" column="empno"></id>
    <result property="ename" column="ename"></result>
    <result property="job" column="job"></result>
    <result property="mgr" column="mgr"></result>
    <result property="hiredate" column="hiredate"></result>
    <result property="sal" column="sal"></result>
    <result property="comm" column="comm"></result>
    <result property="deptno" column="deptno"></result>
</resultmap>
<resultmap id="listempwithdept" extends="empbasemap" type="com.javatest.entity.emp">
    <!--配置关联的对象-->
    <association property="dept" javatype="com.javatest.entity.dept">
        <id property="deptno" column="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
    </association>
</resultmap>
<select id="listempwithdept" resultmap="listempwithdept">
    select *
    from dept d
             left join emp e on d.deptno = e.deptno
</select>

(三)测试类

@test
public void testlistempwithdept() {
    sqlsession sqlsession = mybatishelper.getsqlsession();
    empdao mapper = sqlsession.getmapper(empdao.class);
    list<emp> emplist = mapper.listempwithdept();
    system.out.println(json.tojsonstring(emplist));
    mybatishelper.backandsavesqlsession(sqlsession);
}

        注意：真实开发中，数据库对应的类属性，是和数据库字段一一对应的，如果需要改变，那么就要放在vo类中返回给前端，这里简写。

三、多表一对多查询——collection

(一)定义接口的抽象方法 

public interface deptdao {
    /**
     * 查询每个部门对应的员工
     */
    list<dept> listdeptwithemps();
}

(二)编写mapper.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.javatest.dao.deptdao">
    <resultmap id="deptbasemap" type="com.javatest.entity.dept">
        <result property="deptno" column="deptno"/>
        <result property="dname" column="dname"/>
        <result property="loc" column="loc"/>
    </resultmap>
    <!--一对多查询-->
    <resultmap id="listdeptwithemps" extends="deptbasemap" type="com.javatest.entity.dept">
        <!--配置关联的集合-->
        <collection property="emps" oftype="com.javatest.entity.emp">
            <id property="empno" column="empno"></id>
            <result property="ename" column="ename"></result>
            <result property="job" column="job"></result>
            <result property="mgr" column="mgr"></result>
            <result property="hiredate" column="hiredate"></result>
            <result property="sal" column="sal"></result>
            <result property="comm" column="comm"></result>
            <result property="deptno" column="deptno"></result>
        </collection>
    </resultmap>
    <select id="listdeptwithemps" resultmap="listdeptwithemps">
        select *
        from dept d
                 left join emp e on d.deptno = e.deptno
    </select>
</mapper>

(三)测试类

@test
public void testlistdeptwithemps(){
    sqlsession sqlsession = mybatishelper.getsqlsession();
    deptdao mapper = sqlsession.getmapper(deptdao.class);
    list<dept> deptlist = mapper.listdeptwithemps();
    system.out.println(json.tojsonstring(deptlist));
    mybatishelper.backandsavesqlsession(sqlsession);
}

四、自连接查询——省市县区

(一)tb_area表

create table if not exists tb_area
(
    code  int          not null primary key,
    name  varchar(255) null,
    pcode int          null
);
insert into mydb.tb_area (code, name, pcode) values (110000, '北京市', 0);
insert into mydb.tb_area (code, name, pcode) values (110107, '石景山区', 110000);
insert into mydb.tb_area (code, name, pcode) values (210000, '辽宁省', 0);
insert into mydb.tb_area (code, name, pcode) values (210200, '大连市', 210000);
insert into mydb.tb_area (code, name, pcode) values (210202, '中山区', 210200);
insert into mydb.tb_area (code, name, pcode) values (210400, '抚顺市', 210000);
insert into mydb.tb_area (code, name, pcode) values (210402, '新抚区', 210400);
insert into mydb.tb_area (code, name, pcode) values (310000, '上海市', 0);
insert into mydb.tb_area (code, name, pcode) values (310106, '静安区', 310000);
insert into mydb.tb_area (code, name, pcode) values (310110, '杨浦区', 310000);

(二)tbarea实体类

@data
@noargsconstructor
@allargsconstructor
public class tbarea implements serializable {
    private static final long serialversionuid = 949170561403426696l;
    private integer code;
    private string name;
    private integer pcode;
    private list<tbarea> subarea;
}

(三)tbareadao接口

public interface tbareadao {
    /**
     * 查找省对应的市
     */
    list<tbarea> getcities();
    /**
     * 查找省对应的市，市对应的县区
     */
    list<tbarea> getareas();
}

(四)tbarea.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.javatest.dao.tbareadao">
    <!--基础映射对象-->
    <resultmap id="tbarea" type="com.javatest.entity.tbarea">
        <id property="code" column="code"></id>
        <result property="name" column="name"></result>
        <result property="pcode" column="pcode"></result>
    </resultmap>
    <!--配置关联的集合：省-市-->
    <resultmap id="getcities" extends="tbarea" type="com.javatest.entity.tbarea">
        <collection property="subarea" oftype="com.javatest.entity.tbarea">
            <id property="code" column="citycode"></id>
            <result property="name" column="cityname"></result>
            <result property="pcode" column="citypcode"></result>
        </collection>
    </resultmap>
    <!--配置关联的集合：省-市-县/区-->
    <resultmap id="getareas" extends="tbarea" type="com.javatest.entity.tbarea">
        <collection property="subarea" oftype="com.javatest.entity.tbarea">
            <id property="code" column="citycode"></id>
            <result property="name" column="cityname"></result>
            <result property="pcode" column="citypcode"></result>
            <collection property="subarea" oftype="com.javatest.entity.tbarea">
                <id property="code" column="areacode"></id>
                <result property="name" column="areaname"></result>
                <result property="pcode" column="areapcode"></result>
            </collection>
        </collection>
    </resultmap>
    <!--获取省-市信息-->
    <select id="getcities" resultmap="getcities">
        select prov.*,
               city.code  as citycode,
               city.name  as cityname,
               city.pcode as citypcode
        from tb_area prov
                 left join tb_area city on prov.code = city.pcode
        where prov.pcode = 0
    </select>
    <!--获取省-市-县区信息-->
    <select id="getareas" resultmap="getareas">
        select prov.*,
               city.code  as citycode,
               city.name  as cityname,
               city.pcode as citypcode,
               area.code  as areacode,
               area.name  as areaname,
               area.pcode as areapcode
        from tb_area prov
                 left join tb_area city on prov.code = city.pcode
                 left join tb_area area on city.code = area.pcode
        where prov.pcode = 0
    </select>
</mapper>

sql查询结果：

获取省-市数据：

获取省-市-县区数据：

(五)测试类tbareatest

public class tbareatest {
    @test
    public void testgetcities(){
        sqlsession sqlsession = mybatishelper.getsqlsession();
        tbareadao mapper = sqlsession.getmapper(tbareadao.class);
        list<tbarea> cities = mapper.getcities();
        system.out.println(json.tojsonstring(cities));
        mybatishelper.backandsavesqlsession(sqlsession);
    }
    @test
    public void testgetareas(){
        sqlsession sqlsession = mybatishelper.getsqlsession();
        tbareadao mapper = sqlsession.getmapper(tbareadao.class);
        list<tbarea> areas = mapper.getareas();
        system.out.println(json.tojsonstring(areas));
        mybatishelper.backandsavesqlsession(sqlsession);
    }
}
testgetcities：获取省-市数据
[{"code":110000,"name":"北京市","pcode":0,"subarea":[{"code":110107,"name":"石景山区","pcode":110000}]},
{"code":210000,"name":"辽宁省","pcode":0,"subarea":[{"code":210400,"name":"抚顺市","pcode":210000},{"code":210200,"name":"大连市","pcode":210000}]},
{"code":310000,"name":"上海市","pcode":0,"subarea":[{"code":310110,"name":"杨浦区","pcode":310000},{"code":310106,"name":"静安区","pcode":310000}]}]
testgetareas：获取省-市-县区数据
[{"code":110000,"name":"北京市","pcode":0,"subarea":[{"code":110107,"name":"石景山区","pcode":110000,"subarea":[]}]},
{"code":210000,"name":"辽宁省","pcode":0,"subarea":[{"code":210400,"name":"抚顺市","pcode":210000,"subarea":[{"code":210402,"name":"新抚区","pcode":210400}]},
{"code":210200,"name":"大连市","pcode":210000,"subarea":[{"code":210202,"name":"中山区","pcode":210200}]}]},
{"code":310000,"name":"上海市","pcode":0,"subarea":[{"code":310110,"name":"杨浦区","pcode":310000,"subarea":[]},{"code":310106,"name":"静安区","pcode":310000,"subarea":[]}]}]

到此这篇关于mybatis中高级多表查询(resultmap、association、collection)的文章就介绍到这了,更多相关mybatis resultmap、association、collection内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

赞 (0)

相关文章:

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2025  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com