node.js mysql事务处理及封装

1、封装底层单例数据连接公用库

const mysql = require('mysql')
const dbconnection = require('../config/config')
/**
 *创建数据连接池
 */
const pool = mysql.createPool({
  host: dbconnection.config.database.HOST,
  user: dbconnection.config.database.USERNAME,
  password: dbconnection.config.database.PASSWORD,
  database: dbconnection.config.database.DATABASE
})
/**
 * 非事务执行
 * @param {string} sql 
 * @param {obj} values 
 */
const query =async function(sql, values) {
    return new Promise((resolve, reject) => {
      pool.getConnection(function(err, connection) {
        if (err) {
          resolve(err)
        } else {
          connection.query(sql, values, (err, rows) => {
            if (err) {
              reject(err)
            } else {
              resolve(rows)
            }
            connection.release()
          })
        }
      })
    })
  }
  /**
   * 创建一个数据库连接
   */
  const getConnection=async function(){
    return new Promise((resolve, reject) => {
      pool.getConnection(function(err, connection) {
        if (err) {
          reject(err)
        } else {
          resolve(connection)
        }
      })
    })
  }
  class DBUnity{
    /**
     * 开启数据库连接
     * @param {object} connection 
     */
    constructor(connection){
      this.blTransaction=false;
      this.connection=connection;
      /**
       * 开启事务
       */
      this.beginTransaction=async()=>{
        return new Promise((resolve, reject) => {
          this.connection.beginTransaction((err,success)=>{
            if (err) {
              this.blTransaction=false;
              reject(err)
            } else {
              this.blTransaction=true;
              resolve(success)
            }
          })
        })
      };
      /**
       * 执行sql
       * @returns {object} is class dbResult
       */
      this.query=async(sql)=>{
        return new Promise((resolve, reject) => {
          this.connection.query(sql,(err, rows)=>{
            if(!this.blTransaction){
              this.connection.release();
              reject(new dbResult(0,false,"事务未开启",null))
            }
            if (err) {
              reject(new dbResult(0,false,JSON.stringify(err),null))
            } else {
              resolve(new dbResult(1,true,"success",rows))
            }
          })
        })
      };
      /**
       * 提交事务
       */
      this.commit=async()=>{
        return new Promise((resolve, reject) => {
          this.connection.commit((err)=>{
            if(err){
              this.connection.release();
              reject(err)
            }else{
              this.connection.release();
            }
          })
        })
      };
      /**
       * 回滚事务
       */
      this.rollback=async()=>{
        this.connection.rollback(()=>{
          this.connection.release();
        })
      }
    }
  }

  class dbResult{
    /**
   * 数据层结果返回
   * @param {number} code 返回状态码 0:操作失败,1:操作成功
   * @param {boolean} success 
   * @param {string} message 返回信息
   * @param {object} data 返回数据对象
   */
    constructor(code,success,message,data){
      this.code = code;
      this.message = message;
      this.data = data;
      this.success = success;
    }
  }

  module.exports = {
    query,
    DBUnity,
    getConnection
  }

2、引用

const DBfactory =require('../../mysql/DBfactory') 

const dbtran=async()=>{
  //创建一个连接对象并初始化连接
  let DBConn=new DBfactory.DBUnity(await DBfactory.getConnection())
  //开启事务
  await DBConn.beginTransaction();
  try{
    //执行语句
    let query1=await DBConn.query("select username from user");
    //执行结果
    if(!query1.success){
      //回滚事务并释放连接
      DBConn.rollback()
      return [];
    }
    let query2=await DBConn.query("insert into test2(str) values('huv')");
    if(!query2.success){
      DBConn.rollback()
      return [];
    }
    //提交事务并释放连接
    DBConn.commit()
  }
  catch(e){
    DBConn.rollback()
    console.log(e)
  }
}
module.exports = {
  dbtran
}