node.js拼接mysql工具

node.js中拼接mysql工具

前一阵子被迫分享了一次node.JS，在写sql时真是恼火的不得了。之前写php的时候发现php真是机智，php的双引号是可以识别变量的，而js就惨了，当写sql查询时，写变量得拼字符串，然后sql里本身就有不少的字符串，js的字符串还要单双循环。我真是欲哭无泪啊，写一个稍稍复杂点sql，就要改好几次。从网上找了半天，最后果断写一个。目前只支持增删改查，适合给用nodejs做后台写个小demo的玩家。

上源码：

var sqlJoin = (function () {
    var whereResolve = function (type, whereSwitch) {
        if (whereSwitch.where) {
            type += ' WHERE ';
        }
        var keyAry = (whereSwitch && whereSwitch.whereProp) ? whereSwitch.whereProp : [];
        if (keyAry.length) {

            for (let i = 0; i < keyAry.length; i++) {
                let c = keyAry[i];
                if (typeof c == 'object') {
                    for (var key in c) {
                        if (key != 'operator') type += '`' + key + '`' + (c['operator'] ? c['operator'] : '=') + '' + "'" + c[key] + "'";
                    }
                } else if (/(AND)?(OR)?/.test(c)) {
                    type += ' ' + c + ' ';
                } else if (/(\()?(\))?/.test(c)) {
                    type += c;
                }
            }
        }
        var likeKeyArr = whereSwitch.likeProp ? Object.keys(whereSwitch.likeProp) : [];
        if (likeKeyArr.length) {
            if (likeKeyArr.length > 1) {
                for (let j = 0; j < likeKeyArr.length; j++) {
                    let cur = likeKeyArr[j];
                    if (j == likeKeyArr.length - 1) {
                        type += cur + ' LIKE ' + "'%" + whereSwitch.likeProp[cur] + "%'";
                    } else {
                        type += cur + ' LIKE ' + "'%" + whereSwitch.likeProp[cur] + "%'" + ' AND ';
                    }
                }
            } else {
                for (let key in whereSwitch.likeProp) {
                    type += key + ' LIKE ' + "'%" + whereSwitch.likeProp[key] + "%'";
                }
            }
        }
        return type;
    };
    /**
     * @param sqlType Object query type-> 'INSERT' 'DELETE' 'UPDATE' 'SELECT'
     * @param tableName String the name of the enqueried table
     * @param whereSwitch [,Object] config the WHERE sentence
     * @param limitSwitch [,Object] config the LIMIT sentence
     * @returns String the sql query string joined by sqlJoin function
     
    var sqlJoin = function (sqlType, tableName, whereSwitch, limitSwitch) {
        var SELECT = '',
            UPDATE = '',
            DELETE = '',
            INSERT = '';
        switch (sqlType.type) {
            case 'SELECT':
                SELECT = 'SELECT ';
                if (sqlType.distinct) {
                    SELECT += ' DISTINCT '
                }
                sqlType.prop = sqlType.prop ? sqlType.prop : [];
                if (sqlType.prop && sqlType.prop.length) {
                    for (let i = 0; i < sqlType.prop.length; i++) {
                        var cur = sqlType.prop[i];
                        if (i == sqlType.prop.length - 1) {
                            SELECT += '`' + cur + '`';
                        } else {
                            SELECT += '`' + cur + '`,';
                        }
                    }
                } else {
                    SELECT += ' *'
                }
                SELECT += ' FROM ' + '`' + tableName + '`';
                if (whereSwitch) {
                    SELECT = whereResolve(SELECT, whereSwitch);
                }

                var orderArr = sqlType.orderProp ? Object.keys(sqlType.orderProp) : [];
                if (orderArr.length) {
                    SELECT += ' ORDER BY ';
                    for (let m = 0; m < orderArr.length; m++) {
                        let c = orderArr[m];
                        if (m == orderArr.length - 1) {
                            if (sqlType.orderProp[c]) {
                                SELECT += c + ' ASC'
                            } else {
                                SELECT += c + ' DESC'
                            }
                        } else {
                            if (sqlType.orderProp[c]) {
                                SELECT += c + ' ASC, '
                            } else {
                                SELECT += c + ' DESC, '
                            }
                        }
                    }
                }

                if (limitSwitch && limitSwitch.limit) {
                    SELECT += ' LIMIT ' + limitSwitch.num;
                }

                break;
            case 'UPDATE':
                UPDATE = 'UPDATE ';
                UPDATE += tableName + ' SET ';
                var updatekeys = Object.keys(sqlType.prop);
                for (let i = 0; i < updatekeys.length; i++) {
                    var cur = updatekeys[i];
                    if (i == updatekeys.length - 1) {
                        UPDATE += '`' + cur + '` =' + "'" + sqlType.prop[cur] + "'";
                    } else {
                        UPDATE += '`' + cur + '` =' + "'" + sqlType.prop[cur] + "', ";
                    }
                }
                if (whereSwitch && whereSwitch.where) {
                    UPDATE = whereResolve(UPDATE, whereSwitch);
                }
                if (limitSwitch && limitSwitch.limit) {
                    UPDATE += ' LIMIT ' + limitSwitch.num;
                }
                break;
            case 'DELETE':
                DELETE = 'DELETE FROM ' + tableName;
                if (whereSwitch && whereSwitch.where) {
                    DELETE = whereResolve(DELETE, whereSwitch);
                }
                if (limitSwitch && limitSwitch.limit) {
                    DELETE += ' LIMIT ' + limitSwitch.num;
                }
                break;
            case 'INSERT':
                INSERT = 'INSERT INTO ' + tableName;
                var insertProp = Object.keys(sqlType.prop);
                if (insertProp.length) {
                    for (let i = 0, len = insertProp.length; i < len; i++) {
                        var cur = insertProp[i];
                        if (i == 0) {
                            INSERT += '(`' + cur;
                        } else if (i == len - 1) {
                            INSERT += '`' + cur + '`)';
                        } else {
                            INSERT += '`, `' + cur + '`, ';

                        }
                    }
                    INSERT += ' VALUES ';
                    for (let j = 0, leng = insertProp.length; j < leng; j++) {
                        var curr = insertProp[j];
                        if (j == 0) {
                            INSERT += "('" + sqlType.prop[curr];
                        } else if (j == leng - 1) {
                            INSERT += "'" + sqlType.prop[curr] + "')";
                        } else {
                            INSERT += "','" + sqlType.prop[curr] + "', ";

                        }
                    }
                }
                break;
        }
        return {SELECT, UPDATE, DELETE, INSERT}[sqlType.type];
    };
    return sqlJoin
})();
exports.sqlJoin = sqlJoin;

用法示例：

• 新增

var str = sqlJoin(
     {type: 'INSERT', prop: {mNum: 7570, mName: '晓鑫', points: 14}},
     'members',
     {limit: true, num: 1}
 );
 console.log(str);
 //INSERT INTO members(`mNum`, `mName`, `points`) VALUES ('7570','晓鑫', '14')

• 删除

var str = sqlJoin(
     {type: 'DELETE'},
     'members',
     {where: true, whereProp: [{mId: 67}]},
     {limit: true, num: 1}
 );
 console.log(str);
 //DELETE FROM members WHERE `mId`='67' LIMIT 1

• 修改

var str = sqlJoin(
    {type: 'UPDATE', prop: {points: 14}},
    'members',
    {where: true, whereProp: [{mId: 24}]},
    {limit: true, num: 1}
);
console.log(str);
//UPDATE members SET `points` ='14' WHERE `mId`='24' LIMIT 1

• 查询—— %

var str = sqlJoin(
     {type: 'SELECT'},
     'members',
     {where: true, likeProp: {mNum: 75, mName: '刘', points: 12}},
     {limit: false, num: 1}
 );
 console.log(str);
 //SELECT  * FROM `members` WHERE mNum LIKE '%75%' AND mName LIKE '%刘%' AND points LIKE '%12%'

• 查询—— % + AND/OR

var str = sqlJoin(
     {type: 'SELECT'},
     'members',
     {where: true, whereProp: [{'mNum': 7501, operator: '='},'OR', {mName: '刘想',operator: '='}, 'AND',{points: 5, operator: '>'}]},
     {limit: false, num: 1}
 );
 console.log(str);
 //SELECT  * FROM `members` WHERE `mNum`='7501' OR `mName`='刘想'

• 查询—— % + AND+OR

var str = sqlJoin(
    {type: 'SELECT'},
    'members',
    {where: true, whereProp: ['(', {'mNum': 7501, operator: '='},'OR', {mName: '刘想',operator: '='},')', 'AND',{points: 5, operator: '>'}]},
    {limit: false, num: 1}
);
console.log(str);
//SELECT * FROM `members` WHERE ( `mNum`='7501' OR `mName`='刘想' ) AND `points`>'5'

• 查询——DESC/ASC

//true: ASC,false: DESC
var str = sqlJoin(
    {type: 'SELECT', orderProp: {points:false}},
    'members'
);
console.log(str);
//SELECT  * FROM `members` ORDER BY points DESC

• 查询——WHERE

var str = sqlJoin(
    {type: 'SELECT', prop: ['mName','points','mNum'], orderProp: {points: true}},
    'members',
    {where: true, whereProp: [{mId: 2, operator: '>'}]}
);
console.log(str);
//SELECT `mName`,`points`,`mNum` FROM `members` WHERE `mId`='24'

• 查询——distinct

/*var str = sqlJoin(
    {type: 'SELECT', distinct: true, prop: ['points'], orderProp: {points: true}},
    'members',
    {where: true, whereProp: [{mId: 2, operator: '>'}]}
);
console.log(str);
//SELECT  DISTINCT `points` FROM `members` WHERE `mId`>'2' ORDER BY points ASC