电子书快照表
#电子书快照表
drop table if exists `ebook_snapshot`;
create table `ebook_snapshot`(
`id` bigint auto_increment not null comment 'id',
`ebook_id` bigint not null comment '电子书id',
`date` date not null comment '快照日期',
`view_count` int not null default 0 comment '阅读量',
`vote_count` int not null default 0 comment '点赞量',
`view_increase` int not null default 0 comment '阅读增长',
`vote_increase` int not null default 0 comment '点赞增长',
primary key (`id`),
unique key `ebook_id_date_unique` (`ebook_id`,`date`)
)engine=innodb default charset=utf8mb4 comment '电子书快照表';
生成对应的代码:9-1
快照sql
##在快照表里复制电子书表的id并设置当前日期
insert into ebook_snapshot(ebook_id, date, view_count, vote_count, view_increase, vote_increase)
select t1.id, curdate(),0,0,0,0 from ebook t1
where not exists(
select 1 from ebook_snapshot t2
where t1.id = t2.ebook_id
and t2.`date` = curdate()
);
##更新今日
update ebook_snapshot t1, ebook t2
set t1.view_count = t2.view_count,t1.vote_count = t2.vote_count
where t1.`date` = curdate()
and t1.ebook_id = t2.id;
##获取昨天的数据
select t1.ebook_id, view_count, vote_count from ebook_snapshot t1
where t1.`date` = date_sub(curdate(),interval 1 day);
重点:
##更新快照表,将今天的数据与昨天的数据相减,获得增量.昨天没数据则增量为自身
update ebook_snapshot t1 left join (select ebook_id, view_count, vote_count from ebook_snapshot
where `date` = date_sub(curdate(),interval 1 day)) t2
on t1.ebook_id = t2.ebook_id
set t1.view_increase = (t1.view_count - ifnull(t2.view_count,0)),
t1.vote_increase = (t1.vote_count - ifnull(t2.vote_count,0))
where t1.`date` = curdate();
快照功能开发
后端开发
新增定时任务EbookSnapshotTask:
@Component
public class EbookSnapshotJob {
private static final Logger LOG = LoggerFactory.getLogger(EbookSnapshotJob.class);
@Resource
private EbookSnapshotService ebookSnapshotService;
@Resource
private SnowFlake snowFlake;
/**
* 自定义cron表达式跑批
* 只有等上一次执行完成,下一次才会在下一个时间点执行,错过就错过
*/
@Scheduled(cron = "0 0/1 * * * ?")
public void doSnapshot() {
// 增加日志流水号
MDC.put("LOG_ID", String.valueOf(snowFlake.nextId()));
LOG.info("生成今日电子书快照开始");
Long start = System.currentTimeMillis();
ebookSnapshotService.genSnapshot();
LOG.info("生成今日电子书快照结束,耗时:{}毫秒", System.currentTimeMillis() - start);
}
}
Task里面调用的Service如下:
@Service
public class EbookSnapshotService {
@Resource
private EbookSnapshotMapperCust ebookSnapshotMapperCust;
public void genSnapshot() {
ebookSnapshotMapperCust.genSnapshot();
}
/**
* 获取首页数值数据:总阅读数、总点赞数、今日阅读数、今日点赞数、今日预计阅读数、今日预计阅读增长
*/
public List<StatisticResp> getStatistic() {
return ebookSnapshotMapperCust.getStatistic();
}
/**
* 30天数值统计
*/
public List<StatisticResp> get30Statistic() {
return ebookSnapshotMapperCust.get30Statistic();
}
}
service对应的mapper接口:
public interface EbookSnapshotMapper extends BaseMapper<EbookSnapshot> {
public void genSnapshot();
List<StatisticResp> getStatistic();
List<StatisticResp> get30Statistic();
}
mapprt'对应的xml:
<update id="genSnapshot">
insert into ebook_snapshot(ebook_id, `date`, view_count, vote_count, view_increase, vote_increase)
select t1.id, curdate(), 0, 0, 0, 0
from ebook t1
where not exists(select 1
from ebook_snapshot t2
where t1.id = t2.ebook_id
and t2.`date` = curdate());
update ebook_snapshot t1, ebook t2
set t1.view_count = t2.view_count,
t1.vote_count = t2.vote_count
where t1.`date` = curdate()
and t1.ebook_id = t2.id;
update ebook_snapshot t1 left join (select ebook_id, view_count, vote_count
from ebook_snapshot
where `date` = date_sub(curdate(), interval 1 day)) t2
on t1.ebook_id = t2.ebook_id
set t1.view_increase = (t1.view_count - ifnull(t2.view_count, 0)),
t1.vote_increase = (t1.vote_count - ifnull(t2.vote_count, 0))
where t1.`date` = curdate();
</update>
<!-- 获取首页数值数据:总阅读数、总点赞数、今日阅读数、今日点赞数、今日预计阅读数、今日预计阅读增长 -->
<select id="getStatistic" resultType="com.example.mywiki.response.StatisticResp">
select
t1.`date` as `date`,
sum(t1.view_count) as viewCount,
sum(t1.vote_count) as voteCount,
sum(t1.view_increase) as viewIncrease,
sum(t1.vote_increase) as voteIncrease
from
ebook_snapshot t1
where
t1.`date` >= date_sub(curdate(), interval 1 day)
group by
t1.`date`
order by
t1.`date` asc;
</select>
<select id="get30Statistic" resultType="com.example.mywiki.response.StatisticResp">
select
t1.`date` as `date`,
sum(t1.view_increase) as viewIncrease,
sum(t1.vote_increase) as voteIncrease
from
ebook_snapshot t1
where
t1.`date` between date_sub(curdate(), interval 30 day) and date_sub(curdate(), interval 1 day)
group by
t1.`date`
order by
t1.`date` asc;
</select>
注意mybatis执行多个sql默认会报错,需要在数据源连接后添加参数开启:
&&allowMultiQueries=true
新增EbookSnapshotController:
@RestController
@RequestMapping("/ebook-snapshot")
public class EbookSnapshotController {
@Resource
private EbookSnapshotService ebookSnapshotService;
@GetMapping("/get-statistic")
public CommonResp getStatistic() {
List<StatisticResp> statisticResp = ebookSnapshotService.getStatistic();
CommonResp<List<StatisticResp>> commonResp = new CommonResp<>();
commonResp.setContent(statisticResp);
return commonResp;
}
@GetMapping("/get-30-statistic")
public CommonResp get30Statistic() {
List<StatisticResp> statisticResp = ebookSnapshotService.get30Statistic();
CommonResp<List<StatisticResp>> commonResp = new CommonResp<>();
commonResp.setContent(statisticResp);
return commonResp;
}
}
定义快照返回实体:
@Data
public class StatisticResp {
@JsonFormat(pattern="MM-dd", timezone = "GMT+8")
private Date date;
private int viewCount;
private int voteCount;
private int viewIncrease;
private int voteIncrease;
}
见提交9-2
前端开发
https://www.antdv.com/components/statistic-cn/
新建Statistic.vue,使用<a-row 来放置数据展示,在a-col里获取数据:
<a-col :span="8">
<a-statistic title="总阅读量" :value="statistic.viewCount">
<template #suffix>
<UserOutlined />
</template>
</a-statistic>
</a-col>
statistic变量的定义:
const statistic = ref();
statistic.value = {};
获取数据和增长:
const getStatistic = () => {
axios.get('/ebook-snapshot/get-statistic').then((response) => {
const data = response.data;
if (data.success) {
const statisticResp = data.content;
statistic.value.viewCount = statisticResp[1].viewCount;
statistic.value.voteCount = statisticResp[1].voteCount;
statistic.value.todayViewCount = statisticResp[1].viewIncrease;
statistic.value.todayVoteCount = statisticResp[1].voteIncrease;
// 按分钟计算当前时间点,占一天的百分比
const now = new Date();
const nowRate = (now.getHours() * 60 + now.getMinutes()) / (60 * 24);
// console.log(nowRate)
statistic.value.todayViewIncrease = parseInt(String(statisticResp[1].viewIncrease / nowRate));
// todayViewIncreaseRate:今日预计增长率
statistic.value.todayViewIncreaseRate = (statistic.value.todayViewIncrease - statisticResp[0].viewIncrease) / statisticResp[0].viewIncrease * 100;
statistic.value.todayViewIncreaseRateAbs = Math.abs(statistic.value.todayViewIncreaseRate);
}
});
};
集成Echartss
图形报表,官网:https://echarts.apache.org/zh/index.html
官网很漂亮,个人很喜欢!点击下载——dist,将min.js下载并复制到项目js目录下
然后在index.html里引入该js:
<script src="<%= BASE_URL %>js/echarts_5.0.2.min.js"></script>
通过这个div来展示:
<a-row>
<a-col :span="24" id="main-col">
<div id="main" style="width: 100%;height:300px;"></div>
</a-col>
</a-row>
先将js的变量忽略检查:
declare let echarts: any;
echarts初始化:
const init30DayEcharts = (list: any) => {
// 发布生产后出现问题:切到别的页面,再切回首页,报表显示不出来
// 解决方法:把原来的id=main的区域清空,重新初始化
const mainDom = document.getElementById('main-col');
if (mainDom) {
mainDom.innerHTML = '<div id="main" style="width: 100%;height:300px;"></div>';
}
// 基于准备好的dom,初始化echarts实例
const myChart = echarts.init(document.getElementById('main'));
const xAxis = [];
const seriesView = [];
const seriesVote = [];
for (let i = 0; i < list.length; i++) {
const record = list[i];
xAxis.push(record.date);
seriesView.push(record.viewIncrease);
seriesVote.push(record.voteIncrease);
}
// 指定图表的配置项和数据
const option = {
title: {
text: '30天趋势图'
},
tooltip: {
trigger: 'axis'
},
legend: {
data: ['总阅读量', '总点赞量']
},
grid: {
left: '3%',
right: '3%',
bottom: '3%',
containLabel: true
},
toolbox: {
feature: {
saveAsImage: {}
}
},
xAxis: {
type: 'category',
boundaryGap: false,
data: xAxis
},
yAxis: {
type: 'value'
},
series: [
{
name: '总阅读量',
type: 'line',
// stack: '总量', 不堆叠
data: seriesView,
smooth: true
},
{
name: '总点赞量',
type: 'line',
// stack: '总量', 不堆叠
data: seriesVote,
smooth: true
}
]
};
// 使用刚指定的配置项和数据显示图表。
myChart.setOption(option);
};
获取数据并调用echarts:
const get30DayStatistic = () => {
axios.get('/ebook-snapshot/get-30-statistic').then((response) => {
const data = response.data;
if (data.success) {
const statisticList = data.content;
//调用echarts
init30DayEcharts(statisticList)
}
});
};
具体每个配置项的功能到官网看。
这里注意日期会显示的非常详细,我们只需要显示日就可以了,需要在后端对日期进行格式化:
在StatisticResp里:
@JsonFormat(pattern="MM-dd", timezone = "GMT+8")
private Date date;
见提交9-5
网站优化
初始加载
在index.html里添加:
<div id="app">
<div style="width: 400px;
height: 100px;
position: absolute;
left: 50%;
top: 50%;
margin: -50px 0 0 -200px;font-family: YouYuan;
color: rgba(0, 0, 0, 1) !important;
font-size: 20px !important;
font-weight: 400;">
请稍后,精彩内容马上呈现...
</div>
</div>
此方法使初次加载时显示文案而不是一片空白
新增文档时无顶级分类
//父文档下拉框初始化,免去点新增。[]用于防止空指针
treeSelectData.value = Tool.copy(level1.value) || [];
// 为选择树添加一个"无"的顶级分类
treeSelectData.value.unshift({id: 0, name: '无'});
IDEA链接服务器
购买就不提了。点击上方工具——部署——配置,选择SFTP,新增SSH,连接好后,再点击工具——部署——浏览远程主机即可。
同样点击工具——启用SSH会话,那么就能远程输入命令了
打包jar
为了避免打包出来的版本号,在pom的build里加上:
<finalName>/dist/${artifactId}</finalName>
点击右侧maven,点击install
完成后在target/dist下生成my-wiki.jar文件
然后在/root下新建/wiki,将.jar上传上去即可
启动服务
关于安装jdk、mysql、redis的内容请看这篇文章:
https://blog.ysboke.cn/archives/568.html
启动一系列的命令很多,一个一个输的话不如大成一个shell脚本,把命令放进去。
doc下新建deploy.sh:
echo "脚本开始-----------"
process_id=`ps -ef | grep my-wiki.jar | grep -v grep |awk '{print $2}'`
if [ $process_id ] ; then
sudo kill -9 $process_id
fi
source /etc/profile
nohup java -jar -Dspring.profiles.active=prod ~/wiki/my-wiki.jar > /dev/null 2>&1 &
echo "脚本结束----------"
注意其中的jar的名称和位置要对上。我们把脚本上传到任意位置,我上传到jar同级,shell进入对应位置,
输入 命令运行:
sh deploy.sh
查找java进程:
ps -ef | grep java
前端编译
运行package.json的"build-prod": "vue-cli-service build --mode prod",相当于以下命令:
vue-cli-service build --mode prod
会去加载.env.prod环境文件
打包成了dist文件夹
部署:
root下新建web目录,将dist下的内容上传上去
nginx部署
新建web.conf,内容如下:
server{
listen 80;
server_name item.ysboke.cn;
gzip on;
gzip_min_length 1k;
gzip_buffers 4 16k;
gzip_http_version 1.1;
gzip_comp_level 5;
gzip_types text/plain application/javascript application/x-javascript text/javascript text/css application/xml;
gzip_vary on;
gzip_proxied expired no-cache no-store private auth;
gzip_disable "MSIE [1-6]\.";
location / {
alias /www/web/;
index index.html;
try_files $uri $uri/ /index.html;
}
}
此时我们后端服务端口为8001,前端的请求base_url为域名:8001,因此这里只需对前端进行正向代理即可。
这里开启了gzip。