NodeJS同步事务方法
基于之前的[事务工具]做的微调,用起来更加便捷,主要代码调到(https://blog.csdn.net/CaryChen198/article/details/98495338)
调整内容,update和delete支持判断,不多说,直接上代码
var mysql = require('promise-mysql');
var config = require('../../config');//配置文件
var db_config = config.mysql.default;//mysql连接池配置
/**
* 调用顺序,先调用_getNewSqlParamEntity对SQL和参数进行封装
* 封装完成之后 var err =await sql_dbHelper.execTrans([sql,param]);
* if(err==false){
* }
*/
function execQuery(connection, sql, params){
return new Promise(function (resolve, reject) {
connection.query(sql, params, function (e, results) {
console.log('执行事务execQuery:',e);
if (e) {
resolve(false);
} else {
resolve(true);
}
});
})
}
module.exports = {
/**
* 组装SQL_map
* @param sql sql语句字符串,带占位符
* @param params 数组,如果存在占位符,传入[xx,xx,xx],如果没有传入[]
* @param callback
* @returns {*}
* @private
*/
_getNewSqlParamEntity: function (sql, params, callback) {
if (callback) {
return callback(null, {
sql: sql,
params: params
});
}
return {
sql: sql,
params: params
};
},
/**
* 执行mysql操作
* @param sqlparamsEntities SQL数组及数据
* @returns {Promise}
*/
execTrans:async function (sqlparamsEntities) {
return new Promise(function (resolve, reject) {
mysql.createConnection(db_config).then(async function (res) {
let connection = res.connection;
try {
connection.on('error', function (err) {
throw err;
});
connection.beginTransaction();
for (var i in sqlparamsEntities) {
console.log('执行事务SQL:', sqlparamsEntities[i].sql);
console.log('执行事务param:', sqlparamsEntities[i].params);
let r = await execQuery(connection,sqlparamsEntities[i].sql, sqlparamsEntities[i].params);
if (!r) {
throw r;
}
}
connection.commit();
resolve(true);
} catch (e) {
connection.rollback();
resolve(false);
} finally {
connection.end(function (error) {
});
}
});
})
},
/**
* 新增
* @param sqlParamsEntity SQL数组
* @param table_name 新增表名
* @param param 数据
* @returns {Promise<*>}
*/
save:async function (sqlParamsEntity, table_name, param) {
var columns = [];
var holders = [];
var values = [];
for (var column in param) {
columns.push(column);
holders.push('?');
values.push(param[column]);
}
var sql = 'insert into ' + table_name + '(' + columns.join(',') + ') values ( ' + holders.join(',') + ' ) ';
console.log('mysql insert sql:', sql);
console.log(values);
sqlParamsEntity.push(this._getNewSqlParamEntity(sql, values));
return sqlParamsEntity;
},
/**
* 修改
* @param sqlParamsEntity SQL数组
* @param table_name 修改的表名
* @param set 修改的值
* @param where 修改条件 可带> < >= <= != 不传为= {name:'!=张三'}
* @returns {Promise<*>}
*/
update:async function (sqlParamsEntity, table_name, set,where) {
var columns = [];
var holders = [];
var values = [];
for (var column in set) {
columns.push(column+'= ?');
values.push(set[column]);
}
if(where.length==0){
return sqlParamsEntity;
}
for(var column in where){
var values = where[column];
if(values.indexOf('!=')!=-1){
values = values.split('!=')[1];
holders.push(" and "+column+" != ?");
}else if(values.indexOf('>')!=-1){
values = values.split('>')[1];
holders.push(" and "+column+" > ?");
}else if(values.indexOf('>=')!=-1){
values = values.split('>=')[1];
holders.push(" and "+column+" >= ?");
}else if(values.indexOf('<')!=-1){
values = values.split('<')[1];
holders.push(" and "+column+" < ?");
}else if(values.indexOf('<=')!=-1){
values = values.split('<=')[1];
holders.push(" and "+column+" <= ?");
}else{
holders.push(" and "+column+" = ?");
}
columns.push(values);
}
var sql = 'update ' + table_name + ' set ' + columns.join(',') + ' where 1=1 ' + holders.join(' ');
console.log('mysql update sql:', sql);
console.log(values);
sqlParamsEntity.push(this._getNewSqlParamEntity(sql, values));
return sqlParamsEntity;
},
/**
* 删除数据
* @param sqlParamsEntity SQL数组
* @param table_name 表名
* @param where 条件 可带> < >= <= != 不传为= {name:'!=张三'}
* @returns {Promise<void>}
*/
del:async function(sqlParamsEntity, table_name,where){
if(where.length==0){
return sqlParamsEntity;
}
var where_sql = [];
var whereParam = [];
for(var column in where){
var values = where[column];
if(values.indexOf('!=')!=-1){
values = values.split('!=')[1];
where_sql.push(" and "+column+" != ?");
}else if(values.indexOf('>')!=-1){
values = values.split('>')[1];
where_sql.push(" and "+column+" > ?");
}else if(values.indexOf('>=')!=-1){
values = values.split('>=')[1];
where_sql.push(" and "+column+" >= ?");
}else if(values.indexOf('<')!=-1){
values = values.split('<')[1];
where_sql.push(" and "+column+" < ?");
}else if(values.indexOf('<=')!=-1){
values = values.split('<=')[1];
where_sql.push(" and "+column+" <= ?");
}else{
where_sql.push(" and "+column+" = ?");
}
whereParam.push(values);
}
var sql = 'delete ' + table_name + ' where 1=1 ' + where_sql.join(' ');
console.log('mysql delete sql:', sql,values);
sqlParamsEntity.push(this._getNewSqlParamEntity(sql, whereParam));
return sqlParamsEntity;
}
}
调用:创建一个测试文件
var sql_dbHelper = require('sql_dbHelper');//上面代码的文件
/**
*测试用
*/
async function testDB(){
try{
var sql_param =[];
await sql_dbHelper.save(sql_param,'test',{name:"张三",age:"18",sex:"男"});
await sql_dbHelper.save(sql_param,'test',{name:"李四",age:"22",sex:"男"});
await sql_dbHelper.update(sql_param,'test',{name:"王五"},{id:"2"});//修改id为2的
await sql_dbHelper.update(sql_param,'test',{name:"马六"},{age:" >=18"});//修改年龄大于等于18的
await sql_dbHelper.del(sql_param,'test',{id:1});//删除id等于1的
await sql_dbHelper.del(sql_param,'test',{age:'<18',sex:'!=男'});//删除年龄小于18且性别不是男的所有数据
const result = await sql_dbHelper.execTrans(sql_param);
if(result == false)console.log("操作失败");
else console.log("事务执行成功");
}catch(e){
console.log("执行测试异常:",e);
}
testDB();
最后命令执行testDB.js文件,结果我就不拿出来了。这个我已经在生产上用了,没问题。