sql_builder.js 2.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105
  1. 'use strict';
  2. /**
  3. * sql语句构建器
  4. *
  5. * @author CaiAoLin
  6. * @date 2017/10/11
  7. * @version
  8. */
  9. class SqlBuilder {
  10. /**
  11. * 构造函数
  12. *
  13. * @return {void}
  14. */
  15. constructor() {
  16. this.resetCondition();
  17. }
  18. /**
  19. * 设置andWhere数据
  20. *
  21. * @param {String} field - where中的字段名称
  22. * @param {Object} data - where中的value部分
  23. * @return {void}
  24. */
  25. setAndWhere(field, data) {
  26. if (Object.keys(data).length <= 0) {
  27. return;
  28. }
  29. this.andWhere.push({ field, data });
  30. }
  31. /**
  32. * 重置条件
  33. *
  34. * @return {void}
  35. */
  36. resetCondition() {
  37. this.andWhere = [];
  38. this.orWhere = [];
  39. this.columns = [];
  40. this.limit = -1;
  41. this.offset = -1;
  42. this.orderBy = [];
  43. this.groupBy = [];
  44. }
  45. /**
  46. * 构建sql
  47. *
  48. * @param {String} tableName - 表名
  49. * @return {Array} - 返回数组,第一个元素为sql语句,第二个元素为sql中问号部分的param
  50. */
  51. build(tableName) {
  52. let sql = this.columns.length === 0 ? 'SELECT * FROM ??' : 'SELECT ?? FROM ??';
  53. const sqlParam = this.columns.length === 0 ? [tableName] : [this.columns, tableName];
  54. if (this.andWhere.length > 0) {
  55. const whereArr = [];
  56. for (const where of this.andWhere) {
  57. whereArr.push(' ?? ' + where.data.operate + ' ' + where.data.value);
  58. sqlParam.push(where.field);
  59. }
  60. const whereString = whereArr.join(' AND ');
  61. sql += ' WHERE ' + whereString;
  62. }
  63. if (this.orWhere.length > 0) {
  64. const whereArr = [];
  65. for (const where in this.orWhere) {
  66. whereArr.push(' ?? ' + where.data.operate + ' ' + where.data.value);
  67. sqlParam.push(where.field);
  68. }
  69. const whereString = whereArr.join(' OR ');
  70. // 如果前面已经有设置过WHERE则不需要再重复添加
  71. sql += sql.indexOf('WHERE') > 0 ? whereString : ' WHERE ' + whereString;
  72. }
  73. if (typeof this.limit === 'number' && this.limit > 0) {
  74. this.offset = parseInt(this.offset);
  75. this.offset = isNaN(this.offset) || this.offset < 0 ? 0 : this.offset;
  76. const limitString = this.offset >= 0 ? this.offset + ',' + this.limit : this.limit;
  77. sql += ' LIMIT ' + limitString;
  78. }
  79. if (this.orderBy.length > 0) {
  80. const orderArr = [];
  81. for (const index in this.orderBy) {
  82. orderArr.push(' ?? ' + this.orderBy[index][1]);
  83. sqlParam.push(this.orderBy[index][0]);
  84. }
  85. const orderByString = orderArr.join(',');
  86. sql += ' ORDER BY ' + orderByString;
  87. }
  88. // 重置数据
  89. this.resetCondition();
  90. return [sql, sqlParam];
  91. }
  92. }
  93. module.exports = SqlBuilder;