Calculation/app/lib/sql_builder.js

'use strict';

/**
 * sql语句构建器
 *
 * @author CaiAoLin
 * @date 2017/10/11
 * @version
 */
class SqlBuilder {

    /**
     * 构造函数
     *
     * @return {void}
     */
    constructor() {
        this.resetCondition();
    }

    /**
     * 设置andWhere数据
     *
     * @param {String} field - where中的字段名称
     * @param {Object} data - where中的value部分
     * @return {void}
     */
    setAndWhere(field, data) {
        if (Object.keys(data).length <= 0) {
            return;
        }
        this.andWhere.push({ field, data });
    }

    /**
     * 重置条件
     *
     * @return {void}
     */
    resetCondition() {
        this.andWhere = [];
        this.orWhere = [];
        this.columns = [];
        this.limit = -1;
        this.offset = -1;
        this.orderBy = [];
    }

    /**
     * 构建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 (this.andWhere.length > 0) {
            const whereArr = [];
            for (const where of this.andWhere) {
                whereArr.push(' ?? ' + where.data.operate + ' ' + where.data.value);
                sqlParam.push(where.field);
            }
            const whereString = whereArr.join(' AND ');
            sql += ' WHERE ' + whereString;
        }

        if (this.orWhere.length > 0) {
            const whereArr = [];
            for (const where in this.orWhere) {
                whereArr.push(' ?? ' + where.data.operate + ' ' + where.data.value);
                sqlParam.push(where.field);
            }
            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;
        }

        // 重置数据
        this.resetCondition();

        return [sql, sqlParam];
    }

}
module.exports = SqlBuilder;