封装

class Connect {

    static getInstance() {
        if (!Connect.instance) {
            Connect.instance = new Connect();
        }
        return Connect.instance;
    }

    constructor() {
        this.mysql = require('mysql');
        this.connection = this.mysql.createConnection({
            host: '127.0.0.1',
            user: 'root',
            password: '123456',
            database: 'database'
        });
        this.connection.connect();
    }

    query(sql) {
        // sql = String
        return this._operation(sql);
    }

    select(array, table, where, link) {
        // array = Array
        // table = String
        // where = { key: value }
        // link = 'AND' or 'OR' default 'AND'
        let sql = "SELECT ";
        array.forEach(((value, index) => {
            if (index === 0) {
                sql += value;
            } else {
                sql += ',' + value
            }
        }));
        sql += ' FROM ' + table;
        if (where) {
            sql += this._handleWhereString(where, link);
        }
        return this._operation(sql);
    }

    insert(info, table) {
        // info = { key: value }
        // table = String
        let sql = "INSERT INTO " + table + "(";
        let keyArray = [];
        let valueArray = [];
        Object.keys(info).forEach((key) => {
            keyArray.push(key);
            valueArray.push("'" + info[key] + "'");
        });
        let keyStr = keyArray.join(',');
        let valueStr = valueArray.join(',');
        sql += keyStr + ') ';
        sql += 'VALUES(' + valueStr + ')';
        return this._operation(sql);
    }

    update(info, table, where, link) {
        let sql = "UPDATE " + table + " SET ";
        let sqlArray = [];
        Object.keys(info).forEach((key) => {
            sqlArray.push(key + "='" + info[key] + "'");
        });
        sql += sqlArray.join(',');
        if (where) {
            sql += this._handleWhereString(where, link);
        }
        return this._operation(sql);
    }

    delete(info, table, where, link) {
        // info = { key: value }
        // table = String
        // where = { key: value }
        // link = 'AND' or 'OR' default 'AND'
        let sql = "DELETE FROM " + table;
        if (where) {
            sql += this._handleWhereString(where, link);
        }
        return this._operation(sql);
    }

    _operation(sql) {
        return new Promise((resolve, reject) => {
            this.connection.query(sql, (error, result, fields) => {
                if (error) {
                    console.log(error.message);
                    reject(error.message);
                } else {
                    resolve(result);
                }
            });
        })
    }

    _handleWhereString(where, link) {
        let str = "";
        let whereArray = [];
        Object.keys(where).forEach((key) => {
            whereArray.push(String(key + "='" + where[key] + "'"));
        });
        if (link) {
            let whereStr = whereArray.join(" " + link + " ");
            str += " WHERE " + whereStr;
        } else {
            let whereStr = whereArray.join(" AND ");
            str += " WHERE " + whereStr;
        }
        return str;
    }
}

module.exports = Connect.getInstance();

使用

class User {

    constructor() {
        this.db = require('../dao/connect');
    }

    test() {
        this.db.select(['name'], 'user', { name: 'qyizhong' }).then(result => {
            console.log(result[0].name);
        });
    }
}

module.exports = new User();

例子

一个简易的注册请求例子

app.post('/user/register', (req, res) => {
        let user = require('../controllers/user');
        let info = {
            name: req.body.username,
            password: req.body.password
        };
        user.register(info).then(result => {
            res.json(result);
        }).catch(err => {
            res.json(err);
        });
    });

class User {

    constructor() {
        this.db = require('../dao/connect');
    }

    register(json) {
        return new Promise((resolve, reject) => {
            this.db.select(['name'], 'user', { name: json.name }).then(result => {
                if (result.length === 0) {
                    this.db.insert(json, 'user').then(res => {
                        resolve({code: 0, msg: 'ok'});
                    }).catch(err => {
                        reject({code: -1, msg: 'error'});
                    });
                } else {
                    reject({code: -1, msg: '已经存在此账号'});
                }
            });
        });
    }

}

module.exports = new User();