本次项目基于上次制作的爬虫所获取的数据,将页面的数据展示作了优化,满足以下要求:
(可以通过网页侧面的小标题快速跳转阅览)
前文:记录一次从零开始的nodejs爬虫开发以及网页展示
项目目录
(爬虫与mysql建表命令)
(项目本体)
实现用户注册与登录,记录日志(实现基本要求1与基本要求2)
数据库准备
1.先在mysql本地数据库中建立两张表分别用于储存用户和用户操作
(用户的id 账号密码与 注册时间)
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_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;
2.配置用于连接mysql的连接相关文件,储存于conf文件夹中:
/conf/mysqlConf.js
module.exports = {
mysql: {
host: 'localhost',
user: 'root',
password: 'hdx',
database:'crawl',
// 最大连接数,默认为10
connectionLimit: 10
}
};
(适配本地数据库设置的账号与密码)
3.配置连接池dao文件,集中储存在dao文件夹中:
先配置用户的map文件
userSqlMap.js
var userSqlMap = {
add: 'insert into user(username, password) values(?, ?)',//注册时用
getByUsername: 'select username, password from user where username = ?'//登陆时用
};
module.exports = userSqlMap;
logDAO.js
var mysql = require('mysql');
var mysqlConf = require('../conf/mysqlConf');
var pool = mysql.createPool(mysqlConf.mysql);
// 使用了连接池,重复使用数据库连接,而不必每执行一次CRUD操作就获取、释放一次数据库连接,从而提高了对数据库操作的性能。
// 记录用户操作
module.exports = {
userlog :function (useraction, callback) {
pool.query('insert into user_action(username,request_time,request_method,request_url,status,remote_addr) values(?, ?,?,?,?,?)',
useraction, function (error, result) {
if (error) throw error;
callback(result.affectedRows > 0);
});
},
};
newsDAO.js
var mysql = require('mysql');
var mysqlConf = require('../conf/mysqlConf');
var pool = mysql.createPool(mysqlConf.mysql);
// 使用了连接池,重复使用数据库连接,而不必每执行一次CRUD操作就获取、释放一次数据库连接,从而提高了对数据库操作的性能。
module.exports = {
query_noparam :function(sql, callback) {
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); //事件驱动回调
});
}
});
},
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); //事件驱动回调
});
}
});
},
};
userDAO.js
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);
});
},
};
**前端页面
**1.先完成登录注册页面(index.js)
(1)基本思路是借用angular实现登录注册相关事件
先在根元素定义应用“login“
<!DOCTYPE html>
<html ng-app="login">
(2)绑定输入域的用户名、密码,通过
s
c
o
p
e
对
象
进
行
数
据
传
递
(
使
用
n
g
−
m
o
d
e
l
指
令
)
(
3
)
定
义
scope对象进行数据传递(使用ng-model指令) (3)定义
scope对象进行数据传递(使用ng−model指令)(3)定义scope对象的msg属性用于传递报错信息。
(4)检查两次密码是否相同,不同则进行警告,编写对应的angular代码
$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;
});
前端注册部分:
<!DOCTYPE html>
<html ng-app="login">
<head>
<meta charset="utf-8" />
<title>Login</title>
<link rel="stylesheet" href="http://cdn.bootcss.com/bootstrap/3.3.0/css/bootstrap.min.css">
<script src="https://cdn.staticfile.org/jquery/3.2.1/jquery.min.js"></script>
<script src="https://cdn.staticfile.org/popper.js/1.12.5/umd/popper.min.js"></script>
<script src="https://cdn.staticfile.org/twitter-bootstrap/4.1.0/js/bootstrap.min.js"></script>
<script src="../node_modules/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){
// $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;
});
}
};
});
</script>
</head>
<body>
<div class="container" ng-controller="loginCtrl">
<div class="row">
<div class="col-md-6 col-md-offset-3">
<div class="panel panel-login">
<div class="panel-heading">
<div class="row">
<div class="col-xs-6">
<a href="#" class="active" id="login-form-link">Login</a>
</div>
<div class="col-xs-6">
<a href="#" id="register-form-link">Register</a>
</div>
</div>
<hr>
</div>
<div class="panel-body">
<div class="row">
<div class="col-lg-12">
<form id="login-form" method="post" role="form" style="display: block;">
(5)相同思路可完成登录部分前端代码
<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>
<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>
</div>
</div>
</div>
</div>
<div class="alert alert-warning" ng-if="msg && msg!='ok'">
<a href="#" class="close" data-dismiss="alert">×</a>
<strong>警告!</strong>{{msg}}
</div>
</div>
</div>
</div>
</body>
</html>
(index的angular代码)
$(function() {
$('#login-form-link').click(function(e) {
$("#login-form").delay(100).fadeIn(100);
$("#register-form").fadeOut(100);
$('#register-form-link').removeClass('active');
$(this).addClass('active');
e.preventDefault();
});
$('#register-form-link').click(function(e) {
$("#register-form").delay(100).fadeIn(100);
$("#login-form").fadeOut(100);
$('#login-form-link').removeClass('active');
$(this).addClass('active');
e.preventDefault();
});
});
设置路由users.js:
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:'用户名或密码错误!请检查后输入'});
}
}
});
});
/* 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: '成功注册!请登录'});
})
}
});
});
// 退出登录
router.get('/logout', function(req, res, next){
// 备注:这里用的 session-file-store 在destroy 方法里,并没有销毁cookie
// 所以客户端的 cookie 还是存在,导致的问题 --> 退出登陆后,服务端检测到cookie
// 然后去查找对应的 session 文件,报错
// session-file-store 本身的bug
req.session.destroy(function(err) {
if(err){
res.json('退出登录失败');
return;
}
// req.session.loginUser = null;
res.clearCookie('username');
res.json({result:'/index.html'});
});
});
module.exports = router;
css格式
body {
padding: 50px;
font: 14px "Lucida Grande", Helvetica, Arial, sans-serif;
}
a {
color: #00B7FF;
}
session文件(项目/app.js)
var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var session = require('express-session');
var logger = require('morgan');
var logDAO = require('./dao/logDAO.js');
var usersRouter = require('./routes/users');
var newsRouter = require('./routes/news');
var app = express();
//设置session
app.use(session({
secret: 'sessiontest',
resave: true,
saveUninitialized: false, // 是否保存未初始化的会话
cookie : {
maxAge : 1000 * 60 * 60, // 设置 session 的有效时间,单位毫秒
},
}));
let method = '';
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('成功保存!');
})
}
console.log('请求时间 = ', request_time);
console.log('请求方式 = ', request_method);
console.log('请求链接 = ', request_url);
console.log('请求状态 = ', status);
console.log('请求长度 = ', tokens.res(req, res, 'content-length'),);
console.log('响应时间 = ', tokens['response-time'](req, res) + 'ms');
console.log('远程地址 = ', remote_addr);
console.log('远程用户 = ', tokens['remote-user'](req, res));
console.log('http版本 = ', tokens['http-version'](req, res));
console.log('浏览器信息 = ', tokens['user-agent'](req, res));
console.log('用户 = ', username);
console.log(' ===============',method);
}, ));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));
app.use('/angular', express.static(path.join(__dirname , '/node_modules/angular')));
app.use('/users', usersRouter);
app.use('/news', newsRouter);
// catch 404 and forward to error handler
app.use(function(req, res, next) {
next(createError(404));
});
// error handler
app.use(function(err, req, res, next) {
// set locals, only providing error in development
res.locals.message = err.message;
res.locals.error = req.app.get('env') === 'development' ? err : {};
// render the error page
res.status(err.status || 500);
// res.render('error');
});
module.exports = app;
实现效果:
试运行服务端进行测试
mysql表单成功记录
查询页面相关(search相关),(完成基本要求3,扩展要求1、2)
实现分页以及排序:
前文从零开始nodejs爬虫及数据展示网站,已经完成分页排序和按时间热度排序并进行分析,附带针对分词的强化搜索,此处引用:
(原)index:
var express = require('express');
var router = express.Router();
var mysql = require('../mysql.js');
/* GET 主页 */
router.get('/', function(req, res, next) {
res.render('index', { title: 'Express' });
});
router.get('/process_get', function(request, response) {
//sql
var fetchSql = "select url,source_name,title,author,publish_date " +
"from fetches where author like '%" + request.query.author + "%'";
var tarray=request.query.title.split(" ");
for(var i=0;i<tarray.length;i++){
fetchSql+="and title like '%"+ tarray[i] + "%'";
}
fetchSql+="order by publish_date"
mysql.query(fetchSql, function(err, result, fields) {
response.writeHead(200, {
"Content-Type": "application/json"
});
response.write(JSON.stringify(result));
response.end(); ``
});
});
module.exports = router;
(原)html:
<!DOCTYPE html>
<html>
<header>
<script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.js"></script>
</header>
<head>
<script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.js"></script>
<link href="http://www.itxst.com/package/bootstrap-table-1.14.1/bootstrap-4.3.1/css/bootstrap.css" rel="stylesheet" />
<link href="http://www.itxst.com/package/bootstrap-table-1.14.1/bootstrap-table-1.14.1/bootstrap-table.css" rel="stylesheet" />
<script src="http://www.itxst.com/package/bootstrap-table-1.14.1/bootstrap-table-1.14.1/bootstrap-table.js"></script>
<style>
.cardLayout{
border:rgb(147, 64, 163) solid 10px;
margin: 5px 0px;
}
tr{
border: solid 3px;
}
td{
border:solid 3px;
}
</style>
</head>
<body>
<form>
<br> 标题:<input type="text" id="input1" name="title_text">
<br> 作者:<input type="text" id="input2" name="title_text">
<input class="form-submit" type="button" id="btn1"value="查询">
<input type="reset">
</form>
<div class="cardLayout">
<table width="100%" id="record2"></table>
</div>
<script>
$(document).ready(function() {
$("#btn1").click(function() {
/*$.get('/process_get?title=' + $("#input1").val()+'&author='+$("#input2").val(), function(data) {
$("#record2").empty();
$("#record2").append('<tr class="cardLayout"><td>url</td><td>source_name</td>' +
'<td>title</td><td>author</td><td>publish_date</td></tr>');
for (let list of data) {
let table = '<tr class="cardLayout"><td>';
Object.values(list).forEach(element => {
table += (element + '</td><td>');
});
$("#record2").append(table + '</td></tr>');
}
});*/
$.get('/process_get?title=' + $("#input1").val()+'&author='+$("#input2").val(), function(data) {
$("#record2").bootstrapTable({
search:true, //加上搜索控件
method: 'get', //请求方式
pagination: true, //是否显示分页
striped: true, //是否显示行间隔色
uniqueId: "userId", //每一行的唯一标识,一般为主键列
pageSize: 5, //每页的记录行数
sidePagination : 'client',
columns:[{
field:'url', //对应mysql字段名
title:'链接',
},{
field:'source_name',
title:'来源'
},{
field:'title',
title:'标题'
},{
field:'author',
title:'作者'
},{
field:'publish_date',
title:'发布时间',
}],
data: data,
});
});
});
});
</script>
</body>
</html>
(原)css
<style>
.cardLayout{
border:rgb(147, 64, 163) solid 10px;
margin: 5px 0px;
}
tr{
border: solid 3px;
}
td{
border:solid 3px;
}
</style>
实现效果:
(默认按时间先后排序)
(默认五行一页)
结合本次登录页面,新建news.html页面,讲search.html嵌入其中(借助ng-include引入),优化排版(为后续引入图表做准备):
news.html:
<html ng-app="news">
<head>
<meta charset="utf-8">
<title>News</title>
<link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://cdn.staticfile.org/jquery/2.1.1/jquery.min.js"></script>
<script src="http://cdn.bootcss.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/echarts@4.7.0/dist/echarts.min.js"></script>
<script src='javascripts/dist/echarts-wordcloud.min.js'></script>
<script src="../node_modules/angular/angular.min.js"></script>
<script src="javascripts/news.js" type="text/javascript"></script>
</head>
<body ng-controller="news_Ctrl" ng-init="isShow=false">
<nav class="navbar navbar-inverse navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<a class="navbar-brand" href="#">News</a>
</div>
<div id="navbar" class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li ><a ng-click="showSearch()">检索</a></li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown">图片<span class="caret"></span></a>
<ul class="dropdown-menu">
<li><a ng-click="histogram()">柱状图</a></li>
<li><a ng-click="pie()">饼状图</a></li>
<li><a ng-click="line()">折线图</a></li>
<li><a ng-click="wordcloud()">词云</a></li>
</ul>
</li>
<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>
</ul>
</div>
</div>
</nav>
<!-- 所有的图片都绘制在main1位置-->
<span ng-hide="isShow" id="main1" style="width: 1000px;height:600px;position:fixed; top:70px;left:80px"></span>
<div ng-show="isShow" style="width: 1300px;position:relative; top:70px;left: 80px">
<!-- 查询页面-->
<div ng-include="'search.html'"></div>
</div>
</body>
</html>
search.html:
(被ng-include引入)
<div ng-show="isShow" style="width: 1300px;position:relative; top:70px;left: 80px">
<!-- 查询页面-->
<div ng-include="'search.html'"></div>
</div>
<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>
<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="row">
<!-- <div class="form-group">-->
<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>-->
<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:
var newsDAO = require('../dao/newsDAO');
var express = require('express');
var router = express.Router();
var mywordcutModule = require('./wordcut.js');
var myfreqchangeModule = require('./freqchange.js');
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});
})
}
});
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();
});
}
});
router.get('/pie', 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 author as x,count(author) as y from fetches group by author;";
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) {
//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 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) {
//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 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();
});
}
});
module.exports = router;
(修改后UI借鉴示例,在上方进行了相关分类)
用多种图表进行数据可视化(借助Echarts)(完成基本要求4)
1.先安装需要的环境(此处以jieba为例)
npm install nodejieba -save
2.补充、修改相关路由
news.js:
var newsDAO = require('../dao/newsDAO');
var express = require('express');
var router = express.Router();
var mywordcutModule = require('./wordcut.js');
var myfreqchangeModule = require('./freqchange.js');
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});
})
}
});
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();
});
}
});
router.get('/pie', 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 author as x,count(author) as y from fetches group by author;";
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) {
//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 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) {
//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 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();
});
}
});
module.exports = router;
users.js:
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:'用户名或密码错误!请检查后输入'});
}
}
});
});
/* 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: '成功注册!请登录'});
})
}
});
});
// 退出登录
router.get('/logout', function(req, res, next){
// 备注:这里用的 session-file-store 在destroy 方法里,并没有销毁cookie
// 所以客户端的 cookie 还是存在,导致的问题 --> 退出登陆后,服务端检测到cookie
// 然后去查找对应的 session 文件,报错
// session-file-store 本身的bug
req.session.destroy(function(err) {
if(err){
res.json('退出登录失败');
return;
}
// req.session.loginUser = null;
res.clearCookie('username');
res.json({result:'/index.html'});
});
});
module.exports = router;
freqchange.js
var nodejieba = require('nodejieba');
// var mysql = require('../mysql.js');
//正则表达式去掉一些无用的字符。
const regex_c = /[\t\s\r\n\d\w]|[\+\-\(\),\.。,!?《》@、【】"'::%-\/“”]/g;
var regex_d = /\w{3}\s(.*?) 2020/; //只留下日期的年月
var freqchange = function(vals, keyword) {
var regex_k = eval('/'+keyword+'/g');
var word_freq = {};
vals.forEach(function (data){
var content = data["content"].replace(regex_c,'');
var publish_date = regex_d.exec(data['publish_date'])[1];
var freq = content.match(regex_k).length;// 直接搜这个词。
word_freq[publish_date] = (word_freq[publish_date] + freq ) || 0;
});
return word_freq;
};
exports.freqchange = freqchange;
wordcut.js
var nodejieba = require('nodejieba');
//正则表达式去掉一些无用的字符,与高频但无意义的词。
const regex = /[\t\s\r\n\d\w]|[\+\-\(\),\.。,!?《》@、【】"'::%-\/“”]/g;
var wordcut = function(vals) {
var word_freq = {};
vals.forEach(function (content){
var newcontent = content["content"].replace(regex,'');
if(newcontent.length !== 0){
// console.log();
var words = nodejieba.cut(newcontent);
words.forEach(function (word){
word = word.toString();
word_freq[word] = (word_freq[word] +1 ) || 1;
});
};
});
return word_freq;
};
exports.wordcut = wordcut;
3.动态处理JavaScript,对应完成折线图、饼图、矩形图以及词云:
line.js:
function lineFun() {
$.get('/line', function (data) {
console.log(data);
var myChart = echarts.init(document.getElementById("main1"));
option = {
title: {
text: '"疫情"该词在新闻中的出现次数随时间变化图'
},
xAxis: {
type: 'category',
data: Object.keys(data)
},
yAxis: {
type: 'value'
},
series: [{
data: Object.values(data),
type: 'line',
itemStyle : { normal: {label : {show: true}}}
}],
};
if (option && typeof option === "object") {
myChart.setOption(option, true);
}
});
};
pie.js:
var pieFun = function() {
$.get('/pie', function (data) {
// var newdata = washdata(data);
let newdata = [];
var pattern = /责任编辑:(.+)/;//匹配名字
data.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
});
// 显示 tooltip
myChart.dispatchAction({
type: 'showTip',
seriesIndex: 0,
dataIndex: app.currentIndex
});
}, 1000);
if (option && typeof option === "object") {
myChart.setOption(option, true);
};
});
};
histogram.js
function histogramFun() {
$.get('/histogram', function (data) {
// var newdata = washdata(data);
let xdata=[], ydata=[], newdata;
var pattern = /\d{4}-(\d{2}-\d{2})/;
data.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);
});
}
wordcloud.js:
function wordcloudFun() {
$.get('/wordcloud', function (keywords) {
var mainContainer = document.getElementById('main1');
var chart = echarts.init(mainContainer);
var data = [];
for (var name in keywords) {
data.push({
name: name,
value: Math.sqrt(keywords[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();
};
// chart.setOption(option);
// window.onresize = chart.resize;
});
}
(其中wordp.jpeg图片文件用于词云背景形状)
从网络中获取dist文件,置于/dist中:
准备完毕后,运行服务端,登录访问测试相关功能:
管理用户(基本要求5)
新建子项目administrator
mysql.js(与本地数据库信息匹配)
var mysql = require('mysql');
var pool = mysql.createPool({
host : '127.0.0.1',
user : 'root',
password : 'hdx',
database : 'crawl'
});
exports.query = function(sql,data){
pool.getConnection(function(err,connection){
connection.query(sql,function(err,result){
data(err,result);
connection.release();
});
});
}
app.js
var express = require('express');
var bodyParser = require('body-parser');
var fs = require('fs');
var sql = require('./conf/mysql.js');
var app = express();
app.set('views','./views/');
app.use(bodyParser.urlencoded({ extended: false }));
// display user
app.get('/',function(req,res){
sql.query('select * from user',function(err,result){
if (err) {
res.render('index',{title:"用户列表",datas:[]});
}else{
res.render('index',{title:"用户列表",datas:result});
}
});
});
// add user
app.get('/add',function(req,res){
res.render('add');
});
app.post('/add',function(req,res){
var username = req.body.username;
var password = req.body.password;
sql.query('insert into user(username,password) values("'+username+'","'+ password +'")',function(err,result){
if(err){
res.send('新增失败'+err);
}else {
res.redirect('/');
}
});
});
// edit user
app.get('/edit/:id',function(req,res){
var id = req.params.id;
sql.query('select * from user where id = ' + id,function(err,result){
res.render('edit',{datas:result});
});
});
app.post('/edit',function(req,res){
var id = req.body.id;
var username = req.body.username;
var password = req.body.password;
sql.query('update user set username = "'+username+'" , password = "'+password+'" where id = '+id,function(err,result){
if (err) {
res.send('更新失败'+err);
}else{
res.redirect('/');
}
})
});
// del user
app.get('/del/:id',function(req,res){
var id = req.params.id;
sql.query('delete from user where id = '+id,function(err,result){
if(err){
res.send('删除失败'+err);
}else {
res.redirect('/');
}
});
});
app.listen(3000);
添用户add.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>添加用户</title>
<style>
.myButton {
-moz-box-shadow: 0px 0px 0px 0px #a4e271;
-webkit-box-shadow: 0px 0px 0px 0px #a4e271;
box-shadow: 0px 0px 0px 0px #a4e271;
background-color:#89c403;
-moz-border-radius:28px;
-webkit-border-radius:28px;
border-radius:28px;
border:1px solid #74b807;
display:inline-block;
cursor:pointer;
color:#ffffff;
font-family:Arial;
font-size:17px;
padding: 3px 71px;
margin-top: 10px;
text-decoration:none;
text-shadow:0px 1px 0px #528009;
}
.myButton:hover {
background-color:#77a809;
}
.myButton:active {
position:relative;
top:1px;
}
input{
border: 1px solid #cae;
color:#cea;
}
hr{
border:1px solid #cea;
}
</style>
</head>
<body>
<form class="form-horizontal" action="/add" method="post">
<fieldset>
<div id="legend" class="">
<legend class="leipiplugins-orgvalue">添加用户</legend>
<hr>
</div>
<div class="control-group">
<!-- Text -->
<div class="controls">
<input name="username" type="text" placeholder="请输入您的姓名" title="姓名" value="" class="leipiplugins" leipiplugins="text">
</div>
</div>
<br>
<div class="control-group">
<!-- Text -->
<div class="controls">
<input name="password" type="text" placeholder="请输入您的密码" title="密码" value="" class="leipiplugins" leipiplugins="text">
</div>
</div>
<input class="myButton" type="submit" value="提交">
</fieldset>
</form>
</body>
</html>
编辑用户edit.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>编辑用户</title>
<style>
.myButton {
-moz-box-shadow: 0px 0px 0px 0px #a4e271;
-webkit-box-shadow: 0px 0px 0px 0px #a4e271;
box-shadow: 0px 0px 0px 0px #a4e271;
background-color:#89c403;
-moz-border-radius:28px;
-webkit-border-radius:28px;
border-radius:28px;
border:1px solid #74b807;
display:inline-block;
cursor:pointer;
color:#ffffff;
font-family:Arial;
font-size:17px;
padding: 3px 71px;
margin-top: 10px;
text-decoration:none;
text-shadow:0px 1px 0px #528009;
}
.myButton:hover {
background-color:#77a809;
}
.myButton:active {
position:relative;
top:1px;
}
input{
border: 1px solid #cae;
color:#cea;
}
hr{
border:1px solid #cea;
}
</style>
</head>
<body>
<form class="form-horizontal" action="/edit" method="post">
<fieldset>
<div id="legend" class="">
<legend class="leipiplugins-orgvalue">编辑用户</legend>
<hr>
</div>
<div class="control-group">
<!-- Text -->
<div class="controls">
<input type="hidden" name="id" value="<%= datas[0].id %>">
<input name="username" type="text" value=" <%= datas[0].username %> " class="leipiplugins" leipiplugins="text">
</div>
</div>
<br>
<div class="control-group">
<!-- Text -->
<div class="controls">
<input name="password" type="text" value=" <%= datas[0].password %> " class="leipiplugins" leipiplugins="text">
</div>
</div>
<input class="myButton" type="submit" value="提交">
</fieldset>
</form>
</body>
</html>
总结:
本次是大学以来首次学习web编程的知识,一路上磕磕碰碰遇到了不少困难,目前也仅仅摸到了入门的槛。同时,这也是我在大学中首次接触项目开发的科目,非常幸运能有老师、学长们的指导和帮助,正是有大家的帮助,我才能少走许多弯路,谢谢你们!路漫漫其修远兮,吾将上下而求索,本学期的web编程学习是一个对我而言独特的起点,今后我将会继续努力,争取习得一技之长。