小傻羊7.30学习笔记——两个表合并处理的后端操作

7.30学习笔记——两个表合并处理的后端操作

• Mapper Xml

  package com.cdeledu.yunqi.biz.dao;
  
  import com.cdeledu.yunqi.biz.model.BizReferInfo;
  import java.util.List;
  import java.util.Map;
  
  public interface BizReferInfoMapper {
      int insert(BizReferInfo record);
  
      List<BizReferInfo> selectAll();
  
      /**
       * 获取访问来源列表
       * @param dn
       * @param time
       * @return
       */
      List<Map<String,Object>> selectReferList(String dn,String time);
  
      List<BizReferInfo> selectReferParent();
  }
  

  <?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.cdeledu.yunqi.biz.dao.BizReferInfoMapper">
    <resultMap id="BaseResultMap" type="com.cdeledu.yunqi.biz.model.BizReferInfo">
      <result column="id" jdbcType="INTEGER" property="id" />
      <result column="url" jdbcType="VARCHAR" property="url" />
      <result column="des" jdbcType="VARCHAR" property="des" />
      <result column="pid" jdbcType="INTEGER" property="pid" />
      <result column="description" jdbcType="VARCHAR" property="description" />
    </resultMap>
    <insert id="insert" parameterType="com.cdeledu.yunqi.biz.model.BizReferInfo">
      insert into biz_refer_info (id, url, des, 
        pid, description)
      values (#{id,jdbcType=INTEGER}, #{url,jdbcType=VARCHAR}, #{des,jdbcType=VARCHAR}, 
        #{pid,jdbcType=INTEGER}, #{description,jdbcType=VARCHAR})
    </insert>
    <select id="selectAll" resultMap="BaseResultMap">
      select id, url, des, pid, description
      from biz_refer_info
    </select>
  
    <select id="selectReferParent" resultType="com.cdeledu.yunqi.biz.model.BizReferInfo">
          select
           id, url, des, pid, description
           from biz_refer_info
           where id=pid
            order by pid asc
    </select>
    <select id="selectReferList" resultType="java.util.HashMap">
  
          SELECT
              t1.id,
              t1.description,
              t1.url,
              t1.pid,
              t2.pv,
              t2.uv,
              t2.ip_count
          FROM
              biz_refer_info t1
              JOIN (
          SELECT
              refer,
              sum( pv ) AS pv,
              sum( uv ) AS uv,
              sum( ip_count ) AS ip_count
          FROM
              biz_flow_refer
          WHERE
              dn = #{dn}
              AND dt = #{time}
          GROUP BY
              refer
              ) AS t2 ON t1.id = t2.refer
              order by t1.pid asc
    </select>
  
  </mapper>
  

• Service接口和实现类

  package com.cdeledu.yunqi.biz.service;
  
  import java.util.Map;
  
  /**
   * @author linke
   */
  public interface IBizReferInfoService {
      /**
       * 查询流量来源数据
       *
       * @param dn
       * @param time
       * @return
       */
      Map<String, Object> selectReferList(String dn, String time);
  
  }
  

package com.cdeledu.yunqi.biz.service.impl;

import com.cdeledu.yunqi.biz.dao.BizReferInfoMapper;
import com.cdeledu.yunqi.biz.model.BizReferInfo;
import com.cdeledu.yunqi.biz.service.IBizReferInfoService;
import com.cdeledu.yunqi.common.utils.DateUtils;
import com.cdeledu.yunqi.common.utils.StringUtils;
import com.cdeledu.yunqi.framework.service.BaseService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

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

@Service
@Slf4j
public class BizReferInfoServiceImpl extends BaseService implements IBizReferInfoService {

    @Resource
    private BizReferInfoMapper bizReferInfoMapper;

    @Override
    public Map<String, Object> selectReferList(String dn, String time) {
        Map<String, Object> data = new HashMap<>();
        if (StringUtils.isEmpty(dn)) {
            dn = currentUserFirstDn();
        }
        if (StringUtils.isEmpty(time)) {
            time = DateUtils.formatDate2Str(new Date(), "yyyyMMdd", 1);
        }
        
        //取dn
        Map<String, Object> perms = currentPerms();
        if (perms != null && perms.size() > 0) {
            data.put("dns", perms.get("dns"));
        } else {
            data.put("dns", new ArrayList<>());
        }
        data.put("dn", dn);
        List<Map<String, Object>> list = bizReferInfoMapper.selectReferList(dn, time);
        Map<Integer, BizReferInfo> map = new HashMap<>();
        if (list != null && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> stringObjectMap = list.get(i);
                if (stringObjectMap.get("id") == null || stringObjectMap.get("pid") == null) {
                    continue;
                }
                Integer id = Integer.valueOf(stringObjectMap.get("id") + "");
                Integer pid = Integer.valueOf(stringObjectMap.get("pid") + "");
                String description = stringObjectMap.get("description") + "";
                String url = stringObjectMap.get("url") + "";
                String des = stringObjectMap.get("des") + "";
                Integer pv = stringObjectMap.get("pv") == null ? 0 : Integer.valueOf(stringObjectMap.get("pv") + "");
                Integer uv = stringObjectMap.get("uv") == null ? 0 : Integer.valueOf(stringObjectMap.get("uv") + "");
                Integer ip_count = stringObjectMap.get("ip_count") == null ? 0 : Integer.valueOf(stringObjectMap.get("ip_count") + "");
                BizReferInfo item = new BizReferInfo();
                item.setId(id);
                item.setDescription(description);
                item.setUrl(url);
                item.setPid(pid);
                item.setDes(des);
                item.setPv(pv);
                item.setUv(uv);
                item.setIp(ip_count);
                if (id.equals(pid)) {
                    BizReferInfo bizReferInfo = map.get(pid);
                    if (bizReferInfo != null) {
                        System.out.println(bizReferInfo);
                    } else {
                        map.put(pid, item);
                    }
                } else {
                    BizReferInfo bizReferInfo = map.get(pid);
                    if (bizReferInfo != null) {
                        List<BizReferInfo> children = bizReferInfo.getChildren();
                        children.add(item);
                    } else {
                        List<BizReferInfo> children = new ArrayList<>();
                        bizReferInfo = new BizReferInfo();
                        children.add(item);
                        bizReferInfo.setChildren(children);
                        map.put(pid, bizReferInfo);
                    }
                }
            }
        }
        List<BizReferInfo> listPa = bizReferInfoMapper.selectReferParent();
        Map<Integer, BizReferInfo> mapPa = new HashMap<>();
        for (int i = 0; i < listPa.size(); i++) {
            BizReferInfo bizReferInfo = listPa.get(i);
            if (bizReferInfo != null) {
                mapPa.put(bizReferInfo.getId(), bizReferInfo);
            }
        }
        List<BizReferInfo> reList = new ArrayList<>();
        if (map != null && map.size() > 0) {
            Set<Integer> keys = map.keySet();
            for (Integer key : keys) {
                BizReferInfo tmp = map.get(key);
                BizReferInfo bizReferInfo = mapPa.get(key);
                if (bizReferInfo != null) {
                    tmp.setDescription(bizReferInfo.getDescription());
                    tmp.setDes(bizReferInfo.getDes());
                    tmp.setId(bizReferInfo.getId());
                    tmp.setUrl(bizReferInfo.getUrl());
                    tmp.setPid(bizReferInfo.getPid());
                }
                reList.add(tmp);
            }
        }

        for (int i = 0; i < reList.size(); i++) {
            BizReferInfo bizReferInfo = reList.get(i);
            List<BizReferInfo> children = bizReferInfo.getChildren();
            if (children != null && children.size() > 0) {
                int Pv = 0, Uv = 0, Ip = 0;
                for (int j = 0; j < children.size(); j++) {
                    BizReferInfo bizReferInfo1 = children.get(j);
                    Pv += bizReferInfo1.getPv();
                    Uv += bizReferInfo1.getUv();
                    Ip += bizReferInfo1.getIp();
                }
                bizReferInfo.setPv(Pv);
                bizReferInfo.setUv(Uv);
                bizReferInfo.setIp(Ip);
            }
        }
        data.put("reList", reList);
        return data;
    }
}