1.导出聊天记录参考文章
https://blog.csdn.net/weixin_30561425/article/details/99526793
https://blog.csdn.net/qq_42715494/article/details/83582648
https://zhuanlan.zhihu.com/p/183877990
- 将红框选中的部分删除,只留下消息
- 创建一个maven项目导入依赖包
mysql mysql-connector-java8.0.23
- 创建三个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(); Listlist = new ArrayList (); while ((str = br.readLine()) != null) { String[] strs = str.split(" "); qqChat.setQqDate(strs[0]); qqChat.setQqTime(strs[1]); qqChat.setQqUser(strs[2]); 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中连接数据库
- 开始运行
- 结果如图所示
查找长度大于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年终总结,相信你女朋友一定会很开心的。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)