Mapper.XML基础使用及折线图语句

最近学习JAVA，但是java的lamada没有.NET那么好使（估计是我没熟练使用java原因，嘿嘿）。所以只好写原生mysql语句啦。

一、先定义好要用的方法

public interface SpotVehicleMapper extends BaseMapper<SpotVehicle> {
//这个就是待会要是用的方法，先设置好参数
    List<SpotVehicleVo> getListQuery(Integer groupId, Integer type, Integer timeType, String dataTime);
}

二、开始写Mapper.XML

<select id="getListQuery"  resultType="ym.apps.model.vo.spot.SpotVehicleVo">
        select
        <choose>
            <when test="timeType==0">
                FROM_UNIXTIME(create_time,'%Y-%m') doctime,
            </when>
            <when test="timeType==1">
                FROM_UNIXTIME(create_time,'%Y-%m-%d') doctime,
            </when>
            <when test="timeType==2">
                FROM_UNIXTIME(create_time,'%Y-%m-%d %h') doctime,
            </when>
        </choose>
        count(*) num
        from spot_vehicle a where 1=1
        <choose>
            <when test="timeType==0">
                and FROM_UNIXTIME(a.create_time,'%Y')=date_format(#{dataTime},'%Y')
            </when>
            <when test="timeType==1">
                and FROM_UNIXTIME(a.create_time,'%Y-%m')=date_format(#{dataTime},'%Y-%m')
            </when>
            <when test="timeType==2">
                and FROM_UNIXTIME(a.create_time,'%Y-%m-%d')=date_format(#{dataTime},'%Y-%m-$d')
            </when>
        </choose>
        <if test="type!=null and type!=''">
            and a.type=#{type}
        </if>
        <if test="groupId!=null and groupId!=''">
            and a.monitor_id in (select m.id from spot_monitor m where m.group_id=#{groupId})
        </if>
        group by a.monitor_id,a.type,doctime
        order by doctime desc
    </select>

看着很多其实写成sql语句很短，其中有些字段前段并不会使用，所以需要处理一下。下面是sql语句

select FROM_UNIXTIME(create_time,'%Y-%m') doctime,count(*) num from spot_vehicle a where 1=1 and FROM_UNIXTIME(a.create_time,'%Y')=date_format('日期','%Y') and a.monitor_id in (select m.id from spot_monitor m where m.group_id='主键') group by a.monitor_id,a.type,doctime order by doctime desc

好吧，还是有点长