base_service.js 6.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242
  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.uuid = this.app.uuid;
  24. this.transaction = null;
  25. this.sqlBuilder = null;
  26. this._ = this.app._;
  27. this.depart = 0;
  28. }
  29. /**
  30. * 设置表名
  31. *
  32. * @param {String} table - 表名
  33. * @return {void}
  34. */
  35. set tableName(table) {
  36. this._table = this.app.config.tablePrefix + table;
  37. }
  38. /**
  39. * 获取表名
  40. *
  41. * @return {String} - 返回表名
  42. */
  43. get tableName() {
  44. if (this.depart <= 0) return this._table;
  45. if (!this.ctx.tender) throw new TypeError('tableName error: Without Tender');
  46. return this._table + '_' + (this.ctx.tender.id % this.depart);
  47. }
  48. departTableName(key) {
  49. if (this.depart <= 0) return this._table;
  50. if (!key) throw new TypeError('tableName error: Without DepartKey');
  51. return this._table + '_' + (key % this.depart);
  52. }
  53. /**
  54. * 初始化sqlBuilder
  55. *
  56. * @return {void}
  57. */
  58. initSqlBuilder() {
  59. if (this.sqlBuilder === null) {
  60. this.sqlBuilder = new SqlBuilder();
  61. }
  62. }
  63. /**
  64. * 根据id查找数据
  65. *
  66. * @param {Number} id - 数据库中的id
  67. * @return {Object} - 返回单条数据
  68. */
  69. async getDataById(id) {
  70. return await this.db.get(this.tableName, { id });
  71. }
  72. /**
  73. * 根据条件查找单条数据
  74. *
  75. * @param {Object} condition - 筛选条件
  76. * @return {Object} - 返回单条数据
  77. */
  78. async getDataByCondition(condition) {
  79. return await this.db.get(this.tableName, condition);
  80. }
  81. /**
  82. * 根据条件查找列表数据
  83. *
  84. * @param {Object} condition - 筛选条件
  85. * @return {Array} - 返回数据
  86. */
  87. async getAllDataByCondition(condition) {
  88. if (!condition.where && !condition.limit) throw '缺少查询条件,如查询全表,请自建sql';
  89. return await this.db.select(this.tableName, condition);
  90. }
  91. /**
  92. * 根据id删除数据
  93. *
  94. * @param {Number} id - 数据库中的id
  95. * @return {boolean} - 返回是否删除成功
  96. */
  97. async deleteById(id) {
  98. const result = await this.db.delete(this.tableName, { id });
  99. return result.affectedRows > 0;
  100. }
  101. /**
  102. * 获取总数
  103. *
  104. * @param {Object} option - 过滤条件(参考select方法中的condition)
  105. * @return {Number} - 返回查找的总数
  106. */
  107. async count(option = {}) {
  108. const result = await this.db.count(this.tableName, option);
  109. return result;
  110. }
  111. async defaultUpdate(data, options) {
  112. const result = await this.db.update(this.tableName, data, options);
  113. return result;
  114. }
  115. async defaultUpdateRows(data) {
  116. const result = await this.db.updateRows(this.tableName, data);
  117. return result;
  118. }
  119. /**
  120. * 更新数据
  121. *
  122. * @param {Object} data - 需要更新的数据
  123. * @param {Object} condition - 更新的条件筛选
  124. * @return {Boolean} - 返回更新结果
  125. */
  126. async update(data, condition, transaction = false) {
  127. if (Object.keys(data).length <= 0 || Object.keys(condition).length <= 0) {
  128. return false;
  129. }
  130. const sqlParam = [];
  131. const param = [];
  132. for (const key in data) {
  133. param.push(key + ' = ?');
  134. sqlParam.push(data[key]);
  135. }
  136. const paramString = param.join(',');
  137. const where = [];
  138. for (const key in condition) {
  139. where.push(key + ' = ?');
  140. sqlParam.push(condition[key]);
  141. }
  142. const whereString = where.join(' AND ');
  143. const sql = 'UPDATE ' + this.tableName + ' SET ' + paramString + ' WHERE ' + whereString;
  144. const result = transaction ? await transaction.query(sql, sqlParam) : await this.db.query(sql, sqlParam);
  145. return result.affectedRows > 0;
  146. }
  147. /**
  148. * 获取分页数据(SqlBuilder版本)
  149. *
  150. * @return {Array} - 返回分页数据
  151. */
  152. async getListWithBuilder() {
  153. // 由于egg-mysql不提供like、不等于操作,所以需要自己组sql
  154. if (this.sqlBuilder === null) {
  155. return [];
  156. }
  157. // 分页相关
  158. this.sqlBuilder.limit = this.ctx.pageSize ? this.ctx.pageSize : this.app.config.pageSize;
  159. this.sqlBuilder.offset = this.sqlBuilder.limit * (this.ctx.page - 1);
  160. // 数据筛选
  161. if (this.ctx.sort !== undefined && this.ctx.sort.length > 0) {
  162. this.sqlBuilder.orderBy = [this.ctx.sort];
  163. }
  164. const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
  165. const list = await this.db.query(sql, sqlParam);
  166. return list;
  167. }
  168. /**
  169. * 获取数据条数(SqlBuilder版本)
  170. *
  171. * @return {Array} - 返回分页数据
  172. */
  173. async getCountWithBuilder() {
  174. // 由于egg-mysql不提供like、不等于操作,所以需要自己组sql
  175. if (this.sqlBuilder === null) {
  176. return [];
  177. }
  178. const [sql, sqlParam] = this.sqlBuilder.buildCount(this.tableName);
  179. const result = await this.db.query(sql, sqlParam);
  180. return result ? result.length : 0;
  181. }
  182. /**
  183. * 获取分页数据
  184. *
  185. * @param {Object} condition - 搜索条件
  186. * @return {Array} - 返回分页数据
  187. */
  188. async getList(condition) {
  189. const page = this.ctx.page;
  190. // 分页设置
  191. condition.limit = condition.limit === undefined ? this.app.config.pageSize : condition.limit;
  192. condition.offset = condition.offset === undefined ? this.app.config.pageSize * (page - 1) : condition.offset;
  193. if (this.ctx.sort !== undefined && this.ctx.sort.length > 0) {
  194. condition.orders = [this.ctx.sort];
  195. }
  196. const list = await this.db.select(this.tableName, condition);
  197. return list;
  198. }
  199. round(value, decimal) {
  200. return this.ctx.helper.round(value, this._.isNumber(decimal) ? decimal : 8);
  201. }
  202. // 针对大批量数据插入,优化插入datas,按50条为分隔
  203. async insertBigDatas(transaction, insertDatas) {
  204. const batchSize = 50;
  205. for (let i = 0; i < insertDatas.length; i += batchSize) {
  206. const batchDatas = insertDatas.slice(i, i + batchSize);
  207. await transaction.insert(this.tableName, batchDatas);
  208. }
  209. }
  210. }
  211. module.exports = BaseService;