一个练习项目，好玩的bbs-nodejs-express

代码：

const express = require('express');
const app = express();
const md5 = require('md5');

//npm install express

var secretKey = 'saacac3423@21212';
var pagesize = 20;

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : '127.0.0.1',
  user     : 'root',
  password : '123456',
  database : 'my_bbs'
});
 
connection.connect();

function retsuccess(data){
    return {'code' : 0, 'msg' : '', 'data' : data};
}

function reterror(code, msg){
    return {'code' : code, 'msg' : msg, 'data' : ''};
}

function formatDateTime(dateString) {
  const date = new Date(dateString);
  const year = date.getFullYear();
  const month = String(date.getMonth() + 1).padStart(2, '0');
  const day = String(date.getDate()).padStart(2, '0');
  const hours = String(date.getHours()).padStart(2, '0');
  const minutes = String(date.getMinutes()).padStart(2, '0');
  const seconds = String(date.getSeconds()).padStart(2, '0');
  
  return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
}

//异步函数，用于执行SQL查询
async function queryDatabase(sql, values) {
    return new Promise((resolve, reject) => {
        connection.query(sql, values, (error, results, fields) => {
            if (error) reject(error);
            resolve(results);
        });
    });
}

app.get('/', async (req, res) => {
    res.set('Server', 'nodejs-express');
    res.send("此站接口使用nodejs-express实现，<a href='api.html' target='_blank'>接口列表</a>");
});

app.get('/user/register', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var username = req.query.username;
    var nickname = req.query.nickname;
    var password = req.query.password;
    var passwordMd5 = md5(password);
    
    var sql1 = "select id,username,nickname,addTime from user where username='"+username+"'";
    var results = await queryDatabase(sql1);
    var userinfo = results[0];
    
    if(!userinfo){
        var sql2 = "insert into user(username, password, nickname) value(?, ?, ?)";
        var addSqlParams = [username, passwordMd5, nickname];
        var result = await queryDatabase(sql2, addSqlParams);
        res.send(retsuccess(result.insertId));
    }
    else{
        res.send(reterror(1, '用户名已经存在'));
    }
});

app.get('/user/login', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var username = req.query.username;
    var password = req.query.password;
    var passwordMd5 = md5(password);
    
    var sql1 = "select id,username,nickname,addTime from user where username='"+username+"' and password='"+passwordMd5+"'";
    var results = await queryDatabase(sql1);
    var userinfo = results[0];
    
    if(userinfo){
        var sessionId = md5(secretKey + userinfo['id'] + userinfo['addTime']);
        var sql2 = "update user set sessionId='"+sessionId+"' where id="+userinfo['id'];
        var results = await queryDatabase(sql2);
        userinfo['sessionId'] = sessionId;
        res.send(retsuccess(userinfo));
    }
    else{
        res.send(reterror(1, '用户名或者密码错误'));
    }
});

app.get('/user/logout', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var sessionId = req.query.sessionId;
    
    var sql1 = "select id,username,nickname,addTime from user where sessionId='"+sessionId+"'";
    var results = await queryDatabase(sql1);
    var userinfo = results[0];
    
    var sql2 = "update user set sessionId='' where id="+userinfo['id'];
    var results = await queryDatabase(sql2);
    userinfo['sessionId'] = '';
    
    res.send(retsuccess(userinfo));
});

app.get('/user/getuserinfo', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var sessionId = req.query.sessionId;

    var sql1 = "select id,username,nickname,addTime,sessionId from user where sessionId='"+sessionId+"'";
    var results = await queryDatabase(sql1);
    var userinfo = results[0];
    
    res.send(retsuccess(userinfo));
});

app.get('/post/list', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var page = req.query.page;
    var keyword = req.query.keyword;
    if(page <= 0){
        page = 1;
    }
    
    var start = (page - 1) * pagesize;
    var addsql = " isDel=0 ";
    if(keyword){
        addsql = " isDel=0 and title like '%"+keyword+"%' ";
    }
    
    var sql1 = "select count(1) as count from content where "+addsql;
    var results = await queryDatabase(sql1);
    var count = results[0]['count'];
    var totalpage = Math.ceil(count / pagesize);
    
    var sql2 = "select id,title,userId,userNickename,replyNum,updateTime from content where "+addsql+" order by updateTime desc limit "+start+","+pagesize;
    var results = await queryDatabase(sql2);
    for(var i=0; i<results.length; i++){
        results[i]['updateTime'] = formatDateTime(results[i]['updateTime']);
    }
    
    res.send(retsuccess({'totalpage' : totalpage, 'data' : results}));
});

app.get('/post/detail', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var id = req.query.id;
    
    var sql1 = "select id,title,content,userId,userNickename,replyNum,updateTime from content where isDel=0 and id="+id;
    var results = await queryDatabase(sql1);
    
    res.send(retsuccess(results[0]));
});

app.get('/post/add', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var title = req.query.title;
    var content = req.query.content;
    var sessionId = req.query.sessionId;
    
    var sql1 = "select id,username,nickname,addTime,sessionId from user where sessionId='"+sessionId+"'";
    var results = await queryDatabase(sql1);
    var userinfo = results[0];
    
    var sql2 = "insert into content(title, content, userId, userNickename) value(?, ?, ?, ?)";
    var addSqlParams = [title, content, userinfo['id'], userinfo['nickname']];
    var result = queryDatabase(sql2, addSqlParams);
    
    res.send(retsuccess(result.insertId));
});

app.get('/post/edit', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var id = req.query.id;
    var title = req.query.title;
    var content = req.query.content;
    var sessionId = req.query.sessionId;
    
    var sql1 = "select id,username,nickname,addTime,sessionId from user where sessionId='"+sessionId+"'";
    var results = await queryDatabase(sql1);
    var userinfo = results[0];
    
    var sql2 = "update content set title=?,content=?,userId=?,userNickename=? where id=? and userId=?";
    var addSqlParams = [title, content, userinfo['id'], userinfo['nickname'], id, userinfo['id']];
    var result = queryDatabase(sql2, addSqlParams);
    
    res.send(retsuccess(''));
});

app.get('/post/delete', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var id = req.query.id;
    var sessionId = req.query.sessionId;
    
    var sql1 = "select id,username,nickname,addTime,sessionId from user where sessionId='"+sessionId+"'";
    var results = await queryDatabase(sql1);
    var userinfo = results[0];
    
    var sql2 = "update content set isDel=1 where id=? and userId=?";
    var addSqlParams = [id, userinfo['id']];
    var result = queryDatabase(sql2, addSqlParams);
    
    res.send(retsuccess(''));
});

app.get('/reply/list', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var page = req.query.page;
    var contentId = req.query.contentId;
    if(page <= 0){
        page = 1;
    }
    
    var start = (page - 1) * pagesize;
    var addsql = " isDel=0 and contentId="+contentId+" ";
    
    var sql1 = "select count(1) as count from reply where "+addsql;
    var results = await queryDatabase(sql1);
    var count = results[0]['count'];
    var totalpage = Math.ceil(count / pagesize);
    
    var sql2 = "select id,content,replyUserId,replyUserNickename,addTime from reply where "+addsql+" order by id asc limit "+start+","+pagesize;
    var results = await queryDatabase(sql2);
    for(var i=0; i<results.length; i++){
        results[i]['addTime'] = formatDateTime(results[i]['addTime']);
    }
    
    res.send(retsuccess({'totalpage' : totalpage, 'data' : results}));
});

app.get('/reply/detail', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var id = req.query.id;
    
    var sql1 = "id,content,replyUserId,replyUserNickename,addTime from reply where id="+id;
    var results = await queryDatabase(sql1);
    
    res.send(retsuccess(results[0]));
});

app.get('/reply/add', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var contentId = req.query.contentId;
    var content = req.query.content;
    var sessionId = req.query.sessionId;
    
    var sql1 = "select id,username,nickname,addTime,sessionId from user where sessionId='"+sessionId+"'";
    var results = await queryDatabase(sql1);
    var userinfo = results[0];
    
    var sql2 = "update content set replyNum=replyNum+1 where id=?";
    var addSqlParams2 = [contentId];
    var result = await queryDatabase(sql2, addSqlParams2);
    
    var sql3 = "insert into reply(contentId, content, replyUserId, replyUserNickename) value(?, ?, ?, ?)";
    var addSqlParams3 = [contentId, content, userinfo['id'], userinfo['nickname']];
    var result = await queryDatabase(sql3, addSqlParams3);
    
    res.send(retsuccess(result.insertId));
});

app.get('/reply/edit', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var id = req.query.id;
    var content = req.query.content;
    var sessionId = req.query.sessionId;
    
    var sql1 = "select id,username,nickname,addTime,sessionId from user where sessionId='"+sessionId+"'";
    var results = await queryDatabase(sql1);
    var userinfo = results[0];
    
    var sql3 = "update reply set content=?,replyUserId=?,replyUserNickename=? where id=? and replyUserId=?";
    var addSqlParams3 = [content, userinfo['id'], userinfo['nickname'], id, userinfo['id']];
    var result = await queryDatabase(sql3, addSqlParams3);
    
    res.send(retsuccess(''));
});

app.get('/reply/delete', async (req, res) => {
    res.set('Server', 'nodejs-express');
    var id = req.query.id;
    var sessionId = req.query.sessionId;
    
    var sql1 = "select id,username,nickname,addTime,sessionId from user where sessionId='"+sessionId+"'";
    var results = await queryDatabase(sql1);
    var userinfo = results[0];
    
    var sql1 = "select id,content,replyUserId,replyUserNickename,addTime,contentId from reply where id="+id;
    var results = await queryDatabase(sql1);
    var replyinfo = results[0];
    
    var sql2 = "update content set replyNum=replyNum-1 where id=?";
    var addSqlParams2 = [replyinfo['contentId']];
    var result = await queryDatabase(sql2, addSqlParams2);
    
    var sql3 = "update reply set isDel=1 where id=? and replyUserId=?";
    var addSqlParams3 = [id, userinfo['id']];
    var result = await queryDatabase(sql3, addSqlParams3);
    
    res.send(retsuccess(''));
});

app.listen(1080, () => {
    console.log('express server running at http://127.0.0.1:1080');
});

 

输出：

D:\workspace\studys\study_bbs>D:\software\nodejs\node.exe D:\workspace\studys\study_bbs\start_web_express.js
express server running at http://127.0.0.1:1080