参考文章
https://blog.csdn.net/weixin_30561425/article/details/99526793
https://blog.csdn.net/qq_42715494/article/details/83582648
https://zhuanlan.zhihu.com/p/183877990
1.导出聊天记录
- 将红框选中的部分删除,只留下消息
2.将聊天记录导入到数据库中
- 创建一个maven项目
- 导入依赖包
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
</dependencies>
- 创建三个java文件
- QQChat.java
public class QQChat {
private String qqDate;
private String qqTime;
private String qqUser;
private String qqContent;
public String getQqDate() {
return qqDate;
}
public void setQqDate(String qqDate) {
this.qqDate = qqDate;
}
public String getQqTime() {
return qqTime;
}
public void setQqTime(String qqTime) {
this.qqTime = qqTime;
}
public String getQqUser() {
return qqUser;
}
public void setQqUser(String qqUser) {
this.qqUser = qqUser;
}
public String getQqContent() {
return qqContent;
}
public void setQqContent(String qqContent) {
this.qqContent = qqContent;
}
}
- DBUtil.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBUtil {
public static Connection getConnection() {
String username = "root";
String password = "root";
//qqchat需要改成你自己的数据库名称,上面的账户密码同样
String url = "jdbc:mysql://127.0.0.1:3306/qqchat?serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=utf-8&useSSL=false";
Connection con = null;
try {
con = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
public static void close(Connection con) {
try {
if(con!=null) con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(java.sql.PreparedStatement ps) {
try {
if(ps!=null) ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(ResultSet rs) {
try {
if(rs!=null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
- R2DB.java
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class R2DB {
public static void main(String[] args) {
//处理原始文档,处理后的文档存入999.txt中
//处理之前先把文档前面的说明性文字去掉
processTxt();
//读取原始文档并插入数据库
readAndInsert();
}
private static void readAndInsert() {
String sql = "";
try {
File file = new File("C:\\Users\\86155\\Desktop\\999.txt");
String str = null;
BufferedReader br = new BufferedReader(new FileReader(file));
QQChat qqChat = new QQChat();
List<QQChat> list = new ArrayList<QQChat>();
while ((str = br.readLine()) != null) {
String[] strs = str.split(" ");
qqChat.setQqDate(strs[0]);
qqChat.setQqTime(strs[1]);
qqChat.setQqUser(strs[2]);
/**
* 有的消息内容为空,拆分后数组的长度为3,对于这种消息,
* 设置它为未知消息
*/
if (strs.length==4) {
qqChat.setQqContent(strs[3]);
}else{
qqChat.setQqContent("未知消息");
}
list.add(qqChat);
qqChat = new QQChat();
}
Connection con = null;
PreparedStatement ps = null;
con = DBUtil.getConnection();
try {
for (QQChat q : list) {
sql = "insert into qq_record values('"
+ q.getQqDate() + "','" + q.getQqTime() + "','"
+ q.getQqUser() + "','" + q.getQqContent() + "');";
ps = con.prepareStatement(sql);
ps.executeUpdate();
}
System.out.println("插入成功!");
} catch (SQLException e) {
//如果遇到出错的插入语句,则输出,查看问题在哪里,直接解决即可
System.out.println(sql);
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
private static void processTxt() {
try {
//修改你自己txt文件的路径
File file = new File("C:\\Users\\86155\\Desktop\\lover.txt");
String str = null;
BufferedReader br = new BufferedReader(new FileReader(file));
PrintWriter out = new PrintWriter(new File("C:\\Users\\86155\\Desktop\\999.txt"));
while ((str = br.readLine()) != null) {
//这个正则表达式用来匹配2015-02-10 16:02:50 张三
//如果是导出与一个人的聊天记录就不必用正则,但是我要导出的是群聊,所以要用正则
Pattern pattern = Pattern
.compile("\\d{4}\\-\\d{2}\\-\\d{2}\\s\\d{2}\\:\\d{2}\\:\\d{2}\\s.+");
Matcher matcher = pattern.matcher(str);
if (matcher.matches()) {
//每次输出时间姓名那一行之前都先输出一个换行
out.println();
//将有的文本中的两个空格替换成一个
out.print(str.replace(" ", " ")+" ");
} else {
/**
* 将聊天正文中的空格去掉,同时,有的正文中有单引号,这个会导致在数据插入时
* 出现问题,所以把正文中所有的单引号换成双引号
*/
out.print(str.replace(" ", "").replace("'", "\""));
}
}
out.close();
System.out.println("OK!");
} catch (IOException e) {
e.printStackTrace();
}
}
}
- 项目目录如图所示
- 创建数据库,数据表
- 字段如下所示
- sql语句
DROP TABLE IF EXISTS `qq_record`;
CREATE TABLE `qq_record` (
`qqDate` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`qqTime` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`qqUser` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`qqContent` varchar(2550) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
- 在idea中连接数据库
- 开始运行
- 结果如图所示
3. 对导入的数据进行处理
- 查找长度大于50的消息
SELECT * FROM qq_record where length(qqContent)>50;
- 按时间升序 or 降序
# 时间字段名可以是qqDate,qqTime
SELECT * FROM qq_record ORDER BY 时间字段名 DESC; #降序
SELECT * FROM qq_record ORDER BY 时间字段名 ASC; #升序
SELECT * FROM qq_record ORDER BY 时间字段名 ; #升序
- 查找某一月份的消息数
SELECT count(*) FROM qq_record
WHERE STR_TO_DATE(qqDate ,'%Y-%m-%d')
BETWEEN STR_TO_DATE(起始时间, '%Y-%m-%d')AND STR_TO_DATE(结束时间, '%Y-%m-%d"')
ORDER BY STR_TO_DATE(qqDate, '%Y-%m-%d');
#示例: 查询一月份的数据
SELECT count(*) FROM qq_record
WHERE STR_TO_DATE(qqDate ,'%Y-%m-%d')
BETWEEN STR_TO_DATE('2021-1-1', '%Y-%m-%d')AND STR_TO_DATE('2021-1-31', '%Y-%m-%d"')
ORDER BY STR_TO_DATE(qqDate, '%Y-%m-%d');
- 将得到的数据生成柱状图 (下边的是python代码)
import numpy as np
import matplotlib.pyplot as plt
plt.style.use("ggplot")
x = ['1','2','3','4','5','6','7','8','9','10','11','12']
y = [1618 ,1373 ,1140 ,1134 ,1379 ,1426 ,2214 ,1879 ,1430 ,1695 ,1699 ,1873 ]
fig, ax = plt.subplots(figsize=(10, 7))
ax.bar(
x=x, # Matplotlib自动将非数值变量转化为x轴坐标
height=y, # 柱子高度,y轴坐标
width=0.6, # 柱子宽度,默认0.8,两根柱子中心的距离默认为1.0
align="center", # 柱子的对齐方式,'center' or 'edge'
color="blue", # 柱子颜色
edgecolor="red", # 柱子边框的颜色
linewidth=2.0 # 柱子边框线的大小
)
ax.set_title("hello world", fontsize=15)
# 一个常见的场景是:每根柱子上方添加数值标签
# 步骤:
# 1. 准备要添加的标签和坐标
# 2. 调用ax.annotate()将文本添加到图表
# 3. 调整样式,例如标签大小,颜色和对齐方式
xticks = ax.get_xticks()
for i in range(len(y)):
xy = (xticks[i], y[i] * 1.03)
s = str(y[i])
ax.annotate(
s=s, # 要添加的文本
xy=xy, # 将文本添加到哪个位置
fontsize=12, # 标签大小
color="grey", # 标签颜色
ha="center", # 水平对齐
va="baseline" # 垂直对齐
)
plt.show()
效果图如下:
- 查询某一个小时中的消息数
# 示例: 查询四点到五点的消息数
SELECT count(*) FROM qq_record
WHERE STR_TO_DATE(qqTime, '%H:%i:%s')
BETWEEN STR_TO_DATE('4:00:00', '%H:%i:%s') AND STR_TO_DATE('4:59:59', '%H:%i:%s')
ORDER BY STR_TO_DATE(qqTime, '%H:%i:%s');
本篇文章到这里就结束了,你可以利用上述技能制作一份只属于你和你女朋友的独一无二的QQ年终总结,相信你女朋友一定会很开心的。