艳辉网V3.0之登录系统

最新推荐文章于 2018-11-22 14:19:36 发布

艳学网

最新推荐文章于 2018-11-22 14:19:36 发布

阅读量1k

点赞数

分类专栏：我的项目文章标签：报表邮箱

本文链接：https://blog.csdn.net/sinat_15153911/article/details/54909578

版权

我的项目专栏收录该内容

36 篇文章 0 订阅

订阅专栏

1、前言

不是真的登录验证，而是假装登录，然后根据登录的数据进行分析汇总成报表。

2、技术

1、使用java读取txt的内容，获取QQ号和邮箱地址。
2、使用echart做用户粘度报表，用户活跃度报表，用户恶意登录报表。

3、实现

1、获取QQ群里的QQ号

if(!lineTxt.equals("")){
                        if(lineTxt.startsWith("(")){
                            lineTxt = lineTxt.replace("(", "");
                            lineTxt = lineTxt.replace(")", "");
                            qqNum = lineTxt;

                            Connection con = dbUtil.getConnection(); // 获取连接
                            String sql = "insert into qqnum values(null,?,?,now(),?,1)";
                            PreparedStatement pstmt = con.prepareStatement(sql);
                            pstmt.setString(1, qqName);
                            pstmt.setString(2, qqNum);
                            pstmt.setString(3, search);
                            int result = pstmt.executeUpdate();
                            dbUtil.closeResources(con, pstmt, null);
                            if(result==1){
                                System.out.println("添加"+qqNum+"成功！");
                            }else{
                                System.out.println("添加"+qqNum+"失败！");
                            }
                        }else{
                            qqName = lineTxt;
                        }
                    }

2、获取文本中的邮箱地址

String txt=lineTxt;
                     Matcher matchr = emailer.matcher(txt);  
                        while (matchr.find()) {  
                            String email = matchr.group();
                            if(email.contains("@qq.com")){
                                String qq = email.replace("@qq.com", "");
//                              System.out.println(qq); 
                                Connection con = dbUtil.getConnection(); // 获取连接
                                String sql = "insert into qqnum values(null,null,?,now(),?,1)";
                                PreparedStatement pstmt = con.prepareStatement(sql);
                                pstmt.setString(1, qq);
                                pstmt.setString(2, search);
                                int result = pstmt.executeUpdate();
                                dbUtil.closeResources(con, pstmt, null);
                                if(result==1){
                                    System.out.println("添加"+qq+"成功！");
                                }else{
                                    System.out.println("添加"+qq+"失败！");
                                }
                            }
                        }

1、用户粘度报表sql语句

SELECT u.username,count(log.id) as num FROM loginlog log
LEFT JOIN user u on u.id = log.userId
where log.type = 0 and status = 1
GROUP BY u.username
ORDER BY num DESC

2、用户活跃度报表sql语句

select * FROM
(SELECT u.username,count(log.id) as num FROM loginlog log
LEFT JOIN user u on u.id = log.userId
where log.type = 0 and status = 1
and date_format(log.createTime,'%Y-%m')=date_format(now(),'%Y-%m')   
GROUP BY u.username
ORDER BY num DESC ) A
where A.num > 15

3、用户恶意登录报表sql语句

select * FROM
(SELECT u.username,count(log.id) as num FROM loginlog log
LEFT JOIN user u on u.id = log.userId
where log.type = 0 and status = 0
and date_format(log.createTime,'%Y-%m')=date_format(now(),'%Y-%m')   
GROUP BY u.username
ORDER BY num DESC ) A
where A.num > 6