第三周--疫情统计
这周主要是在完成以下这个题目
1、导入数据库:在导入数据库的过程中就遇到了问题;
经过网上查询,把数据库里的“utf8mb4”全部改为“utf8”,再把utf8mb4_0900_ai_ci 替换为utf8_general_ci 。最后就可以成功导入了。
2、连接数据库
package util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import net.sf.json.JSONArray; import net.sf.json.JSONObject; public class DBUtil { private static String URL="jdbc:mysql://localhost:3306/payiqing?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC"; private static String username="root"; private static String password="123"; public static Connection getConnection() { Connection connection=null; try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { connection=DriverManager.getConnection(URL, username, password); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return connection; } public DBUtil() { // TODO Auto-generated constructor stub } public static String getData(String date) { JSONArray jsonArray=new JSONArray(); Connection connection=getConnection(); String sql="select distinct Province from info where Date like '"+date+"%'"; String tempprovince=""; ResultSet rs=null; try { Statement statement=connection.createStatement(); rs=statement.executeQuery(sql); while(rs.next()) tempprovince+=rs.getString("Province")+";"; rs.close(); String str[]=tempprovince.split(";"); for(int i=0;i<str.length;i++) { if(str[i].trim().equals("")) continue; sql="select sum(Confirmed_num),sum(Yisi_num),sum(Cured_num),sum(Dead_num) from info where Date like '"+date+"%' and Province='"+str[i]+"'"; rs=statement.executeQuery(sql); rs.next(); JSONObject json=new JSONObject(); json.put("name", str[i]); json.put("num", rs.getInt(1)); json.put("yisi", rs.getString(2)); json.put("cure", rs.getString(3)); json.put("dead", rs.getString(4)); rs.close(); sql="select * from info where Date like '"+date+"%' and Province='"+str[i]+"'"; rs=statement.executeQuery(sql); rs.next(); json.put("code", rs.getString("Code")); rs.close(); jsonArray.add(json); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return jsonArray.toString(); } }
servlet层
package 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; import MyDBUtil.*; /** * Servlet implementation class getData */ @WebServlet("/getData") public class getData extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public getData() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub response.getWriter().append("Served at: ").append(request.getContextPath()); } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub // doGet(request, response); request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=UTF-8"); response.getWriter().write(DBUtil.getData(request.getParameter("date"))); } }
实体类
package Dao; public class Data { private String pri; private String confirmnum; public Data() { // TODO Auto-generated constructor stub } public String getPri() { return pri; } public void setPri(String pri) { this.pri = pri; } public String getConfirmnum() { return confirmnum; } public void setConfirmnum(String confirmnum) { this.confirmnum = confirmnum; } }
3、页面布局。这里引用了在网上(https://www.echartsjs.com/zh/index.html)下载的柱形图的实例。
<!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"> <link rel="stylesheet" href="layui/css/layui.css"> <script src="js/echarts.js"></script> <script src="js/jquery.js"></script> <title>疫情统计图</title> <style> input[type=date]::-webkit-inner-spin-button { visibility: hidden; } </style> </head> <body> <input type="date" name="date"><button>查询</button> <div id="main" style="width: 100%;height:550px;overflow: auto;"></div> <script type="text/javascript"> // 基于准备好的dom,初始化echarts实例 var myChart = echarts.init(document.getElementById('main')); // 指定图表的配置项和数据 //myChart.showLoading(); var names=[]; var nums=[]; $("button").click(function(){ $.post( 'http://localhost:8080/yiqing2/getData', {"date":$("input[name=date]").val()}, function(msg){ var json=JSON.parse(msg); var size=json.length; for(i=0;i<size;i++){ names.push(json[i].name); nums.push(parseInt(json[i].num)); } myChart.hideLoading(); var option = { title: { text: $("input[name=date]").val()+'确诊人数' }, tooltip: {}, legend: { data:['确诊人数'] }, xAxis: { data: names }, yAxis: { type:'value' }, series: [{ name: '确诊人数', type: 'bar', data: nums }] }; myChart.setOption(option); tr="<tr><th>省份</th><th>确诊人数</th><th>疑似人数</th><th>治愈人数</th><th>死亡人数</th></tr>"; $('.head').append(tr); for(i=0;i<size;i++) $('.main').append("<tr></tr>"); $('.main tr').each(function(i){ $(this).append("<td>"+json[i].name+"</td>"); $(this).append("<td>"+json[i].num+"</td>"); $(this).append("<td>"+json[i].yisi+"</td>"); $(this).append("<td>"+json[i].cure+"</td>"); $(this).append("<td>"+json[i].dead+"</td>"); }) } ) }) </script> <table class="layui-table"> <thead class="head"> </thead> <tbody class="main"></tbody> </table> </body> </html>
最后运行结果如图: