• mybaties 一对多关系映射


    背景:

    数据库格式如下图所示

    现在要统计出在一段时间内dimension_type为op即所有运营商的pv、uv、vv等指标的数组,以便页面显示出每个运营商在该事件段内历史指标曲线图。

    分析:

    返回的结果格式为:"data": [
        {

      "name": "电信",
          "data": [
            {
              "x": "20170803",
              "y": 2
            },
            {
              "x": "20170804",
              "y": 1
            }
          ]
        },
        {

        "name": "移动"
          "data": [
            {
              "x": "20170806",
              "y": 99
            },
            {
              "x": "20170807",
              "y": 1
            }
          ]
        },
        {

      "name": "联通",
          "data": [
            {
              "x": "20170804",
              "y": 10
            },
            {
              "x": "20170805",
              "y": 2
            }
          ]
        }
      ]

    很明显,返回结果是一个name对应多个data中数据的一对多关系。

    实现:

    首先,构造返回结果对应的java对象DataSeries

    public class DataSeries {
    	private String name;
    	private List<LineDataVo> data;
    	
    	public DataSeries(){
    		
    	}
    	
    	public DataSeries(String name, List<LineDataVo> data){
    		this.name = name;
    		this.data = data;
    	}
    	
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public List<LineDataVo> getData() {
    		return data;
    	}
    	public void setData(List<LineDataVo> data) {
    		this.data = data;
    	}
    
    	@Override
    	public String toString() {
    		return "Series [name=" + name + ", data=" + data + "]";
    	}
    }
    

     LineDataVo为

    public class LineDataVo {
    	private String x;
    	private int y;
    	
    	public LineDataVo(){
    		
    	}
    	public LineDataVo(String x, int y){
    		this.x = x;
    		this.y = y;
    	}
    	public String getX() {
    		return x;
    	}
    	public void setX(String x) {
    		this.x = x;
    	}
    	public int getY() {
    		return y;
    	}
    	public void setY(int y) {
    		this.y = y;
    	}
    	@Override
    	public String toString() {
    		return "LineDataVo [x=" + x + ", y=" + y + "]";
    	}
    	
    }
    

    对应的mapper文件为:

    public interface DimStatisticResultMapper {
    	List<DataSeries> findOperatorFieldTrendDatas(@Param("value")String value, @Param("dimensionType")String dimensionType,
    										@Param("timeType")String timeType, @Param("startTime")int startTime,
                                                   @Param("endTime")int endTime)throws DataAccessException; }

     其中value为要查询的参数,即pv或者vv,或者uv,timeType为统计时间的类型,以天、周或者月为单位

    对应的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.test.dao.DimStatisticResultMapper">
      <resultMap id="BaseResultMap" type="com.test.po.DimStatisticResult">
         <id column="id" jdbcType="INTEGER" property="id" />
         <result column="app_id" jdbcType="VARCHAR" property="appId" />
         <result column="cache_key" jdbcType="VARCHAR" property="cacheKey" />
         <result column="dimension_type" jdbcType="VARCHAR" property="dimensionType" />
         <result column="dimension_value" jdbcType="VARCHAR" property="dimensionValue" />
         <result column="time_type" jdbcType="VARCHAR" property="timeType" />
         <result column="time_value" jdbcType="INTEGER" property="timeValue" />
         <result column="pv" jdbcType="INTEGER" property="pv" />
         <result column="uv" jdbcType="INTEGER" property="uv" />
         <result column="vv" jdbcType="INTEGER" property="vv" />
         <result column="ipv" jdbcType="INTEGER" property="ipv" />
         <result column="total_delay" jdbcType="INTEGER" property="totalDelay" />
         <result column="total_stay" jdbcType="INTEGER" property="totalStay" />
         <result column="bounce_times" jdbcType="INTEGER" property="bounceTimes" />
         <result column="created_date" jdbcType="TIMESTAMP" property="createdDate" />
         <result column="modified_date" jdbcType="TIMESTAMP" property="modifiedDate" />
      </resultMap>
      <resultMap id="lineDataVoMap" type="com.test.vo.LineDataVo">
         <result column="x"  property="x" />
         <result column="y"  property="y" />
      </resultMap>
    
        <resultMap id="DataSeriesMap" type="com.test.vo.DataSeries">
            <result column="dimensionValue"  property="name" />
            <collection property="data" javaType="ArrayList" column="{value=count,dimensionValue=dimensionValue}" ofType="com.test.vo.LineDataVo"
            select="findOperatorFieldTrendData">
                <result property="x" column="time_value"/>
                <result property="y" column="count"/>
            </collection>
        </resultMap>
    
      <sql id="Base_Column_List">
         id,app_id, cache_key,dimension_type,dimension_value,time_type,time_value, pv, uv, 
         vv, ipv, total_delay, total_stay, bounce_times, bounce_times, modified_date
      </sql>
    
        <select id ="findOperatorFieldTrendData" resultMap = "LineDataVoMap">
            select time_value, ${value} as count
            from bd_dim_statistic_result where dimension_type="op"
            and dimension_value=#{dimensionValue}
        </select>
    
        <select id="findOperatorFieldTrendDatas" resultMap="DataSeriesMap" >
            select dimension_value as dimensionValue,time_value ,#{value} as count,pv from bd_dim_statistic_result
            where  dimension_type = #{dimensionType} and time_value between #{startTime} and #{endTime} group by dimension_value
        </select>
    
    </mapper>
    
  • 相关阅读:
    每周进度条07
    软件需求模式阅读笔记06
    每周进度条06
    软件需求模式阅读笔记05
    Django之ModelForm组件
    Django的性能优化
    分页,缓存,序列化,信号
    Django补充——中间件、请求的生命周期等
    Git基础介绍和使用
    Django基础之三
  • 原文地址:https://www.cnblogs.com/rainydayfmb/p/7353299.html
Copyright © 2020-2023  润新知