欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

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

一、ResultMap的使用

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

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

/**
* 查詢每個(gè)員工的總工資
*/
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

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

/**
 * 查詢每個(gè)員工對應(yīng)的部門
 */
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">
    <!--配置關(guān)聯(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);
}

        注意：真實(shí)開發(fā)中，數(shù)據(jù)庫對應(yīng)的類屬性，是和數(shù)據(jù)庫字段一一對應(yīng)的，如果需要改變，那么就要放在VO類中返回給前端，這里簡寫。

三、多表一對多查詢——collection

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

public interface DeptDao {
    /**
     * 查詢每個(gè)部門對應(yīng)的員工
     */
    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">
        <!--配置關(guān)聯(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實(shí)體類

@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 {
    /**
     * 查找省對應(yīng)的市
     */
    List<TbArea> getCities();
    /**
     * 查找省對應(yīng)的市，市對應(yīng)的縣區(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">
    <!--基礎(chǔ)映射對象-->
    <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>
    <!--配置關(guān)聯(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>
    <!--配置關(guān)聯(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":[]}]}]

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

相關(guān)文章

  • java微信紅包實(shí)現(xiàn)算法

    java微信紅包實(shí)現(xiàn)算法

    這篇文章主要為大家詳細(xì)介紹了java微信紅包實(shí)現(xiàn)算法，列出紅包的核心算法，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下
    2017-02-02
  • 用IntelliJ IDEA看Java類圖的方法(圖文)

    用IntelliJ IDEA看Java類圖的方法(圖文)

    這篇文章主要介紹了用IntelliJ IDEA看Java類圖的方法，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2020-02-02
  • IDEA生成項(xiàng)目后出現(xiàn)的iml和idea文件問題

    IDEA生成項(xiàng)目后出現(xiàn)的iml和idea文件問題

    這篇文章主要介紹了IDEA生成項(xiàng)目后出現(xiàn)的iml和idea文件問題,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2024-08-08
  • SpringBoot集成Elasticsearch過程實(shí)例

    SpringBoot集成Elasticsearch過程實(shí)例

    這篇文章主要介紹了SpringBoot集成Elasticsearch過程實(shí)例,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下
    2020-04-04
  • Java實(shí)現(xiàn)bmp和jpeg圖片格式互轉(zhuǎn)

    Java實(shí)現(xiàn)bmp和jpeg圖片格式互轉(zhuǎn)

    本文主要介紹了Java實(shí)現(xiàn)bmp和jpeg圖片格式互轉(zhuǎn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2023-04-04
  • Mybatis返回int或者Integer類型報(bào)錯(cuò)的解決辦法

    Mybatis返回int或者Integer類型報(bào)錯(cuò)的解決辦法

    這篇文章主要介紹了Mybatis返回int或者Integer類型報(bào)錯(cuò)的解決辦法,非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友可以參考下
    2016-12-12
  • Java?Hutool工具包中HttpUtil的日志統(tǒng)一打印及統(tǒng)一超時(shí)時(shí)間配置

    Java?Hutool工具包中HttpUtil的日志統(tǒng)一打印及統(tǒng)一超時(shí)時(shí)間配置

    Hutool是一個(gè)Java基礎(chǔ)工具類,對文件、流、加密解密、轉(zhuǎn)碼、正則、線程、XML等JDK方法進(jìn)行封裝,組成各種Util工具類,這篇文章主要給大家介紹了關(guān)于Java?Hutool工具包中HttpUtil的日志統(tǒng)一打印及統(tǒng)一超時(shí)時(shí)間配置的相關(guān)資料,需要的朋友可以參考下
    2024-01-01
  • java 讀取網(wǎng)頁內(nèi)容的實(shí)例詳解

    java 讀取網(wǎng)頁內(nèi)容的實(shí)例詳解

    這篇文章主要介紹了java 讀取網(wǎng)頁內(nèi)容的實(shí)例詳解的相關(guān)資料,希望通過本文能幫助到大家，讓大家學(xué)習(xí)理解這部分內(nèi)容，需要的朋友可以參考下
    2017-09-09
  • Java填充替換數(shù)組元素實(shí)例詳解

    Java填充替換數(shù)組元素實(shí)例詳解

    這篇文章主要通過兩個(gè)實(shí)例說明Java填充和替換數(shù)組中元素的方法,需要的朋友可以參考下。
    2017-08-08
  • Java編程中使用throw關(guān)鍵字拋出異常的用法簡介

    Java編程中使用throw關(guān)鍵字拋出異常的用法簡介

    這篇文章主要介紹了Java編程中使用throw關(guān)鍵字拋出異常的用法,是Java入門學(xué)習(xí)中的基礎(chǔ)知識,需要的朋友可以參考下
    2015-11-11

最新評論