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>