364 lines
9.9 KiB
JavaScript
364 lines
9.9 KiB
JavaScript
class QueryBuilder {
|
||
constructor() {
|
||
this.conditions = {};
|
||
this.limit = null;
|
||
this.offset = null;
|
||
this.groupBy = null;
|
||
}
|
||
|
||
where(condition, ...params) {
|
||
this.conditions[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;
|
||
}
|
||
|
||
sqdata(sql, params) {
|
||
return new Promise((resolve, reject) => {
|
||
global.sqlReq.query(sql, params, (err, result) => {
|
||
if (err) {
|
||
reject(err);
|
||
}
|
||
resolve(result);
|
||
});
|
||
});
|
||
}
|
||
|
||
getParams() {
|
||
return Object.values(this.conditions).flat();
|
||
}
|
||
|
||
buildConditions() {
|
||
return Object.keys(this.conditions).map(condition => `${condition}`).join(' AND ');
|
||
}
|
||
}
|
||
|
||
class SelectBuilder extends QueryBuilder {
|
||
constructor() {
|
||
super();
|
||
this.selectFields = [];
|
||
this.tables = [];
|
||
this.orderByField = '';
|
||
this.orderByDirection = 'ASC';
|
||
this.subQueries = []; // 用于存储子查询
|
||
this.unions = []; // 存储UNION查询
|
||
}
|
||
// 添加UNION查询
|
||
union(queryBuilder, type = 'UNION') {
|
||
this.unions.push({queryBuilder, type});
|
||
return this;
|
||
}
|
||
|
||
// 添加UNION ALL查询
|
||
unionAll(queryBuilder) {
|
||
this.union(queryBuilder, 'UNION ALL');
|
||
return this;
|
||
}
|
||
|
||
// 构建主查询部分（不含ORDER BY/LIMIT/OFFSET）
|
||
buildMainQuery() {
|
||
const subQuerySQL = this.subQueries.map(({alias, subQuery}) => `(${subQuery}) AS ${alias}`);
|
||
const selectClause = this.selectFields.concat(subQuerySQL).join(', ');
|
||
|
||
let sql = `SELECT ${selectClause}
|
||
FROM ${this.tables.join(' ')}`;
|
||
|
||
const conditionClauses = this.buildConditions();
|
||
if (conditionClauses) {
|
||
sql += ` WHERE ${conditionClauses}`;
|
||
}
|
||
|
||
if (this.groupBy) {
|
||
sql += ` GROUP BY ${this.groupBy}`;
|
||
}
|
||
|
||
const params = this.getParams();
|
||
return {sql, params};
|
||
}
|
||
|
||
// 供UNION查询调用的构建方法
|
||
buildForUnion() {
|
||
return this.buildMainQuery();
|
||
}
|
||
|
||
select(fields) {
|
||
this.selectFields = fields.split(',').map(field => field.trim());
|
||
return this;
|
||
}
|
||
|
||
// 添加子查询
|
||
addSubQuery(alias, subQuery) {
|
||
this.subQueries.push({alias, subQuery});
|
||
return this;
|
||
}
|
||
|
||
whereLike(fields, keyword) {
|
||
const likeConditions = fields.map(field => `${field} LIKE ?`).join(' OR ');
|
||
this.conditions[likeConditions] = fields.map(() => `%${keyword}%`);
|
||
return this;
|
||
}
|
||
|
||
from(table) {
|
||
this.tables.push(table);
|
||
return this;
|
||
}
|
||
|
||
leftJoin(table, condition) {
|
||
this.tables.push(`LEFT JOIN ${table} ON ${condition}`);
|
||
return this;
|
||
}
|
||
|
||
orderBy(field, direction = 'ASC') {
|
||
this.orderByField = field;
|
||
this.orderByDirection = direction.toUpperCase();
|
||
return this;
|
||
}
|
||
|
||
paginate(page, pageSize) {
|
||
if (page <= 0 || pageSize <= 0) {
|
||
throw new Error('分页参数必须大于0');
|
||
}
|
||
this.limit = pageSize;
|
||
this.offset = (page - 1) * pageSize;
|
||
return this;
|
||
}
|
||
|
||
async chidBuild() {
|
||
|
||
let sql = `SELECT ${this.selectFields.join(', ')}
|
||
FROM ${this.tables.join(' ')}`;
|
||
let conditionClauses = this.buildConditions();
|
||
if (conditionClauses) {
|
||
sql += ` WHERE ${conditionClauses}`;
|
||
}
|
||
if (this.orderByField) {
|
||
sql += ` ORDER BY ${this.orderByField} ${this.orderByDirection}`;
|
||
}
|
||
if (this.limit !== null) {
|
||
sql += ` LIMIT ${this.limit}`;
|
||
}
|
||
if (this.offset !== null) {
|
||
sql += ` OFFSET ${this.offset}`;
|
||
}
|
||
return sql;
|
||
}
|
||
|
||
async build() {
|
||
const main = this.buildMainQuery();
|
||
let fullSql = `(${main.sql})`;
|
||
const allParams = [...main.params];
|
||
|
||
// 处理UNION部分
|
||
for (const union of this.unions) {
|
||
const unionBuilder = union.queryBuilder;
|
||
if (!(unionBuilder instanceof SelectBuilder)) {
|
||
throw new Error('UNION query must be a SelectBuilder instance');
|
||
}
|
||
const unionResult = unionBuilder.buildForUnion();
|
||
fullSql += ` ${union.type} (${unionResult.sql})`;
|
||
allParams.push(...unionResult.params);
|
||
}
|
||
|
||
// 添加ORDER BY、LIMIT、OFFSET
|
||
if (this.orderByField) {
|
||
fullSql += ` ORDER BY ${this.orderByField} ${this.orderByDirection}`;
|
||
}
|
||
if (this.limit !== null) {
|
||
fullSql += ` LIMIT ${this.limit}`;
|
||
}
|
||
if (this.offset !== null) {
|
||
fullSql += ` OFFSET ${this.offset}`;
|
||
}
|
||
console.log(fullSql,allParams);
|
||
return await this.sqdata(fullSql, allParams);
|
||
}
|
||
}
|
||
|
||
|
||
class UpdateBuilder extends QueryBuilder {
|
||
constructor() {
|
||
super();
|
||
this.table = '';
|
||
this.updateFields = {};
|
||
}
|
||
|
||
update(table) {
|
||
this.table = table;
|
||
return this;
|
||
}
|
||
|
||
set(field, value) {
|
||
if (value && value.increment && typeof value === 'object' ) {
|
||
this.updateFields[field] = {increment: value.increment};
|
||
} else {
|
||
this.updateFields[field] = value;
|
||
}
|
||
return this;
|
||
}
|
||
|
||
async build() {
|
||
let sql = `UPDATE ${this.table}
|
||
SET `;
|
||
let updateClauses = Object.keys(this.updateFields).map(field => {
|
||
const value = this.updateFields[field];
|
||
if (value && value.increment && typeof value === 'object' ) {
|
||
return `${field} = ${field} + ?`;
|
||
}
|
||
return `${field} = ?`;
|
||
}).join(', ');
|
||
|
||
sql += updateClauses;
|
||
|
||
let conditionClauses = this.buildConditions();
|
||
if (conditionClauses) {
|
||
sql += ` WHERE ${conditionClauses}`;
|
||
}
|
||
// 处理参数，确保自增字段也传入增量值
|
||
const params = [
|
||
...Object.values(this.updateFields).map(value =>
|
||
(value && value.increment && typeof value === 'object' ) ? value.increment : value
|
||
),
|
||
...this.getParams()
|
||
];
|
||
return await this.sqdata(sql, params);
|
||
}
|
||
}
|
||
|
||
class InsertBuilder extends QueryBuilder {
|
||
constructor() {
|
||
super();
|
||
this.table = '';
|
||
this.insertValues = [];
|
||
this.updateValues = {};
|
||
}
|
||
|
||
insertInto(table) {
|
||
this.table = table;
|
||
return this;
|
||
}
|
||
|
||
// 仍然保留单条记录的插入
|
||
values(values) {
|
||
if (Array.isArray(values)) {
|
||
this.insertValues = values;
|
||
} else {
|
||
this.insertValues = [values]; // 将单条记录包装成数组
|
||
}
|
||
return this;
|
||
}
|
||
|
||
// 新增方法，支持一次插入多条记录
|
||
valuesMultiple(records) {
|
||
if (!Array.isArray(records) || records.length === 0) {
|
||
throw new Error('Values must be a non-empty array');
|
||
}
|
||
|
||
// 确保每一条记录都是对象
|
||
records.forEach(record => {
|
||
if (typeof record !== 'object') {
|
||
throw new Error('Each record must be an object');
|
||
}
|
||
});
|
||
|
||
this.insertValues = records;
|
||
return this;
|
||
}
|
||
|
||
// 新增 upsert 方法，支持更新或插入
|
||
upsert(values, updateFields) {
|
||
// values: 要插入的记录
|
||
// updateFields: 如果记录存在时，需要更新的字段
|
||
if (!Array.isArray(values) || values.length === 0) {
|
||
throw new Error('Values must be a non-empty array');
|
||
}
|
||
|
||
// 检查每条记录是否是对象
|
||
values.forEach(record => {
|
||
if (typeof record !== 'object') {
|
||
throw new Error('Each record must be an object');
|
||
}
|
||
});
|
||
|
||
this.insertValues = values;
|
||
this.updateValues = updateFields || {};
|
||
return this;
|
||
}
|
||
|
||
async build() {
|
||
if (this.insertValues.length === 0) {
|
||
throw new Error("No values to insert");
|
||
}
|
||
|
||
// 获取表单列名，假设所有记录有相同的字段
|
||
const columns = Object.keys(this.insertValues[0]);
|
||
|
||
// 构建 VALUES 子句，支持批量插入
|
||
const valuePlaceholders = this.insertValues.map(() =>
|
||
`(${columns.map(() => '?').join(', ')})`
|
||
).join(', ');
|
||
|
||
// 展平所有的插入值
|
||
const params = this.insertValues.flatMap(record =>
|
||
columns.map(column => record[column])
|
||
);
|
||
|
||
// 如果有 updateFields，构建 ON DUPLICATE KEY UPDATE 子句
|
||
let updateClause = '';
|
||
if (Object.keys(this.updateValues).length > 0) {
|
||
updateClause = ' ON DUPLICATE KEY UPDATE ' +
|
||
Object.keys(this.updateValues).map(field => {
|
||
return `${field} = VALUES(${field})`;
|
||
}).join(', ');
|
||
}
|
||
|
||
// 生成 SQL 语句
|
||
const sql = `INSERT INTO ${this.table} (${columns.join(', ')})
|
||
VALUES ${valuePlaceholders} ${updateClause}`;
|
||
// 执行查询
|
||
return await this.sqdata(sql, params);
|
||
}
|
||
}
|
||
|
||
|
||
class DeleteBuilder extends QueryBuilder {
|
||
constructor() {
|
||
super();
|
||
this.table = '';
|
||
}
|
||
|
||
deleteFrom(table) {
|
||
this.table = table;
|
||
return this;
|
||
}
|
||
|
||
async build() {
|
||
let sql = `DELETE
|
||
FROM ${this.table}`;
|
||
let conditionClauses = this.buildConditions();
|
||
if (conditionClauses) {
|
||
sql += ` WHERE ${conditionClauses}`;
|
||
}
|
||
return await this.sqdata(sql, this.getParams());
|
||
}
|
||
}
|
||
|
||
module.exports = {
|
||
SelectBuilder,
|
||
UpdateBuilder,
|
||
InsertBuilder,
|
||
DeleteBuilder,
|
||
};
|