实验要求
实验过程
1.在mysql中新建两个表，用来记录用户信息和操作日志。
记录用户信息的表user：
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;记录操作日志的表user_action：
CREATE TABLE `crawl`.`user_action` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(45) NOT NULL,
`request_time` VARCHAR(45) NOT NULL,
`request_method` VARCHAR(20) NOT NULL,
`request_url` VARCHAR(300) NOT NULL,
`status` int(4),
`remote_addr` VARCHAR(100) NOT NULL,
PRIMARY KEY (`id`))
ENGINE=InnoDB DEFAULT CHARSET=utf8;建立连接mysql配置文件 mysqlConf.js
module.exports = {
mysql: {
host: 'localhost',
user: 'root',
password: 'root'
database: 'crawl'
connectionLimit: 10
//最大连接数，默认为10
}
};2.实现用户注册和登录
引入前端框架angular
<script src="/angular/angular.min.js"></script>引入自己的样式与js
<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;
}；
};
//增加注册用户
$scope.doAdd = function () {
// 检查用户注册时，输入的两次密码是否一致
if($scope.add_password!==$scope.confirm_password){
$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;
});
}
};
});
引入angular.js后，登录时检测user数据表中是否存在该用户名与密码，如果登录成功会跳转到news.html页面，用户则可以看到主界面。
设置登录页的路由
调用userDAO，因为session信息只保存在启动网站内存中，所以要注意保存session信息，记录用户的操作日志
router.post('/login', function(req, res) {
var username = req.body.username;
var password = req.body.password;
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:'用户名或密码错误！请检查后输入'});
}
}
});
});调用的userDAO代码：
var mysql = require('mysql');
var mysqlConf = require('../conf/mysqlConf');
var userSqlMap = require('./userSqlMap');
var pool = mysql.createPool(mysqlConf.mysql);
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);
});
},
}; 最终结果如下：
3.实现查询词支持布尔表达式
在news.html引入查询页面
<div ng-include="'search.html'"></div>search.html代码：
设置查询界面按钮
<form class="form-horizontal" role="form">
<div class="row" style="margin-bottom: 10px;">
<label class="col-lg-2 control-label">标题关键字</label>
<div class="col-lg-3">
<input type="text" class="form-control" placeholder="标题关键字" ng-model="$parent.title1">
</div>
<div class="col-lg-1">
<select class="form-control" autocomplete="off" ng-model="$parent.selectTitle">
<option selected="selected">AND</option>
<option>OR</option>
</select>
</div>
<div class="col-lg-3">
<input type="text" class="form-control" placeholder="标题关键字" ng-model="$parent.title2">
</div>
</div>
<div class="row" style="margin-bottom: 10px;">
<label class="col-lg-2 control-label">内容关键字</label>
<div class="col-lg-3">
<input type="text" class="form-control" placeholder="内容关键字" ng-model="$parent.content1">
</div>
<div class="col-lg-1">
<select class="form-control" autocomplete="off" ng-model="$parent.selectContent">
<option selected="selected">AND</option>
<option>OR</option>
</select>
</div>
<div class="col-lg-3">
<input type="text" class="form-control" placeholder="内容关键字" ng-model="$parent.content2">
</div>
</div>
<div class="form-group">
<div class="col-md-offset-9">
<button type="submit" class="btn btn-default" ng-click="search()">查询</button>
</div>
</div>
</form> 显示查询内容结果：
<div ng-show="isisshowresult">
<table class="table table-striped">
<thead>
<tr>
<td>序号</td>
<td>标题</td>
<td>作者</td>
<td>关键词</td>
<td>链接</td>
<td>发布时间</td>
</tr>
<td>关键词</td>
<td>链接</td>
<td>发布时间</td>
</tr>
<td>{{item.keywords}}</td>
<td>{{item.url}}</td>
<td>{{item.publish_date}}</td>
</tr>
</tbody>
</table>
<div class="row">
<div class="pull-left" style="margin-top: 12px;">
<button type="submit" class="btn btn-primary" ng-click="searchsortASC()" >发布时间升序</button>
<button type="submit" class="btn btn-primary" ng-click="searchsortDESC()">发布时间降序</button>
</div>
<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>
</div>
</div>
后端news.js代码：
将用户输入的数据用get方式传给后端处理。对于用户的输入，若一个词都不输入，则输出全部数据。
$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;
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']);
if (request.session['username']===undefined) {
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用来拼起sql
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); //事件驱动回调
});
}
});
},
};分页显示查询结果：
$scope.initPageSort=function(item){
$scope.pageSize=5; //每页显示的数据量，可以随意更改
$scope.selPage = 1;
$scope.data = item;
$scope.pages = Math.ceil($scope.data.length / $scope.pageSize);
$scope.pageList = [];
$scope.index = 1;
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) {
if (page < 1 || page > $scope.pages) return;
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);
};最终结果：
4.用Echarts实现了3个数据分析图表
（因为没有持续爬取数据，所以最后的图表不准确）
路由代码：
router.get('/histogram', function(request, response) {
console.log(request.session['username']);
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();
});
}
});
router.get('/line', function(request, response) {
console.log(request.session['username']);
if (request.session['username']===undefined) {
// response.redirect('/index.html')
response.json({message:'url',result:'/index.html'});
}else {
var keyword = '疫情';
var fetchSql = "select content,publish_date from fetches where content like'%" + keyword + "%' 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:myfreqchangeModule.freqchange(result, keyword)}));
response.end();
});
}
});
router.get('/wordcloud', function(request, response) {
console.log(request.session['username']);
if (request.session['username']===undefined) {
// response.redirect('/index.html')
response.json({message:'url',result:'/index.html'});
}else {
var fetchSql = "select content from fetches;";
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:mywordcutModule.wordcut(result)}));//返回处理过的数据
response.end();
});
}
});柱状图：
$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 {
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};
ar 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;
});
};
折线图：
$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);
}
}
});
};
查看不同的关键词随时间出现次数需要需要在路由代码中修改keyword的关键词
词云：
$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 string
color: function () {
// Random color
return '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();
};
}
});
}
修改 maskImage.src = './images/logo.png’可以以其他图片样式显示词云
5.将用户操作记录数据库
直接引入var logger = require('morgan’);保存信息
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';
}
if(username!='notlogin'){
logDAO.userlog([username,request_time,request_method,request_url,status,remote_addr], function (success) {
console.log('成功保存！');
})
}
实验总结
这次试验原以为有了上次的积累会容易一些，没想到真正自己操作时还是无从下手，只写了一些断断续续的代码，最终还是在老师的代码示例帮助下完成了实验，这次最重要的经验在于实验前要把整体思路想好，不同功能的代码有序放在不同的子文件夹。自己的代码能力也还存在很大不足，其中Echarts实现饼状图时因为爬取的网站中作者名称的表现形式不同，想要做一个关键词的饼状图因为两边参数修改的问题也失败了。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
