1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192 |
- 'use strict';
- /**
- * sql语句构建器
- *
- * @author CaiAoLin
- * @date 2017/10/11
- * @version
- */
- class SqlBuilder {
- /**
- * 构造函数
- *
- * @return {void}
- */
- constructor() {
- this.andWhere = {};
- this.orWhere = {};
- this.columns = [];
- this.limit = -1;
- this.offset = -1;
- this.orderBy = [];
- }
- /**
- * 设置andWhere数据
- *
- * @param {String} field - where中的字段名称
- * @param {Object} data - where中的value部分
- * @return {void}
- */
- setAndWhere(field, data) {
- if (Object.keys(data).length <= 0) {
- return;
- }
- this.andWhere[field] = data;
- }
- /**
- * 构建sql
- *
- * @param {String} tableName - 表名
- * @return {Array} - 返回数组,第一个元素为sql语句,第二个元素为sql中问号部分的param
- */
- build(tableName) {
- let sql = this.columns.length === 0 ? 'SELECT * FROM ??' : 'SELECT ?? FROM ??';
- const sqlParam = this.columns.length === 0 ? [tableName] : [this.columns, tableName];
- if (Object.keys(this.andWhere).length > 0) {
- const whereArr = [];
- for (const index in this.andWhere) {
- whereArr.push(' ?? ' + this.andWhere[index].operate + ' ' + this.andWhere[index].value);
- sqlParam.push(index);
- }
- const whereString = whereArr.join(' AND ');
- sql += ' WHERE ' + whereString;
- }
- if (Object.keys(this.orWhere).length > 0) {
- const whereArr = [];
- for (const index in this.orWhere) {
- whereArr.push(' ?? ' + this.orWhere[index].operate + ' ' + this.orWhere[index].value);
- sqlParam.push(index);
- }
- const whereString = whereArr.join(' OR ');
- // 如果前面已经有设置过WHERE则不需要再重复添加
- sql += sql.indexOf('WHERE') > 0 ? whereString : ' WHERE ' + whereString;
- }
- if (typeof this.limit === 'number' && this.limit > 0) {
- this.offset = parseInt(this.offset);
- this.offset = isNaN(this.offset) || this.offset < 0 ? 0 : this.offset;
- const limitString = this.offset >= 0 ? this.offset + ',' + this.limit : this.limit;
- sql += ' LIMIT ' + limitString;
- }
- if (this.orderBy.length > 0) {
- const orderArr = [];
- for (const index in this.orderBy) {
- orderArr.push(' ?? ' + this.orderBy[index][1]);
- sqlParam.push(this.orderBy[index][0]);
- }
- const orderByString = orderArr.join(',');
- sql += ' ORDER BY ' + orderByString;
- }
- return [sql, sqlParam];
- }
- }
- module.exports = SqlBuilder;
|