js 拼接mysql语句_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