最近要開(kāi)發(fā)一個(gè)全棧項(xiàng)目。第一次上車(chē)，小本本記錄一下，以免忘記。

1.目錄結(jié)構(gòu)

先來(lái)看一下項(xiàng)目目錄：

web文件夾：是放一些前端的代碼；
node_serve：服務(wù)端的一些文件；
– server.js：服務(wù)端入口文件；
– db : 有關(guān)數(shù)據(jù)庫(kù)的一些文件；
---- db.js：數(shù)據(jù)庫(kù)增、刪、改、查api的封裝；
---- dbconfig.js: 數(shù)據(jù)庫(kù)的配置
---- json.js: 消息請(qǐng)求響應(yīng)(暫時(shí)放在這里先)；
---- sql.js: 對(duì)操作數(shù)據(jù)庫(kù)不同表的sql語(yǔ)句的封裝
數(shù)據(jù)庫(kù)目錄：

2.入口文件：

/*** Created by Walker on 2020/04/30.* 服務(wù)器入口文件* 熱加載 supervisor server.js*/ const express = require("express"); const app = express(); var url = require("url"); var bodyParser = require("body-parser"); //格式化請(qǐng)求消息的中間件 const db = require("./db/db.js");app.use(bodyParser.urlencoded({ extended: false })); //設(shè)置允許跨域的域名，*代表允許任意域名跨域 app.all("*", function (req, res, next) {res.header("Access-Control-Allow-Origin", "*"); //允許的header類(lèi)型res.header("Access-Control-Allow-Headers", "Content-type"); //跨域允許的請(qǐng)求方式res.header("Access-Control-Allow-Methods","PUT,POST,GET,DELETE,OPTIONS,PATCH"); //可選，用來(lái)指定本次預(yù)檢請(qǐng)求的有效期，單位為秒。在此期間，不用發(fā)出另一條預(yù)檢請(qǐng)求。res.header("Access-Control-Max-Age", 1728000); //預(yù)請(qǐng)求緩存20天next(); });//下面是一些供前端使用的測(cè)試接口； //get和post方法，對(duì)從前端接收的請(qǐng)求data的處理會(huì)有區(qū)別，可以注意一下； 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.* 數(shù)據(jù)庫(kù)的配置*/ var mysqlConfig = {host: "localhost",user: "root",password: "123456",port: "3306",database: "sqltest", };module.exports = mysqlConfig

4.數(shù)據(jù)庫(kù)連接、API的封裝

/*** Created by Walker on 2020/05/06.* 數(shù)據(jù)庫(kù)增、刪、改、查封裝*/ //mysql連接池配置文件 const mysql = require("mysql"); const $dbConfig = require("./dbConfig"); //注意改成自己項(xiàng)目中mysql配置文件的路徑 const sql = require("./sql.js"); //sql語(yǔ)句封裝 const pool = mysql.createPool($dbConfig); // 使用連接池，避免開(kāi)太多的線(xiàn)程，提升性能 const json = require("./json");/*** @description 新增一條數(shù)據(jù)* @param {str} table 數(shù)據(jù)庫(kù)表的名稱(chēng)* @param {obj} req 插入的數(shù)據(jù)* @param {obj} res 接口函數(shù)中的res對(duì)象* @param {obj} next 接口函數(shù)中的next對(duì)象*/ 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形式，把操作結(jié)果返回給前臺(tái)頁(yè)面json(res, result, err);// 釋放連接connection.release();});}); };/***@description 刪除一條數(shù)據(jù)@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 修改一條數(shù)據(jù)@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 查找一條數(shù)據(jù)@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 查找全部數(shù)據(jù)@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 遍歷數(shù)據(jù)的值* @param {obj} obj 包含參數(shù)的對(duì)象* */ 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語(yǔ)句的封裝

/*** Created by Walker on 2020/05/06.* 對(duì)操作不同表，sql語(yǔ)句的封裝*/ 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.* 消息請(qǐng)求響應(yīng)*/ //封裝接送模塊 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實(shí)現(xiàn)簡(jiǎn)單的增刪改查

總結(jié)

以上是生活随笔為你收集整理的NodeJS（express）连接、封装操作MySQL的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。