Echarts对数据库数据进行可视化+图表联动（三）
一、效果：
二、数据库结构（数据自己写）：
1 SET NAMES utf8mb4; 2 SET FOREIGN_KEY_CHECKS = 0; 3 4 -- ---------------------------- 5 -- Table structure for fenshu 6 -- ---------------------------- 7 DROP TABLE IF EXISTS `fenshu`; 8 CREATE TABLE `fenshu` ( 9 `yonghunum` int(11) NOT NULL, 10 `fenshu` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, 11 PRIMARY KEY (`yonghunum`) USING BTREE 12 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; 13 14 SET FOREIGN_KEY_CHECKS = 1;
三、代码：
（1）结构：
（此篇仅展示dianli3.jsp的相关内容）
（2）程序
Util包下：DianLi3.java
1 package util; 2 3 public class DianLi3 { 4 private String yonghunum; 5 private String fenshu; 6 public DianLi3(String yonghunum,String fenshu) { 7 this.setYonghunum(yonghunum); 8 this.setFenshu(fenshu); 9 } 10 public String getYonghunum() { 11 return yonghunum; 12 } 13 public void setYonghunum(String yonghunum) { 14 this.yonghunum = yonghunum; 15 } 16 public String getFenshu() { 17 return fenshu; 18 } 19 public void setFenshu(String fenshu) { 20 this.fenshu = fenshu; 21 } 22 }
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（仅展示与dianli3.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.DianLi3; 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("DianLi3".equals(method)) { 50 DianLi3(request,response); 51 } 52 } 53 54 Dao dao=new Dao(); 55 56 public void DianLi3(HttpServletRequest request, HttpServletResponse response) 57 throws ServletException, IOException { 58 response.setCharacterEncoding("UTF-8"); 59 request.setCharacterEncoding("UTF-8"); 60 61 List<DianLi3> list = dao.DianLi3(); 62 request.setAttribute("list", list); 63 64 request.getRequestDispatcher("dianli3.jsp").forward(request, response); 65 66 67 } 68 }
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.DianLi3; 11 12 public class Dao { 13 14 public List<DianLi3> DianLi3() { 15 List<DianLi3> list = new ArrayList<>(); 16 Connection conn = null; 17 ResultSet rs = null; 18 PreparedStatement pstm = null; 19 DianLi3 dianLi = null; 20 try { 21 conn = Database.getConnection(); 22 String sql = "select * from fenshu"; 23 pstm = conn.prepareStatement(sql); 24 rs = pstm.executeQuery(); 25 while (rs.next()) { 26 String yonghunum=rs.getString("yonghunum"); 27 String fenshu = rs.getString("fenshu"); 28 dianLi = new DianLi3(yonghunum,fenshu); 29 list.add(dianLi); 30 } 31 } catch (Exception e) { 32 e.printStackTrace(); 33 } finally { 34 Database.close(conn, pstm, rs); 35 } 36 return list; 37 } 38 }
dianli3.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>居民客户的用电缴费习惯分析3</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=DianLi3" 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">居民客户的用电缴费习惯分析3</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;" class="box1"></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 </tr> 56 </thead> 57 <tbody> 58 <c:forEach items="${list}" var="d"> 59 <tr id="tr" class="active"> 60 <td>${d.yonghunum}</td> 61 <td>${d.fenshu}</td> 62 </tr> 63 </c:forEach> 64 </tbody> 65 </table> 66 67 <script type="text/javascript"> 68 // 基于准备好的dom，初始化echarts实例 69 var myChart = echarts.init(document.getElementById('main')); 70 var option; 71 option = { 72 tooltip: { 73 trigger: 'axis', 74 axisPointer: { 75 type: 'cross', 76 crossStyle: { 77 color: '#999' 78 } 79 } 80 }, 81 legend: { 82 data: ['权重'] 83 }, 84 toolbox: { 85 show: true, 86 feature: { 87 dataView: { show: true, readOnly: false }, 88 magicType: { show: true, type: ['line', 'bar'] }, 89 restore: { show: true }, 90 saveAsImage: { show: true } 91 } 92 }, 93 calculable: true, 94 xAxis: [ 95 { 96 type: 'category', 97 // prettier-ignore 98 data: [<c:forEach items="${list}" var="d"> 99 '${d.yonghunum}', 100 </c:forEach> 101 ], 102 axisPointer: { 103 type: 'shadow' 104 } 105 } 106 ], 107 yAxis: [ 108 { 109 type: 'value', 110 name: '权重', 111 } 112 ], 113 series: [ 114 { 115 name: '权重', 116 type: 'bar', 117 barWidth : 10,//柱图宽度 118 data: [ 119 <c:forEach items="${list}" var="d"> 120 '${d.fenshu}', 121 </c:forEach> 122 ] 123 }, 124 { 125 name: '权重', 126 type: 'line', 127 data: [<c:forEach items="${list}" var="d"> 128 '${d.fenshu}', 129 </c:forEach>], 130 symbol: 'triangle', 131 symbolSize: 20, 132 lineStyle: { 133 color: '#5470C6', 134 width: 4, 135 type: 'dashed' 136 }, 137 itemStyle: { 138 borderWidth: 3, 139 borderColor: '#EE6666', 140 color: 'yellow' 141 } 142 } 143 ] 144 }; 145 myChart.setOption(option); 146 147 // 设置echarts的点击事件 148 myChart.on('click',function (params) { 149 // 获取table下所有的tr 150 let trs = $("#table tbody tr"); 151 for (let i = 0;i<trs.length;i++){ 152 // 获取tr下所有的td 153 let tds = trs.eq(i).find("td"); 154 // 先把之前的标记的success去掉 155 $("#table tbody tr").eq(i).removeClass('success'); 156 // 如果点击图示的名字和table下的某一个行的第一个td的值一样 157 if (params.name == tds.eq(0).text()){ 158 //设置success状态 159 $("#table tbody tr").eq(i).addClass('success'); 160 // 跳转到页面指定的id位置 161 $("html,body").animate({scrollTop:$("#table tbody tr").eq(i).offset().top},1000); 162 } 163 } 164 }); 165 166 // 当鼠标落在tr时，显示浮动 167 $("#table tbody").find("tr").on("mouseenter",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: 'showTip',seriesIndex: 0, name:name});//选中高亮 174 }); 175 // 当鼠标移开tr时候取消浮动 176 $("#table tbody").find("tr").on("mouseleave",function () { 177 // 获得当前匹配元素的个数 178 let row = $(this).prevAll().length; 179 // 获得当前tr下td的名字 180 let name = $("#table tbody").find("tr").eq(row).find("td").eq(0).text(); 181 // 设置浮动 182 myEcharts.dispatchAction({ type: 'hideTip', name:name});//选中高亮 183 }); 184 </script> 185 </body> 186 </html>
