最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MyBatis中高級多表查詢(ResultMap、association、collection)詳解

 更新時間：2024年11月27日 17:12:05   作者：雷神樂樂  
文章主要介紹了MyBatis中高級多表查詢的四種方式：ResultMap、association、collection以及自連接查詢,通過定義接口的抽象方法、編寫mapper.xml和測試類,詳細展示了如何根據(jù)復雜數(shù)據(jù)結(jié)構(gòu)進行數(shù)據(jù)的裝配和查詢,感興趣的朋友一起看看吧

一、ResultMap的使用

  • resultType：適用于裝配數(shù)據(jù)時需要字段與屬性一一對應(包含駝峰)
  • resultMap：適用于復雜數(shù)據(jù)結(jié)構(gòu)。如果不指定里面的配置，其功能與resultType一致(自動對應)；如果指定了里面的配置，會根據(jù)手動指定的列與屬性的對應關系裝配數(shù)據(jù)

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

/**
* 查詢每個員工的總工資
*/
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屬性的意思，開發(fā)中還是要使用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">
    <!--配置關聯(lián)的對象-->
    <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);
}

        注意：真實開發(fā)中，數(shù)據(jù)庫對應的類屬性，是和數(shù)據(jù)庫字段一一對應的，如果需要改變，那么就要放在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">
        <!--配置關聯(lián)的集合-->
        <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);
}

四、自連接查詢——省市縣區(qū)

(一)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, '石景山區(qū)', 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, '中山區(qū)', 210200);
INSERT INTO mydb.tb_area (code, name, pcode) VALUES (210400, '撫順市', 210000);
INSERT INTO mydb.tb_area (code, name, pcode) VALUES (210402, '新?lián)釁^(qū)', 210400);
INSERT INTO mydb.tb_area (code, name, pcode) VALUES (310000, '上海市', 0);
INSERT INTO mydb.tb_area (code, name, pcode) VALUES (310106, '靜安區(qū)', 310000);
INSERT INTO mydb.tb_area (code, name, pcode) VALUES (310110, '楊浦區(qū)', 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();
    /**
     * 查找省對應的市，市對應的縣區(qū)
     */
    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>
    <!--配置關聯(lián)的集合：省-市-->
    <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>
    <!--配置關聯(lián)的集合：省-市-縣/區(qū)-->
    <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>
    <!--獲取省-市-縣區(qū)信息-->
    <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查詢結(jié)果：

獲取省-市數(shù)據(jù)：

獲取省-市-縣區(qū)數(shù)據(jù)：

(五)測試類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：獲取省-市數(shù)據(jù)
[{"code":110000,"name":"北京市","pcode":0,"subArea":[{"code":110107,"name":"石景山區(qū)","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":"楊浦區(qū)","pcode":310000},{"code":310106,"name":"靜安區(qū)","pcode":310000}]}]
testGetAreas：獲取省-市-縣區(qū)數(shù)據(jù)
[{"code":110000,"name":"北京市","pcode":0,"subArea":[{"code":110107,"name":"石景山區(qū)","pcode":110000,"subArea":[]}]},
{"code":210000,"name":"遼寧省","pcode":0,"subArea":[{"code":210400,"name":"撫順市","pcode":210000,"subArea":[{"code":210402,"name":"新?lián)釁^(qū)","pcode":210400}]},
{"code":210200,"name":"大連市","pcode":210000,"subArea":[{"code":210202,"name":"中山區(qū)","pcode":210200}]}]},
{"code":310000,"name":"上海市","pcode":0,"subArea":[{"code":310110,"name":"楊浦區(qū)","pcode":310000,"subArea":[]},{"code":310106,"name":"靜安區(qū)","pcode":310000,"subArea":[]}]}]

到此這篇關于MyBatis中高級多表查詢(ResultMap、association、collection)的文章就介紹到這了,更多相關MyBatis ResultMap、association、collection內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

相關文章

  • SpringBoot外部化配置的最佳實踐指南

    SpringBoot外部化配置的最佳實踐指南

    這篇文章主要介紹了SpringBoot外部化配置的核心思想和實現(xiàn)方式,包括配置來源、加載優(yōu)先級、Profile特定配置、YAML與Properties對比、通配符路徑應用、調(diào)試工具等內(nèi)容,為多環(huán)境部署和動態(tài)管理配置提供了詳細實踐建議,需要的朋友可以參考下
    2025-10-10
  • SpringMVC互聯(lián)網(wǎng)軟件架構(gòu)REST使用詳解

    SpringMVC互聯(lián)網(wǎng)軟件架構(gòu)REST使用詳解

    這篇文章主要為大家詳細介紹了SpringMVC互聯(lián)網(wǎng)軟件架構(gòu)REST的相關資料，具有一定的參考價值，感興趣的小伙伴們可以參考一下
    2017-03-03
  • 深入理解Java線程池從設計思想到源碼解讀

    深入理解Java線程池從設計思想到源碼解讀

    這篇文章主要介紹了深入理解Java線程池從設計思想到源碼解讀,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
    2021-03-03
  • MyBatisPlus代碼生成器的原理及實現(xiàn)詳解

    MyBatisPlus代碼生成器的原理及實現(xiàn)詳解

    這篇文章主要為大家詳細介紹了MyBatisPlus中代碼生成器的原理及實現(xiàn)，文中的示例代碼講解詳細，對我們學習MyBatisPlus有一定幫助，需要的可以參考一下
    2022-08-08
  • 解決Spring使用@MapperScan問題

    解決Spring使用@MapperScan問題

    這篇文章主要介紹了解決Spring使用@MapperScan問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
    2021-09-09
  • SpringBoot集成Drools打造動態(tài)規(guī)則管理模板引擎

    SpringBoot集成Drools打造動態(tài)規(guī)則管理模板引擎

    本文詳解SpringBoot與Drools的整合方法,涵蓋依賴管理、規(guī)則文件編寫、KieService配置等,旨在通過規(guī)則引擎實現(xiàn)業(yè)務邏輯動態(tài)化管理,提升微服務應用的靈活性、可維護性及快速部署能力,對SpringBoot Drools相關知識感興趣的朋友一起看看吧
    2025-07-07
  • java獲取和設置系統(tǒng)變量問題(環(huán)境變量)

    java獲取和設置系統(tǒng)變量問題(環(huán)境變量)

    這篇文章主要介紹了java獲取和設置系統(tǒng)變量問題(環(huán)境變量)，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
    2023-01-01
  • visual studio 2019安裝配置可編寫c/c++語言的IDE環(huán)境

    visual studio 2019安裝配置可編寫c/c++語言的IDE環(huán)境

    這篇文章主要介紹了visual studio 2019安裝配置可編寫c/c++語言的IDE環(huán)境,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
    2020-03-03
  • Java?Web實戰(zhàn)之使用三層架構(gòu)與Servlet構(gòu)建登錄注冊模塊

    Java?Web實戰(zhàn)之使用三層架構(gòu)與Servlet構(gòu)建登錄注冊模塊

    這篇文章介紹了如何使用三層架構(gòu)（View,?Service,?DAO）和JDBCTemplate技術在JavaWeb環(huán)境下實現(xiàn)登錄和注冊功能,詳細說明了構(gòu)建項目的步驟,包括創(chuàng)建數(shù)據(jù)庫表、實體類、DAO層、Service層、Servlet處理及頁面設計,需要的朋友可以參考下
    2024-10-10
  • 一文講解Java的String、StringBuffer和StringBuilder的使用與區(qū)別

    一文講解Java的String、StringBuffer和StringBuilder的使用與區(qū)別

    String是不可變的字符序列,而StringBuffer和StringBuilder是可變的字符序列,本文就來詳細的介紹一下Java的String、StringBuffer和StringBuilder的使用與區(qū)別,感興趣的可以了解一下
    2024-03-03

最新評論

绩溪县| 曲沃县| 邻水| 修文县| 突泉县| 金寨县| 确山县| 长子县| 万宁市| 邛崃市| 广元市| 左权县| 灌阳县| 永寿县| 嘉鱼县| 渭南市| 通州市| 阳高县| 桂平市| 温州市| 青州市| 四川省| 临沧市| 鄂州市| 务川| 罗江县| 贞丰县| 泽普县| 合川市| 华蓥市| 乡宁县| 平泉县| 铅山县| 醴陵市| 安丘市| 阳谷县| 潢川县| 宿松县| 吉林省| 鄂伦春自治旗| 息烽县|