Node.js实现简单的mysql数据库构造函数， 主要是语句生成

这里主要用到了 npm 中的 underscore 包， 需要提前安装

首先创建一个makeUpSql.js文件， 然后将下面代码复制下

var u = require('underscore');
/*插入单行*/
module.exports = class makeUpSql {
    insert(data, tbName){
        this.sql = `INSERT INTO ${tbName}(`;
        u.map(data, (v, k) => {
            this.sql += k + ',';
        });
        this.sql = this.sql.substr(0, this.sql.length - 1) + ") VALUES ('";
        u.each(data, (v) => {
            this.sql += v + "','";  
        })
        this.sql = this.sql.substr(0, this.sql.length - 2) + ")";
        return this;
    }

    update(data, tbName){
        this.where_count = 0;
        this.sql = `UPDATE ${tbName} SET `;
        u.map(data, (v, k) => {
            this.sql += k + "='" + v + "',";
        })
        this.sql = this.sql.substr(0, this.sql.length - 1);
        return this;
    }

    where(left, right = null){
        if(this.where_count == 0)
        {
            if(u.isNull(right)){
                this.sql += "WHERE '" + left + "'";
                this.where_count++;
            }
            this.sql += " WHERE " + left  + "='" + right + "'";
            this.where_count++;
        }
        else
        {
            if(u.isNull(right)){
                this.sql += ' AND ' + "'" + left + "'";
            }
            else{
                this.sql += ' AND ' + left + '=' + "'" + right + "'";
            }
            this.where_count++ ;
        }
        return this;
    }

    select(fields){
        this.where_count = 0;
        this.sql = `SELECT ${fields}`;
        return this;
    } 

    from(tbName){
        this.sql += ' FROM ' + tbName;  
        return this;
    }

    limit(start, length){
        this.sql += ' LIMIT ' + start + ',' + length;   
        return this;
    }

    orderBy(condition){
        this.sql += " ORDER BY " + condition; 
        return this;
    }

    result(){
        return this.sql;    
    }


}

接着在主文件index.js中require(‘./makeUpSql’), 引入次文件便可以使用。

var makeUpSql = new (require('./makeUpSql'));
//数据库构造函数使用例子
## 插入##
makeUpSql.insert(data, tbName).result();   

//data:{'name': 'hello world', 'age': '17', ...}data为对象键值对, name:字段, hello world为值等. tbName: 表名称。

## 查询 ##

makeUpSql.select('*').from('tbName').where('x', 
3).where('y',4).orderBy('id DESC').limit(0, 10).result(); 

//生成如下代码:

SELECT * FROM tbName WHERE x='3' AND y='4' ORDER BY id DESC LIMIT 0, 10;

## 更新 ##

makeUpSql.update(data, tbName).where('x', 3).result();

///data:{'name': 'hello world', 'age': '17', ...}data为对象键值对:

UPDATE tbName SET name='hello world', age='17' WHERE x=3 ;

删除操作目前还没添加

接着我们可以npm install mysql 安装mysql模块，
在mysql中connction.query();中使用此构造函数

        var mysql   = require('mysql');
        connection = mysql.createConnection({
          host     : 127.0.0.1
          user     : 'test',
          password : 'test',
          database : 'database',
        });
        connection.connect();
        var sql = makeUpSql.select('*').from('tbName').where('x', 3).where('y',4).orderBy('id DESC').result(); 
connection.query(sql, function(err, rows) {
...
return rows;   //获取数据库中的相应数据
});

大家可以通过这个随意组合简单的mysql语句