wenbao-website/server/db/Query.ts

import { QueryResult } from 'mysql2';
import { pool } from '../config/db';

/**
 * 执行SELECT查询
 * @param sql SQL查询语句
 * @param params 查询参数
 * @returns 查询结果
 */
export async function query<T extends QueryResult = any>(sql: string, params: any[] = []): Promise<T[]> {
  const [rows] = await pool.execute<T>(sql, params);
  return rows as T[];
}

export async function queryAndReturnInsertId(sql: string, params: any[] = []) : Promise<number> {
  const [rows] = await pool.execute(sql, params);
  const [insertIdRows] = await query('SELECT LAST_INSERT_ID() AS id');
  return insertIdRows.id;
}

/**
 * 执行INSERT查询
 * @param sql SQL插入语句
 * @param params 查询参数
 * @returns 插入结果，包含insertId
 */
export async function insert(sql: string, params: any[] = []): Promise<{ insertId: number }> {
  const [result] = await pool.execute(sql, params);
  return result as { insertId: number };
}

/**
 * 执行UPDATE查询
 * @param sql SQL更新语句
 * @param params 查询参数
 * @returns 更新结果，包含affectedRows
 */
export async function update(sql: string, params: any[] = []): Promise<{ affectedRows: number }> {
  const [result] = await pool.execute(sql, params);
  return result as { affectedRows: number };
}

/**
 * 执行DELETE查询
 * @param sql SQL删除语句
 * @param params 查询参数
 * @returns 删除结果，包含affectedRows
 */
export async function remove(sql: string, params: any[] = []): Promise<{ affectedRows: number }> {
  const [result] = await pool.execute(sql, params);
  return result as { affectedRows: number };
}

/**
 * 执行事务
 * @param callback 事务回调函数
 * @returns 事务执行结果
 */
export async function transaction<T>(callback: (connection: any) => Promise<T>): Promise<T> {
  const connection = await pool.getConnection();
  
  try {
    await connection.beginTransaction();
    const result = await callback(connection);
    await connection.commit();
    return result;
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}