NODEJS增删改查数据库封装——阶段2

阿赛工作室

已于 2022-11-29 20:56:53 修改

阅读量635

点赞数 1

分类专栏： nodejs 物联网项目开发笔记文章标签：数据库 java sql

于 2022-11-29 19:41:47 首次发布

本文链接：https://blog.csdn.net/qq_25753445/article/details/128103937

版权

物联网项目开发笔记同时被 2 个专栏收录

58 篇文章 4 订阅

订阅专栏

nodejs

20 篇文章 0 订阅

订阅专栏

config.json

{
  "sql": {
    "host": "localhost",
    "port": 3306,
    "user": "sqlite3",
    "password": "asai.cc",
    "database": "./asaiData.db"
  },
  "mysql示例": {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "asai.cc",
    "database": "asaiData"
  },
  "sqlite3示例": {
    "host": "localhost",
    "port": 3306,
    "user": "sqlite3",
    "password": "asai.cc",
    "database": "./asaiData.db"
  },
  "excel示例": {
    "host": "localhost",
    "port": 3306,
    "user": "excel",
    "password": "asai.cc",
    "database": "./asaiData.xlsx"
  }
}

_temp.ts

import dbInit from './db';
const config: any = require('./config.json');
const db = new dbInit(config);
const sqlType: string = 'select';
let sqlObj: any;

// 假设我们有个数据表users，有id、userName、userPassword、userType四个字段
if (sqlType === 'insert') {
  // 增
  sqlObj = {
    type: 'insert',
    table: 'user',
    field: 'userName,userPassword,userType',
    // value: '"asai","123456",8',
    value: ['"asai","123456",8', '"阿萨","分sss",88', '"阿萨","分sss",88'],
  };
} else if (sqlType === 'delete') {
  // 删
  sqlObj = {
    type: 'delete',
    table: 'user',
    where: 'userName="SSSS"',
    limit: 2,
    order: 'id desc',
  };
} else if (sqlType === 'update') {
  // 改
  sqlObj = {
    type: 'update',
    table: 'user',
    // set: 'userName="GGGG"',
    set: ['userName="SSSS"', 'userPassword="FFFF"'],
    where: 'id=10',
    limit: 3,
    order: 'id desc',
  };
} else if (sqlType === 'select') {
  // 查
  sqlObj = {
    type: 'select',
    table: 'user',
    field: '*',
    where: 'id>5',
    limit: 1,
    order: 'id desc',
  };
}

db.sqlDb(sqlObj)
  .then((res: any) => {
    clog(666.102, res);
  })
  .catch((err: any) => {
    clog(666.101, err);
  });

function clog(...arg: any) {
  console.log(...arg);
}

type.ts

type Istrings = string[] | string;
type IValues = any[][] | string;
type ISets = [string, any][] | string;
type IWheres = ([string, string, any] | string)[] | string;
type IOrders = [string, 'asc' | 'desc'][] | string;

export default interface Idb {
  type: string;
  table: string;
  field?: Istrings; // ['a','b']
  value?: IValues; // [['a1',10,'b1'],['2a',20,'2b']]
  set?: ISets; // [['a1','b1'],['2a','2b']]=>set a=b
  where?: IWheres; // ['or',['a','like','b'],['a','>','b'],'or','and',['a','like','b'],['a','>','b']]=>where a==='b'
  order?: IOrders; // [['a1','asc'],['b1','desc']]=>order by a asc,b1,desc
  limit?: number;
}

mysql

import type Idb from '../type';
function clog(...arg: any) {
  console.log(...arg);
}

class DbMySQL {
  pool: any;
  config: any;
  constructor(config: any) {
    this.config = config;
    // 建立数据库连接池
    this.pool = require('mysql').createPool(config.sql);
  }

  joinSql(sql: Idb, type: 'where' | 'order' | 'value' | 'set' | 'field') {
    var val = sql[type];
    var tmp = '';
    if (Array.isArray(val)) {
      const len: number = val.length;
      if (len > 0) {
        if (type === 'where') {
          tmp = ' where ';
          let tmpx: any = '';
          for (let i = 0; i < len; i++) {
            let el = val[i];
            if (el === 'or' || el === 'and') {
              if (tmpx) {
                tmp += ')';
              }
              tmpx = el;
              i++;
              el = val[i];
              if (el === 'or' || el === 'and') {
                tmp += ' ' + tmpx;
                tmpx = el;
                i++;
                el = val[i];
              }
              tmp += ' (' + el[0] + ' ' + el[1] + ' ' + el[2];
            } else {
              tmp += ' ' + tmpx + ' ' + el[0] + ' ' + el[1] + ' ' + el[2];
            }
          }
          tmp += ')';
          return tmp;
        } else if (type === 'order') {
          tmp = ' order by';
          for (let i = 0; i < len; i++) {
            let el = val[i];
            if (i > 0) {
              tmp += ',';
            }
            tmp += ' ' + el[0] + ' ' + el[1];
          }
          return tmp;
        } else if (type === 'value') {
          tmp = ' values (';
          for (let i = 0; i < len; i++) {
            let el = val[i] as any[];
            if (i > 0) {
              tmp += '),(';
            }
            tmp += el.join(',');
          }
          tmp += ')';
          return tmp;
        } else if (type === 'set') {
          tmp = ' set ';
          for (let i = 0; i < len; i++) {
            let el = val[i] as [string, any][];
            if (i > 0) {
              tmp += ',';
            }
            tmp += el[0] + '=' + el[1];
          }
          return tmp;
        } else if (type === 'field') {
          tmp = ' (';
          for (let i = 0; i < len; i++) {
            let el = val[i] as string;
            if (i > 0) {
              tmp += ',';
            }
            tmp += el;
          }
          tmp += ')';
          return ' (' + val.join(',') + ')';
        }
      } else {
        if (['value', 'set'].includes(type)) {
          // 不能为空
        }
      }
    } else if (val) {
      if (type === 'where') {
        return ' where ' + val;
      } else if (type === 'order') {
        return ' order by ' + val;
      } else if (type === 'value') {
        return ' values (' + val + ')';
      } else if (type === 'set') {
        return ' set ' + val;
      } else if (type === 'field') {
        return ' (' + val + ')';
      }
    }
    return '';
  }
  makeSql(sql: Idb) {
    var sqls = sql.type;
    if (sqls === 'insert') {
      sqls += ' into';
      sqls += ' ' + sql.table;
      sqls += this.joinSql(sql, 'field');
      sqls += this.joinSql(sql, 'value');
    } else if (sqls === 'delete') {
      sqls += ' from ' + sql.table;
      sqls += this.joinSql(sql, 'where');
      sqls += this.joinSql(sql, 'order');
      sqls += sql.limit ? ' limit ' + sql.limit : '';
    } else if (sqls === 'update') {
      sqls += ' ' + sql.table;
      sqls += this.joinSql(sql, 'set');
      sqls += this.joinSql(sql, 'where');
      sqls += this.joinSql(sql, 'order');
      sqls += sql.limit ? ' limit ' + sql.limit : '';
    } else if (sqls === 'select') {
      sqls += sql.field ? ' ' + sql.field : ' *';
      sqls += ' from ' + sql.table;
      sqls += this.joinSql(sql, 'where');
      sqls += this.joinSql(sql, 'order');
      sqls += sql.limit ? ' limit ' + sql.limit : '';
    }
    clog(666.201, sqls);
    return sqls;
  }
  getErr(err: any) {
    var errs = err?.message || null;
    return errs;
  }
  getResult(rows: any) {
    var result = rows ?? null;
    return result;
  }
  query(sql: Idb, callback: any) {
    clog(666.101, sql, callback);
    this.pool.getConnection((error: any, connection: any) => {
      if (error) {
        clog(666.102, error, connection);
      } else {
        connection.query(this.makeSql(sql), (err: any, rows: any) => {
          clog(666.103, err, rows);
          callback(this.getErr(err), this.getResult(rows));
          connection.release();
        });
      }
    });
  }
  sqlFail(val: any) {
    return { code: 907, data: val };
  }
  sqlEmpty(val: string) {
    return { code: 908, data: val };
  }
  sqlSuccess(val: any) {
    return { code: 909, data: val };
  }
  sqlDb(sql: Idb) {
    return new Promise((resolve, reject) => {
      try {
        this.query(sql, (err: any, result: any) => {
          if (err) {
            reject(this.sqlFail(err));
          } else if (!result || (Array.isArray(result) && !result.length)) {
            resolve(this.sqlEmpty(sql.type + ' is null'));
          } else {
            resolve(this.sqlSuccess(result));
          }
        });
      } catch (err) {
        reject(err);
      }
    });
  }
}
export default DbMySQL;

sqlite

import type Idb from '../type';
function clog(...arg: any) {
  console.log(...arg);
}
class DbSQLite3 {
  pool: any;
  config: any;
  sqlite3: any;
  constructor(config: any) {
    this.config = config;
    this.sqlite3 = require('sqlite3').verbose();
    this.pool = new this.sqlite3.Database(config.sql.database, (err: any) => {
      if (err) throw err;
    });
  }
  joinSql(sql: Idb, type: 'where' | 'order' | 'value' | 'set' | 'field') {
    var val = sql[type];
    var tmp = '';
    if (Array.isArray(val)) {
      const len: number = val.length;
      if (len > 0) {
        if (type === 'where') {
          tmp = ' where ';
          let tmpx: any = '';
          for (let i = 0; i < len; i++) {
            let el = val[i];
            if (el === 'or' || el === 'and') {
              if (tmpx) {
                tmp += ')';
              }
              tmpx = el;
              i++;
              el = val[i];
              if (el === 'or' || el === 'and') {
                tmp += ' ' + tmpx;
                tmpx = el;
                i++;
                el = val[i];
              }
              tmp += ' (' + el[0] + ' ' + el[1] + ' ' + el[2];
            } else {
              tmp += ' ' + tmpx + ' ' + el[0] + ' ' + el[1] + ' ' + el[2];
            }
          }
          tmp += ')';
          return tmp;
        } else if (type === 'order') {
          tmp = ' order by';
          for (let i = 0; i < len; i++) {
            let el = val[i];
            if (i > 0) {
              tmp += ',';
            }
            tmp += ' ' + el[0] + ' ' + el[1];
          }
          return tmp;
        } else if (type === 'value') {
          tmp = ' values (';
          for (let i = 0; i < len; i++) {
            let el = val[i] as any[];
            if (i > 0) {
              tmp += '),(';
            }
            tmp += el.join(',');
          }
          tmp += ')';
          return tmp;
        } else if (type === 'set') {
          tmp = ' set ';
          for (let i = 0; i < len; i++) {
            let el = val[i] as [string, any][];
            if (i > 0) {
              tmp += ',';
            }
            tmp += el[0] + '=' + el[1];
          }
          return tmp;
        } else if (type === 'field') {
          tmp = ' (';
          for (let i = 0; i < len; i++) {
            let el = val[i] as string;
            if (i > 0) {
              tmp += ',';
            }
            tmp += el;
          }
          tmp += ')';
          return ' (' + val.join(',') + ')';
        }
      } else {
        if (['value', 'set'].includes(type)) {
          // 不能为空
        }
      }
    } else if (val) {
      if (type === 'where') {
        return ' where ' + val;
      } else if (type === 'order') {
        return ' order by ' + val;
      } else if (type === 'value') {
        return ' values (' + val + ')';
      } else if (type === 'set') {
        return ' set ' + val;
      } else if (type === 'field') {
        return ' (' + val + ')';
      }
    }
    return '';
  }
  makeSql(sql: Idb) {
    // sqlite查询sql的时候，有些指定的语句不支持order与limit
    var sqls = sql.type;
    if (sqls === 'insert') {
      sqls += ' into';
      sqls += ' ' + sql.table;
      sqls += this.joinSql(sql, 'field');
      sqls += this.joinSql(sql, 'value');
    } else if (sqls === 'delete') {
      sqls += ' from ' + sql.table;
      sqls += this.joinSql(sql, 'where');
    } else if (sqls === 'update') {
      sqls += ' ' + sql.table;
      sqls += this.joinSql(sql, 'set');
      sqls += this.joinSql(sql, 'where');
    } else if (sqls === 'select') {
      sqls += sql.field ? ' ' + sql.field : ' *';
      sqls += ' from ' + sql.table;
      sqls += this.joinSql(sql, 'where');
      sqls += this.joinSql(sql, 'order');
      sqls += sql.limit ? ' limit ' + sql.limit : '';
    }
    clog(666.111, sqls);
    return sqls;
  }
  getErr(err: any) {
    var errs = err?.message || null;
    return errs;
  }
  getResult(rows: any) {
    var result = rows ?? 'OK';
    return result;
  }
  query(sql: Idb, callback: any) {
    clog(666.101, sql, callback);
    if (sql.type === 'select') {
      if (sql.where && (!sql.limit || sql.limit === 1) && false) {
        // 直接返回一个obj
        this.pool.each(this.makeSql(sql), (err: any, rows: any) => {
          clog(666.103, err, rows);
          callback(this.getErr(err), this.getResult(rows));
        });
      } else {
        this.pool.all(this.makeSql(sql), (err: any, rows: any) => {
          clog(666.104, err, rows);
          callback(this.getErr(err), this.getResult(rows));
        });
      }
    } else {
      this.pool.run(this.makeSql(sql), (err: any, rows: any) => {
        clog(666.105, err, rows);
        callback(this.getErr(err), this.getResult(rows));
      });
    }
  }
  sqlFail(val: any) {
    return { code: 907, data: val };
  }
  sqlEmpty(val: string) {
    return { code: 908, data: val };
  }
  sqlSuccess(val: any) {
    return { code: 909, data: val };
  }
  sqlDb(sql: Idb) {
    return new Promise((resolve, reject) => {
      try {
        this.query(sql, (err: any, result: any) => {
          if (err) {
            reject(this.sqlFail(err));
          } else if (!result || (Array.isArray(result) && !result.length)) {
            resolve(this.sqlEmpty(sql.type + ' is null'));
          } else {
            resolve(this.sqlSuccess(result));
          }
        });
      } catch (err) {
        reject(err);
      }
    });
  }
}
export default DbSQLite3;

excel

暂未完成

import type Idb from '../type';
import XLSX from 'xlsx';

function clog(...arg: any) {
  console.log(...arg);
}
class DbExcel {
  pool: any;
  config: any;
  constructor(config: any) {
    this.config = config;
    this.pool = XLSX.readFile(config.sql.database);
  }
  getWhere(el: any, elc: any) {
    return el[elc[0]] === elc[1];
  }
  getOrder(a: any, b: any, elc: any) {
    if (elc[1] === 'asc') {
      return a[elc[0]] - b[elc[0]];
    } else {
      return b[elc[0]] - a[elc[0]];
    }
  }
  readSheet(where: any, order: any, top: number = 0, sheetName: any = '') {
    const workSheet = this.pool.Sheets[sheetName || this.pool.SheetNames[0]];
    let vals = XLSX.utils.sheet_to_json(workSheet);
    if (where && where.length && vals.length) {
      where.forEach((elc: any) => {
        vals = vals.filter((el: any) => this.getWhere(el, elc));
      });
    }
    if (order && order.length && vals.length) {
      order.forEach((elc: any) => {
        vals = vals.sort((a: any, b: any): number => this.getOrder(a, b, elc));
      });
    }
    if (top > 0 && vals.length > top) {
      vals = vals.slice(0, top);
    }
    return vals;
  }

  addData(data: any, sheetName: any = '') {
    const workSheet = this.pool.Sheets[sheetName || this.pool.SheetNames[0]];
    let headerArr: any = XLSX.utils.sheet_to_json(workSheet, {
      header: 1,
      range: 'A1:ZZ1',
    });
    XLSX.utils.sheet_add_json(workSheet, data, {
      header: headerArr[0],
      skipHeader: true,
      origin: -1,
    });
    XLSX.writeFile(this.pool, this.config.database);
  }

  editData(data: any, where: any, sheetName: any = '') {
    const workSheet = this.pool.Sheets[sheetName || this.pool.SheetNames[0]];
    let headerArr: any = XLSX.utils.sheet_to_json(workSheet, {
      header: 1,
      range: 'A1:ZZ1',
    });
    const header = headerArr[0];
    const vals: any = this.readSheet('', '', 0, sheetName);
    let valIndex: any = vals.findIndex((el: any) => {
      let tVal = true;
      if (where && where.length) {
        where.forEach((elc: any) => {
          tVal = tVal && this.getWhere(el, elc);
        });
      }
      return tVal;
    });
    if (valIndex > -1) {
      valIndex += 2;
      XLSX.utils.sheet_add_json(workSheet, data, {
        header,
        skipHeader: true,
        origin: 'A' + valIndex,
      });
      XLSX.writeFile(this.pool, this.config.database);
    } else {
      console.log(where + '查无数据');
    }
  }

  deleteData(where: any, sheetName: any = '') {
    const workSheet = this.pool.Sheets[sheetName || this.pool.SheetNames[0]];
    const vals: any = this.readSheet('', '', 0, sheetName);
    let valIndex: any = vals.findIndex((el: any) => {
      let tVal = true;
      if (where && where.length) {
        where.forEach((elc: any) => {
          tVal = tVal && this.getWhere(el, elc);
        });
      }
      return tVal;
    });
    if (valIndex > -1) {
      valIndex += 1;
      this.delete_row(workSheet, valIndex);
      XLSX.writeFile(this.pool, this.config.database);
    } else {
      console.log(where + '查无数据');
    }
  }
  ec(r: any, c: number) {
    return XLSX.utils.encode_cell({ r: r, c: c });
  }
  delete_row(ws: any, row_index: number) {
    var variable = XLSX.utils.decode_range(ws['!ref']);
    for (var R = row_index; R < variable.e.r; ++R) {
      for (var C = variable.s.c; C <= variable.e.c; ++C) {
        ws[this.ec(R, C)] = ws[this.ec(R + 1, C)];
      }
    }
    variable.e.r--;
    ws['!ref'] = XLSX.utils.encode_range(variable.s, variable.e);
  }

  getErr(err: any) {
    var errs = err?.message || null;
    return errs;
  }
  getResult(rows: any) {
    var result = rows ?? 'OK';
    return result;
  }
  query(sql: Idb, callback: any) {
    return new Promise((resolve: any, reject: any) => {
      try {
        if (sql.type === 'insert') {
          let data = {};
          // let vals=sql.value??''.split(',')
          sql.field??''.split(',').forEach((el:any)=>{

          })
          this.addData(data, sql.table);
        } else if (sql.type === 'delete') {
          let where = {};
          this.deleteData(where, sql.table);
        } else if (sql.type === 'updata') {
          let data = {};
          let where = {};
          this.editData(data, where, sql.table);
        } else if (sql.type === 'select') {
          let where = {};
          let order = {};
          let top = 0;
          this.readSheet(where, order, top, sql.table);
        }
        resolve('OK');
      } catch (e) {
        reject(e);
      }
    });
  }
  sqlFail(val: any) {
    return { code: 907, data: val };
  }
  sqlEmpty(val: string) {
    return { code: 908, data: val };
  }
  sqlSuccess(val: any) {
    return { code: 909, data: val };
  }
  sqlDb(sql: Idb) {
    clog(666.789, sql);
    return new Promise((resolve, reject) => {
      try {
        this.query(sql, (err: any, result: any) => {
          if (err) {
            reject(this.sqlFail(err));
          } else if (!result || (Array.isArray(result) && !result.length)) {
            resolve(this.sqlEmpty(sql.type + ' is null'));
          } else {
            resolve(this.sqlSuccess(result));
          }
        });
      } catch (err) {
        reject(err);
      }
    });
  }
}
export default DbExcel;