原生node连接mysql增删查改

为了写自己的博客开始学node了，记录一下查资料加网课自己学习写的接口，没用框架，把原生的node包装成promise返回
目录结构

需要使用的mysql语句

个人环境
cross-env适配环境，nodemon热部署

{
    "name": "project",
    "version": "1.0.0",
    "description": "",
    "main": "app.js",
    "scripts": {
        "test": "echo \"Error: no test specified\" && exit 1",
        "dev": "cross-env NODE_ENV=dev nodemon ./app.js",
        "prd": "cross-env NODE_ENV=production nodemon ./index.js"
    },
    "author": "",
    "license": "ISC",
    "dependencies": {
        "mysql": "^2.18.1"
    },
    "devDependencies": {
        "@types/express": "^4.17.13",
        "@types/node": "^17.0.32",
        "cross-env": "^7.0.3",
        "nodemon": "^2.0.16"
    }
}

mysql配置 config.js

let MYSQL_CONF
const env = process.env.NODE_ENV
    //获取现在的进程环境

if (env === 'dev') {
    MYSQL_CONF = {
        host: 'localhost',
        port: 3306,
        user: 'root',
        password: 'xxx',
        database: 'blog'
    }
}
if (env === 'production') {
    MYSQL_CONF = {
        host: 'localhost',
        port: 3306,
        user: 'root',
        password: 'xxx',
        database: 'blog'
    }
}

module.exports = MYSQL_CONF

用promise封装mysql的连接池
mysql.js

const mysql = require('mysql'); //引入mysql
const MYSQL_CONF = require('./config') //引入配置

//创建连接池
const pool = mysql.createPool(MYSQL_CONF);

function exec(sql, params = []) {
    return new Promise((resolve, reject) => {
        pool.getConnection(function(err, conn) {
            if (err) {
                console.log('连接失败');
                return
            }
            console.log('连接成功');
            conn.query(sql, params, (err, result) => {
                if (err) {
                    console.error(err);
                    reject(err);
                } else {
                    console.log('查询成功');
                    // console.log('result:', result);
                    resolve(result);
                }
            })
            conn.release();
        })
    })
}

module.exports = exec;

定义成功和失败后返回的模型
resModel.js

class BaseModel {
    constructor(data, message) {
        if (typeof data === 'string') {
            this.message = data
            data = null
            message = null
        }
        if (data) {
            this.data = data
        }
        if (message) {
            this.message = message
        }
    }
}

class SuccessModel extends BaseModel {
    constructor(data, message) {
        super(data, message)
        this.errno = 0
    }
}
class ErrorModel extends BaseModel {
    constructor(data, message) {
        super(data, message)
        this.errno = -1
    }
}

module.exports = {
    SuccessModel,
    ErrorModel
}

controller层

const exec = require('../../db/mysql');

const getList = (author, keyword) => {
    let sql = `select * from article where 1=1 `
    if (author) {
        sql += `and author ='${author}'`
    }
    if (keyword) {
        sql += `and keyword like '%${keyword}%' `
    }
    sql += `order by id desc;`
    return exec(sql)
}

const getDetail = (id) => {
    const sql = `select * from article where id = ${id}`
    return exec(sql).then(article => {
        return article[0]
    })
}

const insertBlog = (blogData) => {
    const sql = `insert into article (author,title,content,data,tag) values (?,?,?,?,?)`;
    const data = new time;
    const params = [
        blogData.author,
        blogData.title,
        blogData.content,
        data,
        blogData.tag
    ]
    return exec(sql, params).then(insertData => {
        if (insertData.insertId)
            return insertData.insertId
    })
}

const deleteBlog = (id, author) => {
    const sql = `delete from article where id = ${id} and author = '${author}' `
    return exec(sql).then(deleteData => {
        // console.log('updateData', updateData);
        if (deleteData.affectedRows > 0)
            return true
        else
            return false
    })
}

const updateBlog = (id, blogData = {}) => {
    //id 是要更新博客的id
    //blogData是一个博客对象，包含title content 属性
    const title = blogData.title;
    const content = blogData.content;
    const sql = `update article set title = '${title}',content = '${content}' WHERE id = ${id}`
    return exec(sql).then(updateData => {
        // console.log('updateData', updateData);
        if (updateData.affectedRows > 0)
            return true
        else
            return false
    })
}


module.exports = {
    getList,
    getDetail,
    updateBlog,
    insertBlog,
    deleteBlog
}

Router使用

const {
    getList,
    getDetail,
    updateBlog,
    insertBlog,
    deleteBlog
} = require('../controller/blog');
const { SuccessModel, ErrorModel } = require('../model/resModel.js');


//原生
const handelBlogRouter = (req, res) => {
    const method = req.method //得到是get 还是post请求

    //获取博客列表
    if (method === 'GET' && req.path === '/api/blog/list') {
        const author = req.query.author || ''
        const keyword = req.query.keyword || ''
        const result = getList(author, keyword)
        return result.then((listData) => {
            return new SuccessModel(listData, '这是获取博客列表的接口')
        }).catch((err) => {
            return new ErrorModel()
        });

    }
    //获取博客详情
    if (method === 'GET' && req.path === '/api/blog/detail') {
        const id = req.query.id
        const result = getDetail(id)
        return result.then((article) => {
            return new SuccessModel(article)
        })

    }
    //新增一篇博客
    if (method === 'POST' && req.path === '/api/blog/new') {
        const blogData = req.body
        const result = insertBlog(blogData)
        return result.then(val => {
            console.log(val);
            return new SuccessModel(`插入到了第${val}行`)
        }).catch(err => {
            return new ErrorModel(err.sqlMessage)
        })
    }

    //更新一篇博客
    if (method === 'POST' && req.path === '/api/blog/update') {
        const id = req.query.id
        const blogData = req.body
        const result = updateBlog(id, blogData)
        return result.then((val) => {
            if (val) {
                return new SuccessModel('更新成功')
            } else {
                return new ErrorModel('更新失败')
            }
        })
    }


    //删除一篇博客
    if (method === 'POST' && req.path === '/api/blog/delete') {
        const id = req.query.id
        const author = req.body.author
        const result = deleteBlog(id, author)
        return result.then((val) => {
            if (val) {
                return new SuccessModel('删除成功')
            } else {
                return new ErrorModel('删除失败')
            }
        })
    }

}
module.exports = handelBlogRouter

app.js入口

//使用原生http
const http = require('http');
const querystring = require('querystring')
const PORT = 8000;
const handleBlogRouter = require('./src/routes/blog')

//用于处理post data
const getPostData = (req) => {
    const promise = new Promise((resolve, reject) => {
        if (req.method !== 'POST') {
            resolve({})
            return
        }
        let postData = ''
        req.on('data', chunk => {
            postData += chunk.toString()
        })
        req.on('end', () => {
            if (!postData) {
                resolve({})
                return
            }
            resolve(
                JSON.parse(postData)
            )
        })
    })
    return promise;
}

const serverHandle = (req, res) => {
    //设置返回格式为JSON
    res.setHeader('Content-type', 'application/json');

    //获取path
    const url = req.url;
    req.path = url.split('?')[0];

    //解析query
    req.query = querystring.parse(url.split('?')[1])

    //处理post Data
    getPostData(req).then(postData => {
        req.body = postData
        //使用promise
        const blogResult = handleBlogRouter(req, res);
        if (blogResult) {
            blogResult.then(blogData => {
                res.end(
                    JSON.stringify(blogData)
                );
            })
            return
        }
        res.writeHead(404, { "Content-type": "text/plain" })
        res.write('404 Not Fond')
        res.end()
    })
}
const server = http.createServer(serverHandle)
server.listen(PORT, () => {
    console.log('8000端口打开');
})