项目要求

基于第一个项目爬虫爬取的数据（3-5个数据源），完成数据展示网站。
基本要求：
1、用户可注册登录网站，非注册用户不可登录查看数据
2、用户注册、登录、查询等操作记入数据库中的日志
3、实现查询词支持布尔表达式 （比如“新冠 AND 肺炎”或者“新冠 OR 肺炎”）
4、爬虫数据查询结果列表支持分页和排序
5、用Echarts或者D3实现3个以上的数据分析图表展示在网站中

实现注册与登录

对登录与注册提出的要求：

1、对用户要有适当提示

2、登录

（1）提示用户名或密码输错

（2）用户不存在

3、注册

（1）两次密码不一致

（2）用户已存在

（3）注册成功跳转登陆页面

创建用户信息数据表

CREATE TABLE `crawl`.`user`(`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,`username` VARCHAR(45) NOT NULL,`password` VARCHAR(45) NOT NULL,`registertime` datetime DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY(`id`),UNIQUE KEY `username_UNIQUE`(`username`))ENGINE=InnoDB DEFAULT CHARSET=utf8;```

记录用户的登陆，查询（具体查询语句）操作

CREATE TABLE `crawl`.`user`(`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,`username` VARCHAR(45) NOT NULL,`password` VARCHAR(45) NOT NULL,`registertime` datetime DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY(`id`),UNIQUE KEY `username_UNIQUE`(`username`))ENGINE=InnoDB DEFAULT CHARSET=utf8;```

建立连接mysql配置文件

module.exports = {mysql: {host: 'localhost',user: 'root',password: 'root'database: 'crawl'connectionLimit: 10//最大连接数，默认为10}
};

登录页的实现

检查用户所输入的账户密码是否与数据库中的相匹配，如果一致则登陆成功，页面跳转至news.html，否则无法跳转。

 <link rel="stylesheet" type="text/css" href="stylesheets/index.css"><script type="text/javascript" src="javascripts/index.js"></script><script>var app = angular.module('login', []);app.controller('loginCtrl', function ($scope, $http, $timeout) {// 登录时，检查用户输入的账户密码是否与数据库中的一致$scope.check_pwd = function () {var data = JSON.stringify({username: $scope.username,password: $scope.password});$http.post("/users/login", data).then(function (res) {if(res.data.msg=='ok') {window.location.href='/news.html';//如果登陆成功则页面跳转}else{$scope.msg=res.data.msg;}},function (err) {$scope.msg = err.data;});};

接着设置登陆页面的信息 

<form id="login-form" method="post" role="form" style="display: block;">
<!--                                登陆部分--><div class="form-group"><input ng-model="username" tabindex="1" class="form-control" placeholder="Username" value=""/></div><div class="form-group"><input type="password" ng-model="password" tabindex="2" class="form-control" placeholder="Password"></div><!--     <div class="form-group text-center">--><!--          <input type="checkbox" tabindex="3" class="" name="remember" id="remember">--><!--          <label for="remember"> Remember Me</label>--><!--     </div>--><div class="form-group"><div class="row"><div class="col-sm-6 col-sm-offset-3"><button id="login-submit" tabindex="4" class="form-control btn btn-login" ng-click="check_pwd()">LOG IN</button></div></div></div>
</form>

再设置登录页的路由

var express = require('express');
var router = express.Router();
var userDAO = require('../dao/userDAO');router.post('/login', function(req, res) {var username = req.body.username;var password = req.body.password;// var sess = req.session;userDAO.getByUsername(username, function (user) {if(user.length==0){res.json({msg:'用户不存在！请检查后输入'});}else {if(password===user[0].password){req.session['username'] = username;res.cookie('username', username);res.json({msg: 'ok'});// res.json({msg:'ok'});}else{res.json({msg:'用户名或密码错误！请检查后输入'});}}});
});

设置session信息，以确保知晓是哪个用户正在对日志进行操作

//设置session
app.use(session({secret: 'sessiontest',//与cookieParser中的一致resave: true,saveUninitialized: false, // 是否保存未初始化的会话cookie : {maxAge : 1000 * 60 * 60, // 设置 session 的有效时间，单位毫秒},
}));

其中调用的userDAO

var mysql = require('mysql');
var mysqlConf = require('../conf/mysqlConf');
var userSqlMap = require('./userSqlMap');
var pool = mysql.createPool(mysqlConf.mysql);
// 使用了连接池，重复使用数据库连接，而不必每执行一次CRUD操作就获取、释放一次数据库连接，从而提高了对数据库操作的性能。module.exports = {add: function (user, callback) {pool.query(userSqlMap.add, [user.username, user.password], function (error, result) {if (error) throw error;callback(result.affectedRows > 0);});},getByUsername: function (username, callback) {pool.query(userSqlMap.getByUsername, [username], function (error, result) {if (error) throw error;callback(result);});},};

于是便能得到登陆界面

注册页面的实现

首先检测用户输入的两次密码是否一致，若一致则能成功注册，否则注册失败。

//增加注册用户
$scope.doAdd = function () {// 检查用户注册时，输入的两次密码是否一致if($scope.add_password!==$scope.confirm_password){// $timeout(function () {//     $scope.msg = '两次密码不一致！';// },100);$scope.msg = '两次密码不一致！';}else {var data = JSON.stringify({username: $scope.add_username,password: $scope.add_password});$http.post("/users/register", data).then(function (res) {if(res.data.msg=='成功注册！请登录') {$scope.msg=res.data.msg;$timeout(function () {window.location.href='index.html';},2000);} else {$scope.msg = res.data.msg;}}, function (err) {$scope.msg = err.data;});}
};

设置注册页面的信息

<form id="register-form" method="post" role="form" style="display: none;"><div class="form-group"><input ng-model="add_username" tabindex="1" class="form-control" placeholder="Username" value=""/></div><div class="form-group"><input type="password" ng-model="add_password" tabindex="2" class="form-control" placeholder="Password"></div><div class="form-group"><input type="password" ng-model="confirm_password" tabindex="2" class="form-control" placeholder="Confirm Password"></div><div class="form-group"><div class="row"><div class="col-sm-6 col-sm-offset-3"><button tabindex="4" class="form-control btn btn-register" ng-click="doAdd()">Register Now</button></div></div></div></form>

设置注册页面的路由

/* add users */
router.post('/register', function (req, res) {var add_user = req.body;// 先检查用户是否存在userDAO.getByUsername(add_user.username, function (user) {if (user.length != 0) {// res.render('index', {msg:'用户不存在！'});res.json({msg: '用户已存在！'});}else {userDAO.add(add_user, function (success) {res.json({msg: '成功注册！请登录'});})}});});

退出登录

退出登录的页面设置

<li><a href="#" class="dropdown-toggle" data-toggle="dropdown">账号管理<span class="caret"></span></a><ul class="dropdown-menu"><li class="dropdown-header">账号</li><li><a ng-click="logout()">退出登录</a></li></ul></li>

设置路由

// 退出登录
router.get('/logout', function(req, res, next){// 备注：这里用的 session-file-store 在destroy 方法里，并没有销毁cookie// 所以客户端的 cookie 还是存在，导致的问题 --> 退出登陆后，服务端检测到cookie// 然后去查找对应的 session 文件，报错// session-file-store 本身的bugreq.session.destroy(function(err) {if(err){res.json('退出登录失败');return;}// req.session.loginUser = null;res.clearCookie('username');res.json({result:'/index.html'});});
});module.exports = router;

实现查询词支持布尔表达式

首先引入查询页面  

<div ng-include="'search.html'"></div>

处理查询页面后端

 // 查询数据$scope.search = function () {var title1 = $scope.title1;var title2 = $scope.title2;var selectTitle = $scope.selectTitle;var content1 = $scope.content1;var content2 = $scope.content2;var selectContent = $scope.selectContent;var sorttime = $scope.sorttime;// 检查用户传的参数是否有问题//用户有可能这样输入：___  and/or  新冠（直接把查询词输在了第二个位置）if(typeof title1=="undefined" && typeof title2!="undefined" && title2.length>0){title1 = title2;}if(typeof content1=="undefined" && typeof content2!="undefined" && content2.length>0){content1 = content2;}// 用户可能一个查询词都不输入，默认就是查找全部数据var myurl = `/news/search?t1=${title1}&ts=${selectTitle}&t2=${title2}&c1=${content1}&cs=${selectContent}&c2=${content2}&stime=${sorttime}`;$http.get(myurl).then(function (res) {if(res.data.message=='data'){$scope.isisshowresult = true; //显示表格查询结果// $scope.searchdata = res.data;$scope.initPageSort(res.data.result)}else {window.location.href=res.data.result;}},function (err) {$scope.msg = err.data;});
};

查询页路由

router.get('/search', function(request, response) {console.log(request.session['username']);//sql字符串和参数if (request.session['username']===undefined) {// response.redirect('/index.html')response.json({message:'url',result:'/index.html'});}else {var param = request.query;newsDAO.search(param,function (err, result, fields) {response.json({message:'data',result:result});})}
});

其中调用的newsDAO.search函数 

search :function(searchparam, callback) {// 组合查询条件var sql = 'select * from fetches ';if(searchparam["t2"]!="undefined"){sql +=(`where title like '%${searchparam["t1"]}%' ${searchparam['ts']} title like '%${searchparam["t2"]}%' `);}else if(searchparam["t1"]!="undefined"){sql +=(`where title like '%${searchparam["t1"]}%' `);};if(searchparam["t1"]=="undefined"&&searchparam["t2"]=="undefined"&&searchparam["c1"]!="undefined"){sql+='where ';}else if(searchparam["t1"]!="undefined"&&searchparam["c1"]!="undefined"){sql+='and ';}if(searchparam["c2"]!="undefined"){sql +=(`content like '%${searchparam["c1"]}%' ${searchparam['cs']} content like '%${searchparam["c2"]}%' `);}else if(searchparam["c1"]!="undefined"){sql +=(`content like '%${searchparam["c1"]}%' `);}if(searchparam['stime']!="undefined"){if(searchparam['stime']=="1"){sql+='ORDER BY publish_date ASC ';}else {sql+='ORDER BY publish_date DESC ';}}sql+=';';pool.getConnection(function(err, conn) {if (err) {callback(err, null, null);} else {conn.query(sql, function(qerr, vals, fields) {conn.release(); //释放连接callback(qerr, vals, fields); //事件驱动回调});}});
},
};

查询结果显示

<!--显示查询结果-->
<div ng-show="isisshowresult"><table class="table table-striped"><thead><tr><td>序号</td><td>标题</td><td>作者</td>
<!--                <td>内容</td>--><td>关键词</td><td>链接</td><td>发布时间</td></tr></thead><tbody><tr ng-repeat="(key, item) in items"><td>{{index+key}}</td><td>{{item.title}}</td><td>{{item.author}}</td>
<!--            <td>{{item.content}}</td>--><td>{{item.keywords}}</td><td>{{item.url}}</td><td>{{item.publish_date}}</td></tr></tbody></table>

实现爬虫数据查询结果列表支持分页 

设置前端

<div class="pull-right"><nav><ul class="pagination"><li><a ng-click="Previous()" role="button"><span role="button">上一页</span></a></li><li ng-repeat="page in pageList" ng-class="{active:isActivePage(page)}" role="button"><a ng-click="selectPage(page)" >{{ page }}</a></li><li><a ng-click="Next()" role="button"><span role="button">下一页</span></a></li></ul></nav>
</div>

 用angular写分页代码

// 分页$scope.initPageSort=function(item){$scope.pageSize=5;　　//每页显示的数据量，可以随意更改$scope.selPage = 1;$scope.data = item;$scope.pages = Math.ceil($scope.data.length / $scope.pageSize); //分页数$scope.pageList = [];//最多显示5页，后面6页之后不会全部列出页码来$scope.index = 1;// var page = 1;// for (var i = page; i < $scope.pages+1 && i < page+5; i++) {//     $scope.pageList.push(i);// }var len = $scope.pages> 5 ? 5:$scope.pages;$scope.pageList = Array.from({length: len}, (x,i) => i+1);//设置表格数据源(分页)$scope.items = $scope.data.slice(0, $scope.pageSize);};

 显示当前页面

//打印当前选中页$scope.selectPage = function (page) {//不能小于1大于最大（第一页不会有前一页，最后一页不会有后一页）if (page < 1 || page > $scope.pages) return;//最多显示分页数5，开始分页转换var pageList = [];if(page>2){for (var i = page-2; i <= $scope.pages && i < page+3; i++) {pageList.push(i);}}else {for (var i = page; i <= $scope.pages && i < page+5; i++) {pageList.push(i);}}$scope.index =(page-1)*$scope.pageSize+1;$scope.pageList = pageList;$scope.selPage = page;$scope.items = $scope.data.slice(($scope.pageSize * (page - 1)), (page * $scope.pageSize));//通过当前页数筛选出表格当前显示数据console.log("选择的页：" + page);};

 设置选中页面的样式

    //设置当前选中页样式$scope.isActivePage = function (page) {return $scope.selPage == page;};//上一页$scope.Previous = function () {$scope.selectPage($scope.selPage - 1);};//下一页$scope.Next = function () {$scope.selectPage($scope.selPage + 1);};

用echarts实现四个分析图表

饼状图

 $scope.pie = function () {$scope.isShow = false;$http.get("/news/pie").then(function (res) {if(res.data.message=='url'){window.location.href=res.data.result;}else {let newdata = [];var pattern = /责任编辑：(.+)/;//匹配名字res.data.result.forEach(function (element) {// "x":  责任编辑：李夏君 ,对x进行处理,只取 名字newdata.push({name: pattern.exec(element["x"])[1], value: element["y"]});});var myChart = echarts.init(document.getElementById('main1'));var app = {};option = null;// 指定图表的配置项和数据var option = {title: {text: '作者发布新闻数量',x: 'center'},tooltip: {trigger: 'item',formatter: "{a} <br/>{b} : {c} ({d}%)"},legend: {orient: 'vertical',left: 'left',// data: ['直接访问', '邮件营销', '联盟广告', '视频广告', '搜索引擎']},series: [{name: '访问来源',type: 'pie',radius: '55%',center: ['50%', '60%'],data: newdata,itemStyle: {emphasis: {shadowBlur: 10,shadowOffsetX: 0,shadowColor: 'rgba(0, 0, 0, 0.5)'}}}]};// myChart.setOption(option);app.currentIndex = -1;setInterval(function () {var dataLen = option.series[0].data.length;// 取消之前高亮的图形myChart.dispatchAction({type: 'downplay',seriesIndex: 0,dataIndex: app.currentIndex});app.currentIndex = (app.currentIndex + 1) % dataLen;// 高亮当前图形myChart.dispatchAction({type: 'highlight',seriesIndex: 0,dataIndex: app.currentIndex});// 显示 tooltipmyChart.dispatchAction({type: 'showTip',seriesIndex: 0,dataIndex: app.currentIndex});}, 1000);if (option && typeof option === "object") {myChart.setOption(option, true);};}});};

折线图

$scope.line = function () {$scope.isShow = false;$http.get("/news/line").then(function (res) {if(res.data.message=='url'){window.location.href=res.data.result;}else {var myChart = echarts.init(document.getElementById("main1"));option = {title: {text: '"疫情"该词在新闻中的出现次数随时间变化图'},xAxis: {type: 'category',data: Object.keys(res.data.result)},yAxis: {type: 'value'},series: [{data: Object.values(res.data.result),type: 'line',itemStyle: {normal: {label: {show: true}}}}],};if (option && typeof option === "object") {myChart.setOption(option, true);}}});};

柱状图

    $scope.histogram = function () {$scope.isShow = false;$http.get("/news/histogram").then(function (res) {if(res.data.message=='url'){window.location.href=res.data.result;}else {// var newdata = washdata(data);let xdata = [], ydata = [], newdata;var pattern = /\d{4}-(\d{2}-\d{2})/;res.data.result.forEach(function (element) {// "x":"2020-04-28T16:00:00.000Z" ,对x进行处理,只取 月日xdata.push(pattern.exec(element["x"])[1]);ydata.push(element["y"]);});newdata = {"xdata": xdata, "ydata": ydata};var myChart = echarts.init(document.getElementById('main1'));// 指定图表的配置项和数据var option = {title: {text: '新闻发布数 随时间变化'},tooltip: {},legend: {data: ['新闻发布数']},xAxis: {data: newdata["xdata"]},yAxis: {},series: [{name: '新闻数目',type: 'bar',data: newdata["ydata"]}]};// 使用刚指定的配置项和数据显示图表。myChart.setOption(option);}},function (err) {$scope.msg = err.data;});};

后端路由

router.get('/histogram', function(request, response) {//sql字符串和参数console.log(request.session['username']);//sql字符串和参数if (request.session['username']===undefined) {// response.redirect('/index.html')response.json({message:'url',result:'/index.html'});}else {var fetchSql = "select publish_date as x,count(publish_date) as y from fetches group by publish_date order by publish_date;";newsDAO.query_noparam(fetchSql, function (err, result, fields) {response.writeHead(200, {"Content-Type": "application/json","Cache-Control": "no-cache, no-store, must-revalidate","Pragma": "no-cache","Expires": 0});response.write(JSON.stringify({message:'data',result:result}));response.end();});}
});

图云

$scope.wordcloud = function () {$scope.isShow = false;$http.get("/news/wordcloud").then(function (res) {if(res.data.message=='url'){window.location.href=res.data.result;}else {var mainContainer = document.getElementById('main1');var chart = echarts.init(mainContainer);var data = [];for (var name in res.data.result) {data.push({name: name,value: Math.sqrt(res.data.result[name])})}var maskImage = new Image();maskImage.src = './images/logo.png';var option = {title: {text: '所有新闻内容 jieba分词 的词云展示'},series: [{type: 'wordCloud',sizeRange: [12, 60],rotationRange: [-90, 90],rotationStep: 45,gridSize: 2,shape: 'circle',maskImage: maskImage,drawOutOfBound: false,textStyle: {normal: {fontFamily: 'sans-serif',fontWeight: 'bold',// Color can be a callback function or a color stringcolor: function () {// Random colorreturn 'rgb(' + [Math.round(Math.random() * 160),Math.round(Math.random() * 160),Math.round(Math.random() * 160)].join(',') + ')';}},emphasis: {shadowBlur: 10,shadowColor: '#333'}},data: data}]};maskImage.onload = function () {// option.series[0].data = data;chart.clear();chart.setOption(option);};window.onresize = function () {chart.resize();};}});
}});

用户注册、登录、查询等操作记入数据库中的日志

app.use(logger(function (tokens, req, res) {console.log('打印的日志信息：');var request_time = new Date();var request_method = tokens.method(req, res);var request_url = tokens.url(req, res);var status = tokens.status(req, res);var remote_addr = tokens['remote-addr'](req, res);if(req.session){var username = req.session['username']||'notlogin';}else {var username = 'notlogin';}
// 直接将用户操作记入mysql中if(username!='notlogin'){logDAO.userlog([username,request_time,request_method,request_url,status,remote_addr], function (success) {console.log('成功保存！');})}

 数据库日志