全国疫情统计可视化地图(连接后台数据库)第一阶段
题目:
代码实现:
用到的插件有:
jquery-1.83.js
echarts.js
可从网上下载
bean层:
package bean; 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; } }
dao层:
package dao; import java.sql.Connection; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.mysql.jdbc.PreparedStatement; import com.mysql.jdbc.ResultSet; import com.mysql.jdbc.Statement; import bean.info; import util.DBUtil; public class showdao { public List<info>select(){ int i=0; Connection conn = DBUtil.getConn(); //连接数据库 List<info> list = new ArrayList<info>(); try { String sql="select * from info"; Statement pstmt = (Statement) conn.createStatement(); ResultSet rs = (ResultSet) pstmt.executeQuery(sql); while(rs.next()) { info info=new info(); info.setId(rs.getInt("Id")); info.setDate(rs.getString("Date")); info.setProvince(rs.getString("Province")); info.setCity(rs.getString("City")); info.setConfirmed_num(rs.getString("Confirmed_num")); info.setYisi_num(rs.getString("Yisi_num")); info.setCured_num(rs.getString("Cured_num")); info.setDead_num(rs.getString("Dead_num")); info.setCode(rs.getString("Code")); list.add(info); i++; if(i>31) { break; } } System.out.println("hhh1"); rs.close(); pstmt.close(); conn.close(); }catch(SQLException e) { e.printStackTrace(); } return list; } public List<info>select1(){ Connection conn = DBUtil.getConn(); //连接数据库 List<info> list1 = new ArrayList<info>(); try { String sql="select * from info"; Statement pstmt = (Statement) conn.createStatement(); ResultSet rs = (ResultSet) pstmt.executeQuery(sql); while(rs.next()) { info info=new info(); info.setId(rs.getInt("Id")); info.setDate(rs.getString("Date")); info.setProvince(rs.getString("Province")); info.setCity(rs.getString("City")); info.setConfirmed_num(rs.getString("Confirmed_num")); info.setYisi_num(rs.getString("Yisi_num")); info.setCured_num(rs.getString("Cured_num")); info.setDead_num(rs.getString("Dead_num")); info.setCode(rs.getString("Code")); list1.add(info); } System.out.println("hhh1"); rs.close(); pstmt.close(); conn.close(); }catch(SQLException e) { e.printStackTrace(); } return list1; } }
package dao; import java.sql.Connection; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.List; import com.mysql.jdbc.PreparedStatement; import com.mysql.jdbc.ResultSet; import com.mysql.jdbc.Statement; import bean.info; import util.DBUtil; public class searchdao { public List<info>selectdate(String date,String date1){ System.out.println(date+"这是date"); System.out.println(date1+"这是date1"); Connection conn = DBUtil.getConn(); //连接数据库 List<info> list = new ArrayList<info>(); try { String sql="select * from info where Date between '"+date+"' and '"+date1+"'"; Statement pstmt = (Statement) conn.createStatement(); ResultSet rs = (ResultSet) pstmt.executeQuery(sql); while(rs.next()) { info info=new info(); info.setId(rs.getInt("Id")); info.setDate(rs.getString("Date")); info.setProvince(rs.getString("Province")); info.setCity(rs.getString("City")); info.setConfirmed_num(rs.getString("Confirmed_num")); info.setYisi_num(rs.getString("Yisi_num")); info.setCured_num(rs.getString("Cured_num")); info.setDead_num(rs.getString("Dead_num")); info.setCode(rs.getString("Code")); list.add(info); } System.out.println("hhh1"); rs.close(); pstmt.close(); conn.close(); }catch(SQLException e) { e.printStackTrace(); } return list; } public List<info>selectdate1(String date,String date1){ int i=0; Connection conn = DBUtil.getConn(); //连接数据库 List<info> list = new ArrayList<info>(); try { String sql="select * from info where Date between '"+date+"' and '"+date1+"'"; Statement pstmt = (Statement) conn.createStatement(); ResultSet rs = (ResultSet) pstmt.executeQuery(sql); while(rs.next()) { info info=new info(); info.setId(rs.getInt("Id")); info.setDate(rs.getString("Date")); info.setProvince(rs.getString("Province")); info.setCity(rs.getString("City")); info.setConfirmed_num(rs.getString("Confirmed_num")); info.setYisi_num(rs.getString("Yisi_num")); info.setCured_num(rs.getString("Cured_num")); info.setDead_num(rs.getString("Dead_num")); info.setCode(rs.getString("Code")); list.add(info); i++; if(i>31) { break; } } System.out.println("hhh1"); rs.close(); pstmt.close(); conn.close(); }catch(SQLException e) { e.printStackTrace(); } return list;}}
servlet层
package 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 dao.showdao; import bean.info; /** * Servlet implementation class showservlet */ @WebServlet("/showservlet") public class showservlet extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public showservlet() { 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 request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); showdao sd = new showdao(); List<info> list = sd.select(); List<info> list1 = sd.select1(); request.setAttribute("list", list); request.setAttribute("list1", list1); System.out.println("showservlet"); request.getRequestDispatcher("ECharts.jsp").forward(request, response); } /** * @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); } }
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 java.util.List; import bean.info; import dao.searchdao; /** * Servlet implementation class searchservlet */ @WebServlet("/searchservlet") public class searchservlet extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public searchservlet() { 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 request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); request.setCharacterEncoding("UTF-8"); String date ="1=1"; date = request.getParameter("date"); String date1="1=1"; date1 = request.getParameter("date1"); searchdao sd = new searchdao(); System.out.println(date); System.out.println(date1); List<info> list1 = sd.selectdate(date,date1); List<info> list = sd.selectdate1(date,date1); request.setAttribute("list1", list1); request.setAttribute("list", list); System.out.println("hhhhh"); request.getRequestDispatcher("ECharts.jsp").forward(request, response); } /** * @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); } }
util层:
package util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * @author 信1805-2 刘子煜 20183542 * 2020年3月3日 *连接数据库操作 */ public class DBUtil { public static String db_url = "jdbc:mysql://localhost:3306/payiqing?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true"; public static String db_user = "root"; public static String db_pass = "101032"; public static Connection getConn () { Connection conn = null; try { //加载驱动 Class.forName("com.mysql.jdbc.Driver"); //获取连接 conn = DriverManager.getConnection(db_url, db_user, db_pass); System.out.println("连接成功!"); } catch (Exception e) { e.printStackTrace(); } return conn; } public static void main(String[] args) { getConn(); } /** * @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(); } } } }
jsp层:
<%@ page language="java" import="java.util.*" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta charset="utf-8"> <title>ECharts</title> <link rel="stylesheet" href="${pageContext.request.contextPath }/plug-ins/css/style.css" /> <!-- 引入 echarts.js --> <script src="${pageContext.request.contextPath}/plug-ins/js/echarts.js"></script> <script src="${pageContext.request.contextPath}/plug-ins/js/jquery.min.js"></script> <script src="${pageContext.request.contextPath}/plug-ins/js/jqery-1.83.js"></script> <script> //1.页面加载 $(function(){ /*//2.获取tbody下面的偶数行并设置背景颜色 $("tbody tr:even").css("background-color","yellow"); //3.获取tbody下面的奇数行并设置背景颜色 $("tbody tr:odd").css("background-color","green");*/ //2.获取tbody下面的偶数行并设置背景颜色 $("tbody tr:even").addClass("even"); $("tbody tr:even").removeClass("even"); //3.获取tbody下面的奇数行并设置背景颜色 $("tbody tr:odd").addClass("odd"); }); </script> </head> <body> <!-- 为ECharts准备一个具备大小(宽高)的Dom --> <div id="main" style="width: 1500px;height:400px;"></div> <script type="text/javascript"> var arry=new Array(); var a=new Array(); <c:forEach items="${list}" var="item"> arry.push("${item.confirmed_num}"); a.push("${item.province}") </c:forEach> console.log(arry); // 基于准备好的dom,初始化echarts实例 var myChart = echarts.init(document.getElementById('main')); // 指定图表的配置项和数据 var option = { title: { text: '' }, tooltip: {}, legend: { data:['确诊人数'] }, xAxis: [{ type: 'category', data:["湖北省","广东省","浙江省","河南省","安徽省","江西省","江苏省","重庆市","山东省","四川省","北京市","黑龙江省","上海市","河北省","陕西省","广西壮族自治区","云南省","海南省","山西省","辽宁省","贵州省","天津市","甘肃省","吉林省","内蒙古自治区","宁夏回族自治区","新疆维吾尔自治区","香港","青海省","台湾","澳门","西藏自治区"], axisLabel:{ interval:0,//0:全部显示,1:间隔为1显示对应类目,2:依次类推,(简单试一下就明白了,这样说是不是有点抽象) rotate:-30,//倾斜显示,-:顺时针旋转,+或不写:逆时针旋转 } }], yAxis: [{type: 'value'}], series: [{ name: '确诊人数', barWidth: '15', type: 'bar', data: arry }] }; // 使用刚指定的配置项和数据显示图表。 myChart.setOption(option); </script> <table border="1" width="1500" height="20" align="center" id="tbl" id="tbl"> <form id="registe" method="post" class="form-horizontal" action="${pageContext.request.contextPath }/searchservlet"> <tr> <div>请输入查询时间段:</div> <td><input type="text" class="form-control" name="date" placeholder="YYYY-MM-DD hh:mm:ss"></td> <td><input type="text" class="form-control" name="date1" placeholder="YYYY-MM-DD hh:mm:ss"></td> </tr> <button type="submit" class="btn btn-primary">查询</button> </form> </table> <table border="1" width="1500" height="100" align="center" id="tbl" id="tbl"> <thead> <tr> <th>编号</th> <th>日期</th> <th>省份</th> <th>城市</th> <th>确诊人数</th> <th>治愈人数</th> <th>死亡人数</th> <th>城市编码</th>> </tr> </thead> <tbody> <c:forEach var="item" items="${list1}" > <tr> <td>${item.id }</td> <td>${item.date }</td> <td>${item.province }</td> <td>${item.city }</td> <td>${item.confirmed_num }</td> <td>${item.cured_num }</td> <td>${item.dead_num }</td> <td>${item.code }</td> </tr> </c:forEach> </tbody> </table> </body> </html>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <!-- 信1805-2 刘子煜 20183542 2020年3月3日 --> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>主界面</title> </head> <body> <div align="center"> <h2>管理界面</h2> <a href="showservlet">查看疫情</a> </div> </body> </html>
运行界面截图:
还可以按时间范围查询显示。