疫情统计可视化（一）升级版

任务：

1.第一阶段目标：

导入全国疫情数据库payiqing.sql（MySQL数据库）。

可以按照时期查询各个省市的疫情统计表格。

以折线图或柱状图展示某天的全国各省的

2.第二阶段目标：疫情统计地图可视化：可以通过地图的形式来直观显示疫情的大致分布情况，还可以查看具体省份的疫情统计情况。

在全国地图上使用不同的颜色代表大概确诊人数区间，颜色的深浅表示疫情的严重程度，可以直观了解高危区域；

鼠标移到每个省份会高亮显示、点击鼠标会显示该省具体疫情情况、点击某个省份显示该省疫情的具体情况

显示该省份对应的感染患者人数、疑似患者人数、治愈人数、死亡人数；确诊人数。

3.第三阶段目标：鼠标移到每个市会高亮显示，并且显示简单的数据；

数据下钻：单击各个省可以下钻到各个地市的数据显示。

代码：

package com.me.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.me.domain.Info;
import com.me.domain.Provinces;
import com.me.utils.DBUtils;


public class InfoDao {

    /**
     * @return
     * @throws SQLException 
     */
    public List<Info> getList(String date) throws SQLException {
        QueryRunner qr = new QueryRunner(DBUtils.getDataSource());
        String sql = "select * from info1 where date = ?";
        List<Info> query = qr.query(sql, new BeanListHandler<Info>(Info.class),date);
        return query;
    }
    public List<Info> getListT(String date) throws SQLException {
        QueryRunner qr = new QueryRunner(DBUtils.getDataSource());
        String sql = "select * from info1 where date = ? ";
        List<Info> query = qr.query(sql, new BeanListHandler<Info>(Info.class),date);
        return query;
    }
    public List<Info> getListC(String date,String province) throws SQLException {
        QueryRunner qr = new QueryRunner(DBUtils.getDataSource());
        String sql = "select * from info1 where date = ? and province =?";
        List<Info> query = qr.query(sql, new BeanListHandler<Info>(Info.class),date,province);
        return query;
    }
    /**
     * @return
     * @throws SQLException 
     */
    public List<Provinces> getList4() throws SQLException {
        QueryRunner qr = new QueryRunner(DBUtils.getDataSource());
        String sql = "select * from provinces";
        List<Provinces> query = qr.query(sql, new BeanListHandler<Provinces>(Provinces.class));
        return query;
    }
    /**
     * @param province
     * @return
     * @throws SQLException 
     */
    public Provinces getProvinces(String province) throws SQLException {
        QueryRunner qr = new QueryRunner(DBUtils.getDataSource());
        String sql = "select * from provinces where  name=? ";
        Provinces user01 = qr.query(sql, new BeanHandler<Provinces>(Provinces.class), province);
        return user01;
    }
    /**
     * @param i
     * @return
     * @throws SQLException 
     */
    public List<Provinces> getListC2(String id) throws SQLException {
        QueryRunner qr = new QueryRunner(DBUtils.getDataSource());
        String sql = "select * from citys where id like ?";
        List<Provinces> query = qr.query(sql, new BeanListHandler<Provinces>(Provinces.class),id+"%");
        return query;
    }
    
}

package com.me.domain;

public class City {
    private String name; 
    private int value;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getValue() {
        return value;
    }
    public void setValue(int value) {
        this.value = value;
    }
    
}

package com.me.domain;

public class Info {
    private int id;
    private String date;
    private String province;
    private String city;
    private String confirmed_num;
    private String yisi_num;
    private String cured_num;
    private String dead_num;
    private String code;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getDate() {
        return date;
    }
    public void setDate(String date) {
        this.date = date;
    }
    public String getProvince() {
        return province;
    }
    public void setProvince(String province) {
        this.province = province;
    }
    public String getCity() {
        return city;
    }
    public void setCity(String city) {
        this.city = city;
    }
    public String getConfirmed_num() {
        return confirmed_num;
    }
    public void setConfirmed_num(String confirmed_num) {
        this.confirmed_num = confirmed_num;
    }
    public String getYisi_num() {
        return yisi_num;
    }
    public void setYisi_num(String yisi_num) {
        this.yisi_num = yisi_num;
    }
    public String getCured_num() {
        return cured_num;
    }
    public void setCured_num(String cured_num) {
        this.cured_num = cured_num;
    }
    public String getDead_num() {
        return dead_num;
    }
    public void setDead_num(String dead_num) {
        this.dead_num = dead_num;
    }
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    
}

package com.me.domain;
public class Provinces {
    private int id;
    private String name;
    private String confirm;
    private String suspect;
    private String heal;
    private String dead;
    private String servere;
    private String lastUpdateTime;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getConfirm() {
        return confirm;
    }
    public void setConfirm(String confirm) {
        this.confirm = confirm;
    }
    public String getSuspect() {
        return suspect;
    }
    public void setSuspect(String suspect) {
        this.suspect = suspect;
    }
    public String getHeal() {
        return heal;
    }
    public void setHeal(String heal) {
        this.heal = heal;
    }
    public String getDead() {
        return dead;
    }
    public void setDead(String dead) {
        this.dead = dead;
    }
    public String getServere() {
        return servere;
    }
    public void setServere(String servere) {
        this.servere = servere;
    }
    public String getLastUpdateTime() {
        return lastUpdateTime;
    }
    public void setLastUpdateTime(String lastUpdateTime) {
        this.lastUpdateTime = lastUpdateTime;
    }

}

package com.me.servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;
import com.me.dao.InfoDao;
import com.me.domain.City;
import com.me.domain.Info;
import com.me.domain.Provinces;


@WebServlet("/info")
public class InfoServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    InfoDao dao = new InfoDao();
    public InfoServlet() {
        super();
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        String method = request.getParameter("method");
        if(method.equals("bg")) {
            try {
                bg(request, response);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else if(method.equals("tu")) {
            try {
                tu(request, response);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else if(method.equals("city")) {
            try {
                city(request, response);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else if(method.equals("d")) {
            try {
                d(request, response);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else if(method.equals("yiqing")) {
            try {
                yiqing(request, response);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else if(method.equals("city2")) {
            try {
                city2(request, response);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else if(method.equals("d2")) {
            try {
                d2(request, response);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //response.getWriter().append("Served at: ").append(request.getContextPath());yiqing
    }
    
    /**
     * @param request
     * @param response
     */
    private void city2(HttpServletRequest request, HttpServletResponse response)throws SQLException, ServletException, IOException {
        // TODO Auto-generated method stub
        String province = request.getParameter("province");
        Provinces provinces = dao.getProvinces(province);
        request.setAttribute("id", provinces.getId());
        request.getRequestDispatcher("city.jsp").forward(request, response);
    }
    
    /**
     * @param request
     * @param response
     */
    private void yiqing(HttpServletRequest request, HttpServletResponse response)throws SQLException, ServletException, IOException {
        List<Provinces> list = dao.getList4();
        Gson gson = new Gson();
        String json = gson.toJson(list);
        request.getSession().setAttribute("list", list);
        response.getWriter().write(json);
    }

    /**
     * @param request
     * @param response
     */
    private void d2(HttpServletRequest request, HttpServletResponse response)throws SQLException, ServletException, IOException {
        String id = request.getParameter("id");
        String id_str = "";
        id_str +=  id.indexOf(0)+id.indexOf(1);
        List<Provinces> list = dao.getListC2(id_str);
        List<City> data = new ArrayList<City>();
        for(int i=0; i<list.size();i++) {
            City city = new City();
            city.setName(list.get(i).getName());
            city.setValue(Integer.parseInt(list.get(i).getConfirm()));
            data.add(city);
        }
        Gson gson = new Gson();
        String json = gson.toJson(data);
        System.out.println(json);
        response.getWriter().write(json);
    }
    
    /**
     * @param request
     * @param response
     */
    private void d(HttpServletRequest request, HttpServletResponse response)throws SQLException, ServletException, IOException {
        // TODO Auto-generated method stub
        String province = request.getParameter("province");
        String time = "2020-02-12 10:14:15";
        List<Info> list = dao.getListC(time,province);
        List<City> data = new ArrayList<City>();
        for(int i=1; i<list.size();i++) {
            City city = new City();
            city.setName(list.get(i).getCity());
            city.setValue(Integer.parseInt(list.get(i).getConfirmed_num()));
            data.add(city);
        }
        Gson gson = new Gson();
        String json = gson.toJson(data);
        System.out.println(json);
        response.getWriter().write(json);
    }

    /**
     * @param request
     * @param response
     */
    private void city(HttpServletRequest request, HttpServletResponse response)throws SQLException, ServletException, IOException {
        // TODO Auto-generated method stub
        String province = request.getParameter("province");
        String time = "2020-02-12 10:14:15";
        List<Info> list = dao.getListC(time,province);
        List<City> data = new ArrayList<City>();
        for(int i=1; i<list.size();i++) {
            City city = new City();
            city.setName(list.get(i).getCity());
            city.setValue(Integer.parseInt(list.get(i).getConfirmed_num()));
            data.add(city);
        }
        Gson gson = new Gson();
        String json = gson.toJson(data);
        System.out.println(json);
        request.setAttribute("list", json);
        request.setAttribute("province", province);
        request.getRequestDispatcher("city.jsp").forward(request, response);
    }

    /**
     * @param request
     * @param response
     */
    private void tu(HttpServletRequest request, HttpServletResponse response)throws SQLException, ServletException, IOException {
        String time = request.getParameter("time");
        List<Info> list = dao.getListT(time);
        Gson gson = new Gson();
        String json = gson.toJson(list);
        //System.out.println(json);
        request.getSession().setAttribute("list", list);
        response.getWriter().write(json);
    }

    /**
     * @param request
     * @param response
     * @throws SQLException 
     * @throws IOException 
     * @throws ServletException 
     */
    private void bg(HttpServletRequest request, HttpServletResponse response) throws SQLException, ServletException, IOException {
        String time = request.getParameter("time");
        List<Info> list = dao.getList(time);
        request.setAttribute("list", list);
        if(list!=null) {
            System.out.println(list.get(0).getDate());
            request.getRequestDispatcher("di.jsp").forward(request, response);
        }else {
            request.getRequestDispatcher("index.jsp").forward(request, response);
        }
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }

}

package com.me.servlet;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/province")
public class ProvinceServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    public ProvinceServlet() {
        super();
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        String method = request.getParameter("method");
        
        //response.getWriter().append("Served at: ").append(request.getContextPath());
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }

}

package com.me.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DBUtils {

    private static DataSource dataSource = new ComboPooledDataSource();

    private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

    public static DataSource getDataSource() {
        return dataSource;
    }
    
    public static Connection getConnection() throws SQLException{
        return dataSource.getConnection();
    }

    
    public static Connection getCurrentConnection() throws SQLException {

        Connection con = tl.get();
        if (con == null) {
            con = dataSource.getConnection();
            tl.set(con);
        }
        return con;
    }


    public static void startTransaction() throws SQLException {
        Connection con = getCurrentConnection();
        if (con != null) {
            con.setAutoCommit(false);
        }
    }

    // 浜嬪姟鍥炴粴
    public static void rollback() throws SQLException {
        Connection con = getCurrentConnection();
        if (con != null) {
            con.rollback();
        }
    }

    
    public static void commitAndRelease() throws SQLException {
        Connection con = getCurrentConnection();
        if (con != null) {
            con.commit(); 
            con.close();
            tl.remove();
        }
    }

    public static void closeConnection() throws SQLException {
        Connection con = getCurrentConnection();
        if (con != null) {
            con.close();
        }
    }

    public static void closeStatement(Statement st) throws SQLException {
        if (st != null) {
            st.close();
        }
    }

    public static void closeResultSet(ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
    }
}

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
    <default-config>
        <property name="user">root</property>
        <property name="password">0608</property>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///payiqing?serverTimezone=UTC</property>
    </default-config> 
</c3p0-config> 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

<!DOCTYPE html>
<html style="height: 100%">

<head>
<meta charset="utf-8">
<base>
<title>地图</title>
<script src="js/jquery-1.11.3.min.js" type="text/javascript"></script>
<script type="text/javascript" src="js/echarts.min.js"></script>
<script type="text/javascript" src="js/china.js"></script>
</head>
<body style="height: 100%; margin: 0">
    <div id="main" style="height: 100%"></div>
</body>
<script type="text/javascript">
    var dt;
    var mydata1 = new Array(0);
    $(function(){
        $.ajax({
            url : "info?method=yiqing",
            async : false,
            type : "POST",
            success : function(data) {
                dt = data;
                for (var i = 0; i < 33; i++) {
                    var d = {
                        
                    };
                    
                    d["name"] = dt[i].name;//.substring(0, 2);
                    d["value"] = dt[i].confirm;
                    d["yisi_num"] = dt[i].suspect;
                    d["cured_num"] = dt[i].heal;
                    d["dead_num"] = dt[i].dead;
                    mydata1.push(d);
                }
                
                //var mdata = JSON.stringify(mydata1);
                var optionMap = {
                    backgroundColor : '#FFFFFF',
                    title : {
                        text : '全国地图大数据',
                        subtext : '',
                        x : 'center'
                    },
                    tooltip : {
                        formatter : function(params) {
                            return params.name + '<br/>' + '确诊人数 : '
                                    + params.value + '<br/>' + '死亡人数 : '
                                    + params['data'].dead_num + '<br/>' + '治愈人数 : '
                                    + params['data'].cured_num + '<br/>'+ '疑似患者人数 : '
                                    + params['data'].yisi_num;
                        }//数据格式化
                    },

                    //左侧小导航图标
                    visualMap : {
                        min : 0,
                        max : 70000,
                        text : [ '多', '少' ],
                        realtime : false,
                        calculable : true,
                        inRange : {
                            color : [ 'lightskyblue', 'yellow', 'orangered' ]
                        }
                    },

                    //配置属性
                    series : [ {
                        type : 'map',
                        mapType : 'china',
                        label : {
                            show : true
                        },
                        data : mydata1
                    } ]
                };
                //初始化echarts实例
                var myChart = echarts.init(document.getElementById('main'));

                //使用制定的配置项和数据显示图表
                myChart.setOption(optionMap);
            },
            error : function() {
                alert("请求失败");
            },
            dataType : "json"
        });
    });
</script>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

<!DOCTYPE html>
<html style="height: 100%">

<head>
<meta charset="utf-8">
<base>
<title>地图阶段二</title>
<script src="js/jquery-1.11.3.min.js" type="text/javascript"></script>
<script type="text/javascript" src="js/echarts.min.js"></script>
<script type="text/javascript" src="js/china.js"></script>
</head>
<body style="height: 100%; margin: 0">
    <div id="main" style="height: 100%"></div>
</body>
<script type="text/javascript">
    var dt;
    var mydata1 = new Array(0);
    $(function(){
        $.ajax({
            url : "info?method=yiqing",
            async : false,
            type : "POST",
            success : function(data) {
                dt = data;
                for (var i = 0; i < 33; i++) {
                    var d = {
                        
                    };
                    
                    d["name"] = dt[i].name;//.substring(0, 2);
                    d["value"] = dt[i].confirm;
                    d["yisi_num"] = dt[i].suspect;
                    d["cured_num"] = dt[i].heal;
                    d["dead_num"] = dt[i].dead;
                    mydata1.push(d);
                }
                
                //var mdata = JSON.stringify(mydata1);
                var optionMap = {
                    backgroundColor : '#FFFFFF',
                    title : {
                        text : '全国地图大数据',
                        subtext : '',
                        x : 'center'
                    },
                    tooltip : {
                        formatter : function(params) {
                            return params.name + '<br/>' + '确诊人数 : '
                                    + params.value + '<br/>' + '死亡人数 : '
                                    + params['data'].dead_num + '<br/>' + '治愈人数 : '
                                    + params['data'].cured_num + '<br/>'+ '疑似患者人数 : '
                                    + params['data'].yisi_num;
                        }//数据格式化
                    },

                    //左侧小导航图标
                    visualMap : {
                        min : 0,
                        max : 70000,
                        text : [ '多', '少' ],
                        realtime : false,
                        calculable : true,
                        inRange : {
                            color : [ 'lightskyblue', 'yellow', 'orangered' ]
                        }
                    },

                    //配置属性
                    series : [ {
                        type : 'map',
                        mapType : 'china',
                        label : {
                            show : true
                        },
                        data : mydata1
                    } ]
                };
                //初始化echarts实例
                var myChart = echarts.init(document.getElementById('main'));
                myChart.on('click', function (params) {
                     var url = "info?method=city2&province=" + params.name;
                     window.location.href = url;
                });
                //使用制定的配置项和数据显示图表
                myChart.setOption(optionMap);
            },
            error : function() {
                alert("请求失败");
            },
            dataType : "json"
        });
    });
</script>
</html>

截图：