NodeJS（express）连接、封装操作MySQL

最近要开发一个全栈项目。第一次上车，小本本记录一下，以免忘记。

1.目录结构

先来看一下项目目录：

web文件夹：是放一些前端的代码；
node_serve：服务端的一些文件；
– server.js：服务端入口文件；
– db : 有关数据库的一些文件；
---- db.js：数据库增、删、改、查api的封装；
---- dbconfig.js: 数据库的配置
---- json.js: 消息请求响应(暂时放在这里先)；
---- sql.js: 对操作数据库不同表的sql语句的封装
数据库目录：

2.入口文件：

/**
 * Created by Walker on 2020/04/30.
 * 服务器入口文件
 * 热加载 supervisor server.js
 */
const express = require("express");
const app = express();
var url = require("url");
var bodyParser = require("body-parser"); //格式化请求消息的中间件
const db = require("./db/db.js");

app.use(bodyParser.urlencoded({ extended: false }));
//设置允许跨域的域名，*代表允许任意域名跨域
app.all("*", function (req, res, next) {
  res.header("Access-Control-Allow-Origin", "*"); //允许的header类型
  res.header("Access-Control-Allow-Headers", "Content-type"); //跨域允许的请求方式
  res.header(
    "Access-Control-Allow-Methods",
    "PUT,POST,GET,DELETE,OPTIONS,PATCH"
  ); //可选，用来指定本次预检请求的有效期，单位为秒。在此期间，不用发出另一条预检请求。
  res.header("Access-Control-Max-Age", 1728000); //预请求缓存20天
  next();
});

//下面是一些供前端使用的测试接口；
//get和post方法，对从前端接收的请求data的处理会有区别，可以注意一下；
app.get("/", (req, res) => res.send("Hello World!"));
app.get("/index_getdata", (req, res) => {
  var name = url.parse(req.url, true).query;
  console.log(name);
});

app.post("/add", (req, res, next) => {
  let body = req.body;
  console.log("dbAdd", req.body);
  let sqlParam = {
    id: body.uuid,
    name: body.name,
  };
  console.log("dbAdd", sqlParam);
  db.dbAdd("teacher", sqlParam, res, next);
});

app.post("/delete_data", (req, res, next) => {
  let body = req.body;
  console.log("delet", req.body);
  let sqlParam = {
    id: body.uuid,
  };
  console.log("delet", sqlParam);
  db.dbDelete("teacher", sqlParam, res, next);
});

app.post("/update_data", (req, res, next) => {
  let body = req.body;
  console.log("update", req.body);
  let sqlParam = {
    name: body.name,
    id: body.uuid,
  };
  console.log("update", sqlParam);
  db.dbUpdate("teacher", sqlParam, res, next);
});

app.post("/queryById_data", (req, res, next) => {
  let body = req.body;
  console.log("queryById_data", req.body);
  let sqlParam = {
    id: body.uuid,
  };
  console.log("queryById_data", sqlParam);
  db.dbQueryById("teacher", sqlParam, res, next);
});

app.get("/queryAll_data", (req, res, next) => {
  db.dbQueryAll("teacher", "", res, next);
});

app.listen(3000, () => console.log("Example app listening on port 3000!"));

3.mysql连接的配置

/**
 * Created by Walker on 2020/05/06.
 * 数据库的配置
 */
var mysqlConfig = {
  host: "localhost",
  user: "root",
  password: "123456",
  port: "3306",
  database: "sqltest",
};

module.exports = mysqlConfig

4.数据库连接、API的封装

/**
 * Created by Walker on 2020/05/06.
 * 数据库增、删、改、查封装
 */
//mysql连接池配置文件
const mysql = require("mysql");
const $dbConfig = require("./dbConfig"); //注意改成自己项目中mysql配置文件的路径
const sql = require("./sql.js"); //sql语句封装
const pool = mysql.createPool($dbConfig); // 使用连接池，避免开太多的线程，提升性能
const json = require("./json");

/**
 * @description 新增一条数据
 * @param  {str} table 数据库表的名称
 * @param  {obj} req 插入的数据
 * @param  {obj} res 接口函数中的res对象
 * @param  {obj} next 接口函数中的next对象
 */
let dbAdd = (table, req, res, next) => {
  pool.getConnection((err, connection) => {
    let paramValue = paramList(req);
    connection.query(sql[table].insert, [...paramValue], (err, result) => {
      if (result) {
        result = "add";
      }
      // 以json形式，把操作结果返回给前台页面
      json(res, result, err);
      // 释放连接
      connection.release();
    });
  });
};

/**
 *@description 删除一条数据
  @param 同abAdd
 */
let dbDelete = (table, req, res, next) => {
  let paramValue = paramList(req);
  pool.getConnection((err, connection) => {
    connection.query(sql[table].delete, [...paramValue], (err, result) => {
      if (result.affectedRows > 0) {
        result = "delete";
      } else {
        result = undefined;
      }
      json(res, result, err);
      connection.release();
    });
  });
};

/**
 *@description 修改一条数据
  @param 同abAdd
 */
let dbUpdate = (table, req, res, next) => {
  let paramValue = paramList(req);
  pool.getConnection((err, connection) => {
    connection.query(sql[table].update, [...paramValue], (err, result) => {
      console.log(result)
      if (result.affectedRows > 0) {
        result = "update";
      } else {
        result = undefined;
      }
      json(res, result, err);
      connection.release();
    });
  });
};

/**
 *@description 查找一条数据
  @param 同abAdd
 */
let dbQueryById = (table, req, res, next) => {
  let paramValue = paramList(req);
  pool.getConnection((err, connection) => {
    connection.query(sql[table].queryById, [...paramValue], (err, result) => {
      if (result != "") {
        var _result = result;
        result = {
          result: "select",
          data: _result,
        };
      } else {
        result = undefined;
      }
      json(res, result, err);
      connection.release();
    });
  });
};

/**
 *@description 查找全部数据
  @param 同abAdd
 */
let dbQueryAll = (table, req, res, next) => {
  pool.getConnection((err, connection) => {
    connection.query(sql[table].queryAll, (err, result) => {
      if (result != "") {
        var _result = result;
        result = {
          result: "selectall",
          data: _result,
        };
      } else {
        result = undefined;
      }
      json(res, result, err);
      connection.release();
    });
  });
};

/**
 * @description 遍历数据的值
 * @param {obj} obj 包含参数的对象
 * */
let paramList = (obj) => {
  let paramArr = [];
  for (let key in obj) {
    if (obj[key]) {
      paramArr.push(obj[key]);
    }
  }
  return paramArr;
};

module.exports = {
  dbAdd,
  dbDelete,
  dbUpdate,
  dbQueryById,
  dbQueryAll,
};

5.SQL语句的封装

/**
 * Created by Walker on 2020/05/06.
 * 对操作不同表，sql语句的封装
 */
let test = {
  insert: "INSERT INTO test(id, name, age) VALUES(?,?,?)",
  update: "UPDATE test SET name=?, age=? WHERE id=?",
  delete: "DELETE FROM test WHERE id=?",
  queryById: "SELECT * FROM test WHERE id=?",
  queryAll: "SELECT * FROM test",
};
let teacher = {
  insert: "INSERT INTO teacher(id, name) VALUES(?,?)",
  update: "UPDATE teacher SET name=? WHERE id=?",
  delete: "DELETE FROM teacher WHERE id=?",
  queryById: "SELECT * FROM teacher WHERE id=?",
  queryAll: "SELECT * FROM teacher",
};

module.exports = {
  teacher,
  test,
};

6.封装接送模块

/**
 * Created by Walker on 2020/05/06.
 * 消息请求响应
 */
//封装接送模块
var json = function (res, result, err) {
  if (typeof result === "undefined") {
    res.json({
      code: "300",
      msg: "操作失败:" + err,
    });
  } else if (result === "add") {
    res.json({
      code: "200",
      msg: "添加成功",
    });
  } else if (result === "delete") {
    res.json({
      code: "200",
      msg: "删除成功",
    });
  } else if (result === "update") {
    res.json({
      code: "200",
      msg: "更改成功",
    });
  } else if (result.result != "undefined" && result.result === "select") {
    res.json({
      code: "200",
      msg: "查找成功",
      data: result.data,
    });
  } else if (result.result != "undefined" && result.result === "selectall") {
    res.json({
      code: "200",
      msg: "全部查找成功",
      data: result.data,
    });
  } else {
    res.json(result);
  }
};

module.exports = json;

ps: Demo正是初始阶段，还望多多指正或者其他建议，感激。

参考到的Blog:
express+mysql实现简单的增删改查