An article to talk about how node encapsulates mysql processing statements

How to encapsulate mysql in node? The following article will introduce to you how node.js encapsulates mysql processing statements. I hope it will be helpful to you!

In the past interfaces, related processing statements were generally written directly in the routing, resulting in code redundancy. Today, the express framework is used to encapsulate related mysql statements.

1. Required packages

npm i mysql -S
npm i express -S

##2.MySql connection file

const mysql = require('mysql')function createConnection() {
    const connection = mysql.createConnection({
        host: '',  //地址
        user: '',  //用户名
        password: '',  //密码
        port: '',  //端口
        database: ''  //数据库名
    });
    return connection;}module.exports.createConnection = createConnection;

【Related tutorial recommendations:

nodejs video tutorial, Programming teaching】

##3. Encapsulation file

Introduce MySQL connection related files to connect to the database

const mysql = require('../mysql/mysql')let connection = null;connection = mysql.createConnection();/**
 * 错误消息
 */let bad_msg = {
    code: 500,
    msg: '内部错误！'}/**
 * 成功消息
 */let success_msg = {
    code: 200,
    msg: '操作成功'}const connections = {
    /**
     * 查询方法
     * @param {*} table 表名
     * @param {*} condition 条件
     * @param {*} params 参数
     * @param {*} search 查询条件
     * @returns 
     */
    find(table, condition, params, search = '*') {
        return new Promise((resolve, reject) => {
            let sql = `SELECT ${search} FROM ${table} WHERE ${condition}`
            connection.query(sql, params, (err, result) => {
                if (err) {
                    reject(bad_msg)
                } else {
                    let _ = JSON.parse(JSON.stringify(success_msg))
                    _.data = result                    resolve(_)
                }
            })
        })
    },
    /**
     * 插入方法
     * @param {*} table 表名
     * @param {*} condition 条件
     * @param {*} params 参数
     * @returns 
     */
    insert(table, condition, params) {
        return new Promise((resolve, reject) => {
            const str = "?"
            let _ = str.repeat((condition.split(',')).length)
            let val = (Array.from(_)).toString()
            let sql = `INSERT INTO ${table}(${condition}) VALUES(${val})`
            connection.query(sql, params, (err, result) => {
                if (err) {
                    reject(bad_msg)
                } else {
                    resolve(success_msg)
                }
            })
        })
    },
    /**
     * 更新方法
     * @param {*} table 表名
     * @param {*} val 值
     * @param {*} condition 条件
     * @param {*} params 参数
     * @returns 
     */
    update(table, val, condition, params) {
        return new Promise((resolve, reject) => {
            let sql = `UPDATE ${table} SET ${val} WHERE ${condition}`
            connection.query(sql, params, (err, result) => {
                if (err) {
                    reject(bad_msg)
                } else {
                    resolve(success_msg)
                }
            })
        })
    },
    /**
     * 删除方法
     * @param {*} table 表名
     * @param {*} condition 条件
     * @param {*} params 参数
     * @returns 
     */
    del(table, condition, params) {
        return new Promise((resolve, reject) => {
            let sql = `DELETE FROM ${table} WHERE ${condition}`
            connection.query(sql, params, (err, result) => {
                if (err) {
                    reject(bad_msg)
                } else {
                    resolve(success_msg)
                }
            })
        })
    },}module.exports = connections

4. Use We use login registration to demonstrate:

const express = require('express')const router = express.Router()const connections = require('../../static/connection')// token生成插件模块const jwt = require('jsonwebtoken');// Token签名var secret = ''const CreatId = require('../../static/ranId')router.post('/user/details', (req, res) => {
    connections.find('user_table', `ID=?`,req.user.ID).then(resp => {
        res.send(resp)
    })})router.post('/api/login', (req, res) => {
    connections.find('user_table', 'user=?', req.body.user).then(resp => {
        let {data} = resp        if (data.length !== 0) {
            for (let i = 0; i < data.length; i++) {
                // 邮箱或者密码不正确的时候
                if (req.body.user !== data[i].user || req.body.pwd !== data[i].pwd) {
                    res.send({
                        code: 202,
                        msg: &#39;用户名或密码有误!&#39;
                    })
                } else {
                    // 邮箱和密码输入正确
                    if (req.body.user === data[i].user && req.body.pwd === data[i].pwd) {
                        // 传输的token内容
                        let token = jwt.sign({ ID: data[i].ID }, secret, { expiresIn: &#39;72H&#39; });
                        // 返回结果
                        res.send({
                            code: 200,
                            msg: &#39;操作成功！&#39;,
                            token: &#39;Bearer &#39; + token,
                        })
                    }
                }
            }
        } else {
            res.send({
                code: 400,
                msg: &#39;账号不存在请注册!&#39;
            })
        }
    }).catch(e => {
        res.send(e)
    })})router.post('/api/register', (req, res) => {
    connections.find('user_table', 'user=?', req.body.user).then(resp => {
        if (resp.data.length > 0) {
            res.send({
                code: 202,
                msg: '该用户已经存在！'
            })
        } else {
            let _ = req.body            let id = CreatId(3) + CreatId(3)
            connections.insert('user_table', 'ID,user,pwd,avatarUrl,location,RegisterTime,isAdmin,isDel', [id, _.user, _.pwd, '/static/userimg/user.webp', _.location, Date.now(), 0, 0]).then(resps => {
                // 传输的token内容
                let token = jwt.sign({ ID: id }, secret, { expiresIn: '72H' });
                // 返回结果
                res.send({
                    code: 200,
                    msg: '操作成功！',
                    token: 'Bearer ' + token,
                })
            })
        }
    })})module.exports = router

For more node-related knowledge, please visit:

nodejs tutorial

!

The above is the detailed content of An article to talk about how node encapsulates mysql processing statements. For more information, please follow other related articles on the PHP Chinese website!