Echarts对数据库数据进行可视化+图表联动（一）
一、效果：
二、数据库结构（数据自己写）：
1 SET NAMES utf8mb4; 2 SET FOREIGN_KEY_CHECKS = 0; 3 4 -- ---------------------------- 5 -- Table structure for cp 6 -- ---------------------------- 7 DROP TABLE IF EXISTS `cp`; 8 CREATE TABLE `cp` ( 9 `yonghunum` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 10 `num` bigint(21) NOT NULL DEFAULT 0, 11 `money` double NULL DEFAULT NULL, 12 `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL 13 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; 14 15 SET FOREIGN_KEY_CHECKS = 1;
三、代码：
（1）结构：
（此篇仅展示dianli1.jsp的相关内容）
（2）程序
Util包下：DianLi1.java
1 package util; 2 3 public class DianLi1 { 4 private String yonghunum; 5 private String num; 6 private String money; 7 private String type; 8 public DianLi1(String yonghunum2, String num2, String money2, String type2) { 9 // TODO Auto-generated constructor stub 10 this.yonghunum=yonghunum2; 11 this.num=num2; 12 this.money=money2; 13 this.type=type2; 14 } 15 public String getYonghunum() { 16 return yonghunum; 17 } 18 public void setYonghunum(String yonghunum) { 19 this.yonghunum = yonghunum; 20 } 21 public String getNum() { 22 return num; 23 } 24 public void setNum(String num) { 25 this.num = num; 26 } 27 public String getMoney() { 28 return money; 29 } 30 public void setMoney(String money) { 31 this.money = money; 32 } 33 public String getType() { 34 return type; 35 } 36 public void setType(String type) { 37 this.type = type; 38 } 39 }
database包下：Database.java
1 package database; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.PreparedStatement; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 9 public class Database { 10 public static final String url="jdbc:mysql://localhost:3306/a";//URL 11 public static final String user="root";//用户名 12 public static final String password="123456";//密码 13 14 public static void main(String[] args) { 15 getConnection(); 16 } 17 18 /** 19 * 连接数据库 20 * @return 21 */ 22 public static Connection getConnection(){ 23 Connection conn=null; 24 try { 25 Class.forName("com.mysql.jdbc.Driver");//加载数据库驱动 26 conn=DriverManager.getConnection(url, user, password); 27 System.out.println("数据库连接成功！"); 28 }catch(Exception e) { 29 e.printStackTrace(); 30 } 31 return conn; 32 } 33 34 /** 35 * 关闭数据库 36 */ 37 public static void close(Connection conn,PreparedStatement pstm) { 38 39 System.out.println("关闭SQL(conn,pstm)"); 40 if(pstm!=null) { 41 try { 42 pstm.close(); 43 }catch(SQLException e) { 44 e.printStackTrace(); 45 } 46 } 47 48 if(conn!=null) { 49 try { 50 conn.close(); 51 }catch(SQLException e) { 52 e.printStackTrace(); 53 } 54 } 55 56 } 57 58 public static void close(Connection conn,PreparedStatement pstm,ResultSet rs) { 59 60 System.out.println("关闭SQL(conn,pstm,rs)"); 61 if(pstm!=null) { 62 try { 63 pstm.close(); 64 }catch(SQLException e) { 65 e.printStackTrace(); 66 } 67 } 68 69 if(conn!=null) { 70 try { 71 conn.close(); 72 }catch(SQLException e) { 73 e.printStackTrace(); 74 } 75 } 76 77 if(rs!=null) { 78 try { 79 rs.close(); 80 }catch(SQLException e) { 81 e.printStackTrace(); 82 } 83 } 84 85 } 86 87 }
servlet包下：Servlet.java（仅展示与dianli1.jsp有关的程序）
1 package servlet; 2 3 import java.io.IOException; 4 import java.util.List; 5 6 import javax.servlet.ServletException; 7 import javax.servlet.annotation.WebServlet; 8 import javax.servlet.http.HttpServlet; 9 import javax.servlet.http.HttpServletRequest; 10 import javax.servlet.http.HttpServletResponse; 11 12 import dao.Dao; 13 import util.DianLi1; 14 15 /** 16 * Servlet implementation class Servlet 17 */ 18 @WebServlet("/Servlet") 19 public class Servlet extends HttpServlet { 20 private static final long serialVersionUID = 1L; 21 22 /** 23 * @see HttpServlet#HttpServlet() 24 */ 25 public Servlet() { 26 super(); 27 // TODO Auto-generated constructor stub 28 } 29 30 /** 31 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) 32 */ 33 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 34 // TODO Auto-generated method stub 35 //response.getWriter().append("Served at: ").append(request.getContextPath()); 36 } 37 38 /** 39 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) 40 */ 41 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 42 // TODO Auto-generated method stub 43 //doGet(request, response); 44 response.setCharacterEncoding("UTF-8"); 45 request.setCharacterEncoding("UTF-8"); 46 System.out.println("进入Servlet"); 47 String method = request.getParameter("method"); 48 System.out.println(method); 49 if("DianLi1".equals(method)) { 50 DianLi1(request,response); 51 } 52 } 53 54 Dao dao=new Dao(); 55 56 public void DianLi1(HttpServletRequest request, HttpServletResponse response) 57 throws ServletException, IOException { 58 response.setCharacterEncoding("UTF-8"); 59 request.setCharacterEncoding("UTF-8"); 60 61 List<DianLi1> list = dao.DianLi1(); 62 request.setAttribute("list", list); 63 64 request.getRequestDispatcher("dianli1.jsp").forward(request, response); 65 66 67 } 68 69 }
dao包下：Dao.java
1 package dao; 2 3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.util.ArrayList; 7 import java.util.List; 8 9 import database.Database; 10 import util.DianLi1; 11 12 public class Dao { 13 public List<DianLi1> DianLi1() { 14 List<DianLi1> list = new ArrayList<>(); 15 Connection conn = null; 16 ResultSet rs = null; 17 PreparedStatement pstm = null; 18 DianLi1 dianLi = null; 19 try { 20 conn = Database.getConnection(); 21 String sql = "select * from cp"; 22 pstm = conn.prepareStatement(sql); 23 rs = pstm.executeQuery(); 24 while (rs.next()) { 25 String yonghunum=rs.getString("yonghunum"); 26 String num = rs.getString("num"); 27 String money = rs.getString("money"); 28 String type = rs.getString("type"); 29 dianLi = new DianLi1(yonghunum,num,money,type); 30 list.add(dianLi); 31 } 32 } catch (Exception e) { 33 e.printStackTrace(); 34 } finally { 35 Database.close(conn, pstm, rs); 36 } 37 return list; 38 } 39 }
dianli1.jsp
1 <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> 2 <%@ page language="java" contentType="text/html; charset=UTF-8" 3 pageEncoding="UTF-8"%> 4 <!DOCTYPE html> 5 <html> 6 <head> 7 <meta charset="UTF-8"> 8 <title>居民客户的用电缴费习惯分析1</title> 9 <link rel="stylesheet" href=".//layui/css/layui.css" media="all"> 10 <script src=".//layui/layui.js"></script> 11 <!-- 使用单文件引入的方式使用echarts.min.jS --> 12 <script src="js/echarts.min.js"></script> 13 <!-- 官网jQuery文件 --> 14 <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script> 15 <!-- 词云图 --> 16 <!-- 引入 echarts.js --> 17 <script src="js/echarts.js"></script> 18 <!-- 最新版本的 Bootstrap 核心 CSS 文件 --> 19 <link rel="stylesheet" 20 href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap.min.css" 21 integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" 22 crossorigin="anonymous"> 23 <!-- 可选的 Bootstrap 主题文件（一般不用引入） --> 24 <link rel="stylesheet" 25 href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap-theme.min.css" 26 integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" 27 crossorigin="anonymous"> 28 <!-- 最新的 Bootstrap 核心 JavaScript 文件 --> 29 <script 30 src="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/js/bootstrap.min.js" 31 integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" 32 crossorigin="anonymous"></script> 33 </head> 34 <body> 35 <form class="layui-form" action="Servlet?method=DianLi1" method="post"> 36 <div class="layui-form-item"> 37 <div class="layui-inline"> 38 <button class="layui-btn" lay-submit 39 style="margin-top: 30px; margin-left: 500px">居民客户的用电缴费习惯分析1</button> 40 </div> 41 </div> 42 </form> 43 <!-- 为 ECharts 准备一个具备大小（宽高）的 DOM --> 44 <div class="layui-form-item"> 45 <div class="layui-inline"> 46 <div id="main" style="width: 1000px; height: 600px;"></div> 47 </div> 48 </div> 49 50 <table class="table table-bordered table-hover" id="table"> 51 <thead> 52 <tr> 53 <th>用户编号</th> 54 <th>缴费金额</th> 55 <th>缴费次数</th> 56 <th>用户类型</th> 57 </tr> 58 </thead> 59 <tbody> 60 <c:forEach items="${list}" var="d"> 61 <tr id="tr" class="active"> 62 <td>${d.yonghunum}</td> 63 <td>${d.money}</td> 64 <td>${d.num}</td> 65 <td>${d.type}</td> 66 </tr> 67 </c:forEach> 68 </tbody> 69 </table> 70 71 <script type="text/javascript"> 72 // 基于准备好的dom，初始化echarts实例 73 var myChart = echarts.init(document.getElementById('main')); 74 var option; 75 option = { 76 title: { 77 text: '居民客户的用电缴费习惯分析', 78 left: 'center' 79 }, 80 grid: { 81 bottom: 80 82 }, 83 toolbox: { 84 feature: { 85 dataZoom: { 86 yAxisIndex: 'none' 87 }, 88 restore: {}, 89 saveAsImage: {} 90 } 91 }, 92 tooltip: { 93 trigger: 'axis', 94 axisPointer: { 95 type: 'cross', 96 animation: false, 97 label: { 98 backgroundColor: '#505765' 99 } 100 } 101 }, 102 legend: { 103 data: ['缴费金额', '缴费次数'], 104 left: 10 105 }, 106 dataZoom: [ 107 { 108 show: true, 109 realtime: true, 110 start: 65, 111 end: 85 112 }, 113 { 114 type: 'inside', 115 realtime: true, 116 start: 65, 117 end: 85 118 } 119 ], 120 xAxis: [ 121 { 122 type: 'category', 123 boundaryGap: false, 124 axisLine: { onZero: false }, 125 // prettier-ignore 126 data: [ 127 <c:forEach items="${list}" var="d"> 128 ${d.yonghunum}, 129 </c:forEach> 130 ] 131 } 132 ], 133 yAxis: [ 134 { 135 name: '缴费金额', 136 type: 'value' 137 }, 138 { 139 name: '缴费次数', 140 nameLocation: 'start', 141 alignTicks: true, 142 type: 'value', 143 inverse: true 144 } 145 ], 146 series: [ 147 { 148 name: '缴费金额', 149 type: 'line', 150 markPoint: { 151 data: [ 152 { type: 'max', name: 'Max' }, 153 { type: 'min', name: 'Min' } 154 ] 155 }, 156 markLine: { 157 data: [{ type: 'average', name: 'Avg' }] 158 }, 159 areaStyle: {}, 160 lineStyle: { 161 width: 1 162 }, 163 emphasis: { 164 focus: 'series' 165 }, 166 markArea: { 167 silent: true, 168 itemStyle: { 169 opacity: 0.3 170 }, 171 data: [ 172 [ 173 { 174 xAxis: '1000000000' 175 }, 176 { 177 xAxis: '1000000000' 178 } 179 ] 180 ] 181 }, 182 // prettier-ignore 183 data: [ 184 <c:forEach items="${list}" var="d"> 185 ${d.money}, 186 </c:forEach> 187 ] 188 }, 189 { 190 name: '缴费次数', 191 type: 'line', 192 yAxisIndex: 1, 193 markPoint: { 194 data: [ 195 { type: 'max', name: 'Max' }, 196 { type: 'min', name: 'Min' } 197 ] 198 }, 199 markLine: { 200 data: [{ type: 'average', name: 'Avg' }] 201 }, 202 areaStyle: {}, 203 lineStyle: { 204 width: 1 205 }, 206 emphasis: { 207 focus: 'series' 208 }, 209 markArea: { 210 silent: true, 211 itemStyle: { 212 opacity: 0.3 213 }, 214 data: [ 215 [ 216 { 217 xAxis: '1000000000' 218 }, 219 { 220 xAxis: '1000000000' 221 } 222 ] 223 ] 224 }, 225 // prettier-ignore 226 data: [ 227 <c:forEach items="${list}" var="d"> 228 ${d.num}, 229 </c:forEach> 230 ] 231 } 232 ] 233 }; 234 myChart.setOption(option); 235 236 // 设置echarts的点击事件 237 myChart.on('click',function (params) { 238 // 获取table下所有的tr 239 let trs = $("#table tbody tr"); 240 for (let i = 0;i<trs.length;i++){ 241 // 获取tr下所有的td 242 let tds = trs.eq(i).find("td"); 243 // 先把之前的标记的success去掉 244 $("#table tbody tr").eq(i).removeClass('success'); 245 // 如果点击图示的名字和table下的某一个行的第一个td的值一样 246 if (params.name == tds.eq(0).text()){ 247 //设置success状态 248 $("#table tbody tr").eq(i).addClass('success'); 249 // 跳转到页面指定的id位置 250 $("html,body").animate({scrollTop:$("#table tbody tr").eq(i).offset().top},1000); 251 } 252 } 253 }); 254 255 // 当鼠标落在tr时，显示浮动 256 $("#table tbody").find("tr").on("mouseenter",function () { 257 // 获得当前匹配元素的个数 258 let row = $(this).prevAll().length; 259 // 获得当前tr下td的名字 260 let name = $("#table tbody").find("tr").eq(row).find("td").eq(0).text(); 261 // 设置浮动 262 myEcharts.dispatchAction({ type: 'showTip',seriesIndex: 0, name:name});//选中高亮 263 }); 264 // 当鼠标移开tr时候取消浮动 265 $("#table tbody").find("tr").on("mouseleave",function () { 266 // 获得当前匹配元素的个数 267 let row = $(this).prevAll().length; 268 // 获得当前tr下td的名字 269 let name = $("#table tbody").find("tr").eq(row).find("td").eq(0).text(); 270 // 设置浮动 271 myEcharts.dispatchAction({ type: 'hideTip', name:name});//选中高亮 272 }); 273 </script> 274 </body> 275 </html>
