'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;