0%

记录日志,改善生活

记录每天状态,改善些许习惯。

记录日志,改善生活


Google Sheets

疫情原因,封锁在家整日无所事事,拿 Google Sheets 拉了个清单,用于记录日常生活。

GoogleSheets

优点是和 Excel 相似,简单易用,数据多平台互通。

缺点也很明显:需要翻墙,加载速度不稳定;展示效果简单;修改记录过于随意;数据未标准化。

Table

返京之后,趁着居家隔离,便打算用 Java Web 自己实现记录日志的功能。开始 Web 项目之前,首先要把历史数据从 Google Sheets 写入到 MySQL 数据库。

根据需求,设计数据库表的结构如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE fact_dailylog(
id INT PRIMARY KEY AUTO_INCREMENT,
d_date DATE,
t_waketime TIMESTAMP,
t_bedtime TIMESTAMP,
vc_improvetime VARCHAR(8),
vc_improve VARCHAR(200),
vc_fishingtime VARCHAR(8),
vc_fishing VARCHAR(200),
vc_eurekatime VARCHAR(8),
vc_eureka VARCHAR(200),
vc_activitytime VARCHAR(8),
vc_activity VARCHAR(200),
vc_point VARCHAR(8),
t_updatetime TIMESTAMP,
vc_remark VARCHAR(200),
vc_backupfield2 VARCHAR(200)
);

在 Google Sheets 字段的基础上,主要的改变是新增了尤里卡时间(Eureka)——记录思维的火花,活动时间——督促自己多加锻炼,得分——综合学习时间/摸鱼时间/尤里卡时间/活动时间等行为,以打分形式对一天的表现进行量化。

GoogleSheetsToMySQL

数据库设计完成,开发一个小工具,用于将历史数据导入到 MySQL。

Google Sheets 可以直接导出为 .tsv 格式。用字符流读取文件,进行 split 后,辅以简单的处理便可插入到 fact_dailylog。核心代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
public class ProcessData {
public static void main(String[] args) throws IOException, SQLException, ParseException {
//字符流读取文件
BufferedReader bufferedReader = new BufferedReader(new FileReader(args[0]));

ArrayList<DailyLog> dailyLogs = new ArrayList<>();
String line;
String improveStr = "";
String fishingStr = "";
String waketime = "";
String bedtime = "";

//循环读取并处理每行数据
while ((line = bufferedReader.readLine()) != null) {
DailyLog dailyLog = new DailyLog();

String[] strArr = line.split("\t");
//接收以下四个字段,无需额外处理
dailyLog.setD_date(strArr[0]);
dailyLog.setT_waketime(strArr[1]);
dailyLog.setVc_backupfield1(strArr[4]);
dailyLog.setT_bedtime(strArr[5]);

//处理improve字段(该字段不为空),通过"h"分割获取「时长」与「说明」,并删去无用字符"-"
improveStr = strArr[2];
String[] improveArr = improveStr.split("h");
dailyLog.setVc_improvetime(improveArr[0]);
if (improveArr.length > 1) {
dailyLog.setVc_improve(improveArr[1].replace("-", ""));
} else {
dailyLog.setVc_improve("");
}

//处理fishing字段(该字段可能为空)
fishingStr = strArr[3];
if (fishingStr.length() > 0) {
String[] fishingArr = fishingStr.split("h");
//如果不为空,则必有「时长」,但「说明」可能为空
dailyLog.setVc_fishingtime(fishingArr[0]);
dailyLog.setVc_fishing((fishingArr[1] == null) ? "" : fishingArr[1].replace("-", ""));
} else {
dailyLog.setVc_fishingtime("0");
dailyLog.setVc_fishing("");
}
dailyLogs.add(dailyLog);
}
//关闭字符流
bufferedReader.close();

//获取数据库连接
Connection con = JDBCUtils.getConnection();
QueryRunner qr = new QueryRunner();

//记录处理条数
int i = 0;

//遍历集合数据,写入MySQL
for (DailyLog dailyLog : dailyLogs) {
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm");
SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");

//waketime的处理无需判断,使用日期拼接时间即可
waketime = dailyLog.getD_date() + " " + dailyLog.getT_waketime();

//bedtime需要判断是否过了0点
if (dailyLog.getT_bedtime().compareTo("12:00") < 0) {
Date date = sdf2.parse(dailyLog.getD_date());
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
calendar.add(Calendar.DAY_OF_MONTH, 1);
Date time = calendar.getTime();
String format = sdf2.format(time);
bedtime = format + " " + dailyLog.getT_bedtime();
} else {
bedtime = dailyLog.getD_date() + " " + dailyLog.getT_bedtime();
}

//获取更新时间
Date updatetime = new Date(System.currentTimeMillis());
//插入SQL
String sql = "insert into fact_dailylog(id, d_date,t_weaktime,t_bedtime,vc_improvetime,vc_improve,vc_fishingtime,vc_fishing,vc_eurekatime,vc_eureka,vc_activitytime,vc_activity,vc_point,vc_backupfield1,t_updatetime) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//更新处理条数
i += qr.update(con, sql, dailyLog.getId(), dailyLog.getD_date(), sdf1.parse(waketime), sdf1.parse(bedtime), dailyLog.getVc_improvetime(), dailyLog.getVc_improve(), dailyLog.getVc_fishingtime(), dailyLog.getVc_fishing(), dailyLog.getVc_eurekatime(), dailyLog.getVc_eureka(), dailyLog.getVc_activitytime(), dailyLog.getVc_activity(), dailyLog.getVc_point(), dailyLog.getVc_backupfield1(), updatetime);
}
System.out.println(i + "条结果被添加");
}
}

运行程序:

1
2
# java -jar GoogleSheetsToMySQL.jar 2020年初隔离日记_-_工作表.tsv
27条结果被添加

项目地址: GoogleSheetsToMySQL - GitHub

DailyLog_v1.0

数据库有了,数据也导入完毕,接下来就要考虑实现在前端展示数据,以及实现数据的增删改操作。

考虑到需求简单,以及对 Java Web 残存的理解有限,使用 Java Web 传统的三层架构进行开发,前端框架采用 bootstrap。

项目地址: DailyLog - GitHub

Dao

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
public class DailyLogDaoImpl implements DailyLogDao {

private DataSource dataSource = JDBCUtils.getDataSource();
QueryRunner qr = new QueryRunner(dataSource);

//查询
@Override
public List<DailyLog> query() throws SQLException {
String sql = "select id,d_date,t_waketime,t_bedtime,vc_improvetime,vc_improve,vc_fishingtime,vc_fishing,vc_eurekatime,vc_eureka,vc_activitytime,vc_activity,vc_point,vc_remark,t_updatetime from fact_dailylog order by d_date desc";
return qr.query(sql, new BeanListHandler<DailyLog>(DailyLog.class));
}

//添加
@Override
public void add(DailyLog dailyLog) throws ParseException, SQLException {
//获取更新时间
Date updatetime = new Date(System.currentTimeMillis());
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm");
//插入
String sql = "insert into fact_dailylog(id, d_date,t_waketime,t_bedtime,vc_improvetime,vc_improve,vc_fishingtime,vc_fishing,vc_eurekatime,vc_eureka,vc_activitytime,vc_activity,vc_point,vc_remark,t_updatetime) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
qr.update(sql, dailyLog.getId(), dailyLog.getD_date(), sdf1.parse(dailyLog.getT_waketime()), sdf1.parse(dailyLog.getT_bedtime()), dailyLog.getVc_improvetime(), dailyLog.getVc_improve(), dailyLog.getVc_fishingtime(), dailyLog.getVc_fishing(), dailyLog.getVc_eurekatime(), dailyLog.getVc_eureka(), dailyLog.getVc_activitytime(), dailyLog.getVc_activity(), dailyLog.getVc_point(), dailyLog.getVc_remark(), updatetime);
}

//删除
@Override
public void delete(int id) throws SQLException {
String sql = "delete from fact_dailylog where id = ?";
qr.update(sql, id);
}

//回显
@Override
public DailyLog queryById(int id) throws SQLException {
String sql = "select id,d_date,t_waketime,t_bedtime,vc_improvetime,vc_improve,vc_fishingtime,vc_fishing,vc_eurekatime,vc_eureka,vc_activitytime,vc_activity,vc_point,vc_remark,t_updatetime from fact_dailylog where id = ?";
return qr.query(sql, new BeanHandler<DailyLog>(DailyLog.class), id);
}

//修改
@Override
public void update(DailyLog dailyLog) throws ParseException, SQLException {
//获取更新时间
Date updatetime = new Date(System.currentTimeMillis());
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm");

String sql = "update fact_dailylog set d_date = ?, t_waketime = ?, t_bedtime = ?, vc_improvetime = ?, vc_improve = ?, vc_fishingtime = ?, vc_fishing = ?, vc_eurekatime = ?, vc_eureka = ?, vc_activitytime = ?, vc_activity = ?, vc_point = ?, vc_remark = ?, t_updatetime = ? where id = ?";
qr.update(sql, dailyLog.getD_date(), sdf1.parse(dailyLog.getT_waketime()), sdf1.parse(dailyLog.getT_bedtime()), dailyLog.getVc_improvetime(), dailyLog.getVc_improve(), dailyLog.getVc_fishingtime(), dailyLog.getVc_fishing(), dailyLog.getVc_eurekatime(), dailyLog.getVc_eureka(), dailyLog.getVc_activitytime(), dailyLog.getVc_activity(), dailyLog.getVc_point(), dailyLog.getVc_remark(), updatetime, dailyLog.getId());
}
}

Service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
public class DailyLogServiceImpl implements DailyLogService {
private DailyLogDaoImpl dailyLogDao = new DailyLogDaoImpl();

@Override
public List<DailyLog> queryDailyLog() throws SQLException {
List<DailyLog> list = dailyLogDao.query();

//截去2020-04-14 00:30:00.0 前面的2020-04-14 和最后的.0
for (DailyLog dailyLog : list) {
dailyLog.setT_waketime(dailyLog.getT_waketime().substring(11, 19));
dailyLog.setT_bedtime(dailyLog.getT_bedtime().substring(11, 19));
}
return list;
}

@Override
public void add(DailyLog dailyLog) throws ParseException, SQLException {
dailyLogDao.add(dailyLog);
}

@Override
public void delete(int id) throws SQLException {
dailyLogDao.delete(id);
}

@Override
public DailyLog queryById(int id) throws SQLException {
DailyLog dailyLog = dailyLogDao.queryById(id);

//截去2020-04-14 00:30:00.0 最后的.0
if (dailyLog.getT_waketime() != null) {
dailyLog.setT_waketime(dailyLog.getT_waketime().substring(0, 19));
}
if (dailyLog.getT_bedtime() != null) {
dailyLog.setT_bedtime(dailyLog.getT_bedtime().substring(0, 19));
}
return dailyLog;
}

@Override
public void update(DailyLog dailyLog) throws ParseException, SQLException {
dailyLogDao.update(dailyLog);
}
}

Web

查询

QueryServlet 代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
@WebServlet(name = "QueryServlet", urlPatterns = "/query")
public class QueryServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
try {
req.setCharacterEncoding("UTF-8");
resp.setContentType("text/html;charset=utf-8");

DailyLogServiceImpl dailyLogService = new DailyLogServiceImpl();
List<DailyLog> list = null;

list = dailyLogService.queryDailyLog();

req.setAttribute("list", list);
req.getRequestDispatcher("query.jsp").forward(req, resp);
} catch (SQLException e) {
e.printStackTrace();
}
}

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doPost(req, resp);
}
}

query.jsp 代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">

<link href="css/bootstrap.css" rel="stylesheet">
<script src="js/jquery-1.11.3.js"></script>
<script src="js/bootstrap.js"></script>
<style>
tr th {
text-align: center;
background-color: #e3e3e3;
}
</style>
</head>
<body>

<table class="table table-bordered table-hover">
<caption>
<h1>
<center>摸鱼日记</center>
</h1>
</caption>
<tr class="text-center">
<td colspan="14" align="center">
<a class="btn btn-primary" href="add.jsp">添加</a>
</td>
</tr>
<%--表头--%>
<tr class="text-center">
<th hidden>id</th>
<th>日期</th>
<th>起床时间</th>
<th>睡觉时间</th>
<th>学习(h)</th>
<th>学习详情</th>
<th>摸鱼(h)</th>
<th>摸鱼详情</th>
<th>尤里卡(h)</th>
<th>尤里卡详情</th>
<th>运动(h)</th>
<th>运动详情</th>
<th>得分</th>
<th>备注</th>
<th></th>
</tr>
<%--
<c:forEach item=${list} var="dailylog" >
<tr>一条记录 每个td是记录的属性值
<c:forEach>
--%>
<c:forEach items="${list}" var="dailylog">
<tr class="text-center">
<td hidden>${dailylog.id}</td>
<td>${dailylog.d_date}</td>
<td>${dailylog.t_waketime}</td>
<td>${dailylog.t_bedtime}</td>
<td>${dailylog.vc_improvetime}</td>
<td>${dailylog.vc_improve}</td>
<td>${dailylog.vc_fishingtime}</td>
<td>${dailylog.vc_fishing}</td>
<td>${dailylog.vc_eurekatime}</td>
<td>${dailylog.vc_eureka}</td>
<td>${dailylog.vc_activitytime}</td>
<td>${dailylog.vc_activity}</td>
<td>${dailylog.vc_point}</td>
<td>${dailylog.vc_remark}</td>
<td>
<a href="delete?id=${dailylog.id}" class="btn btn-danger">删除</a>
<a href="queryById?id=${dailylog.id}" class="btn btn-primary">修改</a>
</td>
</tr>
</c:forEach>

</table>

</body>
</html>

添加

AddServlet 代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
@WebServlet(name = "AddServlet", urlPatterns = "/add")
public class AddServlet extends HttpServlet {

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("UTF-8");
resp.setContentType("text/html;charset=utf-8");
DailyLog dailyLog = new DailyLog();

dailyLog.setD_date(req.getParameter("d_date"));
dailyLog.setT_waketime(req.getParameter("t_waketime"));
dailyLog.setT_bedtime(req.getParameter("t_bedtime"));
dailyLog.setVc_improvetime(req.getParameter("vc_improvetime"));
dailyLog.setVc_improve(req.getParameter("vc_improve"));
dailyLog.setVc_fishingtime(req.getParameter("vc_fishingtime"));
dailyLog.setVc_fishing(req.getParameter("vc_fishing"));
dailyLog.setVc_eurekatime(req.getParameter("vc_eurekatime"));
dailyLog.setVc_eureka(req.getParameter("vc_eureka"));
dailyLog.setVc_activitytime(req.getParameter("vc_activitytime"));
dailyLog.setVc_activity(req.getParameter("vc_activity"));
dailyLog.setVc_remark(req.getParameter("vc_remark"));

//校验起床时间和上床时间
if (dailyLog.getT_bedtime().compareTo(dailyLog.getT_waketime()) < 0) {
resp.getWriter().print("<script language='javascript'>alert('上床时间不能早于起床时间');history.go(-1);</script>");
} else {
DailyLogServiceImpl dailyLogService = new DailyLogServiceImpl();
try {
dailyLogService.add(dailyLog);
resp.getWriter().print("<script language='javascript'>alert('添加成功');window.location.href='query';</script>");
} catch (Exception e) {
e.printStackTrace();
resp.getWriter().print("<script language='javascript'>alert('添加失败');history.go(-1);</script>");
}
}

}
}

add.jsp 代码如下:

add.jsp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!-- HTML5文档-->
<!DOCTYPE html>
<!-- 网页使用的语言 -->
<html lang="zh-CN">
<head>
<!-- 指定字符集 -->
<meta charset="utf-8">
<!-- 使用Edge最新的浏览器的渲染方式 -->
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<!-- viewport视口:网页可以根据设置的宽度自动进行适配,在浏览器的内部虚拟一个容器,容器的宽度与设备的宽度相同。
width: 默认宽度与设备的宽度相同
initial-scale: 初始的缩放比,为1:1 -->
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>添加记录</title>

<!-- 1. 导入CSS的全局样式 -->
<link href="css/bootstrap.min.css" rel="stylesheet" media="screen">
<link href="css/bootstrap-datetimepicker.min.css" rel="stylesheet" media="screen">

</head>
<body>
<div class="container">
<center><h3>添加记录</h3></center>
<form action="add" method="post">
<%--日期-时间选择-html--%>
<div class="form-group">
<label for="d_date" class="col-md-1 control-label">日期</label>
<div class="input-group date form_date col-md-3" data-date="" data-date-format="yyyy-MM-dd"
data-link-field="dtp_input1" data-link-format="yyyy-mm-dd">
<input class="form-control" size="16" type="text" name="d_date" value="" readonly>
<span class="input-group-addon"><span class="glyphicon glyphicon-remove"></span></span>
<span class="input-group-addon"><span class="glyphicon glyphicon-calendar"></span></span>
</div>
<input type="hidden" id="d_date" value=""/><br/>
</div>

<div class="form-group">
<label for="t_waketime" class="col-md-1 control-label">起床时间</label>
<div class="input-group date form_time col-md-3" data-date="" data-date-format="yyyy-MM-dd hh:ii"
data-link-field="dtp_input2" data-link-format="hh:ii">
<input class="form-control" size="16" type="text" name="t_waketime" value="" readonly>
<span class="input-group-addon"><span class="glyphicon glyphicon-remove"></span></span>
<span class="input-group-addon"><span class="glyphicon glyphicon-time"></span></span>
</div>
<input type="hidden" id="t_waketime" value=""/><br/>
</div>

<div class="form-group">
<label for="t_bedtime" class="col-md-1 control-label">上床时间</label>
<div class="input-group date form_time col-md-3" data-date="" data-date-format="yyyy-MM-dd hh:ii"
data-link-field="dtp_input3" data-link-format="hh:ii">
<input class="form-control" size="16" type="text" name="t_bedtime" value="" readonly>
<span class="input-group-addon"><span class="glyphicon glyphicon-remove"></span></span>
<span class="input-group-addon"><span class="glyphicon glyphicon-time"></span></span>
</div>
<input type="hidden" id="t_bedtime" value=""/><br/>
</div>

<div class="form-group">
<label for="vc_improvetime">学习(h):</label>
<select name="vc_improvetime" class="form-control" id="vc_improvetime">
<option value="0">0小时</option>
<option value="1">1小时</option>
<option value="2">2小时</option>
<option value="3">3小时</option>
<option value="4">4小时</option>
<option value="5">5小时</option>
<option value="6">6小时</option>
<option value="7">6小时</option>
<option value="8">6小时</option>
</select>
</div>

<div class="form-group">
<label for="vc_improve">学习详情:</label>
<input type="text" class="form-control" id="vc_improve" name="vc_improve" placeholder="请罗列学习内容">
</div>

<div class="form-group">
<label for="vc_fishingtime">摸鱼(h):</label>
<select name="vc_fishingtime" class="form-control" id="vc_fishingtime">
<option value="0">0小时</option>
<option value="1">1小时</option>
<option value="2">2小时</option>
<option value="3">3小时</option>
<option value="4">4小时</option>
<option value="5">5小时</option>
<option value="6">6小时</option>
<option value="7">6小时</option>
<option value="8">6小时</option>
</select>
</div>

<div class="form-group">
<label for="vc_fishing">摸鱼详情:</label>
<input type="text" class="form-control" id="vc_fishing" name="vc_fishing" placeholder="请罗列摸鱼内容"/>
</div>

<div class="form-group">
<label for="vc_eurekatime">尤里卡(h):</label>
<select name="vc_eurekatime" class="form-control" id="vc_eurekatime">
<option value="0">0小时</option>
<option value="1">1小时</option>
<option value="2">2小时</option>
<option value="3">3小时</option>
<option value="4">4小时</option>
<option value="5">5小时</option>
<option value="6">6小时</option>
<option value="7">6小时</option>
<option value="8">6小时</option>
</select>
</div>

<div class="form-group">
<label for="vc_eureka">尤里卡详情:</label>
<input type="text" class="form-control" id="vc_eureka" name="vc_eureka" placeholder="请罗列尤里卡内容"/>
</div>

<div class="form-group">
<label for="vc_activitytime">活动(h):</label>
<select name="vc_activitytime" class="form-control" id="vc_activitytime">
<option value="0">0小时</option>
<option value="1">1小时</option>
<option value="2">2小时</option>
<option value="3">3小时</option>
<option value="4">4小时</option>
<option value="5">5小时</option>
<option value="6">6小时</option>
</select>
</div>

<div class="form-group">
<label for="vc_activity">活动详情:</label>
<input type="text" class="form-control" id="vc_activity" name="vc_activity" placeholder="请罗列活动内容"/>
</div>

<div class="form-group">
<label for="vc_remark">备注:</label>
<input type="text" class="form-control" id="vc_remark" name="vc_remark"/>
</div>

<div class="form-group" style="text-align: center">
<input class="btn btn-primary" type="submit" value="提交"/>
<input class="btn btn-default" type="reset" value="重置"/>
<input class="btn btn-default" type="button" onclick="javascript:history.back(-1);" value="返回"/>
</div>
</form>
</div>

<!-- 2. 导入jQuery,建议使用1.9以上的版本 -->
<script type="text/javascript" src="js/jquery-1.11.3.js" charset="UTF-8"></script>
<!-- 3. 导入bootstrap和bootstrap-datetimepicker的js文件 -->
<script type="text/javascript" src="js/bootstrap.min.js"></script>
<script type="text/javascript" src="js/bootstrap-datetimepicker.js" charset="UTF-8"></script>
<script type="text/javascript" src="js/bootstrap-datetimepicker.zh-CN.js" charset="UTF-8"></script>
<script type="text/javascript">
$('.form_datetime').datetimepicker({
//language: 'zh-CN',
weekStart: 1,
todayBtn: 1,
autoclose: 1,
todayHighlight: 1,
startView: 2,
forceParse: 0,
showMeridian: 1
});
$('.form_date').datetimepicker({
language: 'zh-CN',
weekStart: 1,
todayBtn: 1,
autoclose: 1,
todayHighlight: 1,
startView: 2,
minView: 2,
forceParse: 0
});
$('.form_time').datetimepicker({
language: 'zh-CN',
weekStart: 1,
todayBtn: 1,
autoclose: 1,
todayHighlight: 1,
startView: 1,
minView: 0,
maxView: 1,
forceParse: 0
});
</script>

</body>
</html>

其中日期/时间选择控件使用的是 bootstrap-datetimepicker,对于中文用户来说也有本地化适配,但中文化后月份也被汉化了,比如我想要 2020-04-15,得到的却是 2020-四月-15,在后面修改数据时影响数据回显,需要对 bootstrap-datetimepicker.zh-CN.js 进行修改:

bootstrap-datetimepicker.zh-CN.js
1
2
- months: ["一月", "二月", "三月", "四月", "五月", "六月", "七月", "八月", "九月", "十月", "十一月", "十二月"],
+ months: ["01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12"],

删除

DeleteServlet 代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@WebServlet(name = "DeleteServlet", urlPatterns = "/delete")
public class DeleteServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
resp.setContentType("text/html;charset=utf-8");

int id = Integer.parseInt(req.getParameter("id"));
DailyLogServiceImpl dailyLogService = new DailyLogServiceImpl();
try {
dailyLogService.delete(id);
resp.getWriter().print("<script language='javascript'>alert('删除成功');window.location.href='query';</script>");
} catch (SQLException e) {
e.printStackTrace();
resp.getWriter().print("<script language='javascript'>alert('删除失败');window.location.href='query';</script>");
}

}
}

修改

数据回显

QueryByIdServlet 代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@WebServlet(name = "QueryByIdServlet", urlPatterns = "/queryById")
public class QueryByIdServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
int id = Integer.parseInt(req.getParameter("id"));
DailyLogServiceImpl dailyLogService = new DailyLogServiceImpl();
DailyLog dailyLog = null;
try {
dailyLog = dailyLogService.queryById(id);
} catch (SQLException e) {
e.printStackTrace();
}
req.setAttribute("dailyLog", dailyLog);
req.getRequestDispatcher("/update.jsp").forward(req, resp);
}
}

update.jsp 代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!-- HTML5文档-->
<!DOCTYPE html>
<!-- 网页使用的语言 -->
<html lang="zh-CN">
<head>
<!-- 指定字符集 -->
<base href="<%=basePath%>"/>
<meta charset="utf-8">
<!-- 使用Edge最新的浏览器的渲染方式 -->
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<!-- viewport视口:网页可以根据设置的宽度自动进行适配,在浏览器的内部虚拟一个容器,容器的宽度与设备的宽度相同。
width: 默认宽度与设备的宽度相同
initial-scale: 初始的缩放比,为1:1 -->
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>修改记录</title>

<!-- 1. 导入CSS的全局样式 -->
<link href="css/bootstrap.min.css" rel="stylesheet" media="screen">
<link href="css/bootstrap-datetimepicker.min.css" rel="stylesheet" media="screen">


</head>
<body>
<div class="container">
<center><h3>修改记录</h3></center>
<form action="update" method="post">
<div class="form-group">
<label for="d_date" class="col-md-1 control-label">日期</label>
<!--设置一个隐藏域,保存联系id-->
<input type="hidden" name="id" value="${dailyLog.id}">
<div class="input-group date form_date col-md-3" data-date="" data-date-format="yyyy-MM-dd"
data-link-field="dtp_input2" data-link-format="yyyy-mm-dd">
<input class="form-control" size="16" type="text" name="d_date" value="${dailyLog.d_date}" readonly>
<span class="input-group-addon"><span class="glyphicon glyphicon-remove"></span></span>
<span class="input-group-addon"><span class="glyphicon glyphicon-calendar"></span></span>
</div>
<input type="hidden" id="d_date" value=""/><br/>
</div>

<div class="form-group">
<label for="t_waketime" class="col-md-1 control-label">起床时间</label>
<div class="input-group date form_time col-md-3" data-date="" data-date-format="yyyy-MM-dd hh:ii"
data-link-field="dtp_input3" data-link-format="hh:ii">
<input class="form-control" size="16" type="text" name="t_waketime" value="${dailyLog.t_waketime}" readonly>
<span class="input-group-addon"><span class="glyphicon glyphicon-remove"></span></span>
<span class="input-group-addon"><span class="glyphicon glyphicon-time"></span></span>
</div>
<input type="hidden" id="t_waketime" value=""/><br/>
</div>

<div class="form-group">
<label for="t_bedtime" class="col-md-1 control-label">上床时间</label>
<div class="input-group date form_time col-md-3" data-date="" data-date-format="yyyy-MM-dd hh:ii"
data-link-field="dtp_input3" data-link-format="hh:ii">
<input class="form-control" size="16" type="text" name="t_bedtime" value="${dailyLog.t_bedtime}" readonly>
<span class="input-group-addon"><span class="glyphicon glyphicon-remove"></span></span>
<span class="input-group-addon"><span class="glyphicon glyphicon-time"></span></span>
</div>
<input type="hidden" id="t_bedtime" value=""/><br/>
</div>

<div class="form-group">
<label for="vc_improvetime">学习(h):</label>
<select name="vc_improvetime" class="form-control" id="vc_improvetime">
<option value="0" <c:if test="${dailyLog.vc_improvetime == '0'}">selected="selected"</c:if>>0小时</option>
<option value="1" <c:if test="${dailyLog.vc_improvetime == '1'}">selected="selected"</c:if>>1小时</option>
<option value="2" <c:if test="${dailyLog.vc_improvetime == '2'}">selected="selected"</c:if>>2小时</option>
<option value="3" <c:if test="${dailyLog.vc_improvetime == '3'}">selected="selected"</c:if>>3小时</option>
<option value="4" <c:if test="${dailyLog.vc_improvetime == '4'}">selected="selected"</c:if>>4小时</option>
<option value="5" <c:if test="${dailyLog.vc_improvetime == '5'}">selected="selected"</c:if>>5小时</option>
<option value="6" <c:if test="${dailyLog.vc_improvetime == '6'}">selected="selected"</c:if>>6小时</option>
<option value="7" <c:if test="${dailyLog.vc_improvetime == '7'}">selected="selected"</c:if>>7小时</option>
<option value="8" <c:if test="${dailyLog.vc_improvetime == '8'}">selected="selected"</c:if>>8小时</option>
</select>
</div>

<div class="form-group">
<label for="vc_improve">学习详情:</label>
<input type="text" class="form-control" id="vc_improve" name="vc_improve" value="${dailyLog.vc_improve}" placeholder="请罗列学习内容">
</div>

<div class="form-group">
<label for="vc_fishingtime">摸鱼(h):</label>
<select name="vc_fishingtime" class="form-control" id="vc_fishingtime">
<option value="0" <c:if test="${dailyLog.vc_fishingtime == '0'}">selected="selected"</c:if>>0小时</option>
<option value="1" <c:if test="${dailyLog.vc_fishingtime == '1'}">selected="selected"</c:if>>1小时</option>
<option value="2" <c:if test="${dailyLog.vc_fishingtime == '2'}">selected="selected"</c:if>>2小时</option>
<option value="3" <c:if test="${dailyLog.vc_fishingtime == '3'}">selected="selected"</c:if>>3小时</option>
<option value="4" <c:if test="${dailyLog.vc_fishingtime == '4'}">selected="selected"</c:if>>4小时</option>
<option value="5" <c:if test="${dailyLog.vc_fishingtime == '5'}">selected="selected"</c:if>>5小时</option>
<option value="6" <c:if test="${dailyLog.vc_fishingtime == '6'}">selected="selected"</c:if>>6小时</option>
<option value="7" <c:if test="${dailyLog.vc_fishingtime == '7'}">selected="selected"</c:if>>7小时</option>
<option value="8" <c:if test="${dailyLog.vc_fishingtime == '8'}">selected="selected"</c:if>>8小时</option>
</select>
</div>

<div class="form-group">
<label for="vc_fishing">摸鱼详情:</label>
<input type="text" class="form-control" id="vc_fishing" name="vc_fishing" value="${dailyLog.vc_fishing}" placeholder="请罗列摸鱼内容"/>
</div>

<div class="form-group">
<label for="vc_eurekatime">尤里卡(h):</label>
<select name="vc_eurekatime" class="form-control" id="vc_eurekatime">
<option value="0" <c:if test="${dailyLog.vc_eurekatime == '0'}">selected="selected"</c:if>>0小时</option>
<option value="1" <c:if test="${dailyLog.vc_eurekatime == '1'}">selected="selected"</c:if>>1小时</option>
<option value="2" <c:if test="${dailyLog.vc_eurekatime == '2'}">selected="selected"</c:if>>2小时</option>
<option value="3" <c:if test="${dailyLog.vc_eurekatime == '3'}">selected="selected"</c:if>>3小时</option>
<option value="4" <c:if test="${dailyLog.vc_eurekatime == '4'}">selected="selected"</c:if>>4小时</option>
<option value="5" <c:if test="${dailyLog.vc_eurekatime == '5'}">selected="selected"</c:if>>5小时</option>
<option value="6" <c:if test="${dailyLog.vc_eurekatime == '6'}">selected="selected"</c:if>>6小时</option>
<option value="7" <c:if test="${dailyLog.vc_eurekatime == '7'}">selected="selected"</c:if>>7小时</option>
<option value="8" <c:if test="${dailyLog.vc_eurekatime == '8'}">selected="selected"</c:if>>8小时</option>
</select>
</div>

<div class="form-group">
<label for="vc_eureka">尤里卡详情:</label>
<input type="text" class="form-control" id="vc_eureka" name="vc_eureka" value="${dailyLog.vc_eureka}" placeholder="请罗列尤里卡内容"/>
</div>

<div class="form-group">
<label for="vc_activitytime">活动(h):</label>
<select name="vc_activitytime" class="form-control" id="vc_activitytime">
<option value="0" <c:if test="${dailyLog.vc_activitytime == '0'}">selected="selected"</c:if>>0小时</option>
<option value="1" <c:if test="${dailyLog.vc_activitytime == '1'}">selected="selected"</c:if>>1小时</option>
<option value="2" <c:if test="${dailyLog.vc_activitytime == '2'}">selected="selected"</c:if>>2小时</option>
<option value="3" <c:if test="${dailyLog.vc_activitytime == '3'}">selected="selected"</c:if>>3小时</option>
<option value="4" <c:if test="${dailyLog.vc_activitytime == '4'}">selected="selected"</c:if>>4小时</option>
<option value="5" <c:if test="${dailyLog.vc_activitytime == '5'}">selected="selected"</c:if>>5小时</option>
<option value="6" <c:if test="${dailyLog.vc_activitytime == '6'}">selected="selected"</c:if>>6小时</option>
</select>
</div>

<div class="form-group">
<label for="vc_activity">活动详情:</label>
<input type="text" class="form-control" id="vc_activity" name="vc_activity" value="${dailyLog.vc_activity}" placeholder="请罗列活动内容"/>
</div>

<div class="form-group">
<label for="vc_remark">备注:</label>
<input type="text" class="form-control" id="vc_remark" name="vc_remark" value="${dailyLog.vc_remark}"/>
</div>

<div class="form-group" style="text-align: center">
<input class="btn btn-primary" type="submit" value="提交"/>
<input class="btn btn-default" type="reset" value="重置"/>
<input class="btn btn-default" type="button" onclick="javascript:history.back(-1);" value="返回"/>
</div>
</form>
</div>

<!-- 2. 导入jQuery,建议使用1.9以上的版本 -->
<script type="text/javascript" src="js/jquery-1.11.3.js" charset="UTF-8"></script>
<!-- 3. 导入bootstrap和bootstrap-datetimepicker的js文件 -->
<script type="text/javascript" src="js/bootstrap.min.js"></script>
<script type="text/javascript" src="js/bootstrap-datetimepicker.js" charset="UTF-8"></script>
<script type="text/javascript" src="js/bootstrap-datetimepicker.zh-CN.js" charset="UTF-8"></script>
<script type="text/javascript">
$('.form_datetime').datetimepicker({
//language: 'zh-CN',
weekStart: 1,
todayBtn: 1,
autoclose: 1,
todayHighlight: 1,
startView: 2,
forceParse: 0,
showMeridian: 1
});
$('.form_date').datetimepicker({
language: 'zh-CN',
weekStart: 1,
todayBtn: 1,
autoclose: 1,
todayHighlight: 1,
startView: 2,
minView: 2,
forceParse: 0
});
$('.form_time').datetimepicker({
language: 'zh-CN',
weekStart: 1,
todayBtn: 1,
autoclose: 1,
todayHighlight: 1,
startView: 1,
minView: 0,
maxView: 1,
forceParse: 0
});
</script>

</body>
</html>

修改数据

UpdateServlet 的代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
@WebServlet(name = "UpdateServlet", urlPatterns = "/update")
public class UpdateServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("UTF-8");
resp.setContentType("text/html;charset=utf-8");
DailyLog dailyLog = new DailyLog();

dailyLog.setId(req.getParameter("id"));
dailyLog.setD_date(req.getParameter("d_date"));
dailyLog.setT_waketime(req.getParameter("t_waketime"));
dailyLog.setT_bedtime(req.getParameter("t_bedtime"));
dailyLog.setVc_improvetime(req.getParameter("vc_improvetime"));
dailyLog.setVc_improve(req.getParameter("vc_improve"));
dailyLog.setVc_fishingtime(req.getParameter("vc_fishingtime"));
dailyLog.setVc_fishing(req.getParameter("vc_fishing"));
dailyLog.setVc_eurekatime(req.getParameter("vc_eurekatime"));
dailyLog.setVc_eureka(req.getParameter("vc_eureka"));
dailyLog.setVc_activitytime(req.getParameter("vc_activitytime"));
dailyLog.setVc_activity(req.getParameter("vc_activity"));
dailyLog.setVc_remark(req.getParameter("vc_remark"));

//校验起床时间和上床时间
if (dailyLog.getT_bedtime().compareTo(dailyLog.getT_waketime()) < 0) {
resp.getWriter().print("<script language='javascript'>alert('上床时间不能早于起床时间');history.go(-1);</script>");
//window.location.href='add.jsp';
} else {
DailyLogServiceImpl dailyLogService = new DailyLogServiceImpl();
try {
dailyLogService.update(dailyLog);
resp.getWriter().print("<script language='javascript'>alert('修改成功');window.location.href='query';</script>");
} catch (Exception e) {
e.printStackTrace();
resp.getWriter().print("<script language='javascript'>alert('修改失败');history.go(-1);</script>");
}
}
}
}

页面效果展示

查询界面

摸鱼日记

修改界面

修改记录

部署

日志记录算是比较私密的东西,从最开始便是计划部署在本地电脑上。但考虑到并非每天都有时间打开电脑,长久下去,势必会影响记录。最终还是决定部署到 VPS 上。

首页

既然要上公网,直接丢一个查询页面不太合适,需要添加一个首页,一方面挽回点美工方面的不足,另一方面可以用来展示个人简介以及 Blog 和 GitHub 等的导航。

从 GitHub 找一个还不错的首页,在此基础上进行修改:

index.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
<!Doctype html>
<html>
<head>
<meta charset="utf-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<meta name="keywords" content="SANNAHA">
<link rel="icon" href="http://sannaha.moe/images/favicon-32x32.ico" type="image/x-icon">
<title>SANNAHA - Home page of SANNAHA</title>
<link rel="stylesheet" type="text/css" href="js/main.css"/>
</head>

<body>
<div class="vi">
<div class="sidebar">
<div class="header">
<h1>Bonjour</h1>
<div class="quote">
<p class="quote-text animate-init">
<span class="animate-init">这里是</span>
<span class="animate-init"> 誓願SANNAHA,</span>
<span class="animate-init"> MBTI 偏好</span>
<span class="animate-init"> INTJ.</span>
</p>
</div>
</div>
<div class="menu">
<p><a href="https://sannaha.moe" class="animate-init" rel="noopener noreferrer">Blog</a></p>
<p><a href="https://github.com/sannaha" class="animate-init" rel="external nofollow noopener noreferrer">Github</a></p>
<p><a href="query" class="animate-init" rel="external nofollow noopener noreferrer">DailyLog</a></p>
</div>
<div class="location">
<i class="location-icon"></i>
<span class="location-text animate-init">Beijing - China</span>
</div>
<div class="relocating">
Navigating to: <span class="relocate-location"></span>...
</div>
</div>

<div class="content">
<span class="close">close</span>
</div>
</div>
<canvas width="1920" height="949"></canvas>
<script type="text/javascript" src="js/index.js"></script>
<script>
$(document).ready(function () {
var delay = 1;
var DELAY_STEP = 200;
var animationOptions = { opacity: 1, top: 0};

$('h1').animate(animationOptions).promise()
.pipe(animateMain)
.pipe(animateLocationIcon);

function animateMain() {
var dfd = $.Deferred();
var els = $('.animate-init');
var size = els.size();

els.each(function (index, el) {
delay++;
$(el).delay(index * DELAY_STEP)
.animate(animationOptions);
(size - 1 === index) && dfd.resolve();
});
return dfd.promise();
}

function animateLocationIcon() {
$('.location-icon').delay(delay * DELAY_STEP).animate({
opacity: 1,
top: 0
}).promise().done(animationQuote);
}

function animationQuote() {}
});

document.addEventListener('touchmove', function (e) {
e.preventDefault()
})
var c = document.getElementsByTagName('canvas')[0],
x = c.getContext('2d'),
pr = window.devicePixelRatio || 1,
w = window.innerWidth,
h = window.innerHeight,
f = 90,
q,
m = Math,
r = 0,
u = m.PI*2,
v = m.cos,
z = m.random
c.width = w*pr
c.height = h*pr
x.scale(pr, pr)
x.globalAlpha = 0.6
function i(){
x.clearRect(0,0,w,h)
q=[{x:0,y:h*.7+f},{x:0,y:h*.7-f}]
while(q[1].x<w+f) d(q[0], q[1])
}
function d(i,j){
x.beginPath()
x.moveTo(i.x, i.y)
x.lineTo(j.x, j.y)
var k = j.x + (z()*2-0.25)*f,
n = y(j.y)
x.lineTo(k, n)
x.closePath()
r-=u/-50
x.fillStyle = '#'+(v(r)*127+128<<16 | v(r+u/3)*127+128<<8 | v(r+u/3*2)*127+128).toString(16)
x.fill()
q[0] = q[1]
q[1] = {x:k,y:n}
}
function y(p){
var t = p + (z()*2-1.1)*f
return (t>h||t<0) ? y(p) : t
}
document.onclick = i
document.ontouchstart = i
i()
</script>
</body>
</html>

效果展示

index

数据库迁移

导出本地数据库 dailylogdailylog.sql 文件中:

1
$ mysqldump -u用户名 -p密码 dailylog > dailylog.sql

登录到 VPS 上的 MySQL,将数据导入到数据库 dailylog

1
2
3
4
5
6
7
8
--创建数据库
mysql> create database dailylog CHARACTER SET utf8;

--使用该数据库
mysql> use dailylog;

--将数据导入到数据库
mysql> source dailylog.sql

子域名

使用 CloudFlare 可以方便地添加子域名。在已有根域名 sannaha.moe 的基础上添加一个子域名 dailylog.sannaha.moe

CloudFlare子域名配置

原计划在 VPS1 部署两个 Web 项目——Blog 和 DailyLog,通过 Nginx 反代 Tomcat 实现共用 80 端口,根据域名不同转发各自的服务:sannaha.moe 访问 Blog,dailylog.sannaha.moe 访问 DailyLog。但最后实现的效果不理想:访问 DailyLog 服务时显示在浏览器的是服务器 ip 而非域名。

最终还是将 DailyLog 项目部署在了 VPS2。

Tomcat

设置 Tomcat 开机自启动:

1
2
# vi /etc/rc.local
/usr/local/apache-tomcat-7.0.52/bin/startup.sh

DailyLog_v1.1

鉴权

如果不对访问者加以限制,那么所有游客都可以对日志记录进行增删改,因此需要鉴别访问者是否有这些权限。但如果制作登录系统,费时费力又不便于使用,那么有没有办法实现这一功能呢?

突然想到个邪道,可以直接拿访客的 IP 判别作为判断权限的依据。虽说自己的网络环境不是固定 IP,但有一样的 IP 是固定的,那就是 VPS 服务器!在 VPS 上搭建好代理服务器,就可以使用该固定 IP 去访问网页了。

获取原始IP

起初使用 req.getRemoteAddr() 去获取访客的 IP 地址,但无论是否开启代理,获取到的结果都是定位显示在“美国加利福尼亚洛杉矶”的一个 IP 段,推测是 Cloudflare 的 CDN 服务器的地址,而非访客的真实 IP 地址。参考网友提供的方案,通过 header 中的 x-forwarded-for 的值得到原始 IP 地址。如果使用了多级代理, x-forwarded-for 的值可能有多个,此时第一个非 unknown 的有效 IP 字符串就是真正的用户 IP。

注:x-forwarded-for 可以伪造,存在安全风险。

获取原始 IP 的工具类:

1
2
3
4
5
6
7
8
public class AuthenticateUtils {
public static String getRemoteIP(HttpServletRequest request) {
if (request.getHeader("x-forwarded-for") == null) {
return request.getRemoteAddr();
}
return request.getHeader("x-forwarded-for");
}
}

修改Servlet

修改 AddServlet、UpdateServlet 以及 DeleteServlet,添加鉴权判断:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
//获取真实IP
String remoteIP = AuthenticateUtils.getRemoteIP(req);
System.out.println("remoteIP:" + remoteIP);

boolean ipFlag = false;

//ip鉴权,从数据库获取有操作权限的ip的正则表达式
try {
List<String> ipRegexList = dailyLogService.queryIpPool();
for (String ipRegex : ipRegexList) {
if (remoteIP != null && remoteIP.matches(ipRegex)) {
ipFlag = true;
}
}
} catch (SQLException e) {
e.printStackTrace();
}

if (ipFlag) {
//原来的处理逻辑
} else {
resp.getWriter().print("<script language='javascript'>alert('您没有操作权限!');window.location.href='query';</script>");
}

设计数据表

创建数据表,写入 IP 过滤规则:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table di_ippool(
id int PRIMARY KEY AUTO_INCREMENT,
vc_ipregex varchar(20),
primary key (id)
);

--注意在insert时会对\进行转义,因此需要\\
mysql> insert into di_ippool (vc_ipregex) values ('110\\.110\\.110\\.\\d{2,3}');
mysql> select * from di_ippool;
+----+---------------------------+
| id | vc_ipregex |
+----+---------------------------+
| 1 | 110\.110\.110\.\d{2,3} |
+----+---------------------------+

这样符合 110.110.110.* 的用户就可以进行增删改操作。

DailyLog_v1.2

删除前需确认

点击删除按钮后,需在弹窗中确认后,才会执行删除操作:

删除前需确认

修改 query.jsp,添加 confirmDel()

1
2
3
4
5
6
7
<script type="text/javascript">
function confirmDel(param) {
if (window.confirm("您确定要删除该条记录吗?")) {
document.location = "delete?id=" + param
}
}
</script>

修改 query.jsp,删除按钮添加 onclick 事件:

1
2
- <a href="delete?id=${dailylog.id}" class="btn btn-danger">删除</a>
+ <a class="btn btn-danger" onclick="confirmDel(${dailylog.id})">删除</a>

限制显示字符长度

行的数据较多,如果不对每列显示的内容加以字数限制,会严重影响观感。限制每列最多展示 14 个字符,多余内容显示为省略号,鼠标悬停可查看完整内容:

详情列限制显示字符长度

修改 query.jsp,添加样式:

1
2
3
4
5
6
7
8
<style type="text/css">
.ellipsis {
overflow: hidden; /*隐藏文字*/
text-overflow: ellipsis; /*文字隐藏后添加省略号*/
white-space: nowrap; /*强制不换行*/
width: 14em; /*限制14字符*/
}
</style>

修改 query.jsp,「详情列」和「备注列」引用样式:

1
2
- <td>${dailylog.vc_remark}</td>
+ <div class="ellipsis" title="${dailylog.vc_remark}">${dailylog.vc_remark}</div>

DailyLog_v1.3

数据可视化

得分数据

得分数据通过 MySQL 进行计算,每天凌晨 4 点定时执行并写入数据库。

设计得分算法

得分数据来自三部分:

  • 学习 + 尤里卡:以学习和尤里卡的总时间进行计算,满 6 小时得 50 分。
  • 摸鱼:以摸鱼时间进行计算,在 1.5 小时得到最高分 20,超出 4 小时转为负分,超出 4 小时不得分(得分数据可视化面积堆叠图不能有负值)。
  • 运动:以运动步数进行计算,满 6000 步得 30 分。

根据得分算法绘制函数图像如下:
得分函数图像

生成得分数据

生成得分数据:

updatePoint.sql
1
2
3
4
use dailylog;
update fact_dailylog a,(SELECT id,CAST(vc_improvetime AS UNSIGNED) int_improvetime,CAST(`vc_eurekatime` AS UNSIGNED) int_eurekatime,
CAST(`vc_fishingtime` AS UNSIGNED) int_fishingtime,CAST(`vc_activity` AS UNSIGNED) int_activity FROM fact_dailylog) b
set a.vc_point=(b.int_improvetime+b.int_eurekatime)*50/6+20-3.2*(b.int_fishingtime-1.5)*(b.int_fishingtime-1.5)+b.int_activity/200 where a.id=b.id;

通过 crontab 设置定时任务,每天凌晨 4 点计算得分:

1
0 4 * * * mysql -u用户名 -p密码 < /data/updatePoint.sql

查询得分数据

修改 DailyLogDaoImpl,查询得分数据:

1
2
3
4
5
6
//查询得分
@Override
public List<Point> showPoint() throws SQLException {
String sql = "select d_date,vc_point from (select d_date,vc_point from fact_dailylog order by d_date desc limit 30) t1 order by d_date;";
return qr.query(sql, new BeanListHandler<Point>(Point.class));
}

添加 ShowPointServlet:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@WebServlet(name = "ShowPointServlet", urlPatterns = "/showPoint")
public class ShowPointServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doPost(req, resp);
}

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("UTF-8");
resp.setContentType("text/html;charset=utf-8");

try {
DailyLogServiceImpl dailyLogService = new DailyLogServiceImpl();

List<Point> pointList = dailyLogService.showPoint();
ObjectMapper om = new ObjectMapper();
String pointJson = om.writeValueAsString(pointList);
resp.getWriter().write(pointJson);
} catch (SQLException e) {
e.printStackTrace();
}
}
}

睡眠数据

睡眠数据在 Java 代码中计算,不写入数据库。

修改 DailyLogDaoImpl,查询睡眠时长数据:

1
2
3
4
5
6
//查询睡眠时长
@Override
public List<Sleep> showSleep() throws SQLException {
String sql = "select d_date,vc_sleepTime from ( select t1.d_date,round(time_to_sec(timediff(t1.t_waketime,t2.t_bedtime))/3600,1) vc_sleepTime from fact_dailylog t1 join ( SELECT DATE_add(d_date,INTERVAL '1' DAY) d_date_add,t_bedtime FROM fact_dailylog ) t2 on t1.d_date=t2.d_date_add order by t1.d_date desc limit 7) t3 order by d_date;";
return qr.query(sql, new BeanListHandler<Sleep>(Sleep.class));
}

修改 DailyLogServiceImpl,将隐私的睡眠时长数据转换为睡眠质量数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
@Override
public List<SleepQuality> showSleep() throws SQLException {
int goodTimes = 0;
int fairTimes = 0;
int poorTimes = 0;
List<SleepQuality> sleepQualityList = new ArrayList<SleepQuality>();

List<Sleep> sleepList = dailyLogDao.showSleep();

for (Sleep sleep : sleepList) {
if (sleep.getVc_sleepTime() >= 7) {
goodTimes++;
} else if (sleep.getVc_sleepTime() <= 5) {
poorTimes++;
} else {
fairTimes++;
}
}

sleepQualityList.add(new SleepQuality("优", goodTimes));
sleepQualityList.add(new SleepQuality("良", fairTimes));
sleepQualityList.add(new SleepQuality("差", poorTimes));

return sleepQualityList;
}

添加 ShowSleepQualityServlet:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
@WebServlet(name = "ShowSleepQualityServlet", urlPatterns = "/showSleepQuality")
public class ShowSleepQualityServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doPost(req, resp);
}

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("UTF-8");
resp.setContentType("text/html;charset=utf-8");

try {
DailyLogServiceImpl dailyLogService = new DailyLogServiceImpl();

List<SleepQuality> sleepQualityList = dailyLogService.showSleep();

ObjectMapper om = new ObjectMapper();
String sleepQualityJson = om.writeValueAsString(sleepQualityList);
resp.getWriter().write(sleepQualityJson);
} catch (SQLException e) {
e.printStackTrace();
}
}
}

统计数据展示页面

创建 show.html,使用 Ajax 异步获取数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
<!DOCTYPE html>
<html>

<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1" />
<meta name="keywords" content="SANNAHA">
<link rel="icon" href="http://sannaha.moe/images/favicon-32x32.ico" type="image/x-icon">
<title>统计数据展示</title>
<style type="text/css">

/* 大屏幕 :大于等于1200px*/
@media (min-width: 1200px) {
.custom {
width: 1000px;
height: 600px;
margin: auto;
padding: 10px 0 10px 0;
top: 10px;
}
}

/*默认*/
@media (min-width: 980px){
.custom {
width: 800px;
height: 450px;
margin: auto;
padding: 10px 0 10px 0;
top: 10px;
}
}

/* 平板电脑和小屏电脑之间的分辨率 */
@media (min-width: 768px) and (max-width: 979px) {
.custom {
width: 800px;
height: 600px;
margin: auto;
padding: 10px 0 10px 0;
top: 10px;
}
}

/* 横向放置的手机和竖向放置的平板之间的分辨率 */
@media (max-width: 767px) {
.custom {
width: 700px;
height: 600px;
margin: auto;
padding: 10px 0 10px 0;
top: 10px;
}
}

/* 横向放置的手机及分辨率更小的设备 */
@media (max-width: 480px) {
.custom {
width: 450px;
height: 400px;
margin: auto;
padding: 10px 0 10px 0;
top: 10px;
}
}
</style>
</head>
<script src="js/echarts.min.js"></script>
<script src="jquery/jquery-1.11.3.js"></script>
<body>
<div class="custom" id="point"></div>
<div class="custom" id="sleep"></div>
<script type="text/javascript">
var pointChart = echarts.init(document.getElementById('point'));
var sleepChart = echarts.init(document.getElementById('sleep'));

pointChart.setOption({
title: {
text: '得分统计',
subtext: '(近30天)'
},
tooltip: {
trigger: 'axis',
formatter: '{b}<br/>{a} : {c}'
},
xAxis: {
data: []
},
toolbox: {
left: 'center',
feature: {
restore: {},
saveAsImage: {}
}
},
dataZoom: [{
startValue: '2020-03-23'
}, {
type: 'inside'
}],
visualMap: {
top: 10,
right: 10,
pieces: [{
gt: 80,
color: '#096'
}, {
gt: 60,
lte: 80,
color: '#ffde33'
}, {
gt: 30,
lte: 60,
color: '#ff9933'
}, {
lte: 30,
color: '#960328'
}],
outOfRange: {
color: '#999'
}
},
yAxis: {
splitLine: {
show: false
}
},
series: [{
name: '得分',
type: 'line',
data: [],
markLine: {
silent: true,
data: [{
yAxis: 30
}, {
yAxis: 60
}, {
yAxis: 80
}, {
yAxis: 100
}]
}
}]
});

pointChart.showLoading();
var pointDates = [];
var points = [];
var pointStartDate = '';

$.ajax({
type: "post",
async: true,
url: "showPoint",
data: {},
dataType: "json",
success: function (result) {
if (result) {
pointStartDate = result[result.length - 7].d_date;
for (var i = 0; i < result.length; i++) {
pointDates.push(result[i].d_date);
points.push(result[i].vc_point);
}
pointChart.hideLoading();
pointChart.setOption({
dataZoom: [{
startValue: pointStartDate
}],
xAxis: {
data: pointDates
},
series: [{
data: points
}]
});
}
}
});

sleepChart.setOption({
title: {
text: '睡眠质量',
subtext: '(近7天)'
},
toolbox: {
left: 'center',
feature: {
restore: {},
saveAsImage: {}
}
},
tooltip: {
trigger: 'item',
formatter: '{a}<br/>{b} : {c} ({d}%)'
},
legend: {
orient: 'vertical',
left: 'right',
data: []
},
series: [
{
name: '睡眠质量',
type: 'pie',
radius: '75%',
center: ['50%', '60%'],
data: [],
emphasis: {
itemStyle: {
shadowBlur: 10,
shadowOffsetX: 0,
shadowColor: 'rgba(0, 0, 0, 0.5)'
}
}
}
]
});

sleepChart.showLoading();
var qualities = [];
var sleepData = [];

$.ajax({
type: "post",
async: true,
url: "showSleepQuality",
data: {},
dataType: "json",
success: function (result) {
if (result) {
for (var i = 0; i < result.length; i++) {
qualities.push(result[i].quality);
var obj = {};
obj.name = result[i].quality;
obj.value = result[i].times;
sleepData.push(obj);
}
sleepChart.hideLoading();
sleepChart.setOption({
legend: {
data: qualities
},
series: [{
data: sleepData
}]
});
}
}
});
</script>
</body>

</html>

数据可视化展示效果:

数据可视化展示效果

DailyLog_v1.4

DarkMode

利用 css 中的 prefers-color-scheme,检测到系统开启深色模式,主题更换为 Dark 模式,支持 Windows 10 / Android 10 / iOS 13 上的多款浏览器:

1
2
3
4
5
6
@media (prefers-color-scheme: dark) {
body {
background-color: #333;
color: #aaa;
}
}

由于数据展示页面中的 ECharts 无法自动切换主题,需要添加单独的按钮手动切换“深色”与“浅色”模式。

HTML 部分:

1
2
3
4
<div align="center">
<button class="button switchLight" onclick="setLight()">浅色</button>
<button class="button switchDark" onclick="setDark()">深色</button>
</div>

js 部分:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
// 切换为浅色
function setLight() {
// 重新初始化为 echarts 为浅色模式
pointChart.dispose();
pointChart = echarts.init(document.getElementById('point'), 'light');
pointChart.setOption(pointOption);
sleepChart.dispose();
sleepChart = echarts.init(document.getElementById('sleep'), 'light');
sleepChart.setOption(sleepOption);
// 修改 body 背景颜色
document.body.style.backgroundColor="#fff";
// 重新加载数据
$.ajax({...});
$.ajax({...});
}

// 切换为深色
function setDark() {
// 重新初始化为 echarts 为深色模式
pointChart.dispose();
pointChart = echarts.init(document.getElementById('point'), 'dark');
pointChart.setOption(pointOption);
sleepChart.dispose();
sleepChart = echarts.init(document.getElementById('sleep'), 'dark');
sleepChart.setOption(sleepOption);
// 修改 body 背景颜色
document.body.style.backgroundColor="#333";
// 重新加载数据
$.ajax({...});
$.ajax({...});
}

定时任务

计算得分

updatePoint.sql
1
2
3
4
use dailylog;
update fact_dailylog a,(SELECT id,CAST(vc_improvetime AS UNSIGNED) int_improvetime,CAST(`vc_eurekatime` AS UNSIGNED) int_eurekatime,
CAST(`vc_fishingtime` AS UNSIGNED) int_fishingtime,CAST(`vc_activity` AS UNSIGNED) int_activity FROM fact_dailylog) b
set a.vc_point=(b.int_improvetime+b.int_eurekatime)*50/6+20-3.2*(b.int_fishingtime-1.5)*(b.int_fishingtime-1.5)+b.int_activity/200 where a.id=b.id;

备份数据库

backup_database.sh
1
2
3
4
5
6
7
8
9
10
#!/bin/bash
baseDir="/export/dump_data/";
cd "$baseDir";
echo "开始备份数据库";
echo `mysqldump -u用户名 -p密码 --single-transaction dailylog > dailylog_$(date +%Y%m%d_%H%M%S).sql`;
echo "备份数据完成";
oldDate=`date --date='8 day ago' +%Y%m%d`;
#删除当前日期-8的备份
echo `rm -f dailylog_$oldDate*`;
echo "删除$oldDate的备份成功"

设置定时任务

通过 crontab -e 设置定时任务,每天凌晨 4 点计算得分,每天凌晨 5 点备份数据库:

1
2
0 4 * * * mysql -u用户名 -p密码 < /export/script/updatePoint.sql
0 5 * * * /export/script/backup_database.sh

查看 crontab 执行日志:

1
2
tail -1000f /var/log/cron | grep backup_database.sh
tail -1000f /var/log/cron | grep updatePoint.sql

Issue

MySQL相关问题

安装MySQL

MySQL 5.7 在 CentOS7 上的安装与配置过程如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 添加MySQL5.7仓库
$ sudo rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

# 确认Mysql仓库成功添加
$ sudo yum repolist all | grep mysql | grep enabled
mysql-connectors-community/x86_64 MySQL Connectors Community enabled: 51
mysql-tools-community/x86_64 MySQL Tools Community enabled: 63
mysql57-community/x86_64 MySQL 5.7 Community Server enabled: 404

# 安装MySQL5.7
$ yum -y install mysql-community-server

# 启动MySQL
$ systemctl start mysqld
# 设置开机启动
$ systemctl enable mysqld
# 查看状态
$ systemctl status mysqld

# 查看root默认密码
$ cat /var/log/mysqld.log | grep -i 'temporary password'
2019-05-31T08:28:50.245578Z 1 [Note] A temporary password is generated for [email protected]: ABCdef<g9_8h

# 运行安全设置脚本,可以完成设置root密码,移除匿名用户,禁止root用户远程连接等
$ mysql_secure_installation

# 重启MySQL
$ systemctl restart mysqld
# 关闭MySQL
$ systemctl stop mysqld

内存不足导致启动失败

在我的 VPS 上启动 MySQL 时报错:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$ systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

$ systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: failed (Result: start-limit) since Wed 2020-04-15 10:55:17 CST; 49s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 7365 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 7348 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

Apr 15 10:55:17 site systemd[1]: Failed to start MySQL Server.
Apr 15 10:55:17 site systemd[1]: Unit mysqld.service entered failed state.
Apr 15 10:55:17 site systemd[1]: mysqld.service failed.
Apr 15 10:55:17 site systemd[1]: mysqld.service holdoff time over, scheduling restart.
Apr 15 10:55:17 site systemd[1]: Stopped MySQL Server.
Apr 15 10:55:17 site systemd[1]: start request repeated too quickly for mysqld.service
Apr 15 10:55:17 site systemd[1]: Failed to start MySQL Server.
Apr 15 10:55:17 site systemd[1]: Unit mysqld.service entered failed state.
Apr 15 10:55:17 site systemd[1]: mysqld.service failed.

从以上信息中并没能找到启动失败的真正原因。尝试从 MySQL 的日志中找线索,发现以下这些错误信息:

1
2
3
4
5
6
7
8
$ tail -f /var/log/mysqld.log
2020-04-15T04:23:40.375928Z 0 [ERROR] InnoDB: mmap(68714496 bytes) failed; errno 12
2020-04-15T04:23:40.375937Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2020-04-15T04:23:40.375942Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-04-15T04:23:40.375956Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-04-15T04:23:40.375962Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-04-15T04:23:40.375967Z 0 [ERROR] Failed to initialize builtin plugins.
2020-04-15T04:23:40.375970Z 0 [ERROR] Aborting

内存不足,导致分配 InnoDB 存储引擎缓冲池内存失败了。

当前 VPS 的内存只有 512 MB,在运行着 Blog 网站的情况下,查看下可用内存,只有 114 MB 空闲:

1
2
3
4
$ free -m
total used free shared buff/cache available
Mem: 481 243 114 28 123 176
Swap: 0 0 0

通过 my.cnf 调整 innodb_buffer_pool_size 的值,设置为 32M

/etc/my.cnf
1
2
3
4
5
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
innodb_buffer_pool_size = 32M

重启 mysql 服务,启动成功!此时再查看可用内存,只有 4 MB 空闲:

1
2
3
4
$ free -m
total used free shared buff/cache available
Mem: 481 394 4 28 81 25
Swap: 0 0 0

登进 MySQL 数据库,尝试使用 source 命令导入数据,提示 mysql 服务挂掉,看来内存资源还是太紧张了,最终放弃在该 VPS 上运行 MySQL 服务。最终的解决方案是通过新开一台内存充足的 VPS 运行 MySQL 服务以及整个 DailyLog 项目。

数据库备份报错

使用 mysqldump 创建备份时报错:

mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES

使用 --single-transaction 解决该问题:

1
$ mysqldump -u用户名 -p密码 dailylog --single-transaction dailylog > dailylog.sql

导入备份数据

1
$ mysql -u用户名 -p密码 dailylog < dailylog_20201103_083934.sql 

更新得分报错

1
2
3
4
5
update fact_dailylog a,(SELECT id,CAST(vc_improvetime AS UNSIGNED) int_improvetime,CAST(`vc_eurekatime` AS UNSIGNED) int_eurekatime,
CAST(`vc_fishingtime` AS UNSIGNED) int_fishingtime,CAST(`vc_activity` AS UNSIGNED) int_activity FROM fact_dailylog) b
set a.vc_point=(b.int_improvetime+b.int_eurekatime)*50/6+20-3.2*(b.int_fishingtime-1.5)*(b.int_fishingtime-1.5)+b.int_activity/200 where a.id=b.id;

ERROR 1292 (22007): Truncated incorrect INTEGER value: ''

降低密码强度要求

为 MySQL 数据库创建用户时,提示密码强度不符合要求:

1
2
mysql> CREATE USER 'dailylog'@'localhost' IDENTIFIED BY 'password';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

查看密码强度要求:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select @@validate_password_policy;
+----------------------------+
| @@validate_password_policy |
+----------------------------+
| MEDIUM |
+----------------------------+

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+

说明:

  • validate_password_dictionary_file:插件用于验证密码强度的字典文件路径。
  • validate_password_length:密码最小长度,参数默认为 8,它有最小值的限制,最小值为 validate_password_number_count + validate_password_special_char_count + (2 * validate_password_mixed_case_count)
  • validate_password_mixed_case_count:密码至少要包含的小写字母个数和大写字母个数。
  • validate_password_number_count:密码至少要包含的数字个数。
  • validate_password_special_char_count:密码至少要包含的特殊字符数。
  • validate_password_policy:密码强度检查等级,默认为 MEDIUM,要求密码必须符合长度,且必须含有数字,小写或大写字母,特殊字符。检查等级有以下取值:
Policy Tests Performed
0 or LOW Length
1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters
2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file

授权问题

迁移前的数据库为 dailylog 用户创建的 dailylog 数据库,包含 fact_dailylogdi_ippool 两张表。迁移时错误地将数据恢复到了 root 用户创建的 dailylog 数据库中。尝试通过将两张表授权给 dailylog 用户来解决:

1
2
3
--将dailylog数据库中两张表的所有操作权限授权给dailylog用户
mysql> grant all on dailylog.di_ippool to dailylog@localhost;
mysql> grant all on dailylog.fact_dailylog to dailylog@localhost;

对所有数据库中的所有表进行授权和撤销授权:

1
2
3
4
--授权所有数据库的所有表的所有权限给dailylog用户
grant all on *.* to dailylog@localhost;
--撤销授权
revoke all on *.* from dailylog@localhost;

定时任务脚本 backup_database.sh 在执行时报错,提示没有操作权限,决定删除 root 用户创建的 dailylog 数据库,重新由 dailylog 用户创建。

时区问题

数据库迁移完成后,发现查询出来的时间有问题,不难看出是出现了 8 个小时的时差,需要对 Linux 系统以及 MySQL 数据库设置时区。

  1. 修改 MySQL 时区:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 在mysql查看时区设置,系统默认使用的是CST,mysql时区使用系统时区
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.56 sec)

-- 查看mysql当前时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-04-15 04:01:48 |
+---------------------+
1 row in set (0.20 sec)

-- 修改mysql全局时区为东8区
mysql> set global time_zone = '+8:00';
-- 修改当前会话时区为东8区
mysql> set time_zone = '+8:00';
mysql> flush privileges;
  1. 修改 Linux 系统时区:
1
2
3
4
5
6
7
# 查看Linux系统时区
$ date -R
Wed, 15 Apr 2020 04:00:13 +0000

# 修改Linux系统时区
$ cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
cp: overwrite ‘/etc/localtime’? y

全部修改完成后,检查时差问题是否还存在:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 查看mysql时区设置
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | UTC |
| time_zone | +08:00 |
+------------------+--------+

-- 查询数据,发现时间恢复正常
mysql> select t_waketime,t_bedtime from fact_dailylog;
+---------------------+---------------------+
| t_waketime | t_bedtime |
+---------------------+---------------------+
| 2020-02-22 08:30:00 | 2020-02-23 01:00:00 |
| 2020-02-23 09:20:00 | 2020-02-24 01:00:00 |
| 2020-02-24 10:30:00 | 2020-02-24 22:00:00 |
| 2020-02-25 09:10:00 | 2020-02-26 00:30:00 |
| 2020-02-26 09:00:00 | 2020-02-27 01:00:00 |
| 2020-02-27 09:30:00 | 2020-02-28 01:00:00 |
| 2020-02-28 10:30:00 | 2020-02-29 02:00:00 |
| 2020-02-29 10:30:00 | 2020-03-01 02:00:00 |
| 2020-03-01 10:00:00 | 2020-03-02 01:30:00 |
+---------------------+---------------------+

记录数据更新时间

在执行 sql 脚本更新得分后发现 t_waketime 字段的值全部被修改为当前时间,检查发现是错误地将 t_waketime 字段设置成了自动更新时间的字段,而非 t_updatetime 字段:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select d_date,t_waketime,t_bedtime,t_updatetime from fact_dailylog;
+------------+---------------------+---------------------+---------------------+
| d_date | t_waketime | t_bedtime | t_updatetime |
+------------+---------------------+---------------------+---------------------+
| 2020-04-24 | 2020-05-04 18:40:28 | 2020-04-25 00:39:00 | 2020-04-25 00:39:24 |
| 2020-04-25 | 2020-05-04 18:40:28 | 2020-04-26 02:00:00 | 2020-05-01 09:12:16 |
| 2020-04-26 | 2020-05-04 18:40:28 | 2020-04-27 01:00:00 | 2020-05-01 09:12:30 |
| 2020-04-27 | 2020-05-04 18:40:28 | 2020-04-28 00:00:00 | 2020-05-01 09:12:44 |
| 2020-04-28 | 2020-05-04 18:40:28 | 2020-04-29 00:00:00 | 2020-05-01 09:14:35 |
| 2020-04-29 | 2020-05-04 18:40:28 | 2020-04-30 02:30:00 | 2020-05-01 09:24:32 |
+------------+---------------------+---------------------+---------------------+

mysql> desc fact_dailylog;
+-----------------+--------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| d_date | date | YES | | NULL | |
| t_waketime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| t_bedtime | timestamp | NO | | 0000-00-00 00:00:00 | |
| vc_improvetime | varchar(8) | YES | | NULL | |
| vc_improve | varchar(200) | YES | | NULL | |
| vc_fishingtime | varchar(8) | YES | | NULL | |
| vc_fishing | varchar(200) | YES | | NULL | |
| vc_eurekatime | varchar(8) | YES | | NULL | |
| vc_eureka | varchar(200) | YES | | NULL | |
| vc_activitytime | varchar(8) | YES | | NULL | |
| vc_activity | varchar(200) | YES | | NULL | |
| vc_point | varchar(8) | YES | | NULL | |
| t_updatetime | timestamp | NO | | 0000-00-00 00:00:00 | |
| vc_remark | varchar(200) | YES | | NULL | |
| vc_backupfield2 | varchar(200) | YES | | NULL | |
+-----------------+--------------+------+-----+---------------------+-----------------------------+

取消与添加自动更新时间:

1
2
3
4
5
-- 取消自动更新时间
alter table fact_dailylog change t_waketime t_waketime timestamp;

-- 添加自动更新时间,在发生update时触发
alter table fact_dailylog MODIFY t_updatetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

由于没有设置数据备份以及 binlog 日志,对于被修改的数据,只能靠饿了吗订单时间进行人工追溯。同时开启 binlog 日志,以防类似事件发生。

binlog日志

binlog 是 MySQL sever 层维护的一种二进制日志,与 innodb 引擎中的 redo/undo log 是完全不同的日志;其主要是用来记录对 mysql 数据更新或潜在发生更新的 SQL 语句,并以”事务”的形式保存在磁盘中。

作用主要有:

  • 复制:MySQL Replication 在 Master 端开启 binlog,Master 把它的二进制日志传递给 slaves 并回放来达到 master-slave 数据一致的目的
  • 数据恢复:通过 mysqlbinlog 工具恢复数据
  • 增量备份

开启binlog日志

修改 my.cnf 配置文件,开启 binlog 日志:

/etc/my.cnf
1
2
3
[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin

重启 mysql 服务:

1
service mysqld restart

登录 mysql,查看配置是否生效:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 查看是否开启binlog
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+

-- 查看binlog文件列表
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2931 |
+------------------+-----------+

-- 查看binlog的状态。可查看当前二进制日志文件的状态信息,显示正在写入的二进制文件,及当前position
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 2931 | | |
+------------------+----------+--------------+------------------+

查看binlog日志

默认情况下 binlog 日志是二进制格式,无法直接查看。可使用 MySQL 官方提供的 mysqlbinlog 进行查看:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
$ mysqlbinlog /var/lib/mysql/mysql-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 2006
#200504 21:12:29 server id 1 end_log_pos 2442 Query thread_id=17 exec_time=0 error_code=0
SET TIMESTAMP=1588597949/*!*/;
SET @@session.sql_mode=2097152/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
update fact_dailylog set d_date = '2020-05-04', t_waketime = '2020-05-04 11:30:00', t_bedtime = '2020-05-04 19:01:00', vc_improvetime = '0', vc_improve = '', vc_fishingtime = '0', vc_fishing = '', vc_eurekatime = '4', vc_eureka = '', vc_activitytime = '0', vc_activity = '', vc_point = null, vc_remark = '', t_updatetime = '2020-05-04 21:12:29' where id = '106'
/*!*/;
# at 2442
#200504 21:37:49 server id 1 end_log_pos 2470 Intvar
SET INSERT_ID=107/*!*/;
# at 2470
#200504 21:37:49 server id 1 end_log_pos 2931 Query thread_id=19 exec_time=0 error_code=0
SET TIMESTAMP=1588599469/*!*/;
insert into fact_dailylog(id, d_date,t_waketime,t_bedtime,vc_improvetime,vc_improve,vc_fishingtime,vc_fishing,vc_eurekatime,vc_eureka,vc_activitytime,vc_activity,vc_point,vc_remark,t_updatetime) values (null,'2020-05-05','2020-05-05 06:30:00','2020-05-05 23:00:00','1','学习测试','2','摸鱼测试','3','尤里卡测试','4','活动测试',null,'备注测试','2020-05-04 21:37:49')
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET [email protected]_COMPLETION_TYPE*/;

通过binlog恢复

恢复,就是让 MySQL 将保存在 binlog 日志中指定段落区间的 SQL 语句重新执行一次。

导出 SQL 语句:

1
mysqlbinlog --start-position=2470 --stop-position=2931 /var/lib/mysql/mysql-bin.000001 > /export/data/recover.sql

删除最新 insert 的那条数据,然后执行 SQL 脚本便可恢复:

1
mysql> source /export/data/recover.sql

资源文件加载问题

跨域导致

添加记录页面能够正常显示,修改记录页面就出现了问题。F12 可以看到修改页面的 css 样式文件和 js 脚本文件未能成功加载。

修改功能无法加载资源文件

添加页面正常加载资源文件

两个页面文件引用的是同一套样式和脚本,但因为使用 Cloudflare 提供的 SSL/TLS 加密导致了奇怪的问题。两个页面加载资源时使用的协议不同,使用 https 的可以加载,而使用 http 的则不行。

将页面文件中的资源地址从相对位置修改为固定的 URL,除了字体文件无法加载外已经可以正常使用了。

加载字体失败

字体文件是在 bootstrap.min.css 中以相对位置引入的,可以修改路径的相对位置:

1
2
3
4
5
- url('../bootstrap/fonts/glyphicons-halflings-regular.woff') format('woff'),
+ url('./glyphicons-halflings-regular.woff') format('woff'),

- url('../bootstrap/fonts/glyphicons-halflings-regular.ttf') format('truetype'),
+ url('./glyphicons-halflings-regular.ttf') format('truetype'),

修改后字体文件仍然加载失败,但资源确实是存在的,应该是跨域问题——页面属于 dailylog.sannaha.moe,资源属于 sannaha.moe

最终的解决方案是将资源存放在 dailylog.sannaha.moe 下。

Nginx代理导致

因为服务器使用 Nginx 部署了新项目 合成大P家 ,需要对 DailyLog 项目使用的 Tomcat 进行反向代理。

由于反向代理的配置问题,资源文件的地址被错误地指向了 http://127.0.0.1,无法正常加载。

Nginx反向代理导致文件无法正常加载

Nginx 需要作出如下修改:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
server {
listen 80 default_server;
listen [::]:80 default_server;
server_name thinklong.me;

location / {
- proxy_pass http://127.0.0.1:8080;
+ proxy_pass http://thinklong.me:8080;
}

error_page 404 /404.html;
location = /404.html {
}

error_page 500 502 503 504 /50x.html;
location = /50x.html {
}
}

注:此时 DailyLog 项目已被移至 thinklong.me 域名下。

IP鉴权问题

修复 Nginx 代理导致的资源文件加载问题后,发现会带来另一个问题:IP 鉴权功能失效。观察 Tomcat 日志,发现访问时 remoteIP 由原来的 110.110.110.110 变为了 110.110.110.110,110.110.110.110,因此无法与数据库中允许的 IP 列表匹配,需要在 di_ippool 表中插入新的匹配规则:

1
mysql> insert into di_ippool values(1, '110\.110\.110\.110,110\.110\.110\.110');

经过转义后插入到 di_ippool 表中的实际数据为 110.110.110.110,110.110.110.110,可以使用该规则正则匹配新 remoteIP

小结

由于对访客 IP 的认知有误,以及对正则表达式的不熟悉,IP 鉴权这一部分的功能在设计上就存在以下问题:

  • 起初误将 remoteAddr 认定为访客的 IP,以为自己虽然没有固定 IP,但 IP 的变化是在一定范围内,想要通过正则表达式来匹配自己的 IP。然而它其实是 CDN 的 IP,并且会在一定时间间隔后变更到其他网段。
  • IP 鉴权中使用了 matches() 方法来检测传入的 remoteIP 是否符合数据库存储的正则表达式,此处更好的解决方案是使用 equals() 方法判断传入的 remoteIP 是否与数据库存储的 IP 相等。即便使用 matches() 方法来检测,在判断某一具体字符串是否符合正则表达式时,正则表达式的写法应与该字符串一致,具体到该案例便是应将正则表达式写为 110.110.110.110,而非之前的 110\.110\.110\.110。发现的契机是 110.110.110.110,110.110.110.110 无法被正则表达式 110\.110\.110\.110,110\.110\.110\.110 识别。

页面自适应问题

统计数据展示页面在 PC 端和手机端会有不同的显示效果,需要根据分辨率不同调整 css 样式。

  1. 添加 viewport 元标签:
1
<meta name="viewport" content="width=device-width, initial-scale=1" />
  1. 根据分辨率调整 css 样式:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
<style type="text/css">
/* 大屏幕 :大于等于1200px*/
@media (min-width: 1200px) {
.custom {
width: 1000px;
height: 600px;
margin: auto;
padding: 10px 0 10px 0;
top: 10px;
}
}

/*默认*/
@media (min-width: 980px){
.custom {
width: 800px;
height: 450px;
margin: auto;
padding: 10px 0 10px 0;
top: 10px;
}
}

/* 平板电脑和小屏电脑之间的分辨率 */
@media (min-width: 768px) and (max-width: 979px) {
.custom {
width: 800px;
height: 600px;
margin: auto;
padding: 10px 0 10px 0;
top: 10px;
}
}

/* 横向放置的手机和竖向放置的平板之间的分辨率 */
@media (max-width: 767px) {
.custom {
width: 700px;
height: 600px;
margin: auto;
padding: 10px 0 10px 0;
top: 10px;
}
}

/* 横向放置的手机及分辨率更小的设备 */
@media (max-width: 480px) {
.custom {
width: 450px;
height: 400px;
margin: auto;
padding: 10px 0 10px 0;
top: 10px;
}
}
</style>

区分 CSS 分辨率和设备分辨率

我们需要处理两种分辨率:一个是真正的设备屏幕分辨率,另一个便是 CSS 可测量的分辨率。

在过去,这两种分辨率本质上是一致的。在现在,越来越多的手机、平板,甚至桌面端都采用了高分辨率的屏幕。CSS 分辨率和设备分辨率开始出现认知上的差异。

CSS 分辨率是在 CSS 样式中用来测量的单位,屏幕设备分辨率是实际的屏幕像素的数量。除了两种分辨率的不同,还有密度显示比(DPR)——屏幕分辨率 / CSS 分辨率,如:

  • iPhone 11,每个 CSS 像素有 2 个设备像素,即 DPR 是 2x
  • iPhone 11 Pro,每个 CSS 像素有 3 个设备像素,DPR 为 3x
  • 三星 Galaxy S10,每个 CSS 像素有 4 个设备像素,DPR 为 4x

三星 Galaxy S10 的分辨率:

  • 设备分辨率:1440px × 3040px
  • CSS 分辨率:360px × 760px
  • 密度显示比:4x

为什么 Web 会出现两种不同的分辨率?答案其实是不得不这么做。

我们继续以 Galaxy S10 为例,它的屏幕分辨率为 1440px × 3040px。这个分辨率大小已经超过了部分桌面版的屏幕分辨率,而却被塞入非常小的设备中。如果不区分 CSS 分辨率和设备分辨率,桌面端的网站在手机上显示就会展现真实的宽度,我们就没有办法来创造手机和电脑不同样式的响应式网站。

可以通过 yesviz 查询 CSS 分辨率。

Tips

Linux用户、密码与授权

1
2
3
4
5
6
7
8
9
10
11
12
13
# 创建dailylog用户,并设置密码
$ adduser dailylog

# 给予dailylog用户sudo权限
$ vi /etc/sudoers
root ALL=(ALL) ALL
dailylog ALL=(ALL) ALL

# 修改root用户密码
$ passwd

# 修改普通用户(dailylog)密码
$ passwd dailylog

MySQL用户、密码与授权

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 忘记root用户密码
-- 1.修改my.cnf,添加skip-grant-tables
# vi /etc/my.cnf
[mysqld]
skip-grant-tables
-- 2.重启mysql服务
systemctl restart mysqld.service
-- 3.登录mysql,无需输入密码
# mysql -uroot -p
-- 4.修改用户密码
mysql> update mysql.user set authentication_string=password('newpassword') where user='root' and Host = 'localhost';
-- 5.刷新权限并退出
mysql> flush privileges;
mysql> exit
-- 6.删除my.cnf中的skip-grant-tables,重启mysql服务后,即可用新密码进行登录

-- 创建新用户
mysql> create user dailylog identified by 'password';
-- 在root用户下执行授权,将dailylogDb数据库下的所有表的所有权限授予dailylog用户,允许其在host为‘ipaddress’的网络中使用密码'password'进行访问
mysql> grant all privileges on dailylogDb.* to [email protected]'ipaddress' identified by 'password';
-- 刷新权限
mysql> flush privileges;

参考资料
GoogleSheetsToMySQL - GitHub
DailyLog - GitHub
MySQL在低内存机器下启动失败
用Java来获取访问者真实的IP地址
jsp:超出字数显示省略号;鼠标悬停显示完整文本
腾讯工程师带你深入解析 MySQL binlog
ECharts官方实例
Echarts通过Ajax实现动态数据加载
区分CSS分辨率和设备分辨率

  • 本文作者: SANNAHA
  • 本文链接: https://sannaha.moe/DailyLog/
  • 版权声明: 本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!