jurong_circle_shopping_black/config/dbv2.js

// queryBuilder.js
class QueryBuilder {
    constructor() {
        this.conditions = []; // { sql, params }
        this.limit = null;
        this.offset = null;
        this.groupBy = null;
        this.orderBy = null;
    }

    where(condition, ...params) {
        this.conditions.push({ sql: condition, params });
        return this;
    }

    setLimit(limit) {
        this.limit = limit;
        return this;
    }

    setOffset(offset) {
        this.offset = offset;
        return this;
    }

    setGroupBy(groupBy) {
        this.groupBy = groupBy;
        return this;
    }

    orderByField(field, direction = 'ASC') {
        this.orderBy = { field, direction: direction.toUpperCase() };
        return this;
    }

    buildConditions() {
        if (!this.conditions.length) return { sql: '', params: [] };
        const sql = this.conditions.map(c => `(${c.sql})`).join(' AND ');
        const params = this.conditions.flatMap(c => c.params);
        return { sql, params };
    }

    buildLimitOffset() {
        let sql = '';
        if (this.limit !== null) sql += ` LIMIT ${this.limit}`;
        if (this.offset !== null) sql += ` OFFSET ${this.offset}`;
        return sql;
    }

    async execute(db) {
        const { sql, params } = this.build();
        const [rows] = await db.query(sql, params);
        return rows;
    }
}

// ------------------- SELECT -------------------
class SelectBuilder extends QueryBuilder {
    constructor() {
        super();
        this.selectFields = [];
        this.tables = [];
        this.joins = [];
        this.unions = [];
    }

    select(fields) {
        if (typeof fields === 'string') {
            this.selectFields = fields.split(',').map(f => f.trim());
        } else if (Array.isArray(fields)) {
            this.selectFields = fields;
        }
        return this;
    }

    from(table) {
        this.tables.push(table);
        return this;
    }

    join(type, table, condition) {
        this.joins.push(`${type.toUpperCase()} JOIN ${table} ON ${condition}`);
        return this;
    }

    union(queryBuilder, type = 'UNION') {
        this.unions.push({ queryBuilder, type });
        return this;
    }

    unionAll(queryBuilder) {
        return this.union(queryBuilder, 'UNION ALL');
    }

    build() {
        const selectClause = this.selectFields.length ? this.selectFields.join(', ') : '*';
        let sql = `SELECT ${selectClause} FROM ${this.tables.join(', ')}`;
        if (this.joins.length) sql += ' ' + this.joins.join(' ');

        const { sql: whereSql, params } = this.buildConditions();
        if (whereSql) sql += ` WHERE ${whereSql}`;

        if (this.groupBy) sql += ` GROUP BY ${this.groupBy}`;

        // 处理 UNION
        for (const u of this.unions) {
            const { sql: uSql, params: uParams } = u.queryBuilder.build();
            sql += ` ${u.type} ${uSql}`;
            params.push(...uParams);
        }

        if (this.orderBy) {
            sql += ` ORDER BY ${this.orderBy.field} ${this.orderBy.direction}`;
        }

        sql += this.buildLimitOffset();
        return { sql, params };
    }

    async paginateWithCount(db, page = 1, pageSize = 10) {
        if (page <= 0 || pageSize <= 0) {
            throw new Error('分页参数必须大于0');
        }

        // count 查询
        let countSql = `SELECT COUNT(*) as total FROM ${this.tables.join(', ')}`;
        if (this.joins.length) countSql += ' ' + this.joins.join(' ');

        const { sql: whereSql, params } = this.buildConditions();
        if (whereSql) countSql += ` WHERE ${whereSql}`;

        const [countRows] = await db.query(countSql, params);
        const total = countRows[0].total;

        // 数据查询
        this.setLimit(pageSize).setOffset((page - 1) * pageSize);
        const { sql, params: dataParams } = this.build();
        const [rows] = await db.query(sql, dataParams);

        return {
            data: rows,
            total,
            page,
            pageSize,
            totalPages: Math.ceil(total / pageSize)
        };
    }
}

// ------------------- UPDATE -------------------
class UpdateBuilder extends QueryBuilder {
    constructor() {
        super();
        this.table = '';
        this.updateFields = {};
    }

    update(table) {
        this.table = table;
        return this;
    }

    set(field, value) {
        if (typeof value === 'object' && value.increment !== undefined) {
            this.updateFields[field] = { increment: value.increment };
        } else {
            this.updateFields[field] = value;
        }
        return this;
    }

    setFields(fieldsObj) {
        Object.entries(fieldsObj).forEach(([k, v]) => this.set(k, v));
        return this;
    }

    build() {
        const updateClauses = Object.keys(this.updateFields).map(field => {
            const val = this.updateFields[field];
            if (typeof val === 'object' && val.increment !== undefined) {
                return `${field} = ${field} + ?`;
            }
            return `${field} = ?`;
        }).join(', ');

        let sql = `UPDATE ${this.table} SET ${updateClauses}`;
        const { sql: whereSql, params: whereParams } = this.buildConditions();
        if (whereSql) sql += ` WHERE ${whereSql}`;

        const params = [
            ...Object.values(this.updateFields).map(v =>
                (typeof v === 'object' && v.increment !== undefined) ? v.increment : v
            ),
            ...whereParams
        ];
        return { sql, params };
    }
}

// ------------------- INSERT -------------------
class InsertBuilder extends QueryBuilder {
    constructor() {
        super();
        this.table = '';
        this.records = [];
        this.updateValues = {};
    }

    into(table) {
        this.table = table;
        return this;
    }

    values(records) {
        if (!Array.isArray(records)) records = [records];
        this.records = records;
        return this;
    }

    upsert(records, updateFields) {
        this.values(records);
        this.updateValues = updateFields;
        return this;
    }

    build() {
        if (!this.records.length) throw new Error('No values to insert');

        const columns = Object.keys(this.records[0]);
        const placeholders = `(${columns.map(() => '?').join(', ')})`;
        const valuesClause = this.records.map(() => placeholders).join(', ');
        const params = this.records.flatMap(r => columns.map(c => r[c]));

        let sql = `INSERT INTO ${this.table} (${columns.join(', ')}) VALUES ${valuesClause}`;

        if (Object.keys(this.updateValues).length) {
            const updates = Object.keys(this.updateValues)
                .map(f => `${f} = VALUES(${f})`) // MySQL 8.0+ 兼容写法
                .join(', ');
            sql += ` ON DUPLICATE KEY UPDATE ${updates}`;
        }

        return { sql, params };
    }
}

// ------------------- DELETE -------------------
class DeleteBuilder extends QueryBuilder {
    constructor() {
        super();
        this.table = '';
    }

    from(table) {
        this.table = table;
        return this;
    }

    build() {
        let sql = `DELETE FROM ${this.table}`;
        const { sql: whereSql, params } = this.buildConditions();
        if (!whereSql) throw new Error('DELETE without WHERE is not allowed!');
        sql += ` WHERE ${whereSql}`;
        return { sql, params };
    }
}

module.exports = {
    SelectBuilder,
    UpdateBuilder,
    InsertBuilder,
    DeleteBuilder
};