Echarts对数据库数据进行可视化+图表联动（二）
一、效果：
二、数据库结构（数据自己写）：
1 SET NAMES utf8mb4; 2 SET FOREIGN_KEY_CHECKS = 0; 3 4 -- ---------------------------- 5 -- Table structure for dianli 6 -- ---------------------------- 7 DROP TABLE IF EXISTS `dianli`; 8 CREATE TABLE `dianli` ( 9 `money` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 10 `count` int(11) NULL DEFAULT NULL, 11 `num` int(11) 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）结构：
（此篇仅展示dianli.jsp的相关内容）
（2）程序
Util包下：DianLi.java
1 package util; 2 3 public class DianLi { 4 private String yonghunum; 5 private String num; 6 private String money; 7 private String type; 8 public DianLi(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（仅展示与dianli.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.DianLi; 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("DianLi".equals(method)) { 50 DianLi(request,response); 51 } 52 } 53 54 Dao dao=new Dao(); 55 56 public void DianLi(HttpServletRequest request, HttpServletResponse response) 57 throws ServletException, IOException { 58 response.setCharacterEncoding("UTF-8"); 59 request.setCharacterEncoding("UTF-8"); 60 61 List<DianLi> list = dao.DianLi(); 62 request.setAttribute("list", list); 63 64 request.getRequestDispatcher("dianli.jsp").forward(request, response); 65 66 } 67 }
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.DianLi; 11 12 public class Dao { 13 public List<DianLi> DianLi() { 14 List<DianLi> list = new ArrayList<>(); 15 Connection conn = null; 16 ResultSet rs = null; 17 PreparedStatement pstm = null; 18 DianLi dianLi = null; 19 try { 20 conn = Database.getConnection(); 21 String sql = "select * from dianli"; 22 pstm = conn.prepareStatement(sql); 23 rs = pstm.executeQuery(); 24 while (rs.next()) { 25 String yonghunum=rs.getString("count"); 26 String num = rs.getString("num"); 27 String money = rs.getString("money"); 28 String type = rs.getString("type"); 29 dianLi = new DianLi(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 }
dianli.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>居民客户的用电缴费习惯分析2</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 <style> 34 .box1 { 35 float: left; 36 margin-left: 10px 37 } 38 39 .box2 { 40 float: right; 41 margin-left: 10px 42 } 43 </style> 44 </head> 45 <body> 46 <form class="layui-form" action="Servlet?method=DianLi" method="post"> 47 <div class="layui-form-item"> 48 <div class="layui-inline"> 49 <button class="layui-btn" lay-submit 50 style="margin-top: 30px; margin-left: 500px">居民客户的用电缴费习惯分析2</button> 51 </div> 52 </div> 53 </form> 54 <!-- 为 ECharts 准备一个具备大小（宽高）的 DOM --> 55 <div class="layui-form-item"> 56 <div class="layui-inline"> 57 <div id="main" style="width: 500px; height: 300px;" class="box1"></div> 58 <div id="main1" style="width: 700px; height: 300px;" class="box2"></div> 59 </div> 60 </div> 61 62 <table class="table table-bordered table-hover" id="table"> 63 <thead> 64 <tr> 65 <th>用户类型</th> 66 <th>用户总数</th> 67 <th>总缴费金额</th> 68 <th>总缴费次数</th> 69 </tr> 70 </thead> 71 <tbody> 72 <c:forEach items="${list}" var="d"> 73 <tr id="tr" class="active"> 74 <td>${d.type}</td> 75 <td>${d.yonghunum}</td> 76 <td>${d.money}</td> 77 <td>${d.num}</td> 78 </tr> 79 </c:forEach> 80 </tbody> 81 </table> 82 83 <script type="text/javascript"> 84 // 基于准备好的dom，初始化echarts实例 85 var myChart = echarts.init(document.getElementById('main')); 86 var option; 87 option = { 88 tooltip: { 89 trigger: 'item' 90 }, 91 legend: { 92 data: [ 93 <c:forEach items="${list}" var="d"> 94 "${d.type}", 95 </c:forEach> 96 ], 97 top: '5%', 98 left: 'center' 99 }, 100 series: [ 101 { 102 name: 'Access From', 103 type: 'pie', 104 radius: ['40%', '70%'], 105 avoidLabelOverlap: false, 106 itemStyle: { 107 borderRadius: 10, 108 borderColor: '#fff', 109 borderWidth: 2 110 }, 111 label: { 112 show: false, 113 position: 'center' 114 }, 115 emphasis: { 116 label: { 117 show: true, 118 fontSize: '40', 119 fontWeight: 'bold' 120 } 121 }, 122 labelLine: { 123 show: false 124 }, 125 data: [ 126 <c:forEach items="${list}" var="d"> 127 { 128 name:'${d.type}', 129 value:'${d.yonghunum}' 130 }, 131 </c:forEach> 132 ] 133 } 134 ] 135 }; 136 myChart.setOption(option); 137 138 // 设置echarts的点击事件 139 myChart.on('click',function (params) { 140 // 获取table下所有的tr 141 let trs = $("#table tbody tr"); 142 for (let i = 0;i<trs.length;i++){ 143 // 获取tr下所有的td 144 let tds = trs.eq(i).find("td"); 145 // 先把之前的标记的success去掉 146 $("#table tbody tr").eq(i).removeClass('success'); 147 // 如果点击图示的名字和table下的某一个行的第一个td的值一样 148 if (params.name == tds.eq(0).text()){ 149 //设置success状态 150 $("#table tbody tr").eq(i).addClass('success'); 151 // 跳转到页面指定的id位置 152 $("html,body").animate({scrollTop:$("#table tbody tr").eq(i).offset().top},1000); 153 } 154 } 155 }); 156 157 // 当鼠标落在tr时，显示浮动 158 $("#table tbody").find("tr").on("mouseenter",function () { 159 // 获得当前匹配元素的个数 160 let row = $(this).prevAll().length; 161 // 获得当前tr下td的名字 162 let name = $("#table tbody").find("tr").eq(row).find("td").eq(0).text(); 163 // 设置浮动 164 myEcharts.dispatchAction({ type: 'showTip',seriesIndex: 0, name:name});//选中高亮 165 }); 166 // 当鼠标移开tr时候取消浮动 167 $("#table tbody").find("tr").on("mouseleave",function () { 168 // 获得当前匹配元素的个数 169 let row = $(this).prevAll().length; 170 // 获得当前tr下td的名字 171 let name = $("#table tbody").find("tr").eq(row).find("td").eq(0).text(); 172 // 设置浮动 173 myEcharts.dispatchAction({ type: 'hideTip', name:name});//选中高亮 174 }); 175 176 177 // 基于准备好的dom，初始化echarts实例 178 var myChart1 = echarts.init(document.getElementById('main1')); 179 var option1; 180 option1 = { 181 dataset: { 182 source: [ 183 ['num', '用户数量', '用户类型'], 184 <c:forEach items="${list}" var="d"> 185 ['${d.yonghunum}','${d.yonghunum}','${d.type}'], 186 </c:forEach> 187 ] 188 }, 189 grid: { containLabel: true }, 190 xAxis: { name: '用户总数' }, 191 yAxis: { type: 'category' }, 192 visualMap: { 193 orient: 'horizontal', 194 left: 'center', 195 min: 10, 196 max: 100, 197 text: ['High Num', 'Low Num'], 198 // Map the score column to color 199 dimension: 0, 200 inRange: { 201 color: ['#65B581', '#FFCE34', '#FD665F'] 202 } 203 }, 204 series: [ 205 { 206 type: 'bar', 207 encode: { 208 // Map the "amount" column to X axis. 209 x: '用户数量', 210 // Map the "product" column to Y axis 211 y: '用户类型' 212 } 213 } 214 ] 215 }; 216 myChart1.setOption(option1); 217 218 // 设置echarts的点击事件 219 myChart1.on('click',function (params) { 220 // 获取table下所有的tr 221 let trs = $("#table tbody tr"); 222 for (let i = 0;i<trs.length;i++){ 223 // 获取tr下所有的td 224 let tds = trs.eq(i).find("td"); 225 // 先把之前的标记的success去掉 226 $("#table tbody tr").eq(i).removeClass('success'); 227 // 如果点击图示的名字和table下的某一个行的第一个td的值一样 228 if (params.name == tds.eq(0).text()){ 229 //设置success状态 230 $("#table tbody tr").eq(i).addClass('success'); 231 // 跳转到页面指定的id位置 232 $("html,body").animate({scrollTop:$("#table tbody tr").eq(i).offset().top},1000); 233 } 234 } 235 }); 236 237 // 当鼠标落在tr时，显示浮动 238 $("#table tbody").find("tr").on("mouseenter",function () { 239 // 获得当前匹配元素的个数 240 let row = $(this).prevAll().length; 241 // 获得当前tr下td的名字 242 let name = $("#table tbody").find("tr").eq(row).find("td").eq(0).text(); 243 // 设置浮动 244 myEcharts.dispatchAction({ type: 'showTip',seriesIndex: 0, name:name});//选中高亮 245 }); 246 // 当鼠标移开tr时候取消浮动 247 $("#table tbody").find("tr").on("mouseleave",function () { 248 // 获得当前匹配元素的个数 249 let row = $(this).prevAll().length; 250 // 获得当前tr下td的名字 251 let name = $("#table tbody").find("tr").eq(row).find("td").eq(0).text(); 252 // 设置浮动 253 myEcharts.dispatchAction({ type: 'hideTip', name:name});//选中高亮 254 }); 255 256 myChart.connect([myChart1]); 257 258 </script> 259 </body> 260 </html>
