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