一、Echarts简介
代码已上传至码云:echarts_boot: echarts使用demo
ECharts是一个使用 JavaScript 实现的"数据可视化"库, 它可以流畅的运行在 PC 和移动设备上
什么是数据可视化?
也就是可以将数据通过图表的形式展示出来,
Echarts官网:Apache ECharts
本篇文章以此柱状图为例:
二、业务背景
数据库中分别有学生表、课程表、成绩表三张表
使用echarts对 某科目全班平均分近三年变化、某个人总分数变化、某人某年各科成绩作统计和可视化操作
表结构如下:
三、数据准备
/* Navicat Premium Data Transfer Source Server : CloudShell Source Server Type : MySQL Source Server Version : 50736 (5.7.36) Source Host : 124.70.69.186:13306 Source Schema : echarts_test Target Server Type : MySQL Target Server Version : 50736 (5.7.36) File Encoding : 65001 Date: 01/07/2023 15:44:13 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `cou_num` int(11) DEFAULT NULL COMMENT '课程号', `cou_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '课程名', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='课程表'; -- ---------------------------- -- Records of course -- ---------------------------- BEGIN; INSERT INTO `course` (`id`, `cou_num`, `cou_name`) VALUES (1, 1, '语文'); INSERT INTO `course` (`id`, `cou_num`, `cou_name`) VALUES (2, 2, '数学'); INSERT INTO `course` (`id`, `cou_num`, `cou_name`) VALUES (3, 3, '英语'); COMMIT; -- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `cou_num` int(11) NOT NULL COMMENT '课程号', `stu_num` int(11) NOT NULL COMMENT '学号', `score` int(11) DEFAULT NULL COMMENT '成绩', `time` timestamp NULL DEFAULT NULL COMMENT '时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='成绩表'; -- ---------------------------- -- Records of score -- ---------------------------- BEGIN; INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (1, 1, 1, 90, '2023-06-30 09:57:19'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (2, 2, 1, 88, '2023-06-30 09:57:31'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (3, 3, 1, 95, '2023-06-30 09:57:42'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (4, 1, 2, 70, '2023-06-30 09:57:54'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (5, 2, 2, 99, '2023-06-30 09:58:04'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (6, 3, 2, 60, '2023-06-30 09:58:12'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (7, 1, 3, 56, '2023-06-30 09:58:20'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (8, 2, 3, 32, '2023-06-30 09:58:31'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (9, 3, 3, 43, '2022-06-30 09:58:48'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (10, 1, 1, 88, '2022-06-30 09:57:19'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (11, 2, 1, 86, '2022-06-30 09:57:31'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (12, 3, 1, 93, '2022-06-30 09:57:42'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (13, 1, 2, 70, '2022-06-30 09:57:54'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (14, 2, 2, 99, '2022-06-30 09:58:04'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (15, 3, 2, 60, '2022-06-30 09:58:12'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (16, 1, 3, 56, '2022-06-30 09:58:20'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (17, 2, 3, 32, '2022-06-30 09:58:31'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (18, 3, 3, 43, '2022-06-30 09:58:48'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (19, 1, 1, 86, '2021-06-30 09:57:19'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (20, 2, 1, 84, '2021-06-30 09:57:31'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (21, 3, 1, 91, '2021-06-30 09:57:42'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (22, 1, 2, 70, '2021-06-30 09:57:54'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (23, 2, 2, 99, '2021-06-30 09:58:04'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (24, 3, 2, 60, '2021-06-30 09:58:12'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (25, 1, 3, 56, '2021-06-30 09:58:20'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (26, 2, 3, 32, '2021-06-30 09:58:31'); INSERT INTO `score` (`id`, `cou_num`, `stu_num`, `score`, `time`) VALUES (27, 3, 3, 43, '2021-06-30 09:58:48'); COMMIT; -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `stu_num` int(11) NOT NULL COMMENT '学号', `stu_name` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '姓名', `stu_sex` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '性别', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='学生表'; -- ---------------------------- -- Records of student -- ---------------------------- BEGIN; INSERT INTO `student` (`id`, `stu_num`, `stu_name`, `stu_sex`) VALUES (1, 1, '张三', '男'); INSERT INTO `student` (`id`, `stu_num`, `stu_name`, `stu_sex`) VALUES (2, 2, '李四', '女'); INSERT INTO `student` (`id`, `stu_num`, `stu_name`, `stu_sex`) VALUES (3, 3, '王五', '男'); COMMIT; SET FOREIGN_KEY_CHECKS = 1;
复制
四、构建后端工程
1.添加依赖
mysql、mybaits-plus和lombok
<dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- mybaits-plus --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.0</version> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.4</version> </dependency> <!--mysql--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.11</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13.1</version> <scope>test</scope> </dependency>
复制
2.编辑配置文件
server: port: 18889 spring: datasource: url: jdbc:mysql://127.0.0.1:3306/echarts_test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&AllowPublicKeyRetrieval=True&useSSL=false username: root password: root mybatis-plus: mapper-locations: mapper/*.xml configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl map-underscore-to-camel-case: true type-aliases-package: com.crazyk.echartsboot.bo
复制
3.根据数据库表结构构件项目包和类
以学生实体为例
实体类
package com.crazyk.echartsboot.bo; import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import lombok.Data; /** * @program: EchartsBoot * @description: 学生实体 * @author: CrazyK * @create: 2023-06-30 10:02 **/ @Data @TableName("student") public class Student { @TableId(type = IdType.AUTO) private Integer id; /** * 学号 */ private Integer stuNum; /** * 姓名 */ private String stuName; /** * 性别 */ private String StuSex; }
复制
mapper接口
package com.crazyk.echartsboot.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.crazyk.echartsboot.bo.Student; import org.apache.ibatis.annotations.Mapper; /** * @program: EchartsBoot * @description: 学生接口 * @author: CrazyK * @create: 2023-06-30 10:08 **/ @Mapper public interface StudentMapper extends BaseMapper<Student> { }
复制
service实现类
package com.crazyk.echartsboot.service; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.crazyk.echartsboot.bo.Student; import com.crazyk.echartsboot.mapper.StudentMapper; import org.springframework.stereotype.Service; /** * @program: EchartsBoot * @description: 学生服务 * @author: CrazyK * @create: 2023-06-30 10:10 **/ @Service public class StudentService extends ServiceImpl<StudentMapper, Student> { }
复制
controller控制类
package com.crazyk.echartsboot.controller; import com.crazyk.echartsboot.service.StudentService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; import java.util.stream.Collectors; /** * @program: EchartsBoot * @description: 学生控制层 * @author: CrazyK * @create: 2023-06-30 10:12 **/ @RestController @RequestMapping("/student") public class StudentController { @Autowired private StudentService studentService; }
复制
mapper.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.crazyk.echartsboot.mapper.StudentMapper"> </mapper>
复制
4.编写业务代码
博主太懒了,不想写sql,所以用的stream
package com.crazyk.echartsboot.controller; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.crazyk.echartsboot.bo.Course; import com.crazyk.echartsboot.bo.Score; import com.crazyk.echartsboot.bo.Student; import com.crazyk.echartsboot.service.CourseService; import com.crazyk.echartsboot.service.ScoreService; import com.crazyk.echartsboot.service.StudentService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.*; import java.util.stream.Collectors; /** * @program: EchartsBoot * @description: 成绩控制层 * @author: CrazyK * @create: 2023-06-30 10:14 **/ @RestController @RequestMapping("/score") public class ScoreController { @Autowired private ScoreService scoreService; @Autowired private CourseService courseService; @Autowired private StudentService studentService; /** * 某科目全班平均分 近三年变化 */ @RequestMapping("avg") public Map<String, Integer> avg(String couName){ Integer couNum = courseService.getOne(new QueryWrapper<Course>().eq("cou_name", couName)) .getId(); Map<String, List<Score>> map = scoreService.list(new QueryWrapper<Score>().eq("cou_num", couNum)) .stream() .collect(Collectors.groupingBy(s -> s.getTime().toString().substring(24))); Map<String,Integer> res = new HashMap<>(); for (String key : map.keySet()){ Double average = map.get(key).stream() .map(s -> s.getScore()) .mapToInt(Integer::valueOf) .average().orElse(0D); Integer score = Integer.valueOf(String.valueOf(average).substring(0,2)); res.put(key,score); } System.out.println(res); return res; } /** * 某个人总分数变化 */ @RequestMapping("sum") public Map<String,Integer> sum(String stuName){ Integer stuNum = studentService.getOne(new QueryWrapper<Student>().eq("stu_name",stuName)).getStuNum(); Map<String, List<Score>> map = scoreService.list(new QueryWrapper<Score>().eq("stu_num", stuNum)) .stream() .collect(Collectors.groupingBy(s -> s.getTime().toString().substring(24))); Map<String,Integer> res = new HashMap<>(); for (String key : map.keySet()){ Integer sum = map.get(key).stream() .map(s -> s.getScore()) .mapToInt(Integer::valueOf) .sum(); res.put(key,sum); } System.out.println(res); return res; } /** * 某人某年各科成绩 */ @RequestMapping("own") public Map<String,Integer> own(String stuName,String year){ Integer stuNum = studentService.getOne(new QueryWrapper<Student>().eq("stu_name",stuName)).getStuNum(); Map<Integer, List<Score>> map = scoreService.list(new QueryWrapper<Score>().eq("stu_num", stuNum)) .stream() .filter(s -> s.getTime().toString().substring(24).equals(year)) .collect(Collectors.groupingBy(s -> s.getCouNum())); Map<String,Integer> res = new HashMap<>(); for (Integer key : map.keySet()){ String couName = courseService.getOne(new QueryWrapper<Course>().eq("cou_num",map.get(key).get(0).getCouNum())).getCouName(); Integer score = map.get(key).get(0).getScore(); res.put(couName,score); } System.out.println(res); return res; } }
复制
5.接口测试
测试一下刚才写的三个接口
测试没问题就可以开始写前端了
五、构建前端工程
1.创建Vue工程
我创建的是vue2
vue create 项目名
复制
部署成功后安装需要的依赖
npm install vue-router --save #vueRouter npm i element-ui -S #element-ui npm install echarts -S #echarts npm install axios #axios
复制
2.全局引入
在main.js全局引入,这是vue项目的入口
import Vue from 'vue' import App from './App.vue' import router from './router' import ElementUI from 'element-ui' import 'element-ui/lib/theme-chalk/index.css' import * as echarts from "echarts"; import {postRequest} from "@/utils/api"; import {getRequest} from "@/utils/api"; import {putRequest} from "@/utils/api"; import {deleteRequest} from "@/utils/api"; Vue.config.productionTip = false Vue.use(ElementUI,{size:'small'}); Vue.prototype.$echarts = echarts; new Vue({ router, render: h => h(App), }).$mount('#app')
复制
3.配置代理
在vue.config.js文件中添加代理,使请求转发到后端端口
const { defineConfig } = require('@vue/cli-service') module.exports = defineConfig({ transpileDependencies: true }) let proxyObj = {} proxyObj['/'] = { //websocket ws: false, //目标地址 target: 'http://localhost:18889', changeOrigin: true, pathRewrite: { '^/': '/' } }; module.exports = defineConfig({ transpileDependencies: true, devServer:{ host: 'localhost', port: 8080, proxy: proxyObj } })
复制
4.编写请求工具类
api.js用于封装请求
import axios from "axios"; let base = ''; //json格式post请求 export const postRequest = (url,params) => { return axios({ method: 'post', url: base + url, data:params }) } //put请求 export const putRequest = (url,params) => { return axios({ method: 'put', url:base + url, data:params }) } //get请求 export const getRequest = (url,params) => { return axios({ method: 'get', url:base + url, data:params }) } //delete请求 export const deleteRequest = (url,params) => { return axios({ method: 'delete', url:base + url, data:params }) }
复制
然后在main.js中引入请求封装
//插件形式使用请求 Vue.prototype.postRequest = postRequest; Vue.prototype.getRequest = getRequest; Vue.prototype.putRequest = putRequest; Vue.prototype.deleteRequest = deleteRequest;
复制
5.编写业务组件
<template> <div> <div> <table> <tr> <td><el-tag>请输入年份</el-tag></td> <td><el-input v-model="year" size="small" style="margin-left: 8px"></el-input></td> <td><div> </div></td> <td><el-tag>请输入学生姓名</el-tag></td> <td><el-input v-model="stuName" size="small" style="margin-left: 8px"></el-input></td> <td><div> </div></td> <span slot="footer" class="dialog-footer"> <el-button type="primary" @click="own" size="mini">查询某人某年各科成绩</el-button> </span> </tr> <tr> <td><el-tag>请输入学生姓名</el-tag></td> <td><el-input v-model="stuName" size="small" style="margin-left: 8px"></el-input></td> <td><div> </div></td> <span slot="footer" class="dialog-footer"> <el-button type="primary" @click="sum" size="mini">查询某个人总分数变化</el-button> </span> </tr> <tr> <td><el-tag>请输入科目名称</el-tag></td> <td><el-input v-model="couName" size="small" style="margin-left: 8px"></el-input></td> <td><div> </div></td> <span slot="footer" class="dialog-footer"> <el-button type="primary" @click="avg" size="mini">查询某科目班级平均分</el-button> </span> </tr> </table> </div> <div style="height: 500px"> <h2>this.titleEcharts</h2> <div style="height: 100%;width: 100%" ref="histChart"></div> </div> </div> </template> <script > import * as echarts from 'echarts' export default { data() { return { chart:null, option:{ tooltip: { trigger: 'axis', axisPointer: { type: 'shadow' } }, grid: { left: '3%', right: '4%', bottom: '3%', containLabel: true }, xAxis: [ { type: 'category', data: [], axisTick: { alignWithLabel: true } } ], yAxis: [ { type: 'value' } ], series: [ { name: 'Direct', type: 'bar', barWidth: '60%', data: [] } ] }, year:"", stuName:"", couName:"", titleEcharts:"输入表单信息点击按钮查询" } }, watch: { seriveData: { deep: true, handler() { this.updateChartView() } } }, mounted() { this.chart = echarts.init(this.$refs.histChart); this.updateChartView(); window.addEventListener('resize',this.handleWindowResize); }, updated() { this.updateChartView(); }, beforeDestroy() { window.removeEventListener('resize',this.handleWindowResize); }, methods: { /** * 某人某年各科成绩 */ own(){ this.option.xAxis[0].data = []; this.option.series[0].data = []; let url = '/score/own?stuName=' + this.stuName + '&year=' + this.year; this.getRequest(url).then(resp => { console.log(resp.data) const map = new Map(Object.entries(resp.data)); for (let [key,value] of map){ this.option.xAxis[0].data.push(key); this.option.series[0].data.push(value); } this.titleEcharts = this.stuName + "同学" + this.year + "年各科成绩" this.stuName = ""; this.year = ""; this.updateChartView(); }); }, /** * 某个人总分数变化 */ sum(){ this.option.xAxis[0].data = []; this.option.series[0].data = []; let url = '/score/sum?stuName=' + this.stuName; this.getRequest(url).then(resp => { console.log(resp.data) const map = new Map(Object.entries(resp.data)); for (let [key,value] of map){ this.option.xAxis[0].data.push(key); this.option.series[0].data.push(value); } this.titleEcharts = this.stuName + "同学各年总分数变化" this.stuName = ""; this.year = ""; this.updateChartView(); }); }, /** * 某科目班级平均分 */ avg(){ this.option.xAxis[0].data = []; this.option.series[0].data = []; let url = '/score/avg?couName=' + this.couName; this.getRequest(url).then(resp => { console.log(resp.data) const map = new Map(Object.entries(resp.data)); for (let [key,value] of map){ this.option.xAxis[0].data.push(key); this.option.series[0].data.push(value); } this.titleEcharts = this.couName + "科目班级平均分" this.couName = ""; this.updateChartView(); }); }, /** * 更新echarts视图 */ updateChartView() { if (!this.chart) return this.chart.setOption(this.option, true) }, /** * 窗口缩放时动态调整echarts大小 */ handleWindowResize() { if (!this.chart) return; this.chart.resize(); } } } </script> <style scoped> h2{ text-align: center; padding: 30px; font-size: 18px; } </style>
复制
六、页面展示
1.查询某人某年各科成绩
2.查询某个人总分数变化
3.查询某科目班级平均分