第一阶段目标：
1.导入全国疫情数据库payiqing.sql（MySQL数据库）。
2.可以按照时期查询各个省市的疫情统计表格。
3.以折线图或柱状图展示某天的全国各省的确诊人数。
运行结果：
本次实验主要通过在https://www.echartsjs.com/zh/index.html网址中的各种图标代码的学习，在引用从该网址上的下载好的echarts.min.js，在调用后使用，其余的与以前所学相同。
Bean层代码：
package com.bean; public class Data { 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; } public Data(int id,String Date,String Province,String City,String Confirmed_num,String Yisi_num,String Cured_num,String Dead_num,String Code) { this.id=id; this.Date = Date; this.Province = Province; this.City = City; this.Confirmed_num = Confirmed_num; this.Yisi_num = Yisi_num; this.Cured_num = Cured_num; this.Dead_num = Dead_num; this.Code = Code; } public Data(int id,String Province,String City,String Confirmed_num,String Yisi_num,String Cured_num,String Dead_num,String Code) { this.id=id; this.Province = Province; this.City = City; this.Confirmed_num = Confirmed_num; this.Yisi_num = Yisi_num; this.Cured_num = Cured_num; this.Dead_num = Dead_num; this.Code = Code; } public Data(int id,String Date,String Province,String Confirmed_num,String Cured_num,String Dead_num,String Code) { this.id=id; this.Date = Date; this.Province = Province; this.Confirmed_num = Confirmed_num; this.Cured_num = Cured_num; this.Dead_num = Dead_num; this.Code = Code; } public Data(String Date,String Province,String Confirmed_num,String Cured_num,String Dead_num,String Code) { this.Date = Date; this.Province = Province; this.Confirmed_num = Confirmed_num; this.Cured_num = Cured_num; this.Dead_num = Dead_num; this.Code = Code; } public Data(int id ,String Date,String Province,String Code) { this.id=id; this.Date = Date; this.Province = Province; this.Code = Code; } }
Dao层代码：
package com.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import com.bean.*; import com.jdbc.Util; public class Dao { public List<Data> dlist(String sDate,String eDate) { // 查询所有信息 String begin=""; String []s=sDate.split("-"); for(int i=0;i<s.length;i++) { begin+=s[i].toString(); } String end=""; String []e=eDate.split("-"); for(int i=0;i<e.length;i++) { end+=e[i].toString(); } int bg=Integer.valueOf(begin); int ed=Integer.valueOf(end); List<Data> list = new ArrayList<Data>(); // 创建集合 Connection conn = Util.getConn(); String sql = "select * from info "; // SQL查询语句 try { PreparedStatement pst = conn.prepareStatement(sql); ResultSet rs = pst.executeQuery(); Data data = null; while (rs.next()) { int id= rs.getInt("id");; String Province = rs.getString("Province"); String Confirmed_num = rs.getString("Confirmed_num"); String Yisi_num = rs.getString("Yisi_num"); String Cured_num = rs.getString("Cured_num"); String Dead_num = rs.getString("Dead_num"); String Code = rs.getString("Code"); String City = rs.getString("City"); String Date=rs.getString("Date"); String num=""; String date=Date.substring(0, 10); String []d=date.split("-"); for(int i=0;i<d.length;i++) { num+=d[i].toString(); } int k=Integer.valueOf(num); if(k>=bg&&k<=ed) { data = new Data(id,Province,City,Confirmed_num,Yisi_num,Cured_num,Dead_num,Code); } list.add(data); } rs.close(); // 关闭 pst.close(); // 关闭 } catch (SQLException e1) { e1.printStackTrace(); // 抛出异常 } return list; // 返回一个集合 } public List<Data> pres() { // 查询所有信息 List<Data> list = new ArrayList<Data>(); Connection conn = Util.getConn(); String sql = "select * from info"; // SQL查询语句 try { PreparedStatement pst = conn.prepareStatement(sql); ResultSet rs = pst.executeQuery(); Data data = null; int index=0; while (rs.next()) { index++; int id= rs.getInt("id");; String Province = rs.getString("Province"); String Confirmed_num = rs.getString("Confirmed_num"); String Yisi_num = rs.getString("Yisi_num"); String Cured_num = rs.getString("Cured_num"); String Dead_num = rs.getString("Dead_num"); String Code = rs.getString("Code"); String City = rs.getString("City"); String Date=rs.getString("Date"); if(index<=32) { data = new Data(id,Date,Province,City,Confirmed_num,Yisi_num,Cured_num,Dead_num,Code); list.add(data); } } rs.close(); // 关闭 pst.close(); // 关闭 } catch (SQLException e1) { e1.printStackTrace(); // 抛出异常 } return list; // 返回一个集合 } public Data SelectAll() { // 根据ID进行查询 String sql = "select * from info "; Connection conn = Util.getConn(); Statement state = null; ResultSet rs = null; Data data = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { int id= rs.getInt("id");; String Province = rs.getString("Province"); String Confirmed_num = rs.getString("Confirmed_num"); String Yisi_num = rs.getString("Yisi_num"); String Cured_num = rs.getString("Cured_num"); String Dead_num = rs.getString("Dead_num"); String Code = rs.getString("Code"); String City = rs.getString("City"); String Date=rs.getString("Date"); data = new Data(id,Date,Province,City,Confirmed_num,Yisi_num,Cured_num,Dead_num,Code); } } catch (Exception e) { e.printStackTrace(); } finally { Util.close(rs, state, conn); } return data; } }
Jdbc层代码：
package com.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * 数据库连接工具 * @author YP */ public class Util { public static String db_url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC"; 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(); } } } }
Servlet层代码：
package com.servlet; import java.io.IOException; 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 javax.servlet.http.HttpSession; import com.bean.Data; import com.dao.Dao; @WebServlet("/AllDataServlet") public class AllDataServlet extends HttpServlet { // 显示全部数据 private static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { this.doPost(req, resp); } protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); Dao dao = new Dao(); List<Data> pres=null; pres=dao.pres(); req.setAttribute("pres",pres); req.getRequestDispatcher("ShowData.jsp").forward(req, resp); } }
package com.servlet; import java.io.IOException; 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 javax.servlet.http.HttpSession; import com.dao.Dao; import com.bean.*; @WebServlet("/ShowDataServlet") public class ShowDataServlet extends HttpServlet { // 显示全部数据 private static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { this.doPost(req, resp); } protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String sDate=req.getParameter("stime"); String eDate=req.getParameter("etime"); Dao dao = new Dao(); List<Data> list=null; list=dao.dlist(sDate, eDate); req.setAttribute("list",list); req.getRequestDispatcher("AllDataServlet").forward(req, resp); } }
HTML界面代码：
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <%@page import="com.bean.Data" %> <!DOCTYPE html> <html> <head> <meta charset="ISO-8859-1"> <title>Insert title here</title> </head> <meta charset="UTF-8"> <script src="echarts.min.js"></script> <title>显示</title> </head> <body > <br> <h1>疫情统计表</h1> <div id="main" style="height:400px"></div> <div id="main2" style="height:600px;"></div> <div> 开始日期：<input type="date" id="btime" name="btime">(日期格式xxxx-xx-xx)<br /> 截止日期：<input type="date" id="etime" name="etime"> <button type="button" class="btn btn-info" onclick="checkfind()">查询</button> <br><br> <table > <tr> <td>编号</td> <td>省份</td> <td>地区</td> <td>确诊人数</td> <td>疑似人数</td> <td>治愈人数</td> <td>死亡人数</td> <td>总人数</td> </tr> <c:forEach items="${list}" var="item"> <tr> <td>${item.id }</td> <td>${item.province }</td> <td>${item.city }</td> <td>${item.confirmed_num }</td> <td>${item.yisi_num }</td> <td>${item.cured_num}</td> <td>${item.dead_num }</td> <td>${item.code }</td> </tr> </c:forEach> </table> </div> <script type="text/javascript"> // 基于准备好的dom，初始化echarts实例 var myChart = echarts.init(document.getElementById('main')); var arr = new Array(); var index = 0; <c:forEach items="${pres}" var="goods"> arr[index++] = ${goods.confirmed_num}; </c:forEach> // 指定图表的配置项和数据 var option = { title: { text: '全国疫情统计(柱状图)' }, tooltip: { show: true }, legend: { data:['患者数'] }, xAxis : [ { type : 'category', axisLabel:{ interval:0, rotate:40, }, data : [ <c:forEach items="${pres}" var="g"> ["${g.province}"], </c:forEach> ] } ], yAxis : [ { type : 'value' } ], series : [ { name:'患者数', type:'bar', data: arr } ] }; // 使用刚指定的配置项和数据显示图表 myChart.setOption(option); var myChart2=echarts.init(document.getElementById("main2")); var option={ title:{ text:'全国疫情统计(折线图)' }, toolbox:{ show:true, feature:{ saveAsImage:{ show:true } } }, legend:{ data:['患者数'] }, xAxis : [ { type : 'category', axisLabel:{ interval:0, rotate:40, }, data : [ <c:forEach items="${pres}" var="g"> ["${g.province}"], </c:forEach> ] } ], yAxis : [ { type : 'value' } ], series:[{ name:'患者数', type:'line', data:arr, itemStyle:{ normal:{ label : { show: true }, borderColor:'blue', lineStyle:{ 5, type:'solid' } } } }] }; //使用前面指定的配置项和数据项显示图表 myChart2.setOption(option); </script> <script type="text/javascript"> function checkfind() { var stime=document.getElementById("btime").value; var etime=document.getElementById("etime").value; if(btime==""&&etime=="") { alert("请输入时间！"); return ; } else { window.location.href = "ShowDataServlet?name="+name+"&stime="+stime+"&etime="+etime; } } </script> </body> </html>