普通查询

实体定义

public class SiteData {

    //站点id
    private Integer siteId;

    //站点父级站点id
    private Integer pid;

    //站点名称
    private String siteName;

    //排序号
    private Integer sort;
    //站点下属人员数量
    private Integer totalNum;

    //下属站点集合
    private List<SiteData> sites;

    public List<SiteData> getSites() {
        return sites;
    }

    public void setSites(List<SiteData> sites) {
        this.sites = sites;
    }

    public Integer getSiteId() {
        return siteId;
    }

    public void setSiteId(Integer siteId) {
        this.siteId = siteId;
    }

    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }

    public String getSiteName() {
        return siteName;
    }

    public void setSiteName(String siteName) {
        this.siteName = siteName;
    }

    public Integer getSort() {
        return sort;
    }

    public void setSort(Integer sort) {
        this.sort = sort;
    }

    public Integer getTotalNum() {
        return totalNum;
    }

    public void setTotalNum(Integer totalNum) {
        this.totalNum = totalNum;
    }
}

mapper 定义

<resultMap id="siteDataRM" type="com.sinldo.api.business.getsites.vo.SiteData" autoMapping="true">
   <id column="GroupID" property="siteId"></id>
   <result column="GroupName" property="siteName"></result>
   <result column="GroupID" property="sort"></result>
   <result column="center" property="pid"></result>
   <association property="totalNum" column="{groupId=GroupID}" javaType="integer" select="getSitePersonnelCount"/>
   <collection property="sites" column="SiteID"
            ofType="com.sinldo.api.business.getsites.vo.SiteData" >
      <id column="SiteID" property="siteId"></id>
      <result column="GroupID" property="pid"></result>
      <result column="BornRank" property="sort"></result>
      <result column="SiteName" property="siteName"></result>
      <association property="totalNum" column="{siteId=SiteID,groupId=GroupID}" javaType="integer" select="getSitePersonnelCount"/>
   </collection>
</resultMap>

<select id="getSiteDataList" resultMap="siteDataRM">
   SELECT SiteID,s.GroupID,SiteName,g.GroupName,s.BornRank,0 AS center
    FROM Sites s
   LEFT JOIN Groups g ON s.GroupID=g.GroupID
   WHERE s.GroupID != 0
   <if test="groupId != null" >
      AND s.GroupID = #{groupId}
   </if>
   <if test="siteId != null" >
      AND s.siteId = #{siteId}
   </if>
   ORDER BY s.GroupID ASC,s.BornRank ASC
</select>
<select id="getSitePersonnelCount" resultType="integer">
   select count(0) from personnel where 1=1
   <if test="groupId != null" >
      AND GroupID = #{groupId}
   </if>
   <if test="siteId != null" >
      AND siteId = #{siteId}
   </if>
</select>

分组查询

实体定义

package com.sinldo.api.business.networknotice.resendmessage.vo;

import java.util.ArrayList;
import java.util.List;

/**
 *  组织架构树节点
 */
public class DepartTreeNode {
    private String pname;

    private Integer pid;

    private List<DepartCount> list;

    /**
     * 向集合中添加元素
     * @param departCount
     */
    public void add(DepartCount departCount){
        if(list == null)
            list = new ArrayList<>();
        list.add(departCount);
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }

    public List<DepartCount> getList() {
        return list;
    }

    public void setList(List<DepartCount> list) {
        this.list = list;
    }

}

package com.sinldo.api.business.networknotice.resendmessage.vo;

/**
 * 部门计数器
 */
public class DepartCount {

    //站点名称
    private String siteName;

    //站点id
    private Integer siteId;

    //已经看了多少人
    private Integer isSee;

    //发送了多人
    private Integer isSend;

    public String getSiteName() {
        return siteName;
    }

    public void setSiteName(String siteName) {
        this.siteName = siteName;
    }

    public Integer getSiteId() {
        return siteId;
    }

    public void setSiteId(Integer siteId) {
        this.siteId = siteId;
    }

    public Integer getIsSee() {
        return isSee;
    }

    public void setIsSee(Integer isSee) {
        this.isSee = isSee;
    }

    public Integer getIsSend() {
        return isSend;
    }

    public void setIsSend(Integer isSend) {
        this.isSend = isSend;
    }
}

mapper 定义

<resultMap id="get-getReSendMessageVOListByNoticeId"
           type="com.sinldo.api.business.networknotice.resendmessage.vo.DepartTreeNode" autoMapping="true">
    <id column="groupId" property="pid"></id>
    <result column="groupName" property="pname"></result>
    <collection property="list" column="siteId"
                ofType="com.sinldo.api.business.networknotice.resendmessage.vo.DepartCount" >
        <id column="siteId" property="siteId"></id>
        <result column="siteName" property="siteName"></result>
        <result column="seeCount" property="isSee"></result>
        <result column="sendCount" property="isSend"></result>
    </collection>
</resultMap>

<select id="getReSendMessageVOListByNoticeId" resultMap="get-getReSendMessageVOListByNoticeId">
    select a.SiteID AS  siteId,
    a.SiteName AS  siteName,
    a.bornRank as bornRank,
    a.GroupID AS  groupId,
    a.GroupName AS  groupName,isnull(b.count,0) as seeCount,isnull(c.count,0) as sendCount  from
    (
    select  s.SiteID AS  siteId,
    s.SiteName AS  siteName,
    s.bornRank as bornRank,
    g.GroupID AS  groupId,
    g.GroupName AS  groupName
    from sites s
    left join Groups g on s.GroupID = g.groupID
    where 1=1
    ) a left join

    (
    SELECT
    count(rm.sender_voip) as count,
    max(p.siteId) as siteId
    FROM
    sghn_notice_reply_member rm,
    Personnel p
    WHERE
    1 = 1
    <if test="noticeId != null">
        AND
        rm.notice_id =  #{noticeId}
    </if>
    and rm.operation != 'del'
    and p.operation != 'del'
    and rm.sender_voip = p.VOIP
    group by p.siteId
    ) b on a.siteId=b.siteId
    left join (
    SELECT
    count(p.voip) as count,
    max(p.siteId) as siteId
    FROM
    sc_comp_notice sn,
    Personnel p
    WHERE
    1 = 1
    <if test="noticeId != null">
        AND
        sn.notice_id =  #{noticeId}
    </if>
    and sn.operation != 'del'
    and p.operation != 'del'
    and p.voip in (${receiver_voips})
    group by p.siteId

    ) c on a.siteId=c.siteId
    where 1=1
    <if test="siteIds!= null">
        AND
        a.siteId in (${siteIds})
    </if>
    order by a.groupId asc, a.bornRank ASC
</select>