NodeJs operates on MySQL classes

This class encapsulates several commonly used methods: insert, update, delete, query, open transaction, transaction submission, transaction rollback and so on. With this class, it is much more convenient to operate MYSQL.

Batch processing, stored procedures and other methods have not been added, because I feel that there is no batch processing used in the current writing, so there is no batch processing added here, and if later used to batch processing, plus.

In the past, when using C # to operate databases, there were similar operation classes: MSSQL Helper, Oracle Helper, MySQL Helper and so on. Now we just write an operation MySql with NodeJs. If we want to operate other databases, we should be able to write it in this way.

I'll talk about it later. If you're in a hurry, you can come to my github.( https://github.com/xiaotuni/angular-map-http2 ) Download the project and run it.

const mysql = require('mysql');

/**
 * Type of operation, insert, update, delete, query
 */
const OperatorType = {
  Insert: 0,
  Update: 1,
  Delete: 2,
  QueryList: 3,
  QueryOne: 4,
}

/**
 * Data manipulation class
 * QueryOne,Query,InsertSQL,DeleteSQL,UpdateSQL,BeginTransaction,Rollback,Commit
 * 
 * @class MySqlHelper
 */
class MySqlHelper {

  constructor() {
    this.__CreatePool();
  }

  /**
   * Create a resource pool
   * 
   * @memberof MySqlHelper
   */
  __CreatePool() {
    this.pool = mysql.createPool({
      connectionLimit: 10,
      host: 'localhost', // Database Connection
      user: 'liaohb',    // Database Name User Name
      password: 'xiaotuni', // Password
      database: 'nodejs'   // Tablespace
    });
  }

  /**
   * Resource pool information
   * 
   * @param {any} error Error Event Error Function
   * @returns 
   * @memberof MySqlHelper
   */
  poolInfo(error) {
    if (!this.pool) {
      this.__CreatePool();
    }
    if (!this.pool) {
      error && error({ code: 500, msg: 'Failure of database connection' });
      return null;
    }
    return this.pool;
  }
  /**
   * Insert operation
   * 
   * @param {any} sql Insert statement
   * @param {any} success Method invoked after success
   * @param {any} error Method invoked after failure
   * @memberof MySqlHelper
   */
  Query(sql, success, error) {
    this.__ExecuteSQL(sql, success, error, OperatorType.QueryList);
  }
  /**
   * Query operation, get a statement
   * 
   * @param {any} sql Insert statement
   * @param {any} success Method invoked after success
   * @param {any} error Method invoked after failure
   * @memberof MySqlHelper
   */
  QueryOne(sql, success, error) {
    this.__ExecuteSQL(sql, success, error, OperatorType.QueryOne);
  }
  /**
   * update operation
   * 
   * @param {any} Sql Modify statement
   * @param {any} Success Method invoked after success
   * @param {any} Error Method invoked after failure
   * @memberof MySqlHelper
   */
  UpdateSQL(Sql, Success, Error) {
    this.__ExecuteSQL(Sql, Success, Error, OperatorType.Update);
  }

  /**
   * Insert operation
   * 
   * @param {any} Sql Insert statement
   * @param {any} Success Method invoked after success
   * @param {any} Error Method invoked after failure
   * @memberof MySqlHelper
   */
  InsertSQL(Sql, Success, Error) {
    this.__ExecuteSQL(Sql, Success, Error, OperatorType.Insert);
  }

  /**
   * Delete operation
   * 
   * @param {any} Sql Delete statement
   * @param {any} Success Method invoked after success
   * @param {any} Error Method invoked after failure
   * @memberof MySqlHelper
   */
  DeleteSQL(Sql, Success, Error) {
    this.__ExecuteSQL(Sql, Success, Error, OperatorType.Delete);
  }

  /**
   * Executing SQL statements
   * 
   * @param {any} Sql SQL Sentence
   * @param {any} Success Method invoked after success
   * @param {any} Error Method invoked after failure
   * @param {any} Type Type [query, update, delete, modify, etc]
   * @returns 
   * @memberof MySqlHelper
   */
  __ExecuteSQL(Sql, Success, Error, Type) {
    const __self = this;
    const __ProcessResult = (__sql, result, fields, Type) => {
      const _type = Type || OperatorType.QueryOne;
      let __result = result;
      switch (Type) {
        case OperatorType.Insert:
          const { insertId } = result;
          __result = { insertId };
          break;
        case OperatorType.Delete:
          break;
        case OperatorType.Update:
          break;
        case OperatorType.QueryList:
          break;
        case OperatorType.QueryOne:
          __result = result && result.length > 0 ? result[0] : null;
          break;
      }
      return __result;
    };
    const { IsBeginTrConn, BeginTrConn } = this;
    if (!!IsBeginTrConn) {
      console.log('Transaction thread ID: ', BeginTrConn.threadId);
      // transaction processing
      BeginTrConn.query(Sql, (err, result, fields) => {
        if (err) {
          __self.Rollback(err);
          Error && Error(err);
          return;
        }
        const __result = __ProcessResult(Sql, result, fields, Type);
        Success && Success({ fields, result: __result });
      });
    } else {
      const poolInfo = this.poolInfo(Error);
      if (!poolInfo) {
        return;
      }
      const __query = poolInfo.query(Sql, (err, result, fields) => {
        if (err) {
          Error && Error(err);
          return;
        }
        const __result = __ProcessResult(__query.sql, result, fields, Type);
        Success && Success({ fields, result: __result });
      });
    }
  }

  /**
   * Open transaction
   * 
   * @param {any} Success Method invoked after success
   * @param {any} Error Method invoked after failure
   * @returns 
   * @memberof MySqlHelper
   */
  BeginTransaction(Success, Error) {
    const poolInfo = this.poolInfo(Error);
    if (!poolInfo) {
      return;
    }
    const __self = this;
    poolInfo.getConnection((err, conn) => {
      if (err) {
        Error && Error(err);
      }
      conn.beginTransaction((btErr) => {
        if (btErr) {
          Error && Error(btErr);
        }
        console.log('Start Transaction Processing...');
        __self.BeginTrConn = conn;
        __self.IsBeginTrConn = true;
        Success && Success();
      });
    });
  }

  /**
   * rollback
   * 
   * @param {any} ErrorInfo Roll back error information
   * @returns 
   * @memberof MySqlHelper
   */
  Rollback(ErrorInfo) {
    const { IsBeginTrConn, BeginTrConn } = this;
    const __self = this;
    if (!IsBeginTrConn) {
      return;
    }
    if (!BeginTrConn) {
      return;
    }

    console.log('Rollback->Transaction thread ID: ', BeginTrConn.threadId);
    BeginTrConn.rollback(() => {
      console.log('rollback,Rollback reason:', ErrorInfo);
      delete __self.IsBeginTrConn;
      delete __self.BeginTrConn;
    });
  }

  /**
   * Submission of events
   * 
   * @param {any} Success Method invoked after success
   * @param {any} Error Method invoked after failure
   * @returns 
   * @memberof MySqlHelper
   */
  Commit(Success, Error) {
    const { IsBeginTrConn, BeginTrConn } = this;
    const __self = this;
    if (!IsBeginTrConn) {
      return;
    }
    if (!BeginTrConn) {
      return;
    }
    BeginTrConn.commit((err) => {
      if (err) {
        console.log('Transaction commit failed and rollback operation performed...');
        __self.Rollback(err);
        Error && Error(err);
        return;
      }
      console.log('Successful transaction submission...');
      console.log('Commit->Successful transaction submission...affair ID: ', BeginTrConn.threadId);
      delete __self.IsBeginTrConn;
      delete __self.BeginTrConn;
      Success && Success();
    });
  }

  /**
   * Close connection pool
   * 
   * @param {any} Success 
   * @param {any} Error 
   * @returns 
   * @memberof MySqlHelper
   */
  ClosePool(Success, Error) {
    const __self = this;
    const poolInfo = this.poolInfo(Error);
    if (!poolInfo) {
      return;
    }
    poolInfo.end((err) => {
      if (err) {
        Error && Error(err);
        return;
      }
      Success && Success();

      if (__self.__pool) {
        delete this.pool;
        delete this.__pool;
      }
    });
  }
}

module.exports = MySqlHelper;