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实现简单的增删改查