sql_builder.js 2.7 KB

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