base_service.js 4.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192
  1. 'use strict';
  2. /**
  3. * 业务逻辑基类
  4. *
  5. * @author CaiAoLin
  6. * @date 2017/10/11
  7. * @version
  8. */
  9. const Service = require('egg').Service;
  10. // sql拼装器
  11. const SqlBuilder = require('../lib/sql_builder');
  12. class BaseService extends Service {
  13. /**
  14. * 构造函数
  15. *
  16. * @param {Object} ctx - egg全局context
  17. * @return {void}
  18. */
  19. constructor(ctx) {
  20. super(ctx);
  21. this.db = this.app.mysql;
  22. this.cache = this.app.redis;
  23. this.transaction = null;
  24. this.sqlBuilder = null;
  25. }
  26. /**
  27. * 设置表名
  28. *
  29. * @param {String} table - 表名
  30. * @return {void}
  31. */
  32. set tableName(table) {
  33. this._table = this.app.config.tablePrefix + table;
  34. }
  35. /**
  36. * 获取表名
  37. *
  38. * @return {String} - 返回表名
  39. */
  40. get tableName() {
  41. return this._table;
  42. }
  43. /**
  44. * 初始化sqlBuilder
  45. *
  46. * @return {void}
  47. */
  48. initSqlBuilder() {
  49. if (this.sqlBuilder === null) {
  50. this.sqlBuilder = new SqlBuilder();
  51. }
  52. }
  53. /**
  54. * 根据id查找数据
  55. *
  56. * @param {Number} id - 数据库中的id
  57. * @return {Object} - 返回单条数据
  58. */
  59. async getDataById(id) {
  60. return await this.db.get(this.tableName, { id });
  61. }
  62. /**
  63. * 根据条件查找单条数据
  64. *
  65. * @param {Object} condition - 筛选条件
  66. * @return {Object} - 返回单条数据
  67. */
  68. async getDataByCondition(condition) {
  69. return await this.db.get(this.tableName, condition);
  70. }
  71. /**
  72. * 根据条件查找单条数据
  73. *
  74. * @param {Object} condition - 筛选条件
  75. * @return {Array} - 返回数据
  76. */
  77. async getAllDataByCondition(condition) {
  78. return await this.db.select(this.tableName, condition);
  79. }
  80. /**
  81. * 根据id删除数据
  82. *
  83. * @param {Number} id - 数据库中的id
  84. * @return {boolean} - 返回是否删除成功
  85. */
  86. async deleteById(id) {
  87. const result = await this.db.delete(this.tableName, { id });
  88. return result.affectedRows > 0;
  89. }
  90. /**
  91. * 获取总数
  92. *
  93. * @param {Object} option - 过滤条件(参考select方法中的condition)
  94. * @return {Number} - 返回查找的总数
  95. */
  96. async count(option = {}) {
  97. const result = await this.db.count(this.tableName, option);
  98. return result;
  99. }
  100. /**
  101. * 更新数据
  102. *
  103. * @param {Object} data - 需要更新的数据
  104. * @param {Object} condition - 更新的条件筛选
  105. * @return {Boolean} - 返回更新结果
  106. */
  107. async update(data, condition) {
  108. if (Object.keys(data).length <= 0 || Object.keys(condition).length <= 0) {
  109. return false;
  110. }
  111. const sqlParam = [];
  112. const param = [];
  113. for (const key in data) {
  114. param.push(key + ' = ?');
  115. sqlParam.push(data[key]);
  116. }
  117. const paramString = param.join(',');
  118. const where = [];
  119. for (const key in condition) {
  120. where.push(key + ' = ?');
  121. sqlParam.push(condition[key]);
  122. }
  123. const whereString = where.join(' AND ');
  124. const sql = 'UPDATE ' + this.tableName + ' SET ' + paramString + ' WHERE ' + whereString;
  125. const result = await this.db.query(sql, sqlParam);
  126. return result.affectedRows > 0;
  127. }
  128. /**
  129. * 获取分页数据(SqlBuilder版本)
  130. *
  131. * @return {Array} - 返回分页数据
  132. */
  133. async getListWithBuilder() {
  134. // 由于egg-mysql不提供like、不等于操作,所以需要自己组sql
  135. if (this.sqlBuilder === null) {
  136. return [];
  137. }
  138. // 分页相关
  139. this.sqlBuilder.limit = this.app.config.pageSize;
  140. this.sqlBuilder.offset = this.app.config.pageSize * (this.ctx.page - 1);
  141. // 数据筛选
  142. if (this.ctx.sort !== undefined && this.ctx.sort.length > 0) {
  143. this.sqlBuilder.orderBy = [this.ctx.sort];
  144. }
  145. const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
  146. const list = await this.db.query(sql, sqlParam);
  147. return list;
  148. }
  149. /**
  150. * 获取分页数据
  151. *
  152. * @param {Object} condition - 搜索条件
  153. * @return {Array} - 返回分页数据
  154. */
  155. async getList(condition) {
  156. const page = this.ctx.page;
  157. // 分页设置
  158. condition.limit = condition.limit === undefined ? this.app.config.pageSize : condition.limit;
  159. condition.offset = condition.offset === undefined ? this.app.config.pageSize * (page - 1) : condition.offset;
  160. if (this.ctx.sort !== undefined && this.ctx.sort.length > 0) {
  161. condition.orders = [this.ctx.sort];
  162. }
  163. const list = await this.db.select(this.tableName, condition);
  164. return list;
  165. }
  166. }
  167. module.exports = BaseService;