修改预测参数，提交到数据库

jinpengyong

2020-08-05 91c1c308abec43a67335cdebb574940ed41b09cf

修改预测参数，提交到数据库

 src/main/java/com/moral/controller/ScreenController.java

@@ -1990,8 +1990,9 @@
        return new ResultBean<List<Map<String, Object>>>(weatherList);
    }

    @RequestMapping("updateForecastWeather")
    public void updateForecastWeather(@RequestBody List<Map<String,Object>> list) {
        weatherService.updateForecastWeather(list);
    @PostMapping("updateForecastWeather")
    public void updateForecastWeather(HttpServletRequest request){
        Map<String, Object> parameters = getParametersStartingWith(request, null);
        weatherService.updateForecastWeather(parameters);
    }
}

 src/main/java/com/moral/mapper/DeviceMapper.java

@@ -54,4 +54,6 @@
    List<Integer> getHasWindDirAndWindSpeedDeviceVersion();



    List<Device> selectDevicesAll(Map<String, Object> params);



    List<Device> getDeviceListByMonitorPointIds(List<Integer> id);

}

 src/main/java/com/moral/mapper/ForecastWeatherMapper.java

@@ -10,4 +10,6 @@
    List<Map<String, Object>> getForecast(Map<String, Object> parameters);

    void updateForecastWeather(Map<String, Object> parameters);

    Map<String, Object> getPrecip6Hour(Map<String, Object> parameters);
}

 src/main/java/com/moral/mapper/HistoryHourlyMapper.java

@@ -2,6 +2,7 @@

import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface HistoryHourlyMapper {
@@ -17,4 +18,7 @@
    String getPressureByMac(@Param("mac") String mac,@Param("time") String time);
    //获取风向
    Map<String,Object> getDataByMac(@Param("mac") String mac,@Param("time") String time);

    List<Map<String,Object>> getBeamByMacs(Map<String,Object> params);

}

 src/main/java/com/moral/mapper/MonitorPointMapper.java

@@ -31,4 +31,5 @@


    MonitorPoint getFirstMonitorPointByProvinceCode(@Param("provinceCode") int provinceCode);



    List<MonitorPoint>  getMonitorList(Map<String, Object> params);

}

 src/main/java/com/moral/mapper/RealWeatherMapper.java

@@ -6,4 +6,6 @@
public interface RealWeatherMapper {

    List<Map<String,Object>> getRealWeather(Map<String, Object> parameters);

    Map<String, Object> getPrecip6Hour(Map<String, Object> parameters);
}

 src/main/java/com/moral/service/WeatherService.java

@@ -14,5 +14,5 @@


    List<Map<String, Object>> getWeatherData(Map<String, Object> parameters) throws ParseException;



    void updateForecastWeather(List<Map<String,Object>> list);

    void updateForecastWeather(Map<String, Object> parameters);

}


 src/main/java/com/moral/service/impl/WeatherServiceImpl.java

@@ -24,13 +24,16 @@
import com.moral.entity.Area;

import com.moral.entity.AreaNames;

import com.moral.entity.City;

import com.moral.entity.Device;

import com.moral.entity.MonitorPoint;

import com.moral.entity.Organization;

import com.moral.entity.Province;

import com.moral.mapper.AreaMapper;

import com.moral.mapper.CityMapper;

import com.moral.mapper.DeviceMapper;

import com.moral.mapper.ForecastWeatherMapper;

import com.moral.mapper.HangzhouAqiMapper;

import com.moral.mapper.HistoryHourlyMapper;

import com.moral.mapper.MonitorPointMapper;

import com.moral.mapper.ProvinceMapper;

import com.moral.mapper.RealWeatherMapper;

@@ -63,6 +66,12 @@


    @Resource

    private RealWeatherMapper realWeatherMapper;



    @Resource

    private DeviceMapper deviceMapper;



    @Resource

    private HistoryHourlyMapper historyHourlyMapper;



    @Override

    public Map<String, Object> getWeatherDataByRegion(Map<String, Object> parameters) {

@@ -179,7 +188,6 @@
        parameters.put("start", sdf.parse(time));

        parameters.put("end", sdf.parse(nextDay));

        List<Map<String, Object>> forecastList = forecastWeatherMapper.getForecastHour(parameters);



        MonitorPoint monitorPoint = monitorPointMapper.selectByPrimaryKey(Integer.valueOf(monitorPointId));

        parameters.put("areaCode", monitorPoint.getAreaCode());

        String sensors = "O3C";

@@ -192,7 +200,6 @@
            parameters.put("areaCode", monitorPoint.getCityCode());

            aqiList = hangzhouAqiMapper.getAqiDataByAreaCode(parameters);

        }



        for (Map<String, Object> map : aqiList) {

            String O3 = map.get("O3").toString().replace("\"", "");

            map.put("O3", O3);

@@ -225,7 +232,11 @@
                    hour = "24";

                }

                map.put("time", Integer.valueOf(hour));

                values.add(forecastMap.get("result").toString().replace("\"", ""));

                if (forecastMap.get("result") != null) {

                    values.add(forecastMap.get("result").toString().replace("\"", ""));

                } else {

                    values.add("");

                }

                values.add("");

                map.put("values", values);

                resultList.add(map);

@@ -239,7 +250,11 @@
                    hour = "24";

                }

                map.put("time", Integer.valueOf(hour));

                values.add(forecastMap.get("result").toString().replace("\"", ""));

                if (forecastMap.get("result") != null) {

                    values.add(forecastMap.get("result").toString().replace("\"", ""));

                } else {

                    values.add("");

                }

                for (Map<String, Object> aqiMap : aqiList) {

                    if (forecastMap.get("time").equals(aqiMap.get("time"))) {

                        values.add(aqiMap.get(sensor1).toString());

@@ -292,6 +307,14 @@
        parameters.put("monitorPointId", monitorPoint.getId());

        String time = parameters.get("time").toString();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        Date now = new Date();

        Date selectTime = sdf1.parse(time + " 00:00:00");

        Calendar c1 = Calendar.getInstance();

        c1.setTime(selectTime);

        c1.add(Calendar.DAY_OF_MONTH, -1);

        Date beforeDay = c1.getTime();

        String format = sdf.format(beforeDay);

        Calendar c = Calendar.getInstance();

        c.setTime(sdf.parse(time));

        c.add(Calendar.HOUR_OF_DAY, 1);

@@ -301,17 +324,55 @@
        parameters.put("start", start);

        parameters.put("end", end);

        List<Map<String, Object>> forecastList = forecastWeatherMapper.getForecast(parameters);



        List<Map<String, Object>> foreList = new ArrayList<>();



        for (Map<String, Object> map : forecastList) {

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("time", map.get("time").toString());

            hashMap.put("type", "预测");

            hashMap.put("TVOC", "");

            hashMap.put("altitude", "");

            hashMap.put("vegetation", "");

            hashMap.put("AQI", "");

            hashMap.put("SO2C", "");

            hashMap.put("NO2C", "");

            hashMap.put("COC", "");

            hashMap.put("O3C", "");

            hashMap.put("PM25C", "");

            hashMap.put("PM10C", "");

            hashMap.put("city", parameters.get("city").toString());



            if (selectTime.getTime() <= now.getTime()) {

                hashMap.put("fxTime", (format + " 15:00").substring(5, 16));

            } else {

                String s = sdf1.format(now).split(" ")[1].split(":")[0];

                Integer hour = Integer.valueOf(s);

                if (hour > 7) {

                    hashMap.put("fxTime", (format + " 07:00").substring(5, 16));

                } else if (hour > 3 && hour < 7) {

                    hashMap.put("fxTime", (format + " 15:00").substring(5, 16));

                }

            }

            Map<String, Object> jsonMap = (Map<String, Object>) JSONObject.parse(map.get("json").toString());

            hashMap.putAll(jsonMap);

            foreList.add(hashMap);

        }



        for (Map<String, Object> foreMap : foreList) {

            String ftime = foreMap.get("time").toString();

            Calendar calendar = Calendar.getInstance();

            Date endTime = sdf1.parse(ftime);

            calendar.setTime(endTime);

            calendar.add(Calendar.HOUR, -6);

            Date startTime = calendar.getTime();

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("areaCode", Integer.valueOf(parameters.get("areaCode").toString()));

            hashMap.put("start", startTime);

            hashMap.put("end", endTime);

            hashMap.put("monitorPointId", Integer.valueOf(parameters.get("monitorPointId").toString()));

            Map<String, Object> precip6HourMap = forecastWeatherMapper.getPrecip6Hour(hashMap);

            if (precip6HourMap != null) {

                foreMap.put("precip6", precip6HourMap.get("precip6").toString());

            }

        }



        List<Map<String, Object>> aqiList = hangzhouAqiMapper.getAqi(parameters);

@@ -330,8 +391,6 @@
            realAqilist.add(hashMap);

        }



        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");



        for (Map<String, Object> map : realAqilist) {

            Date endTime = sdf1.parse(map.get("time").toString());

            c.setTime(endTime);

@@ -342,10 +401,40 @@
            hashMap.put("start", startTime);

            hashMap.put("end", endTime);

            Map<String, Object> O3Map = hangzhouAqiMapper.getAvgO3EightHours(hashMap);

            map.put("O3指数8小时", O3Map.get("O3指数8小时").toString());

            if (O3Map.get("O3浓度8小时") != null) {

                map.put("O3浓度8小时", O3Map.get("O3浓度8小时").toString());

            map.put("O3_8H", O3Map.get("O3_8H").toString());

            if (O3Map.get("O3C_8H") != null) {

                map.put("O3C_8H", O3Map.get("O3C_8H").toString());

            }

        }



        //光照

        String code = parameters.get("areaCode").toString();

        Map<String, Object> params = new HashMap<>();

        if (code.endsWith("00")) {

            params.put("cityCode", Integer.valueOf(code));

        } else {

            params.put("areaCode", Integer.valueOf(code));

        }



        List<MonitorPoint> monitorList = monitorPointMapper.getMonitorList(params);



        List<Integer> pointList = new ArrayList<>();

        for (MonitorPoint point : monitorList) {

            Integer pointId = point.getId();

            pointList.add(pointId);

        }



        List<Device> deviceList = deviceMapper.getDeviceListByMonitorPointIds(pointList);

        List<String> macList = new ArrayList<>();

        List<Map<String, Object>> beamList = new ArrayList<>();

        if (deviceList.size() != 0) {

            for (Device device : deviceList) {

                String mac = device.getMac();

                macList.add(mac);

            }



            parameters.put("macs", macList);

            beamList = historyHourlyMapper.getBeamByMacs(parameters);

        }



        List<Map<String, Object>> realList = realWeatherMapper.getRealWeather(parameters);

@@ -357,6 +446,28 @@
            hashMap.putAll(jsonMap);

            hashMap.put("city", parameters.get("city").toString());

            realWeatherList.add(hashMap);

        }



        for (Map<String, Object> realMap : realWeatherList) {

            String rtime = realMap.get("time").toString();

            for (Map<String, Object> beamMap : beamList) {

                String btime = realMap.get("time").toString();

                if (btime.equals(rtime)) {

                    realMap.put("beam", beamMap.get("beam").toString());

                }

            }

            Calendar calendar = Calendar.getInstance();

            Date endTime = sdf1.parse(rtime);

            calendar.setTime(endTime);

            calendar.add(Calendar.HOUR, -6);

            Date startTime = calendar.getTime();

            Map<String, Object> hashMap = new HashMap<>();

            hashMap.put("areaCode", Integer.valueOf(parameters.get("areaCode").toString()));

            hashMap.put("start", startTime);

            hashMap.put("end", endTime);

            hashMap.put("monitorPointId", Integer.valueOf(parameters.get("monitorPointId").toString()));

            Map<String, Object> precip6HourMap = realWeatherMapper.getPrecip6Hour(hashMap);

            realMap.put("precip6", precip6HourMap.get("precip6").toString());

        }



        List<Map<String, Object>> list = new ArrayList<>();

@@ -393,13 +504,18 @@
                }

            }

        }





        list.removeAll(Collections.singleton(null));





        for (Map<String, Object> map : list) {

            map.put("type", "实测");

            map.put("city", parameters.get("city").toString());

        }



        List<Map<String, Object>> resultList = new ArrayList<>();



        for (Map<String, Object> foreMap : foreList) {

            for (Map<String, Object> realMap : list) {

                if (foreMap.get("time").equals(realMap.get("time"))) {

@@ -418,39 +534,65 @@
            hashMap.put("city", parameters.get("city").toString());

            resultList.add(hashMap);

        }



        for (Map<String, Object> map : resultList) {

            String date = map.get("time").toString().substring(0, 16);

            map.put("time", date);

        }

        return resultList;

    }



    @Override

    public void updateForecastWeather(List<Map<String, Object>> list) {

        for (Map<String, Object> map : list) {

            String time = map.get("time").toString();

            Map<String, Object> parameters = new HashMap<>();

            parameters.put("time", time);

            Integer monitorPointId;

            String city = map.get("city").toString();

            Area area = areaMapper.getAreaByName(city);

            if (area == null) {

                Integer cityCode = cityMapper.getCityByName(city).getCityCode();

                monitorPointId = monitorPointMapper.getFirstMonitorPointByCityCode(cityCode).getId();

            } else {

                Integer areaCode = area.getAreaCode();

                monitorPointId = monitorPointMapper.getFirstMonitorPointByAreaCode(areaCode).getId();

            }

            parameters.put("monitorPointId", monitorPointId);



            Map<String, Object> jsonMap = new HashMap<>();

            for (String key : map.keySet()) {

                if (!key.equals("city") && !key.equals("time") && !key.equals("type")) {

                    String value = map.get(key).toString();

                    jsonMap.put(key, value);

    public void updateForecastWeather(Map<String, Object> parameters) {

        List<Integer> list = new ArrayList<>();

        List<Map<String, Object>> resultList = new ArrayList<>();

        for (int i = 0; i < 48; i++) {

            list.add(i);

            resultList.add(null);

        }

        for (Integer s : list) {

            Map<String, Object> map = new HashMap<>();

            for (String key : parameters.keySet()) {

                String replace = key.split("\\[")[0];

                if (replace.equals(s + "")) {

                    String ss = key.split("\\[")[1].replace("]", "");

                    map.put(ss, parameters.get(key).toString());

                }

            }

            resultList.set(s, map);

        }

        for (Map<String, Object> map : resultList) {

            if (map.get("type").equals("预测")) {

                String time = map.get("time").toString() + ":00";

                Map<String, Object> hashMap = new HashMap<>();

                hashMap.put("time", time);

                String city = map.get("city").toString();

                Area area = areaMapper.getAreaByName(city);

                Map<String, Object> idMap = new HashMap<>();

                if (area == null) {

                    Integer cityCode = cityMapper.getCityByName(city).getCityCode();

                    idMap.put("cityCode", cityCode);

                } else {

                    Integer areaCode = area.getAreaCode();

                    idMap.put("areaCode", areaCode);

                }



            String json = JSONObject.toJSONString(jsonMap);

            parameters.put("json", json);

                List<MonitorPoint> monitorList = monitorPointMapper.getMonitorList(idMap);

                Map<String, Object> jsonMap = new HashMap<>();

                for (String key : map.keySet()) {

                    if (!key.equals("city") && !key.equals("time") && !key.equals("type") && !key.equals("fxTime")) {

                        String value = map.get(key).toString();

                        jsonMap.put(key, value);

                    }

                }



            forecastWeatherMapper.updateForecastWeather(parameters);

                String json = JSONObject.toJSONString(jsonMap);

                hashMap.put("json", json);

                for (MonitorPoint monitorPoint : monitorList) {

                    hashMap.put("monitorPointId", monitorPoint.getId());

                    forecastWeatherMapper.updateForecastWeather(hashMap);

                }

            }

        }

    }

}


 src/main/resources/mapper/DeviceMapper.xml

@@ -481,5 +481,8 @@
        </where>
    </select>


    <select id="getDeviceListByMonitorPointIds" resultType="com.moral.entity.Device">
        select * from device where monitor_point_id in
        <foreach  item="item" collection="list" index="index"  open="(" separator="," close=")">#{item}</foreach>
    </select>
</mapper>

 src/main/resources/mapper/ForecastWeatherMapper.xml

@@ -7,7 +7,7 @@
    <select id="getForecastHour" resultType="java.util.Map">
        SELECT
        DATE_FORMAT(time, #{typeFormat}) time,
        (case when result is null then json->'$.tem' else result end) result
        (case when result is null then json->'$.temp' else result end) result
        from forecast_weather
        where time >= #{start}
        AND time <![CDATA[<]]> #{end}
@@ -30,4 +30,16 @@
        where monitor_point_id=#{monitorPointId}
        and time=#{time}
    </update>


    <select id="getPrecip6Hour" resultType="java.util.Map">
    select
    round(sum(json->'$.precip'),2) 'precip6'
    FROM
    forecast_weather
    WHERE
    time >= #{start}
    AND time <![CDATA[<]]> #{end}
    AND monitor_point_id = #{monitorPointId}
    </select>
</mapper>

 src/main/resources/mapper/HangzhouAqiMapper.xml

@@ -98,8 +98,8 @@

    <select id="getAvgO3EightHours" resultType="java.util.Map">
    select
    AVG(aqi_json->'$.O3') 'O3指数8小时',
    AVG(aqi_json->'$.O3C') 'O3浓度8小时'
    AVG(aqi_json->'$.O3') 'O3_8H',
    AVG(aqi_json->'$.O3C') 'O3C_8H'
    FROM
    hangzhou_aqi
    WHERE

 src/main/resources/mapper/HistoryHourlyMapper.xml

@@ -56,10 +56,25 @@
    <select id="getTVOCByMac" resultType="java.lang.String">
        select json->'$.${sensor}[0]' as e17 from history_hourly where mac=#{mac} and time=#{time}
    </select>

    <select id="getPressureByMac" resultType="java.lang.String">
        select json->'$.e28[0]' as e28 from history_hourly where mac=#{mac} and time=#{time}
    </select>

    <select id="getDataByMac" resultType="java.util.Map">
                select json  from history_hourly where mac=#{mac} and time=#{time}
        select json  from history_hourly where mac=#{mac} and time=#{time}
    </select>

    <select id="getBeamByMacs" resultType="java.util.Map">
        SELECT
        DATE_FORMAT(h.time,'%Y-%m-%d %H:%i:%s') time,
        avg(h.json->'$.e12[0]') beam
        FROM history_hourly h
        WHERE h.mac in
        <foreach  item="item" collection="macs" index="index"  open="(" separator="," close=")">#{item}</foreach>
        and h.time >= #{start}
        AND h.time <![CDATA[<]]> #{end}
        and h.json->'$.e12[0]' is not null
        group by DATE_FORMAT(h.time,'%Y-%m-%d %H:%i:%s')
    </select>
</mapper>

 src/main/resources/mapper/MonitorPointMapper.xml

@@ -1,186 +1,200 @@
<?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.moral.mapper.MonitorPointMapper">
  <resultMap id="BaseResultMap" type="com.moral.entity.MonitorPoint">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="longitude" jdbcType="REAL" property="longitude" />
    <result column="latitude" jdbcType="REAL" property="latitude" />
    <result column="province_code" jdbcType="INTEGER" property="provinceCode" />
    <result column="city_code" jdbcType="INTEGER" property="cityCode" />
    <result column="area_code" jdbcType="INTEGER" property="areaCode" />
    <result column="organization_id" jdbcType="INTEGER" property="organizationId" />
    <result column="address" jdbcType="VARCHAR" property="address" />
    <result column="is_delete" jdbcType="CHAR" property="isDelete" />
    <result column="description" jdbcType="VARCHAR" property="description" />
      <result column="state" jdbcType="INTEGER" property="state" />
    <!-- 与省市县一对一的关系 -->
    <association property="areaNames" javaType="com.moral.entity.AreaNames">
      <result column="province_name" property="provinceName" jdbcType="VARCHAR" />
      <result column="city_name" property="cityName" jdbcType="VARCHAR" />
      <result column="area_name" property="areaName" jdbcType="VARCHAR" />
      <result column="town_name" property="townName" jdbcType="VARCHAR" />
      <result column="village_name" property="villageName" jdbcType="VARCHAR" />
    </association>
      <association property="organization" javaType="com.moral.entity.Organization">
          <result column="organization_id" property="id" jdbcType="INTEGER" />
          <result column="organization_name" property="name" jdbcType="VARCHAR" />
      </association>
  </resultMap>
    <sql id="Example_Where_Clause">
        <where>
            <foreach collection="oredCriteria" item="criteria" separator="or">
                <if test="criteria.valid">
                    <trim prefix="(" prefixOverrides="and" suffix=")">
                        <foreach collection="criteria.criteria" item="criterion">
                            <choose>
                                <when test="criterion.noValue">
                                    and ${criterion.condition}
                                </when>
                                <when test="criterion.singleValue">
                                    and ${criterion.condition} #{criterion.value}
                                </when>
                                <when test="criterion.betweenValue">
                                    and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                                </when>
                                <when test="criterion.listValue">
                                    and ${criterion.condition}
                                    <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                                        #{listItem}
                                    </foreach>
                                </when>
                            </choose>
                        </foreach>
                    </trim>
                </if>
            </foreach>
        </where>
    </sql>
    <sql id="Base_Column_List">
    <resultMap id="BaseResultMap" type="com.moral.entity.MonitorPoint">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="longitude" jdbcType="REAL" property="longitude"/>
        <result column="latitude" jdbcType="REAL" property="latitude"/>
        <result column="province_code" jdbcType="INTEGER" property="provinceCode"/>
        <result column="city_code" jdbcType="INTEGER" property="cityCode"/>
        <result column="area_code" jdbcType="INTEGER" property="areaCode"/>
        <result column="organization_id" jdbcType="INTEGER" property="organizationId"/>
        <result column="address" jdbcType="VARCHAR" property="address"/>
        <result column="is_delete" jdbcType="CHAR" property="isDelete"/>
        <result column="description" jdbcType="VARCHAR" property="description"/>
        <result column="state" jdbcType="INTEGER" property="state"/>
        <!-- 与省市县一对一的关系 -->
        <association property="areaNames" javaType="com.moral.entity.AreaNames">
            <result column="province_name" property="provinceName" jdbcType="VARCHAR"/>
            <result column="city_name" property="cityName" jdbcType="VARCHAR"/>
            <result column="area_name" property="areaName" jdbcType="VARCHAR"/>
            <result column="town_name" property="townName" jdbcType="VARCHAR"/>
            <result column="village_name" property="villageName" jdbcType="VARCHAR"/>
        </association>
        <association property="organization" javaType="com.moral.entity.Organization">
            <result column="organization_id" property="id" jdbcType="INTEGER"/>
            <result column="organization_name" property="name" jdbcType="VARCHAR"/>
        </association>
    </resultMap>
    <sql id="Example_Where_Clause">
        <where>
            <foreach collection="oredCriteria" item="criteria" separator="or">
                <if test="criteria.valid">
                    <trim prefix="(" prefixOverrides="and" suffix=")">
                        <foreach collection="criteria.criteria" item="criterion">
                            <choose>
                                <when test="criterion.noValue">
                                    and ${criterion.condition}
                                </when>
                                <when test="criterion.singleValue">
                                    and ${criterion.condition} #{criterion.value}
                                </when>
                                <when test="criterion.betweenValue">
                                    and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                                </when>
                                <when test="criterion.listValue">
                                    and ${criterion.condition}
                                    <foreach close=")" collection="criterion.value" item="listItem" open="("
                                             separator=",">
                                        #{listItem}
                                    </foreach>
                                </when>
                            </choose>
                        </foreach>
                    </trim>
                </if>
            </foreach>
        </where>
    </sql>
    <sql id="Base_Column_List">
        id, name, longitude, latitude, province_code, city_code, area_code, organization_id,
        address, is_delete, description
    </sql>
    <sql id="Relation_Province_City_Area_List">
    <sql id="Relation_Province_City_Area_List">
        are.area_name, cti.city_name, pro.province_name, t.town_name, v.village_name
    </sql>
    <select id="selectWithAreaNameByExample" parameterType="tk.mybatis.mapper.entity.Example" resultMap="BaseResultMap">
        select
        <if test="distinct">
            distinct
        </if>
        mpt.*        ,
        org.name as organization_name    ,
        <include refid="Relation_Province_City_Area_List" />
        from monitor_point mpt
        left join area are on mpt.area_code = are.area_code
        left join city cti on mpt.city_code = cti.city_code
        left join province pro on mpt.province_code = pro.province_code
        left join organization org on  mpt.organization_id = org.id
        left join town t on mpt.town_code = t.town_code
        left join village v on mpt.village_code = v.village_code
    <select id="selectWithAreaNameByExample" parameterType="tk.mybatis.mapper.entity.Example" resultMap="BaseResultMap">
        select
        <if test="distinct">
            distinct
        </if>
        mpt.* ,
        org.name as organization_name ,
        <include refid="Relation_Province_City_Area_List"/>
        from monitor_point mpt
        left join area are on mpt.area_code = are.area_code
        left join city cti on mpt.city_code = cti.city_code
        left join province pro on mpt.province_code = pro.province_code
        left join organization org on mpt.organization_id = org.id
        left join town t on mpt.town_code = t.town_code
        left join village v on mpt.village_code = v.village_code
        where mpt.id in (
            select id from monitor_point
            <if test="_parameter != null">
                <include refid="Example_Where_Clause" />
            </if>
            <if test="orderByClause != null">
                order by ${orderByClause}
            </if>
        )
    </select>
    <select id="getMonitorPointsByAreaName" resultMap="BaseResultMap">
        SELECT
            mp.* 
        FROM
            monitor_point mp,
            area a
        <if test="cityName != null">
            ,city c 
        </if>
        WHERE
            mp.area_code = a.area_code 
            AND a.area_name = #{areaName}
        <if test="cityName != null">
            AND c.city_name = #{cityName}
            AND mp.city_code = c.city_code
        </if>
    </select>
    <select id="selectByMap" parameterType="java.util.Map" resultMap="BaseResultMap">
        SELECT mpt.* from monitor_point mpt
        <where>
            <if test="@com.moral.common.bean.Constants@isNotSpecialOrgId(orgId)">
                (
                1 > 1
                    <if test="orgIds!=null and orgIds.size() > 0">
                        or mpt.organization_id in
                        <foreach collection="orgIds" item="tempOrgId" open="(" close=")" separator=",">
                            #{tempOrgId,jdbcType=INTEGER}
                        </foreach>
                    </if>
                )
            </if>
            <![CDATA[
        select id from monitor_point
        <if test="_parameter != null">
            <include refid="Example_Where_Clause"/>
        </if>
        <if test="orderByClause != null">
            order by ${orderByClause}
        </if>
        )
    </select>
    <select id="getMonitorPointsByAreaName" resultMap="BaseResultMap">
        SELECT
        mp.*
        FROM
        monitor_point mp,
        area a
        <if test="cityName != null">
            ,city c
        </if>
        WHERE
        mp.area_code = a.area_code
        AND a.area_name = #{areaName}
        <if test="cityName != null">
            AND c.city_name = #{cityName}
            AND mp.city_code = c.city_code
        </if>
    </select>
    <select id="selectByMap" parameterType="java.util.Map" resultMap="BaseResultMap">
        SELECT mpt.* from monitor_point mpt
        <where>
            <if test="@com.moral.common.bean.Constants@isNotSpecialOrgId(orgId)">
                (
                1 > 1
                <if test="orgIds!=null and orgIds.size() > 0">
                    or mpt.organization_id in
                    <foreach collection="orgIds" item="tempOrgId" open="(" close=")" separator=",">
                        #{tempOrgId,jdbcType=INTEGER}
                    </foreach>
                </if>
                )
            </if>
            <![CDATA[
            AND mpt.longitude < #{mapBounds.Ge,jdbcType=NUMERIC}
            AND mpt.longitude > #{mapBounds.Le,jdbcType=NUMERIC}
            AND mpt.latitude  < #{mapBounds.Fe,jdbcType=NUMERIC}
            AND mpt.latitude  > #{mapBounds.Ke,jdbcType=NUMERIC}
              ]]>
             <if test="regionCode != null">
             AND mpt.${regionType}_code = #{regionCode}
             </if>
     		 
            <if test="isDelete != null">
            AND    mpt.is_delete = #{isDelete,jdbcType=VARCHAR}
            </if>
        </where>
    </select>
    <select id="selectOrganizationIds" parameterType="integer" resultType="integer">
            <if test="regionCode != null">
                AND mpt.${regionType}_code = #{regionCode}
            </if>

            <if test="isDelete != null">
                AND mpt.is_delete = #{isDelete,jdbcType=VARCHAR}
            </if>
        </where>
    </select>
    <select id="selectOrganizationIds" parameterType="integer" resultType="integer">
                call proc_organizationIds_GetByMonitorPointId(#{id,jdbcType=NUMERIC})
    </select>
    <select id="selectVersionsById" parameterType="integer" resultType="integer">
    <select id="selectVersionsById" parameterType="integer" resultType="integer">
            select version from device_version dve
            right join device dev  on dve.id = dev.device_version_id
            right join monitor_point mpt on mpt.id = dev.monitor_point_id
            where mpt.id = #{id}  ORDER BY version DESC
    </select>
    <select id="selectAllById" resultType="java.util.Map">
    <select id="selectAllById" resultType="java.util.Map">
            SELECT *
            FROM monitor_point
            WHERE id = #{id}
    </select>
    <select id="getIdByOrganizationId" resultType="java.util.Map">
            SELECT id
            FROM monitor_point
            WHERE organization_id IN
                <foreach collection="organizationIdList" index="index" item="organizationId" open="(" separator="," close=")">  
                    #{organizationId}  
                </foreach>
    </select>
    <select id="getIdByOrganizationId" resultType="java.util.Map">
        SELECT id
        FROM monitor_point
        WHERE organization_id IN
        <foreach collection="organizationIdList" index="index" item="organizationId" open="(" separator="," close=")">
            #{organizationId}
        </foreach>
    </select>

    <select id="getDeviceList" resultType="com.moral.entity.Device">
    <select id="getDeviceList" resultType="com.moral.entity.Device">
        SELECT d.* FROM `device` as d,monitor_point as mp
        where d.monitor_point_id=mp.id
        and mp.id=#{id}
    </select>
    <select id="getMonitorPointById" resultType="com.moral.entity.MonitorPoint">
    <select id="getMonitorPointById" resultType="com.moral.entity.MonitorPoint">
        select * from monitor_point where id=#{id}
    </select>
    <select id="getMonitorPointListByAccountId" resultType="com.moral.entity.MonitorPoint">
    <select id="getMonitorPointListByAccountId" resultType="com.moral.entity.MonitorPoint">
        SELECT mp.* FROM account a,`monitor_point` mp,organization o
        where a.organization_id=o.id
        and o.id=mp.organization_id
        and a.id=#{id};
    </select>

    <select id="getFirstMonitorPointByAreaCode" resultType="com.moral.entity.MonitorPoint" resultMap="BaseResultMap">
    <select id="getFirstMonitorPointByAreaCode" resultType="com.moral.entity.MonitorPoint" resultMap="BaseResultMap">
        select * from monitor_point where area_code=#{areaCode} limit 0,1
    </select>

    <select id="getFirstMonitorPointByCityCode" resultType="com.moral.entity.MonitorPoint" resultMap="BaseResultMap">
    <select id="getFirstMonitorPointByCityCode" resultType="com.moral.entity.MonitorPoint" resultMap="BaseResultMap">
        select * from monitor_point where city_code=#{cityCode} limit 0,1
    </select>

    <select id="getFirstMonitorPointByProvinceCode" resultType="com.moral.entity.MonitorPoint" resultMap="BaseResultMap">
    <select id="getFirstMonitorPointByProvinceCode" resultType="com.moral.entity.MonitorPoint"
            resultMap="BaseResultMap">
        select * from monitor_point where province_code=#{provinceCode} limit 0,1
    </select>

    <select id="getMonitorList" resultMap="BaseResultMap">
        select * from monitor_point
        where
        <if test="areaCode != null">
            area_Code = #{areaCode}
        </if>

        <if test="cityCode != null">
            city_Code = #{cityCode}
        </if>
    </select>
</mapper>

 src/main/resources/mapper/RealWeatherMapper.xml

@@ -17,4 +17,15 @@
    ORDER BY
    time
   </select>

    <select id="getPrecip6Hour" resultType="java.util.Map">
    select
    round(sum(json->'$.precip'),2) 'precip6'
    FROM
    real_weather
    WHERE
    time >= #{start}
    AND time <![CDATA[<]]> #{end}
    AND monitor_point_id = #{monitorPointId}
    </select>
</mapper>