service/module/DBModule.js

const mysql = require('mysql');
const sys = require('../config/sys_test');
const pool = mysql.createPool({
  connectionLimit: 1,
  connectTimeout: 60 * 60 * 1000,
  host: sys.mysql.host,
  user: sys.mysql.user,
  password: sys.mysql.pwd,
  database: sys.mysql.db
});

class DBModule {
  constructor(args = {}) {
    Object.keys(args).forEach(key => {
      this[key] = args[key];
    })
  }

  /**
   * 使用连接池中已有的连接执行sql脚本
   * @param  {string} sql sql脚本
   * @param  {array} args sql中的参数
   * @return {promise}      
   */
  _query(sql, args = []) {
    return new Promise((resolve, reject) => {
      pool.getConnection((err, connection) => {
        if (err) return reject(err);

        if (sys.debug) {
          console.log(`run ${sql} \narray ${args.map(item=> `${item}  type: ${typeof item}\n`)}`)
        }
        connection.query(sql, args, (err, result) => {
          if (err) {
            reject(err);
          } else {
            resolve(result);
          }
  
          connection.release();
        });
      });
    });
  }

  
  /**
   * 执行对象插入数据库操作
   * @return {promise}      
   */
  insert() {
    let sql, {attrs, vals, err} = DBModule.getDBModuleArgs(this);

    if (err) {
      throw `object ${this.constructor.toString()} lack necessary field (table, fields)`;
    } 

    sql = `insert into \`${this.table}\`(${attrs.join(',')}) values(${vals.map(()=>'?')})`;

    return this._query(sql, vals);
  }

  /**
   * 执行对象更新映射到数据库操作，对象必须存在有效的主键
   * @return {promise}      
   */
  update() {
    let sql, setFields, attrs, vals, err;

    if (this[this.primary] === undefined || this[this.primary] === null) {
      throw `object ${this.constructor.toString()} ${this.primary} is ${this[this.primary]}`;
    }

    ({attrs, vals, err} = DBModule.getDBModuleArgs(this));

    if (err) {
      throw `object ${this.constructor.toString()} lack necessary field (table, fields)`;
    } 

    vals.push(this[this.primary]);

    setFields = attrs.map(attr=>`${attr}=?`);
    sql = `update \`${this.table}\` set ${setFields.join(',')} where ${this.primary}=?`;

    return this._query(sql, vals);
  }

  /**
   * 查看某条件的数据是否存在
   * @return {promise}      
   */
  exists() {
    let fields, {attrs, vals, err} = DBModule.getDBModuleArgs(this);
    
    if (err) {
      throw `object ${this.constructor.toString()} lack necessary field (table, fields)`;
    }

    fields = attrs.map(attr=>` ${attr}=? `);
    fields.push(' 1=1 ');

    return this._query(`select count(*) as count from \`${this.table}\` where ${fields.join(' and ')}`, vals)
        .then(result => {
          return result[0].count !== 0
        });
  }

  /**
   * 删除数据库中单行操作，对象必须存在有效的主键
   * @return {promise}      
   */
  delete() {
    let sql, vals;

    if (this[this.primary] === undefined || this[this.primary] === null) {
      throw `object ${this.constructor.toString()} ${this.primary} is ${this[this.primary]}`;
    } else if (DBModule.getDBModuleArgs(this).err) {
      throw `object ${this.constructor.toString()} lack necessary field (table, fields)`;
    }

    vals = [this[this.primary]]
    sql = `delete from \`${this.table}\` where ${this.primary}=?`;

    return this._query(sql, vals);
  }

  /**
   * 查询该对象表中的数据
   * @param connect 多字段要连接字符 如 and or
   * @param start 分页时开始位置
   * @param limit 分页时要获取的条数
   * @param afterAgs 要附加的参数
   * @return {promise}      
   */
  query({connect = DBModule.AND, start = 0, limit, sql} = {}, beforeAgs) {
    let conneSql = '', fields, {attrs, vals, err} = DBModule.getDBModuleArgs(this);

    if (err) {
      throw `object ${this.constructor.toString()} lack necessary field (table, fields)`;
    } else if (connect !== DBModule.AND && connect !== DBModule.OR) {
      throw `query functiton connect args must be DBModule.AND or DBModule.OR`;
    }

    if (Number.isFinite(Number(this[this.primary]))) {
      attrs.push(this.primary);
      vals.push(this[this.primary]);
    }

    if (limit > 0) {
      conneSql = ` LIMIT ?,?`;
      vals.push(start);
      vals.push(limit);
    }

    fields = attrs.map(attr => ` \`${this.table}\`.${attr}=? `);
    fields.push(' 1=1 ');

    if (sql) {
      sql = `select * from (${sql}) as \`${this.table}\` `;
    } else {
      sql = `select * from \`${this.table}\` `;
    }

    sql = `${sql} where ${fields.join(connect)} ${conneSql}`

    if (beforeAgs && beforeAgs.length > 0) {
      vals = beforeAgs.concat(vals);
    }

    return this._query(sql, vals);
  }

  /**
   * 分页模式数据统一处理
   * @param {*} paging number 是否要开启分页模式
   *      limit  number 每页的条数
   *      start  number 从哪条开始读取
   *      header string 获取表的表头
   *      vals   array  sql的参数
   *      sql  string sql语句表头用{{header}}代替
   */
  paging({paging = 0, limit = 15, start = 0, sql, headers = 'header', vals = []} = {}) {
    let pData, pCount, conneSql = '';

    if (!sql) return Promise.resolve(1);
    if (limit > 0) {
      conneSql = ` LIMIT ?,?`;
      vals.push(start);
      vals.push(limit);
    }

    pData = this._query(sql.replace('{{header}}', headers) + conneSql, vals);

    if (paging) {
      pCount = this._query(sql.replace('{{header}}', 'count(*) as len'), vals);
      return Promise.all([pData, pCount])
          .then((arrs) => {
            return {
              total: arrs[1][0].len, 
              data: arrs[0],
              average: limit
            }
          });
    } else {
      return pData;
    }
  }

  /**
   * 检测对象是否符合DBModule要求并获取对象能执行_query的参数
   * @param {object} dbModule要检测的对象
   * @return {object} fileds: err attrs vals
   *    {boolean} err 是否符合DBModule的要求
   *    {array} attrs 要进行数据库操作的字段
   *    {array} vals 要进行数据库操作的字段对应的值 
   */
  static getDBModuleArgs(dbModule) {
    if(!((dbModule.table && dbModule.table.length > 0) &&
       (dbModule.fields && dbModule.fields instanceof Array && dbModule.fields.length > 0) 
      )) {
      return {err: true};
    }

    let attrs = [], vals = [];

    for (let field of dbModule.fields) {
      if (dbModule[field] !== undefined) {
        attrs.push('`'+field+'`');
        vals.push(dbModule[field]);
      }
    }

    return {attrs, vals, err: false};
  }

  static get AND() {
    return 'and';
  } 

  static get OR() {
    return 'or';
  }

  static get HOT() {
    return 'hot';
  }

  static get SOAR() {
    return 'soar';
  }
}

module.exports = exports = DBModule;