Spring boot+Mybatis，實現後端統計兩張無法關聯的表中的總戶數，總人數，而後拼接為一條記錄，返回分頁查詢page給前端

風碎峰發表於2020-11-09

Spring boot+Mybatis實現後端拼接欄位，結果分頁顯示

一、前言

這是筆者過去兩週接到的任務之一，也是挺折騰的一個需求。從兩張表中統計資料，然後拼接欄位。任誰接到這樣的任務都覺得小菜一碟，spring boot + Mybatis專案可以通過兩張表關聯查詢，然後程式碼層處理。筆者最初也是這麼想的，然而拿到最初的需求和設計才知道，想法太簡單了。當然後來又有了新的變化和要求，整個過程改來改去，就這麼一個任務模組改了3次，包括前端也是筆者親自寫，所以很是折騰。當然這個過程中也有很多收穫和值得總結之處，所以寫下本篇，算是一個小結。

二、需求分析

先上圖，這是團隊分給筆者的任務截圖（一部分）：

要求表是樹形結構，其餘的沒有任何說明，文件，補充，只是說可參考XXX的頁面（此處吐槽一下，好的公司和團隊很重要，尤其是健全的開發團隊，一個產品經理和專案經理的作用很大。至少在會議上拆解和分配任務的時候，描述儘可能功能詳細點，不然全靠任務完成者去問，去冥想？大家夥兒能去大公司還是進大公司吧，唉~）。

給了一週左右的時間吧，前後端都是筆者寫，還有其他的任務模組。而這塊優先順序很高，拿到任務後有點懵逼。

首先去看了一下團隊開發說的可供參考的程式碼，本以為有所幫助，但是很失望，所以只能靠自己了。

首先統計物件是兩張表，保障戶：zgq_user和非保障戶：zgq_no_user。保障戶表要拿到總保障戶戶數，總保障人人數；非保障戶表要拿到非保障戶戶數，非保障戶人數；然後總戶數=總保障戶戶數+非保障戶戶數，總人數=總保障人人數+非保障戶人數。拼接後顯示如下：

是不是覺得很簡單？

別急，這才是第一步，或者說到此只是描述了一遍PFD上的任務分配而已。再仔細回顧會議細節和參考同事程式碼的時候，他們寫的資料頭入庫了，都是從後端資料庫中拿到資料返回給前端，而團隊非責任提醒我也可以新建一張表。。。。。。照著這個思路很快就發現統計物件是兩張表，從資料匯入模板到表結構完全就沒有任何關聯。而前端顯示的是樹形結構，父級顯示鎮，子級多個children，資料結構如下：

{
	總人數：
	總戶數:
	總保障戶人數
	總保障戶數
	非保障戶人數
	非保障戶數
	children:[
		{

		}
	]
}

就到這一步了麼？別急還沒完！

首先一個鎮分三種情況，如下：

一個鎮
既有保障又有非保障，需要拼接的記錄：總人數=總保障戶人數+非保障戶人數，總戶數=總保障戶數+非保障戶數，總保障戶數，總保障戶人數，非保障戶數，非保障戶人數
只有保障，需要拼接的記錄：總人數=總保障戶人數+非保障戶人數（0），總戶數=總保障戶數+非保障戶數（0），總保障戶數，總保障戶人數，非保障戶數（0），非保障戶人數（0）
只有非保障，需要拼接的記錄：總人數=總保障戶人數（0）+非保障戶人數，總戶數=總保障戶數（0）+非保障戶數，總保障戶數（0），總保障戶人數（0），非保障戶數，非保障戶人數

而村級也就是children中的每個子級也是這三種情況。

三、實現過程

經過仔細的分析需求後，筆者並沒有在後端建表，建了也沒用，非同步介面，表不關聯，還是從程式碼拼接好記錄後返回page。

這個需求一直改，改到現在已經第三次了，歷經三次思路的改變，本節將詳細記錄這個過程。

1、思路一

由於這次任務急，又兼顧寫前後端，所以筆者在分析了需求後決定採用分開查詢兩張表中所有資料，然後按照鎮，村分組，分組後採用並交差完成統計和拼接。

程式碼就不公佈了，就是使用Java8的stream流分組，遍歷map，鎮裡面套著村的map去處理。經測試發現問題很大，在父級和子級有的資料根本就是錯誤的。比如一個鎮的非保障戶資料和人數都為0，而子級卻不為0。

2、思路二

基於開始時的思路發現問題後，筆者又理了一遍需求，詳細的拆解了任務，從鎮級別開始，可能涉及的資料處理模組一一剝離，然後到村。當然了，其過程遠沒有筆者寫的這麼簡簡單單。把統計情況，會遇到的異常等等，都寫出來，最後回推，總結出要實現整個統計過程大概需要5個輔助方法，程式碼如下。

首先定義實體類：

package com.fw.hs.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.fw.hs.enums.ChangeField;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.util.List;

/**
 * @program: homestead
 * @Date: 2020/10/28 15:45
 * @Author: 
 * @Version: 1.0
 * @Description:
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value = "ZgqAllDistrict物件", description = "資格權-全區宅基地使用物件現狀統計表")
public class ZgqAllDistrict implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "主鍵")
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private String id;

    @ChangeField(desc = "行政區劃")
    @ApiModelProperty(value = "行政區劃")
    private String adName;

    @ChangeField(desc = "總戶數")
    @ApiModelProperty(value = "總戶數")
    private Long totalHu;

    @ChangeField(desc = "總人數")
    @ApiModelProperty(value = "總人數")
        private Long totalPeople;

    @ChangeField(desc = "總保障戶數")
    @ApiModelProperty(value = "總保障戶數")
    private Long totalUserHu;

    @ChangeField(desc = "總保障戶人數")
    @ApiModelProperty(value = "總保障戶人數")
    private Long totalZgqUser;

    @ChangeField(desc = "非保障戶數")
    @ApiModelProperty(value = "非保障戶數")
    private Long totalNoUserHu;

    @ChangeField(desc = "非保障戶人數")
    @ApiModelProperty(value = "非保障戶人數")
    private Long totalZgqNoUer;

    /** 鎮轄下村（社群）級單位 */
    @TableField(exist = false)
    private List<ZgqAllDistrict> children;

}

controller類：

package com.fw.hs.controller;

import com.fw.hs.entity.ZgqAllDistrict;
import com.fw.hs.service.IZgqAllDistrictService;
import com.fw.hs.vo.ZgqRegionStatisticsVo;
import com.fw.hs.vo.query.ZgqAllDistrictQuery;
import com.fwcloud.common.web.wrapper.ReturnWrapMapper;
import com.fwcloud.common.web.wrapper.ReturnWrapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * @program: homestead
 * @Date: 2020/10/28 16:27
 * @Author: 
 * @Version: 1.0
 * @Description:
 */
@Api(tags = "資格權-統計-介面")
@RestController
@RequestMapping("/api/zgq")
public class ZgqAllDistrictController {

    @Autowired
    private IZgqAllDistrictService iZgqAllDistrictService;

    @ApiOperation(value = "全區現狀表-樹形結構", consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE)
    @PostMapping(value = "list-all-district", consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE)
    public ReturnWrapper getListByAdName(ZgqAllDistrictQuery<ZgqAllDistrict> query) {
        return ReturnWrapMapper.ok(iZgqAllDistrictService.getListTree(query));
    }

   
}

service類：

package com.fw.hs.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import com.fw.hs.entity.ZgqAllDistrict;
import com.fw.hs.vo.ZgqRegionStatisticsVo;
import com.fw.hs.vo.query.ZgqAllDistrictQuery;

import java.util.List;

/**
 * @program: homestead
 * @Date: 2020/10/28 16:17
 * @Author: 
 * @Version: 1.0
 * @Description:
 */
public interface IZgqAllDistrictService extends IService<ZgqAllDistrict> {

    /**
     * 全區宅基地使用物件現在統計樹形結構列表
     * @param query
     * @return
     */
    IPage<ZgqAllDistrict> getListTree(ZgqAllDistrictQuery<ZgqAllDistrict> query);
   
}

業務邏輯類：

package com.fw.hs.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.fw.hs.entity.ZgqAllDistrict;
import com.fw.hs.mapper.ZgqAllDistrictMapper;
import com.fw.hs.mapper.ZgqNoUserMapper;
import com.fw.hs.mapper.ZgqUserMapper;
import com.fw.hs.service.IZgqAllDistrictService;
import com.fw.hs.vo.ZgqAllDistrictByTownVo;
import com.fw.hs.vo.ZgqAllDistrictVo;
import com.fw.hs.vo.ZgqRegionStatisticsVo;
import com.fw.hs.vo.query.ZgqAllDistrictQuery;
import com.fwcloud.common.base.support.IdGenerator;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @program: homestead
 * @Date: 2020/10/28 16:19
 * @Author: 
 * @Version: 1.0
 * @Description:
 */
@Service
public class ZgqAllDistrictServiceImpl extends ServiceImpl<ZgqAllDistrictMapper, ZgqAllDistrict> implements IZgqAllDistrictService {

    @Resource
    private ZgqUserMapper zgqUserMapper;
    @Resource
    private ZgqNoUserMapper zgqNoUserMapper;

    @Override
    public IPage<ZgqAllDistrict> getListTree(ZgqAllDistrictQuery<ZgqAllDistrict> query) {
        // 宣告一個名為zgqAllDistrictList的ArrayList，用來存放統計表所有的記錄ZgqAllDistrict
        List<ZgqAllDistrict> zgqAllDistrictList = new ArrayList<>();
        // 獲取保障戶和非保障戶的戶數，人數資料列表
        List<ZgqAllDistrictByTownVo> zgqUsers = zgqUserMapper.getListByTown();
        List<ZgqAllDistrictByTownVo> zgqNoUsers = zgqNoUserMapper.getListByZhen();
        // 使用 map() 獲取鎮級別行政區劃
        List<String> town = zgqUsers.stream().map(ZgqAllDistrictByTownVo::getTown).collect(Collectors.toList());
        List<String> zhen = zgqNoUsers.stream().map(ZgqAllDistrictByTownVo::getTown).collect(Collectors.toList());
        // 宣告一個名為listAll的ArrayList，用來存放鎮級別行政區劃全集
        List<String> listAll = new ArrayList<>();
        // 求全集
        listAll.addAll(town);
        listAll.addAll(zhen);
        // 去重並集
        List<String> listAllDistinct = listAll.stream().distinct().collect(Collectors.toList());
        // 遍歷listAllDistinct
        for (String s : listAllDistinct) {
            // 如果取到的s為空值，跳出迴圈
            if (s == null) {
                break;
            }
            ZgqAllDistrict zgqAllDistrict = new ZgqAllDistrict();
            long id = IdGenerator.nextId();
            String zgqAllDistrictId = String.valueOf(id);
            zgqAllDistrict.setId(zgqAllDistrictId);
            zgqAllDistrict.setAdName(s);
            // 呼叫getVoByTown()方法，獲取當前鎮的保障戶和非保障戶資料:{town,numHu,numRen}
            ZgqAllDistrictByTownVo zgqUserVo = getVoByTown(zgqUsers, s);
            ZgqAllDistrictByTownVo zgqNoUserVo = getVoByTown(zgqNoUsers, s);
            // 獲取當前鎮下的所有村的保障戶和非保障戶資料列表
            List<ZgqAllDistrict> userChild = getChildByTownStreet(s);
            List<ZgqAllDistrict> noUserChild = getChildByZhen(s);
            // 獲取子級記錄—即當前鎮下所有村的記錄
            List<ZgqAllDistrict> records = getRecords(userChild, noUserChild);
            zgqAllDistrict.setChildren(records);
            System.out.println(userChild);
            System.out.println(noUserChild);
            if (zgqUserVo.getNumHu() != 0 && zgqNoUserVo.getNumHu() != 0) {
                long totalUserHu = zgqUserVo.getNumHu();
                long totalZgqUser = zgqUserVo.getNumRen();
                long totalNoUserHu = zgqNoUserVo.getNumHu();
                long totalZgqNoUer = zgqNoUserVo.getNumRen();
                long totalHu = totalUserHu + totalNoUserHu;
                long totalPeople = totalZgqUser + totalZgqNoUer;
                zgqAllDistrict.setTotalHu(totalHu);
                zgqAllDistrict.setTotalPeople(totalPeople);
                zgqAllDistrict.setTotalUserHu(totalUserHu);
                zgqAllDistrict.setTotalZgqUser(totalZgqUser);
                zgqAllDistrict.setTotalNoUserHu(totalNoUserHu);
                zgqAllDistrict.setTotalZgqNoUer(totalZgqNoUer);
            } else if (zgqUserVo.getNumHu() != 0 && zgqNoUserVo.getNumHu() == 0) {
                long totalUserHu = zgqUserVo.getNumHu();
                long totalZgqUser = zgqUserVo.getNumRen();
                long totalNoUserHu = 0;
                long totalZgqNoUer = 0;
                long totalHu = totalUserHu + totalNoUserHu;
                long totalPeople = totalZgqUser + totalZgqNoUer;
                zgqAllDistrict.setTotalHu(totalHu);
                zgqAllDistrict.setTotalPeople(totalPeople);
                zgqAllDistrict.setTotalUserHu(totalUserHu);
                zgqAllDistrict.setTotalZgqUser(totalZgqUser);
                zgqAllDistrict.setTotalNoUserHu(totalNoUserHu);
                zgqAllDistrict.setTotalZgqNoUer(totalZgqNoUer);
            } else if (zgqUserVo.getNumHu() == 0 && zgqNoUserVo.getNumHu() != 0) {
                long totalUserHu = 0;
                long totalZgqUser = 0;
                long totalNoUserHu = zgqNoUserVo.getNumHu();
                long totalZgqNoUer = zgqNoUserVo.getNumRen();
                long totalHu = totalUserHu + totalNoUserHu;
                long totalPeople = totalZgqUser + totalZgqNoUer;
                zgqAllDistrict.setTotalHu(totalHu);
                zgqAllDistrict.setTotalPeople(totalPeople);
                zgqAllDistrict.setTotalUserHu(totalUserHu);
                zgqAllDistrict.setTotalZgqUser(totalZgqUser);
                zgqAllDistrict.setTotalNoUserHu(totalNoUserHu);
                zgqAllDistrict.setTotalZgqNoUer(totalZgqNoUer);
            } else {
                long totalUserHu = 0;
                long totalZgqUser = 0;
                long totalNoUserHu = 0;
                long totalZgqNoUer = 0;
                long totalHu = totalUserHu + totalNoUserHu;
                long totalPeople = totalZgqUser + totalZgqNoUer;
                zgqAllDistrict.setTotalHu(totalHu);
                zgqAllDistrict.setTotalPeople(totalPeople);
                zgqAllDistrict.setTotalUserHu(totalUserHu);
                zgqAllDistrict.setTotalZgqUser(totalZgqUser);
                zgqAllDistrict.setTotalNoUserHu(totalNoUserHu);
                zgqAllDistrict.setTotalZgqNoUer(totalZgqNoUer);
                zgqAllDistrict.setChildren(records);
            }
            zgqAllDistrictList.add(zgqAllDistrict);
        }
        ZgqAllDistrictQuery<ZgqAllDistrict> q = new ZgqAllDistrictQuery<>();
        // 分頁引數
        IPage<ZgqAllDistrict> page = q.initPageQuery();
        page.setRecords(zgqAllDistrictList);
        page.setSize(zgqAllDistrictList.size());
        return page;
    }

    /**
     * 方法名：getVoByTown
     * 描述：根據鎮例項獲取當前的ZgqAllDistrictByTownVo物件，如果為空，則進行復制，並新增到voList中
     * @param voList
     * @param string
     * @return
     */
    private ZgqAllDistrictByTownVo getVoByTown(List<ZgqAllDistrictByTownVo> voList, String string) {
        ZgqAllDistrictByTownVo zgqAllDistrictByTownVo = null;
        for (int i = 0; i < voList.size(); i++) {
            ZgqAllDistrictByTownVo zgqAllDistrictByTownVo1 = voList.get(i);
            if (string.equals(zgqAllDistrictByTownVo1.getTown())) {
                zgqAllDistrictByTownVo = zgqAllDistrictByTownVo1;
                break;
            }
        }
        if (zgqAllDistrictByTownVo == null) {
            zgqAllDistrictByTownVo = new ZgqAllDistrictByTownVo();
            zgqAllDistrictByTownVo.setTown(string);
            long value = 0;
            zgqAllDistrictByTownVo.setNumHu(value);
            zgqAllDistrictByTownVo.setNumRen(value);
            voList.add(zgqAllDistrictByTownVo);
        }
        return zgqAllDistrictByTownVo;
    }
    /**
     * 方法名：getRecords
     * 描述：該方法取同鎮下的所有村級別行政區劃的去重並集，迴圈處理得到同鎮下的村級子級記錄ZgqAllDistrict
     * @param userChild
     * @param noUserChild
     * @return
     */
    private List<ZgqAllDistrict> getRecords(List<ZgqAllDistrict> userChild, List<ZgqAllDistrict> noUserChild) {
        // 宣告一個名為records的ArrayList
        List<ZgqAllDistrict> records = new ArrayList<>();
        // 使用 map() 獲取保障戶和非保障戶村（社群）級別行政區劃
        List<String> village = userChild.stream().map(ZgqAllDistrict::getAdName).collect(Collectors.toList());
        List<String> country = noUserChild.stream().map(ZgqAllDistrict::getAdName).collect(Collectors.toList());
        // 宣告一個名為listAll的ArrayList,用來存放村（社群）級別行政區劃全集
        List<String> listAll1 = new ArrayList<>();
        // 求全集
        listAll1.addAll(village);
        listAll1.addAll(country);
        // 去重並集
        List<String> stringList = listAll1.stream().distinct().collect(Collectors.toList());
        // 遍歷stringList
        for (String str : stringList) {
            // 如果取到的s為空值，跳出迴圈
            if (str == null) {
                break;
            }
            ZgqAllDistrict record = new ZgqAllDistrict();
            long id = IdGenerator.nextId();
            String recordId = String.valueOf(id);
            record.setId(recordId);
            record.setAdName(str);
            // 呼叫getZgqAllDistrictByCountry()方法，獲取保障戶一個村的資料
            ZgqAllDistrict zgqUserAllDistrict = getZgqAllDistrictByCountry(userChild, str);
            // 呼叫getZgqAllDistrictByCountry()方法，獲取保障戶一個村的資料
            ZgqAllDistrict zgqNoUserAllDistrict = getZgqAllDistrictByCountry(noUserChild, str);
            if (zgqUserAllDistrict.getTotalUserHu() != 0 && zgqNoUserAllDistrict.getTotalNoUserHu() != 0) {
                long totalUserHu = zgqUserAllDistrict.getTotalUserHu();
                long totalZgqUser = zgqUserAllDistrict.getTotalZgqUser();
                long totalNoUserHu = zgqNoUserAllDistrict.getTotalNoUserHu();
                long totalZgqNoUer = zgqNoUserAllDistrict.getTotalZgqNoUer();
                long totalHu = totalUserHu + totalNoUserHu;
                long totalPeople = totalZgqUser + totalZgqNoUer;
                record.setTotalHu(totalHu);
                record.setTotalPeople(totalPeople);
                record.setTotalUserHu(totalUserHu);
                record.setTotalZgqUser(totalZgqUser);
                record.setTotalNoUserHu(totalNoUserHu);
                record.setTotalZgqNoUer(totalZgqNoUer);
            } else if (zgqUserAllDistrict.getTotalUserHu() != 0 && zgqNoUserAllDistrict.getTotalNoUserHu() == 0) {
                long totalUserHu = zgqUserAllDistrict.getTotalUserHu();
                long totalZgqUser = zgqUserAllDistrict.getTotalZgqUser();
                long totalNoUserHu = 0;
                long totalZgqNoUer = 0;
                long totalHu = totalUserHu + totalNoUserHu;
                long totalPeople = totalZgqUser + totalZgqNoUer;
                record.setTotalHu(totalHu);
                record.setTotalPeople(totalPeople);
                record.setTotalUserHu(totalUserHu);
                record.setTotalZgqUser(totalZgqUser);
                record.setTotalNoUserHu(totalNoUserHu);
                record.setTotalZgqNoUer(totalZgqNoUer);
            } else if (zgqUserAllDistrict.getTotalUserHu() == 0 && zgqNoUserAllDistrict.getTotalNoUserHu() != 0) {
                long totalUserHu = 0;
                long totalZgqUser = 0;
                long totalNoUserHu = zgqNoUserAllDistrict.getTotalNoUserHu();
                long totalZgqNoUer = zgqNoUserAllDistrict.getTotalZgqNoUer();
                long totalHu = totalUserHu + totalNoUserHu;
                long totalPeople = totalZgqUser + totalZgqNoUer;
                record.setTotalHu(totalHu);
                record.setTotalPeople(totalPeople);
                record.setTotalUserHu(totalUserHu);
                record.setTotalZgqUser(totalZgqUser);
                record.setTotalNoUserHu(totalNoUserHu);
                record.setTotalZgqNoUer(totalZgqNoUer);
            } else {
                long totalUserHu = 0;
                long totalZgqUser = 0;
                long totalNoUserHu = 0;
                long totalZgqNoUer = 0;
                long totalHu = totalUserHu + totalNoUserHu;
                long totalPeople = totalZgqUser + totalZgqNoUer;
                record.setTotalHu(totalHu);
                record.setTotalPeople(totalPeople);
                record.setTotalUserHu(totalUserHu);
                record.setTotalZgqUser(totalZgqUser);
                record.setTotalNoUserHu(totalNoUserHu);
                record.setTotalZgqNoUer(totalZgqNoUer);
            }
            records.add(record);
        }
        return records;
    }

    /**
     * 方法名：getZgqAllDistrictByCountry
     * 根據村（社群）處理同鎮所有村級子級ZgqAllDistrict記錄，如果記錄為空，所屬例項全部賦值為0
     * @param childSameTown
     * @param country
     * @return
     */
    private ZgqAllDistrict getZgqAllDistrictByCountry(List<ZgqAllDistrict> childSameTown, String country) {
        ZgqAllDistrict zgqAllDistrict = null;
        for (int i = 0; i < childSameTown.size(); i++) {
            ZgqAllDistrict zgqAllDistrict1 = childSameTown.get(i);
            if (country.equals(zgqAllDistrict1.getAdName())) {
                zgqAllDistrict = zgqAllDistrict1;
                break;
            }
        }
        if (zgqAllDistrict == null) {
            zgqAllDistrict = new ZgqAllDistrict();
            zgqAllDistrict.setAdName(country);
            long value = 0;
            zgqAllDistrict.setTotalHu(value);
            zgqAllDistrict.setTotalPeople(value);
            zgqAllDistrict.setTotalUserHu(value);
            zgqAllDistrict.setTotalZgqUser(value);
            zgqAllDistrict.setTotalNoUserHu(value);
            zgqAllDistrict.setTotalZgqNoUer(value);
            childSameTown.add(zgqAllDistrict);
        }
        return zgqAllDistrict;
    }

    /**
     * 方法名：getChildByTownStreet
     * 描述：根據引數townStreet,即鎮（街道）獲取當前鎮下的所有村的ZgqAllDistrictVo物件，經處理後返回儲存ZgqAllDistrict物件的list
     * @param townStreet
     * @return
     */
    private List<ZgqAllDistrict> getChildByTownStreet(String townStreet) {
        // 獲取保障戶的戶數，人數資料列表
        List<ZgqAllDistrictVo> zgqUserList = zgqUserMapper.getListByTownAndCname();
        List<ZgqAllDistrictVo> userListVo = zgqUserList.stream().filter(ZgqAllDistrictVo -> townStreet.equals(ZgqAllDistrictVo.getTownStreet())).collect(Collectors.toList());
        List<ZgqAllDistrict> userChildS = new ArrayList<>();
        for (ZgqAllDistrictVo vo : userListVo) {
            ZgqAllDistrict userChild = new ZgqAllDistrict();
            String adName1 = vo.getVillageCommunity();
            long totalUserHu = vo.getHuShu();
            long totalZgqUser = vo.getRenShu();
            long totalNoUserHu = 0;
            long totalZgqNoUer = 0;
            long totalHu = totalUserHu + totalNoUserHu;
            long totalPeople = totalZgqUser + totalZgqNoUer;
            userChild.setAdName(adName1);
            userChild.setTotalHu(totalHu);
            userChild.setTotalPeople(totalPeople);
            userChild.setTotalUserHu(totalUserHu);
            userChild.setTotalZgqUser(totalZgqUser);
            userChild.setTotalNoUserHu(totalNoUserHu);
            userChild.setTotalZgqNoUer(totalZgqNoUer);
            userChildS.add(userChild);
        }
        return userChildS;
    }

    /**
     * 方法名：getChildByZhen
     * 描述：根據引數zhen,即鎮（街道）獲取當前鎮下的所有村的ZgqAllDistrictVo物件，經處理後返回儲存ZgqAllDistrict物件的list
     * @param zhen
     * @return
     */
    private List<ZgqAllDistrict> getChildByZhen(String zhen) {
        // 獲取非保障戶的戶數，人數資料列表
        List<ZgqAllDistrictVo> zgqNoUserList = zgqNoUserMapper.getListByZhenAndCun();
        List<ZgqAllDistrictVo> noUserListVo = zgqNoUserList.stream().filter(ZgqAllDistrictVo -> ZgqAllDistrictVo.getTownStreet().equals(zhen)).collect(Collectors.toList());
        List<ZgqAllDistrict> noUserChildS = new ArrayList<>();
        for (ZgqAllDistrictVo districtVo : noUserListVo) {
            ZgqAllDistrict noUserChild = new ZgqAllDistrict();
            String adName2 = districtVo.getVillageCommunity();
            long totalUserHu = 0;
            long totalZgqUser = 0;
            long totalNoUserHu = districtVo.getHuShu();
            long totalZgqNoUer = districtVo.getRenShu();
            long totalHu = totalUserHu + totalNoUserHu;
            long totalPeople = totalZgqUser + totalZgqNoUer;
            noUserChild.setAdName(adName2);
            noUserChild.setTotalHu(totalHu);
            noUserChild.setTotalPeople(totalPeople);
            noUserChild.setTotalUserHu(totalUserHu);
            noUserChild.setTotalZgqUser(totalZgqUser);
            noUserChild.setTotalNoUserHu(totalNoUserHu);
            noUserChild.setTotalZgqNoUer(totalZgqNoUer);
            noUserChildS.add(noUserChild);
        }
        return noUserChildS;
    }
}

Mapper類：

package com.fw.hs.mapper;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fw.hs.entity.ZgqUser;
import com.fw.hs.vo.ChartResVo.BarChartResVo;
import com.fw.hs.vo.ChartResVo.PieChartResVo;
import com.fw.hs.vo.ZgqAllDistrictByTownVo;
import com.fw.hs.vo.ZgqAllDistrictVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * <p>
 * 資格權-使用者記錄表 Mapper 介面
 * </p>
 *
 * @author 
 * @since 2020-09-21
 */
@Mapper
public interface ZgqUserMapper extends BaseMapper<ZgqUser> {

    /**
     * 根據鎮獲取保障戶戶數和人數資料列表
     * @return
     */
    List<ZgqAllDistrictByTownVo> getListByTown();
   /**
     * 根據鎮和村獲取保障書戶數和人數資料列表
     * @return
     */
    List<ZgqAllDistrictVo> getListByTownAndCname();

}

package com.fw.hs.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.fw.hs.entity.ZgqNoUser;
import com.fw.hs.vo.ChartResVo.BarChartResVo;
import com.fw.hs.vo.ChartResVo.PieChartResVo;
import com.fw.hs.vo.ZgqAllDistrictByTownVo;
import com.fw.hs.vo.ZgqAllDistrictVo;
import com.fw.hs.vo.query.ZgqNoUserQuery;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

 /**
  * @Author: 
  * @Date: 2020/10/30 13:35
  */
@Mapper
public interface ZgqNoUserMapper extends BaseMapper<ZgqNoUser>{

   
    /**
     * 根據鎮獲取非保障戶戶數和人數資料列表
     * @return
     */
    List<ZgqAllDistrictByTownVo> getListByZhen();
    /**
     * 根據鎮和村獲取非保障戶戶數和人數資料列表
     * @return
     */
    List<ZgqAllDistrictVo> getListByZhenAndCun();

}

Vo類：

package com.fw.hs.vo.query;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.lang.reflect.Field;

/**
 * @program: homestead
 * @Date: 2020/10/28 16:36
 * @Author: 
 * @Version: 1.0
 * @Description:
 */
@Data
public class ZgqAllDistrictQuery<T> extends PageQuery<T>{

    @ApiModelProperty("總戶數")
    private Long totalHu$RHK;

    @ApiModelProperty("總人數")
    private Long totalPeople$RHK;

    @ApiModelProperty("總保障戶數")
    private Long totalUserHu$RHK;

    @ApiModelProperty("總保障戶人數")
    private Long totalZgqUser$RHK;

    @ApiModelProperty("非保障戶數")
    private Long totalNoUserHu$RHK;

    @ApiModelProperty("行政區劃")
    private String adName$RHK;

    @ApiModelProperty("升序")
    private String order$ASC;

    @ApiModelProperty("降序")
    private String order$DESC;

    @Override
    protected Object getCurrent() {
        return this;
    }

    @Override
    protected Field[] getDeclaredFields() {
        return this.getClass().getDeclaredFields();
    }
}

package com.fw.hs.vo;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * @program: homestead
 * @Date: 2020/10/29 16:34
 * @Author: 
 * @Version: 1.0
 * @Description:
 */
@Data
public class ZgqAllDistrictByTownVo {

    @ApiModelProperty("鎮（街道）")
    private String town;

    @ApiModelProperty("戶數")
    private Long numHu;

    @ApiModelProperty("人數")
    private Long numRen;
}

package com.fw.hs.vo;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * @program: homestead
 * @Date: 2020/10/28 15:55
 * @Author: 
 * @Version: 1.0
 * @Description:
 */
@Data
public class ZgqAllDistrictVo {

    @ApiModelProperty("鎮（街道）")
    private String townStreet;

    @ApiModelProperty("村（社群）")
    private String villageCommunity;

    @ApiModelProperty("戶數")
    private Long huShu;

    @ApiModelProperty("人數")
    private Long renShu;

}

<?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.fw.hs.mapper.ZgqUserMapper">

    <resultMap id="BaseResultMap2" type="com.fw.hs.entity.ZgqUser">
        <id column="id" property="id"/>
        <result column="xm" property="xm"/>
        <result column="xb" property="xb"/>
        <result column="sfzh" property="sfzh"/>
        <result column="gx" property="gx"/>
        <result column="dz" property="dz"/>
        <result column="cun" property="cun"/>
        <result column="ad_name" property="adName"/>
        <result column="bz" property="bz"/>
        <result column="huhao" property="huhao"/>
        <result column="zhen" property="zhen"/>
        <result column="bgzt" property="bgzt"/>
    </resultMap>

    <!--根據鎮和村分組查詢保障戶數和保障人數列表-->
    <select id="getListByTownAndCname" resultType="com.fw.hs.vo.ZgqAllDistrictVo">
        SELECT
        zhen AS townStreet,
        cun AS villageCommunity,
        COUNT(DISTINCT huhao) AS huShu,
        COUNT(DISTINCT id) AS renShu
        FROM zgq_user
        GROUP BY zhen,cun
    </select>

    <!--根據鎮分組查詢保障戶數和保障人數列表-->
    <select id="getListByTown" resultType="com.fw.hs.vo.ZgqAllDistrictByTownVo">
        SELECT
        zhen AS town,
        COUNT(DISTINCT huhao) AS numHu,
        COUNT(DISTINCT id) AS numRen
        FROM zgq_user
        GROUP BY zhen
    </select>

</mapper>

<?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.fw.hs.mapper.ZgqNoUserMapper">

    <!--根據鎮和村分組查詢非保障戶數和非保障人數列表-->
    <select id="getListByZhenAndCun" resultType="com.fw.hs.vo.ZgqAllDistrictVo">
       SELECT
       zhen AS townStreet,
       cun AS villageCommunity,
       COUNT(DISTINCT id) AS huShu,
       COUNT(DISTINCT id) AS renShu
       FROM zgq_no_user
       GROUP BY zhen,cun
    </select>

    <!--根據鎮分組查詢非保障戶數和非保障人數列表-->
    <select id="getListByZhen" resultType="com.fw.hs.vo.ZgqAllDistrictByTownVo">
       SELECT
       zhen AS town,
       COUNT(DISTINCT id) AS numHu,
       COUNT(DISTINCT id) AS numRen
       FROM zgq_no_user
       GROUP BY zhen
    </select>

</mapper>

3、思路三

這是基於新的需求變更而調整的，最新的要求按照ad_code來統計，將兩張表都加入ad_code和ad_name欄位。將登陸使用者所在的行政區劃下所有行政區都統計出來。其實第二種思路也可以拿來用，但是前端處理資料可能會複雜一些，所以才有了思路三，依賴寫SQL完成統計，分頁依靠程式碼實現。

先貼出統計SQL，如下：

<?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.fw.hs.mapper.ZgqAllDistrictMapper">

    <!-- 查詢統計全區資格權保障戶和非保障戶資料 -->
    <select id="getStatisticsData" resultType="com.fw.hs.vo.ZgqRegionStatisticsVo">
      select zgq.xzqdm,totalUserHu,totalZgqUser,totalNoUserHu,totalZgqNoUer,(totalUserHu+totalNoUserHu) as totalHu,(totalZgqUser+totalZgqNoUer) as totalPeople from
      (select ROW_NUMBER() over(order by xzqdm) as tabn1,* from (select UserHu.* from (
      SELECT substring(ad_code,1,6) xzqdm,count(huhao) totalUserHu,count(sfzh) totalZgqUser FROM zgq_user group by substring(ad_code,1,6) UNION
      SELECT substring(ad_code,1,9) xzqdm,count(huhao) totalUserHu,count(sfzh) totalZgqUser FROM zgq_user group by substring(ad_code,1,9) UNION
      SELECT substring(ad_code,1,12) xzqdm,count(huhao) totalUserHu,count(sfzh) totalZgqUser FROM zgq_user group by substring(ad_code,1,12)) UserHu)t_zgq) as zgq
      LEFT JOIN
      (select ROW_NUMBER() over(order by xzqdm) as tabn2,* from (select NoUserHu.* from (
      SELECT substring(ad_code,1,6) xzqdm,count(sfzh) totalNoUserHu,count(sfzh) totalZgqNoUer FROM zgq_no_user group by substring(ad_code,1,6) UNION
      SELECT substring(ad_code,1,9) xzqdm,count(sfzh) totalNoUserHu,count(sfzh) totalZgqNoUer FROM zgq_no_user group by substring(ad_code,1,9) UNION
      SELECT substring(ad_code,1,12) xzqdm,count(sfzh) totalNoUserHu,count(sfzh) totalZgqNoUer FROM zgq_no_user group by substring(ad_code,1,12)) NoUserHu)t_no_zgq) as no_zgq
      ON tabn1=tabn2
    </select>
</mapper>

對映類：

package com.fw.hs.vo;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @program: homestead
 * @Date: 2020/11/4 22:54
 * @Author: 
 * @Version: 1.0
 * @Description:
 */
@Data
@NoArgsConstructor
public class ZgqRegionStatisticsVo {

    @ApiModelProperty("行政區程式碼")
    private String xzqdm;

    @ApiModelProperty("總戶數")
    private long totalHu;

    @ApiModelProperty("總人數")
    private long totalPeople;

    @ApiModelProperty("總保障戶數")
    private long totalUserHu;

    @ApiModelProperty("總保障戶人數")
    private long totalZgqUser;

    @ApiModelProperty("非保障總戶數")
    private long totalNoUserHu;

    @ApiModelProperty("非保障戶總人數")
    private long totalZgqNoUer;

}

mapper類：

package com.fw.hs.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fw.hs.entity.ZgqAllDistrict;
import com.fw.hs.vo.ZgqRegionStatisticsVo;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
 * @program: homestead
 * @Date: 2020/10/28 15:50
 * @Author: 
 * @Version: 1.0
 * @Description:
 */
@Mapper
public interface ZgqAllDistrictMapper extends BaseMapper<ZgqAllDistrict> {

    /**
     * 查詢獲取全區宅基地資格權統計表資料
     * @param xzqdm
     * @return
     */
    List<ZgqRegionStatisticsVo> getStatisticsData(String xzqdm);
}

service類：

package com.fw.hs.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import com.fw.hs.entity.ZgqAllDistrict;
import com.fw.hs.vo.ZgqRegionStatisticsVo;
import com.fw.hs.vo.query.ZgqAllDistrictQuery;

import java.util.List;

/**
 * @program: homestead
 * @Date: 2020/10/28 16:17
 * @Author: 
 * @Version: 1.0
 * @Description:
 */
public interface IZgqAllDistrictService extends IService<ZgqAllDistrict> {

    /**
     * 查詢獲取全區宅基地資格權統計表資料
     * @param xzqdm
     * @return
     */
    List<ZgqRegionStatisticsVo> getStatisticsData(String xzqdm);
}

業務邏輯層：

package com.fw.hs.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.fw.hs.entity.ZgqAllDistrict;
import com.fw.hs.mapper.ZgqAllDistrictMapper;
import com.fw.hs.mapper.ZgqNoUserMapper;
import com.fw.hs.mapper.ZgqUserMapper;
import com.fw.hs.service.IZgqAllDistrictService;
import com.fw.hs.vo.ZgqAllDistrictByTownVo;
import com.fw.hs.vo.ZgqAllDistrictVo;
import com.fw.hs.vo.ZgqRegionStatisticsVo;
import com.fw.hs.vo.query.ZgqAllDistrictQuery;
import com.fwcloud.common.base.support.IdGenerator;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @program: homestead
 * @Date: 2020/10/28 16:19
 * @Author:
 * @Version: 1.0
 * @Description:
 */
@Service
public class ZgqAllDistrictServiceImpl extends ServiceImpl<ZgqAllDistrictMapper, ZgqAllDistrict> implements IZgqAllDistrictService {

    @Resource
    private ZgqUserMapper zgqUserMapper;
    @Resource
    private ZgqNoUserMapper zgqNoUserMapper;

    /**
     * 查詢獲取全區宅基地資格權統計表資料
     * @param xzqdm
     * @return
     */
    @Override
    public List<ZgqRegionStatisticsVo> getStatisticsData(String xzqdm) {
        List<ZgqRegionStatisticsVo> list = baseMapper.getStatisticsData(xzqdm);
        List<ZgqRegionStatisticsVo> statisticsData = new ArrayList<>();
        ZgqRegionStatisticsVo zgqRegionStatisticsVo = new ZgqRegionStatisticsVo();
        for (int i = 0; i < list.size(); i++) {
            ZgqRegionStatisticsVo zgqRegionStatisticsVo1 = list.get(i);
            System.out.println(zgqRegionStatisticsVo1);
            if (zgqRegionStatisticsVo1.getTotalNoUserHu() != 0 && zgqRegionStatisticsVo1.getTotalZgqNoUer() != 0) {
                zgqRegionStatisticsVo = zgqRegionStatisticsVo1;
            } else {
                zgqRegionStatisticsVo.setXzqdm(zgqRegionStatisticsVo1.getXzqdm());
                zgqRegionStatisticsVo.setTotalHu(zgqRegionStatisticsVo1.getTotalUserHu()+zgqRegionStatisticsVo1.getTotalNoUserHu());
                zgqRegionStatisticsVo.setTotalPeople(zgqRegionStatisticsVo1.getTotalZgqUser()+zgqRegionStatisticsVo1.getTotalZgqNoUer());
                zgqRegionStatisticsVo.setTotalUserHu(zgqRegionStatisticsVo1.getTotalUserHu());
                zgqRegionStatisticsVo.setTotalZgqUser(zgqRegionStatisticsVo1.getTotalZgqUser());
                zgqRegionStatisticsVo.setTotalNoUserHu(zgqRegionStatisticsVo1.getTotalNoUserHu());
                zgqRegionStatisticsVo.setTotalZgqNoUer(zgqRegionStatisticsVo1.getTotalZgqNoUer());
            }
            statisticsData.add(zgqRegionStatisticsVo);
        }
        return statisticsData;
    }
}

controller類：

package com.fw.hs.controller;

import com.fw.hs.entity.ZgqAllDistrict;
import com.fw.hs.service.IZgqAllDistrictService;
import com.fw.hs.vo.ZgqRegionStatisticsVo;
import com.fw.hs.vo.query.ZgqAllDistrictQuery;
import com.fwcloud.common.web.wrapper.ReturnWrapMapper;
import com.fwcloud.common.web.wrapper.ReturnWrapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * @program: homestead
 * @Date: 2020/10/28 16:27
 * @Author: 
 * @Version: 1.0
 * @Description:
 */
@Api(tags = "資格權-統計-介面")
@RestController
@RequestMapping("/api/zgq")
public class ZgqAllDistrictController {

    @Autowired
    private IZgqAllDistrictService iZgqAllDistrictService;

    @ApiOperation(value = "獲取全區宅基地資格權統計表資料", consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE)
    @GetMapping("/getStatisticsData")
    public ReturnWrapper<List<ZgqRegionStatisticsVo>> getStatisticsList(@RequestParam(required = false) String xzqdm) {
        return ReturnWrapMapper.ok(iZgqAllDistrictService.getStatisticsData(xzqdm));
    }
}

四、測試

思路二的測試結果如圖：

思路三測試結果如圖：

五、總結

筆者個人是傾向能SQL完成的就儘量寫SQL，不能的創造條件也要搞。這就導致了這次開發的時候走了極端，思路二就那麼幾行SQL，卻寫了那麼多程式碼；而思路三二十多行SQL，程式碼量很少。其實具體還是看業務和需求變化的，選擇哪種看效率。

當然了，筆者還存在很多需要提升的地方，經過這次任務也暴露出筆者完全對前端的不感冒，硬著頭皮上，趕時間，真的很痛苦！

查詢條件和條數，先查詢兩條免費的，後面為vip
2020-10-12
NHibernate使用ICriteria分頁並返回資料庫記錄總條數
2014-01-18
資料庫
分庫分表後的分頁查詢
2021-04-25
查詢多張表記錄數的儲存過程
2006-04-28
儲存過程
MySQL 查詢所有表中的記錄數
2017-03-19
MySql
MySql先分組統計總記錄數，再獲取記錄數中的最大值
2020-11-03
MySql
Laravel 關聯查詢限制條數和分組查詢顯示為零的結果
2019-03-22
Laravel
中後端管理系統前後分離、前端框架的實現拙見
2017-07-01
後端前端框架
vue做後臺管理系統，記住列表的查詢條件和分頁
2017-11-01
Vue
SQL 單表多條記錄分組查詢分頁程式碼
2012-04-20
SQL
PHP查詢資料庫中滿足條件的記錄條數(二種實現方法)
2015-08-26
PHP資料庫
得嘞，分頁外掛 PageHelper 返回記錄總數 total 竟然出錯了！
2024-03-20
sqlserver查詢一個庫所有表的記錄數
2016-09-12
SQLServer
Spring Data Jpa 複雜查詢總結 (多表關聯以及自定義分頁 )
2018-05-13
Spring
MongoDB隨機查詢返回一條或N條記錄的方法
2019-07-03
MongoDB隨機
Java實現後端分頁
2024-03-20
Java後端
通用的後端返回給前端的狀態資訊Result
2024-04-22
後端前端
mysql三張表關聯查詢
2018-10-12
MySql
[MySQL] - 聯表查詢，查詢一個不在另一個表的記錄
2017-12-20
MySql
web 端展現報表時查詢表單如何實現引數聯動
2020-06-15
Web
app後端設計–總目錄
2017-12-04
APP後端
mysql 查詢記錄數大於一千萬的表
2024-08-23
MySql
如何查詢總和等於給定數字的整數陣列中的所有對
2019-01-25
陣列
spring-data-jpa + SpringBoot + bootstrapTable 後端分頁模糊查詢
2017-08-22
Spring BootAPT後端
python實現給定一個數和陣列，求陣列中兩數之和為給定的數
2019-02-19
Python陣列
前後端聯調實踐總結
2017-08-01
後端
【總結】二分查詢 —— 一種減而治之的查詢方法（1）
2018-11-08
查詢資料庫後是返回ResultSet實現中遇到的問題
2002-10-11
資料庫
分頁總頁數計算方法所有分頁通用
2016-08-23
更新一張與另一張表關聯的連線欄位記錄
2024-10-12
查詢 SQL SERVER 所有表記錄數
2018-01-05
SQLServer
app後端設計–總目錄(轉)
2017-11-07
APP後端
Mysql-基本練習(10-設定分組條件、查詢結果排序、限制查詢結果返回的數量、TRUNCATE刪除表記錄)
2018-05-17
MySql排序
SQL Server在分頁獲取資料的同時獲取到總記錄數的兩種方法
2019-01-01
SQLServer
百億級資料分表後怎麼分頁查詢？
2020-11-09
偶然的實驗，查詢兩條記錄的全表很久，10046真好用...
2017-11-06
億萬級分庫分表後如何進行跨表分頁查詢
2023-03-29
Oracle查詢庫中記錄數大於2千萬的所有表
2014-08-14
Oracle