NodeJS同步事务方法2

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文件，结果我就不拿出来了。这个我已经在生产上用了，没问题。