摘要：中拼接工具前一陣子被迫分享了一次，在寫時真是惱火的不得了。我真是欲哭無淚啊，寫一個稍稍復雜點，就要改好幾次。目前只支持增刪改查，適合給用做后臺寫個小的玩家。上源碼用法示例新增曉鑫曉鑫刪除修改查詢劉劉查詢劉想劉想查詢劉想劉想查詢查詢查詢

前一陣子被迫分享了一次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