7. 基于Vue+Element+nodeJs+Express+MySql后台管理系统-请求封装和sql语句封装

一、请求封装

有一些请求方法使用非常频繁，这里我们将它封装一下；在项目根目录下创建一个sql文件夹，然后下面创建一个http.js文件，用来管理共用的请求方法。

let mysql = require('mysql');
let db = require('../config/db');
let pool = mysql.createPool(db);
const jwt = require('jwt-simple');
const express = require('express')
const app = express()
app.set('jwtTokenSecret', 'YOUR_SECRET_STRING')

const $http = {
    connPool (sql, val, cb) {
        pool.getConnection((err, conn) => {
            let query = conn.query(sql, val, (err, result) => {
                if (err) {
                    console.log(err);
                }
                cb(err, result);
                conn.release();
            });
        });
    },
    // json格式
    writeJson(res, result) {
        if(typeof result === 'undefined') {
            res.send('err')
        } else {
            res.json(result);
        }
    },
    // user验证
    userVerify(req, res, cb) {
        let params = req.body
        let userId = params.userId
        let resultData = {}
        let token=req.headers.token //获取前端请求头发送过来的token
        let decoded = jwt.decode(token, app.get('jwtTokenSecret'))
        if (!userId || decoded.iss!=userId) {
          resultData = {
            code: 2,
            message: 'userId有误'
          }
          $http.writeJson(res, resultData)
        } else {
          if(decoded.exp <= Date.now()){
            resultData = {
              code: 20,
              message: '登录过期'
            }
            $http.writeJson(res, resultData)
          }else{
            cb()
          }
        }
    }
};

module.exports = $http

二、sql语句封装

在项目根目录下创建一个sql文件夹，然后下面创建一个sqlMap.js文件，用来封装sql语句。

这是mysqljs中的语法，？就是变量，双??是表名或字段名，单?则为value。

const sqlMap = {
  user: {
    // 登陆
    login: 'select * from user where name = ? and password = ? and state != 0',
    // 新增用户
    add: 'insert IGNORE into user (name, password, email, create_time, update_time) values (?, ?, ?, ?, ?)',
    // 更新用户信息
    updateInfo: 'update user set name = ?, password = ?, email = ?, update_time = ? where id = ? and type != 1 and state != 0',
    // 更改用户状态
    updateState: 'update user set state = ?, update_time = ? where id = ? and type != 1 and state != 0',
    // 获取用户信息
    getDetail: 'select * from user where id = ? and state != 0',
    // 查询周报列表条数
    selectTotal: 'select count(*) as totalCount from user where state != 0',
    // 用户列表
    selectList: 'select id, name, email, type, create_time, update_time, state from user where state != 0'
  },
  weekly: {
    // 新增周报
    add: 'insert into weekly (userId, startTime, endTime, title, thisWeekWork, nextWeekWork, collaboration, create_time, update_time) values (?, ?, ?, ?, ?, ?, ?, ?, ?)',
    // 更新周报信息
    updateInfo: 'update weekly set startTime = ?, endTime = ?, title = ?, thisWeekWork = ?, nextWeekWork = ?, collaboration = ?, update_time = ? where id = ? and state != 0',
    // 更新周报状态
    updateState: 'update weekly set state = ?, update_time = ? where id = ? and state != 0',
    // 查询周报列表条数
    selectTotal: 'select count(*) as totalCount from weekly where state != 0',
     // 查询周报列表
    selectList: 'select weekly.id, userId, user.name as userName, startTime, endTime, title, weekly.create_time, weekly.update_time, weekly.state from weekly,user where weekly.userId = user.id and weekly.state != 0',
    // 以id为主条件查询某篇周报详情
    getDetail: 'select weekly.id, userId, user.name as userName, startTime, endTime, title, thisWeekWork, nextWeekWork, collaboration, weekly.create_time, weekly.update_time, weekly.state from weekly,user where weekly.userId = user.id and weekly.state != 0 and weekly.id=?'
  }
}
module.exports = sqlMap;

三、项目链接

周报管理系统git仓库链接： nodeJs工作周报后台管理系统OA模板

程序员接单_竹立荷塘的博客-CSDN博客