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

代码：

const Koa = require('koa');
const Router = require('koa-router');
const md5 = require('md5');

//npm install koa koa-router

const app = new Koa();
const router = new Router();

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){
    var result = JSON.stringify({'code' : 0, 'msg' : '', 'data' : data});
    return result;
}

function reterror(code, msg){
    var result = JSON.stringify({'code' : code, 'msg' : msg, 'data' : ''});
    return result;
}

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);
    });
  });
}

// 定义路由
router.get('/', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    ctx.body = "此站接口使用nodejs-koa实现，<a href='api.html' target='_blank'>接口列表</a>";
});

router.get('/user/register', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    var username = ctx.query.username;
    var nickname = ctx.query.nickname;
    var password = ctx.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);
        ctx.body = retsuccess(result.insertId);
    }
    else{
        ctx.body = reterror(1, '用户名已经存在');
    }
});

router.get('/user/login', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    var username = ctx.query.username;
    var password = ctx.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;
        ctx.body = retsuccess(userinfo);
    }
    else{
        ctx.body = reterror(1, '用户名或者密码错误');
    }
});

router.get('/user/logout', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    var sessionId = ctx.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'] = '';
    
    ctx.body = retsuccess(userinfo);
});

router.get('/user/getuserinfo', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    var sessionId = ctx.query.sessionId;

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

router.get('/post/list', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    var page = ctx.query.page;
    var keyword = ctx.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']);
    }
    
    ctx.body = retsuccess({'totalpage' : totalpage, 'data' : results});
});

router.get('/post/detail', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    var id = ctx.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);
    
    ctx.body = retsuccess(results[0]);
});

router.get('/post/add', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    var title = ctx.query.title;
    var content = ctx.query.content;
    var sessionId = ctx.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);
    
    ctx.body = retsuccess(result.insertId);
});

router.get('/post/edit', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    var id = ctx.query.id;
    var title = ctx.query.title;
    var content = ctx.query.content;
    var sessionId = ctx.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);
    
    ctx.body = retsuccess('');
});

router.get('/post/delete', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    var id = ctx.query.id;
    var sessionId = ctx.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);
    
    ctx.body = retsuccess('');
});

router.get('/reply/list', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    var page = ctx.query.page;
    var contentId = ctx.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']);
    }
    
    ctx.body = retsuccess({'totalpage' : totalpage, 'data' : results});
});

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

router.get('/reply/add', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    var contentId = ctx.query.contentId;
    var content = ctx.query.content;
    var sessionId = ctx.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);
    
    ctx.body = retsuccess(result.insertId);
});

router.get('/reply/edit', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    var id = ctx.query.id;
    var content = ctx.query.content;
    var sessionId = ctx.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);
    
    ctx.body = retsuccess('');
});

router.get('/reply/delete', async (ctx) => {
    ctx.set('Server', 'nodejs-koa');
    var id = ctx.query.id;
    var sessionId = ctx.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);
    
    ctx.body = retsuccess('');
});

// 使用路由中间件
app.use(router.routes());
app.use(router.allowedMethods());

// 启动服务器
app.listen(1100, () => {
    console.log('Server is running on port 1100');
});

 

输出：

D:\workspace\studys\study_bbs>D:\software\nodejs\node.exe D:\workspace\studys\study_bbs\start_web_koa.js
Server is running on port 1100