软件工程第四周作业

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import util.DButil;

public class Function {
    
    public static  String query(String a,String b) throws SQLException{
        JSONArray jsonArray=new JSONArray();
        String sql = "select province,sum(confirmed_num) as total from todaydata  where date_format(Date,'%Y-%m-%d') between '"+a+"' and '"+b+"'"
                + "group by province order by total desc";
        Connection conn = DButil.getConn();
        conn.setAutoCommit(false);
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            JSONObject json=new JSONObject();
            String province=resultSet.getString("province");
            if(province==null||province.equals("")) {
                province="鍩庡競";
            }
            int total=resultSet.getInt("total");
            json.put("province", province);
            json.put("count", total+"");
            jsonArray.add(json);
        }
        conn.commit();
        conn.close();
        return jsonArray.toString();
    }
    
    
    
    
    
    public static String getData(String a,String b) throws SQLException{
        JSONArray jsonArray=new JSONArray();
        String sql="select * from todaydata  where date_format(Date,'%Y-%m-%d') between '"+a+"' and '"+b+"' ";
        Connection conn=DButil.getConn();
        try {
        Statement st=conn.createStatement();
        ResultSet rs=st.executeQuery(sql);
        while(rs.next()) {
            JSONObject json=new JSONObject();
            json.put("id",rs.getInt("id"));
            json.put("date", rs.getTimestamp("Date").toString());
            String province=rs.getString("province");
            if(province==null||province.equals("")) {
                province="鐪�";
            }
            json.put("province", province);
            String city=rs.getString("city");
            if(city==null||city.equals("")) {
                city="甯傚尯";
            }
            json.put("city", city);
            json.put("confirmed_num", rs.getInt("Confirmed_num"));
            json.put("Cured_num", rs.getString("Cured_num"));
            json.put("Dead_num",rs.getString("Dead_num"));
            jsonArray.add(json);
            }
        }
        catch(Exception e) {
            System.out.println(e.getMessage());
        }
        finally {
            if(conn!=null) {
               conn.close();
            }
        }
        
        return jsonArray.toString();
    }

}

package getDataTest;

import java.io.IOException;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.naming.InitialContext;
import javax.net.ssl.HttpsURLConnection;

import org.apache.commons.dbutils.QueryRunner;
import org.jsoup.Jsoup;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.mchange.v2.c3p0.DataSources;

import util.DataSourceUtils;

public class GetYiQing {
    

      public static String USER_AGENT = "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:49.0) Gecko/20100101 Firefox/49.0";

      public static String HOST = "i.snssdk.com";

      public static String REFERER = "https://i.snssdk.com/feoffline/hot_list/template/hot_list/forum_tab.html?activeWidget=1";
      
      public static void main(String[] args) throws IOException, SQLException {
        

            String url = "https://i.snssdk.com/forum/home/v1/info/?activeWidget=1&forum_id=1656784762444839";

            String resultBody;
            try {
                SSL.trustAllHttpsCertificates();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            HttpsURLConnection.setDefaultHostnameVerifier(SSL.hv);
            
            resultBody = Jsoup.connect(url).

                    userAgent(USER_AGENT).header("Host", HOST).header("Referer", REFERER).execute().body();

                JSONObject jsonObject = JSON.parseObject(resultBody);

                String ncovStringList = jsonObject.getJSONObject("forum").getJSONObject("extra").getString("ncov_string_list");

                JSONObject ncovListObj = JSON.parseObject(ncovStringList);
                JSONArray todaydata = ncovListObj.getJSONArray("provinces");
                QueryRunner queryRunner = new QueryRunner(DataSourceUtils.getDataSource());
                String sql = "insert into todaydata values(?,?,?,?,?,?,?,?)";
                String confirmedNum,deathsNum,cityname,cityid,treatingNum,provinceid;
                String reprovinceid=null;
                int confirmedNumSum=0,deathsNumSum=0,treatingNumSum=0;
                for(int i=0;i<todaydata.size();i++) {
                    JSONObject todayData1 = todaydata.getJSONObject(i);
                    String updateDate = todayData1.getString("updateDate");
                    JSONArray city = todayData1.getJSONArray("cities");
                    for(int j=0;j<city.size();j++) {
                        JSONObject cities = city.getJSONObject(j);
                        confirmedNum= cities.getString("confirmedNum");
                        deathsNum = cities.getString("deathsNum");
                        cityname = cities.getString("name");
                        cityid = cities.getString("id");
                        treatingNum = cities.getString("treatingNum");
                        provinceid = cityid.substring(0,2);
                        reprovinceid=provinceid;
                        confirmedNumSum+=Integer.parseInt(confirmedNum);
                        deathsNumSum+=Integer.parseInt(deathsNum);
                        treatingNumSum+=Integer.parseInt(treatingNum);
                        queryRunner.update(sql, updateDate,provinceid,cityname,confirmedNum,deathsNum,treatingNum,cityid,null);
                    }
                    queryRunner.update(sql,updateDate,reprovinceid,null,confirmedNumSum,deathsNumSum,treatingNumSum,null,null);    
                    confirmedNumSum=0;
                    deathsNumSum=0;
                    treatingNumSum=0;
                }
              }
        }

package getDataTest;

import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.SSLSession;

public class SSL {
    static HostnameVerifier hv = new HostnameVerifier() {
        public boolean verify(String urlHostName, SSLSession session) {
            //System.out.println("Warning: URL Host: " + urlHostName + " vs. " + session.getPeerHost());
            return true;
        }
    };

    static void trustAllHttpsCertificates() throws Exception {
        javax.net.ssl.TrustManager[] trustAllCerts = new javax.net.ssl.TrustManager[1];
        javax.net.ssl.TrustManager tm = new miTM();
        trustAllCerts[0] = tm;
        javax.net.ssl.SSLContext sc = javax.net.ssl.SSLContext.getInstance("SSL");
        sc.init(null, trustAllCerts, null);
        javax.net.ssl.HttpsURLConnection.setDefaultSSLSocketFactory(sc.getSocketFactory());
    }

    static class miTM implements javax.net.ssl.TrustManager, javax.net.ssl.X509TrustManager {
        public java.security.cert.X509Certificate[] getAcceptedIssuers() {
            return null;
        }

        public boolean isServerTrusted(java.security.cert.X509Certificate[] certs) {
            return true;
        }

        public boolean isClientTrusted(java.security.cert.X509Certificate[] certs) {
            return true;
        }

        public void checkServerTrusted(java.security.cert.X509Certificate[] certs, String authType)
                throws java.security.cert.CertificateException {
            return;
        }

        public void checkClientTrusted(java.security.cert.X509Certificate[] certs, String authType)
                throws java.security.cert.CertificateException {
            return;
        }
    }
}

package servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;

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 dao.Function;


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

   
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");      
        response.setContentType("utf-8");         
        response.setCharacterEncoding("utf-8");        
        String time1=request.getParameter("time1");
        String time2=request.getParameter("time2");
        System.out.println(time1+"--->"+time2);
        String json=null;
        try {
             json=Function.getData(time1, time2);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println(json);
        PrintWriter out=response.getWriter();
        out.println(json);
        out.close();
    }

    
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");     
        response.setContentType("utf-8");        
        response.setCharacterEncoding("utf-8");        
        String time1=request.getParameter("time1");
        String time2=request.getParameter("time2");
        System.out.println(time1+"--->"+time2);
        String json=null;
        try {
             json=Function.query(time1, time2);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println(json);
        PrintWriter out=response.getWriter();
        out.println(json);
        out.close();
    }

}

package util;

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 DataSourceUtils {

    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 {

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

    // 开启事务
    public static void startTransaction() throws SQLException {
        Connection con = getConnection();
        if (con != null) {
            con.setAutoCommit(false);
        }
    }

    // 事务回滚
    public static void rollback() throws SQLException {
        Connection con = getConnection();
        if (con != null) {
            con.rollback();
        }
    }

    // 提交并且 关闭资源及从ThreadLocall中释放
    public static void commitAndRelease() throws SQLException {
        Connection con = getConnection();
        if (con != null) {
            con.commit(); // 事务提交
            con.close();// 关闭资源
            tl.remove();// 从线程绑定中移除
        }
    }

    // 关闭资源方法
    public static void closeConnection() throws SQLException {
        Connection con = getConnection();
        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();
        }
    }

}

package util;

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

/**
 * ���ݿ����ӹ���
 * @author Hu
 *
 */
public class DButil {
    
    public static String db_url = "jdbc:mysql://localhost:3306/class?useSSL=false";
    public static String db_user = "root";
    public static String db_pass = "root";
    
    public static Connection getConn () {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");//��������
            conn = DriverManager.getConnection(db_url, db_user, db_pass);//�������ݿ�
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return conn;
    }
    
    /**
     * �ر�����
     * @param state
     * @param conn
     */
    public static void close (Statement state, Connection conn) {
        if (state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    public static void close (ResultSet rs, Statement state, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        if (state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>全国疫情统计</title>
<style type="text/css">
#btn {
    /* 按钮美化 */
    margin-top: 5px;
    width: 75px; /* 宽度 */
    height: 23px; /* 高度 */
    border-width: 0px; /* 边框宽度 */
    border-radius: 3px; /* 边框半径 */
    background: #1E90FF; /* 背景颜色 */
    cursor: pointer; /* 鼠标移入按钮范围时出现手势 */
    outline: none; /* 不显示轮廓线 */
    font-family: Microsoft YaHei; /* 设置字体 */
    color: white; /* 字体颜色 */
    font-size: 14px; /* 字体大小 */
}

#btn:hover {
    background: orange;
}
table td,th{
         padding: 20px;
        text-align: center;
        border:1px solid #70aefb ;
        vertical-align:middle;
    }
</style>
<script src="echarts.js"></script>
<script src="jquery-3.4.1.js"></script>

<script>
    $(function(){
            
        $("#btn").click(function(){
            
            $.post("GetdataServlet",{time1:$("#time1").val(),time2:$("#time2").val()},function(result){
                    if(result!=null){
                        var array=JSON.parse(result);
                        console.log(result);
                        console.log(array);
                        var province=[];
                        var count=[];
                        for(var i=0;i<array.length;i++){
                            province.push(array[i].province);
                            count.push(array[i].count);
                        }
                        var myChart = echarts.init(document.getElementById('main'));
                        myChart.hideLoading();
                            myChart.setOption({
                             title: {
                                  text: '疫情统计'
                              },
                                legend: {
                                        data:['确诊人数']
                                   },
                            xAxis: {
                                data: province,
                                axisLabel: {interval:0,rotate:40 }
                            },
                            
                            yAxis: {
                                
                            },
                            series: [{
                                // 根据名字对应到相应的系列
                                  name: '确诊人数',
                                data:count

                            }]
                            
                        });
                            
                            $.get("GetdataServlet?time1="+$("#time1").val()+"&time2="+$("#time2").val(),function(result){
                                if(result!=null){
                                    
                                    var json=JSON.parse(result);
                                    for(var j=0;j<json.length;j++){
                                        html+="<tr><td>"+json[j].id+"</td><td>"+json[j].date+"</td><td>"+json[j].province+"</td><td>"+json[j].city+"</td><td>"+json[j].confirmed_num+"</td><td>"+json[j].Cured_num+"</td><td>"+json[j].Dead_num+"</td></tr>"
                                    }

                                    console.log(json);
                                    $("#table").html(html+"</table>");
                                }else{
                                    
                                }
                            });
                    
                            
                            
                    }else{
                        alert("暂无该数据统计情况");
                    }
            });        
            
            
    
            
        
        });
    });
</script>
</head>
<body>
    <input type="text" id="time1" name="time1" />
    <span>--------></span>
    <input type="text" id="time2" name="time2" />
    <input type="button" value="查询" id="btn">
    <div id="main" style="width: 1200px; height: 500px;"></div>
    <div id="table"></div>
    <script>
        var myChart = echarts.init(document.getElementById('main'));
        // 显示标题，图例和空的坐标轴
        myChart.setOption({
            title : {
                text : '疫情统计柱形图'
            },
            tooltip : {},
            legend : {
                data : [ '确诊人数' ]
            },
            xAxis : {
                data : []
            },
            yAxis : {},
            series : [ {
                name : '确诊人数',
                type : 'bar',
                data : []
            } ]
        });
    </script>
</body>
</html>