前端学习 express nodejs 连接数据库 增删改查接口实例

1、安装express

# 安装 MySQL 驱动
npm install mysql2

# 安装 dotenv 管理环境变量
# npm install dotenv

# 安装数据库连接池管理
npm install mysql2-promise

# 安装 express
npm install -g express-generator

2、新建项目

express 项目名

3、创建env文件

# 数据库配置
DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASSWORD=your_password
DB_DATABASE=your_database
DB_CONNECTION_LIMIT=10

# 可选：开发/生产环境
NODE_ENV=development

4、 创建数据库连接模块

// db/connection.js
const mysql = require('mysql2/promise');
require('dotenv').config();

class Database {
  constructor() {
    this.pool = null;
    this.init();
  }

  init() {
    try {
      this.pool = mysql.createPool({
        host: process.env.DB_HOST || 'localhost',
        port: process.env.DB_PORT || 3306,
        user: process.env.DB_USER || 'root',
        password: process.env.DB_PASSWORD || '123456',
        database: process.env.DB_DATABASE || 'test',
        waitForConnections: true,
        connectionLimit: process.env.DB_CONNECTION_LIMIT || 10,
        queueLimit: 0,
        enableKeepAlive: true,
        keepAliveInitialDelay: 0
      });

      console.log('✅ MySQL 连接池创建成功',process.env.DB_PASSWORD );
    } catch (error) {
      console.error('❌ 创建数据库连接池失败:', error.message);
    }
  }

  async getConnection() {
    try {
      const connection = await this.pool.getConnection();
      console.log('🔗 获取数据库连接成功');
      return connection;
    } catch (error) {
      console.error('❌ 获取数据库连接失败:', error.message);
      throw error;
    }
  }

  async execute(sql, params = []) {
    let connection;
    try {
      connection = await this.getConnection();
      const [results] = await connection.execute(sql, params);
      return results;
    } catch (error) {
      console.error('❌ 执行SQL失败:', error.message);
      console.error('SQL:', sql);
      console.error('参数:', params);
      throw error;
    } finally {
      if (connection) connection.release();
    }
  }

  async query(sql, params = []) {
    try {
      const [results] = await this.pool.query(sql, params);
      return results;
    } catch (error) {
      console.error('❌ 查询失败:', error.message);
      console.error('SQL:', sql);
      console.error('参数:', params);
      throw error;
    }
  }

  // 测试连接
  async testConnection() {
    try {
      const result = await this.execute('SELECT 1 + 1 AS solution');
      console.log('✅ 数据库连接测试成功:', result);
      return true;
    } catch (error) {
      console.error('❌ 数据库连接测试失败:', error.message);
      return false;
    }
  }

  // 关闭连接池
  async close() {
    if (this.pool) {
      await this.pool.end();
      console.log('🔒 数据库连接池已关闭');
    }
  }
}

// 创建单例实例
const database = new Database();
module.exports = database;

5、创建模型/服务层

// models/userModel.js
const db = require('./connection');

class UserModel {
  // 创建用户表
  async createTable() {
    const sql = `
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL UNIQUE,
        email VARCHAR(100) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `;
    await db.execute(sql);
    console.log('✅ 用户表创建/检查完成');
  }

  // 创建用户
  async createUser(userData) {
    const sql = `
      INSERT INTO users (username, email, password) 
      VALUES (?, ?, ?)
    `;
    const params = [userData.username, userData.email, userData.password];
    const result = await db.execute(sql, params);
    return { id: result.insertId, ...userData };
  }

  // 查询所有用户
  async getAllUsers() {
    const sql = `SELECT id, username, email, created_at FROM users`;
    return await db.execute(sql);
  }

  // 根据ID查询用户
  async getUserById(id) {
    const sql = `SELECT id, username, email, created_at FROM users WHERE id = ?`;
    const results = await db.execute(sql, [id]);
    return results[0] || null;
  }

  // 根据用户名查询用户
  async getUserByUsername(username) {
    const sql = `SELECT * FROM users WHERE username = ?`;
    const results = await db.execute(sql, [username]);
    return results[0] || null;
  }

  // 更新用户
  async updateUser(id, userData) {
    const sql = `
      UPDATE users 
      SET username = ?, email = ?, password = ? 
      WHERE id = ?
    `;
    const params = [userData.username, userData.email, userData.password, id];
    const result = await db.execute(sql, params);
    return result.affectedRows > 0;
  }

  // 删除用户
  async deleteUser(id) {
    const sql = `DELETE FROM users WHERE id = ?`;
    const result = await db.execute(sql, [id]);
    return result.affectedRows > 0;
  }

  // 分页查询用户
  async getUsersPaginated(page = 1, limit = 10) {
    const offset = (page - 1) * limit;
    const sql = `
      SELECT id, username, email, created_at 
      FROM users 
      LIMIT ? OFFSET ?
    `;
    return await db.execute(sql, [limit, offset]);
  }

  // 统计用户总数
  async countUsers() {
    const sql = `SELECT COUNT(*) as total FROM users`;
    const result = await db.execute(sql);
    return result[0].total;
  }
    // 通用查询方法
  async query(sql, params = []) {
    return await db.execute(sql, params);
  }

  // 创建带状态的用户表
  async createTable() {
    const sql = `
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL UNIQUE,
        email VARCHAR(100) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        status TINYINT DEFAULT 1 COMMENT '0:禁用 1:启用 2:锁定',
        gender tiny DEFAULT 0 COMMENT '0:未知 1:男 2:女',
        department_id INT DEFAULT 0,
        role_id INT DEFAULT 3 COMMENT '角色ID',
        phone VARCHAR(20),
        avatar VARCHAR(255),
        last_login_time TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_username (username),
        INDEX idx_status (status),
        INDEX idx_department (department_id)
      )
    `;
    await db.execute(sql);
    console.log('✅ 用户表创建/检查完成');
    return true;
  }
}

module.exports = new UserModel();

6、创建控制器

// controllers/userController.js
const db = require('./connection');

class UserController {
  // ============ 用户列表相关 ============
  
  // 获取用户列表（带分页和查询条件）
  async getUserList(req, res) {
    try {
      console.log('📝 收到用户列表查询请求:', req.body);
      
      const { 
        page = 1, 
        pageSize = 10, 
        username, 
        email,
        role_id,
        status,
        department_id,
        startTime,
        endTime 
      } = req.body;
      
      // 构建查询条件
      let whereClause = 'WHERE 1=1';
      const params = [];
      
      if (username) {
        whereClause += ' AND username LIKE ?';
        params.push(`%${username}%`);
      }
      
      if (email) {
        whereClause += ' AND email LIKE ?';
        params.push(`%${email}%`);
      }
      
      if (status !== undefined && status !== '') {
        whereClause += ' AND status = ?';
        params.push(parseInt(status));
      }
      
      if (role_id) {
        whereClause += ' AND role_id = ?';
        params.push(parseInt(role_id));
      }
      
      if (department_id) {
        whereClause += ' AND department_id = ?';
        params.push(parseInt(department_id));
      }
      
      if (startTime) {
        whereClause += ' AND created_at >= ?';
        params.push(startTime);
      }
      
      if (endTime) {
        whereClause += ' AND created_at <= ?';
        params.push(endTime);
      }
      
      const offset = (page - 1) * pageSize;
      
      console.log('📊 执行用户列表查询...');
      
      // 查询数据
      const sql = `
        SELECT 
          u.*,
          r.name as role_name,
          d.name as department_name
        FROM users u
        LEFT JOIN roles r ON u.role_id = r.id
        LEFT JOIN departments d ON u.department_id = d.id
        ${whereClause} 
        ORDER BY u.id DESC 
        LIMIT ? OFFSET ?
      `;
      
      const rows = await db.query(sql, [...params, parseInt(pageSize), offset]);
      
      // 查询总数
      const countSql = `
        SELECT COUNT(*) as total 
        FROM users u
        LEFT JOIN roles r ON u.role_id = r.id
        LEFT JOIN departments d ON u.department_id = d.id
        ${whereClause}
      `;
      
      const countResult = await db.query(countSql, params);
      const total = countResult[0]?.total || 0;
      
      console.log(`✅ 用户列表查询成功: 找到 ${total} 条记录，返回 ${rows.length} 条`);
      
      // 格式化数据
      const formattedRows = rows.map(row => ({
        ...row,
        created_at: row.created_at ? new Date(row.created_at).toLocaleString('zh-CN') : '',
        last_login_time: row.last_login_time ? new Date(row.last_login_time).toLocaleString('zh-CN') : null
      }));
      
      res.json({
        success: true,
        data: {
          list: formattedRows,
          total,
          page: parseInt(page),
          pageSize: parseInt(pageSize)
        },
        message: 'success'
      });
      
    } catch (error) {
      console.error('❌ 获取用户列表失败:', error);
      res.status(500).json({
        success: false,
        message: '获取用户列表失败: ' + error.message
      });
    }
  }

  // 获取树形用户列表
  async getUserTreeList(req, res) {
    try {
      console.log('📝 收到树形用户列表请求');
      
      // 获取所有部门
      const deptSql = 'SELECT id, name FROM departments ORDER BY parent_id, id';
      const departments = await db.query(deptSql);
      
      // 获取所有用户
      const userSql = `
        SELECT 
          u.id, 
          u.username, 
          u.email, 
          u.department_id,
          u.status,
          r.name as role_name
        FROM users u
        LEFT JOIN roles r ON u.role_id = r.id
        WHERE u.status = 1
        ORDER BY u.id
      `;
      const users = await db.query(userSql);
      
      // 构建树形结构
      const treeData = departments.map(dept => ({
        id: `dept_${dept.id}`,
        name: dept.name,
        type: 'department',
        children: 6
          .filter(user => user.department_id === dept.id)
          .map(user => ({
            id: user.id,
            name: user.username,
            email: user.email,
            role: user.role_name,
            type: 'user'
          }))
      }));
      
      // 添加未分配部门的用户
      const unassignedUsers = users.filter(user => !user.department_id);
      if (unassignedUsers.length > 0) {
        treeData.push({
          id: 'dept_0',
          name: '未分配部门',
          type: 'department',
          children: unassignedUsers.map(user => ({
            id: user.id,
            name: user.username,
            email: user.email,
            role: user.role_name,
            type: 'user'
          }))
        });
      }
      
      console.log(`✅ 树形用户列表查询成功: ${treeData.length} 个部门`);
      
      res.json({
        success: true,
        data: treeData,
        message: 'success'
      });
    } catch (error) {
      console.error('❌ 获取树形用户列表失败:', error);
      res.status(500).json({
        success: false,
        message: '获取树形用户列表失败: ' + error.message
      });
    }
  }

  // ============ 字典数据相关 ============
  
  // 获取用户状态字典
  async getUserStatus(req, res) {
    try {
      console.log('📝 收到状态字典请求');
      
      // 从数据库查询状态字典
      const sql = 'SELECT value, label FROM user_status ORDER BY value';
      const statusList = await db.query(sql);
      
      // 如果没有表或数据，返回默认数据
      if (!statusList || statusList.length === 0) {
        const defaultStatus = [
          { value: 0, label: '禁用' },
          { value: 1, label: '启用' },
          { value: 2, label: '锁定' }
        ];
        return res.json({
          success: true,
          data: defaultStatus,
          message: 'success'
        });
      }
      
      console.log(`✅ 状态字典查询成功: ${statusList.length} 条`);
      
      res.json({
        success: true,
        data: statusList,
        message: 'success'
      });
    } catch (error) {
      console.error('❌ 获取用户状态字典失败:', error);
      
      // 返回默认数据作为降级方案
      const defaultStatus = [
        { value: 0, label: '禁用' },
        { value: 1, label: '启用' },
        { value: 2, label: '锁定' }
      ];
      
      res.json({
        success: true,
        data: defaultStatus,
        message: 'success'
      });
    }
  }

  // 获取用户性别字典
  async getUserGender(req, res) {
    try {
      console.log('📝 收到性别字典请求');
      
      // 从数据库查询性别字典
      const sql = 'SELECT value, label FROM user_gender ORDER BY value';
      const genderList = await db.query(sql);
      
      // 如果没有表或数据，返回默认数据
      if (!genderList || genderList.length === 0) {
        const defaultGender = [
          { value: 0, label: '未知' },
          { value: 1, label: '男' },
          { value: 2, label: '女' }
        ];
        return res.json({
          success: true,
          data: defaultGender,
          message: 'success'
        });
      }
      
      console.log(`✅ 性别字典查询成功: ${genderList.length} 条`);
      
      res.json({
        success: true,
        data: genderList,
        message: 'success'
      });
    } catch (error) {
      console.error('❌ 获取用户性别字典失败:', error);
      
      // 返回默认数据
      const defaultGender = [
        { value: 0, label: '未知' },
        { value: 1, label: '男' },
        { value: 2, label: '女' }
      ];
      
      res.json({
        success: true,
        data: defaultGender,
        message: 'success'
      });
    }
  }

  // 获取用户部门列表
  async getUserDepartment(req, res) {
    try {
      console.log('📝 收到部门列表请求');
      
      // 从数据库查询部门
      const sql = 'SELECT id, name, parent_id as parentId FROM departments ORDER BY parent_id, id';
      const departmentList = await db.query(sql);
      
      // 如果没有表或数据，返回默认数据
      if (!departmentList || departmentList.length === 0) {
        const defaultDepartments = [
          { id: 1, name: '技术部', parentId: 0 },
          { id: 2, name: '市场部', parentId: 0 },
          { id: 3, name: '人事部', parentId: 0 },
          { id: 4, name: '前端开发组', parentId: 1 },
          { id: 5, name: '后端开发组', parentId: 1 }
        ];
        return res.json({
          success: true,
          data: defaultDepartments,
          message: 'success'
        });
      }
      
      console.log(`✅ 部门列表查询成功: ${departmentList.length} 条`);
      
      res.json({
        success: true,
        data: departmentList,
        message: 'success'
      });
    } catch (error) {
      console.error('❌ 获取用户部门列表失败:', error);
      
      // 返回默认数据
      const defaultDepartments = [
        { id: 1, name: '技术部', parentId: 0 },
        { id: 2, name: '市场部', parentId: 0 },
        { id: 3, name: '人事部', parentId: 0 }
      ];
      
      res.json({
        success: true,
        data: defaultDepartments,
        message: 'success'
      });
    }
  }

  // 获取用户角色字典
  async getUserRole(req, res) {
    try {
      console.log('📝 收到角色字典请求');
      
      // 从数据库查询角色
      const sql = 'SELECT id, name, code FROM roles ORDER BY id';
      const roleList = await db.query(sql);
      
      // 如果没有表或数据，返回默认数据
      if (!roleList || roleList.length === 0) {
        const defaultRoles = [
          { id: 1, name: '超级管理员', code: 'super_admin' },
          { id: 2, name: '管理员', code: 'admin' },
          { id: 3, name: '普通用户', code: 'user' },
          { id: 4, name: '访客', code: 'guest' }
        ];
        return res.json({
          success: true,
          data: defaultRoles,
          message: 'success'
        });
      }
      
      console.log(`✅ 角色字典查询成功: ${roleList.length} 条`);
      
      res.json({
        success: true,
        data: roleList,
        message: 'success'
      });
    } catch (error) {
      console.error('❌ 获取用户角色字典失败:', error);
      
      // 返回默认数据
      const defaultRoles = [
        { id: 1, name: '超级管理员', code: 'super_admin' },
        { id: 2, name: '管理员', code: 'admin' },
        { id: 3, name: '普通用户', code: 'user' }
      ];
      
      res.json({
        success: true,
        data: defaultRoles,
        message: 'success'
      });
    }
  }

  // ============ 用户操作相关 ============

  // 新增用户
  async addUser(req, res) {
    try {
      console.log('📝 收到新增用户请求:', req.body);
      
      const { 
        username, 
        email, 
        password, 
        phone, 
        role_id, 
        department_id, 
        gender = 0, 
        status = 1,
        remark 
      } = req.body;
      
      // 验证必填字段
      if (!username || !email || !password) {
        return res.status(400).json({
          success: false,
          message: '用户名、邮箱和密码是必填项'
        });
      }
      
      // 检查用户名是否已存在
      const checkSql = 'SELECT id FROM users WHERE username = ? OR email = ?';
      const existing = await db.query(checkSql, [username, email]);
      
      if (existing && existing.length > 0) {
        return res.status(409).json({
          success: false,
          message: '用户名或邮箱已存在'
        });
      }
      
      // 插入用户数据
      const insertSql = `
        INSERT INTO users 
        (username, email, password, phone, role_id, department_id, gender, status, remark) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      
      const result = await db.query(insertSql, [
        username, 
        email, 
        password, 
        phone || null, 
        role_id || null, 
        department_id || null, 
        gender, 
        status, 
        remark || null
      ]);
      
      console.log(`✅ 用户创建成功，ID: ${result.insertId}`);
      
      // 获取创建的用户信息
      const userSql = 'SELECT * FROM users WHERE id = ?';
      const user = await db.query(userSql, [result.insertId]);
      
      res.json({
        success: true,
        data: user[0],
        message: '用户添加成功'
      });
    } catch (error) {
      console.error('❌ 新增用户失败:', error);
      res.status(500).json({
        success: false,
        message: '新增用户失败: ' + error.message
      });
    }
  }

  // 批量添加用户
  async batchAddUser(req, res) {
    try {
      console.log('📝 收到批量添加用户请求');
      
      // 检查是否有文件
      if (!req.file) {
        return res.status(400).json({
          success: false,
          message: '请上传文件'
        });
      }
      
      // 这里可以解析Excel或CSV文件
      // 暂时模拟处理
      const mockUsers = [
        { username: 'batch1', email: 'batch1@test.com', password: '123456', role_id: 3, department_id: 1 },
        { username: 'batch2', email: 'batch2@test.com', password: '123456', role_id: 3, department_id: 2 }
      ];
      
      const results = [];
      for (const userData of mockUsers) {
        try {
          const insertSql = `
            INSERT INTO users 
            (username, email, password, role_id, department_id, status) 
            VALUES (?, ?, ?, ?, ?, 1)
          `;
          
          const result = await db.query(insertSql, [
            userData.username,
            userData.email,
            userData.password,
            userData.role_id,
            userData.department_id
          ]);
          
          results.push({
            id: result.insertId,
            username: userData.username,
            success: true
          });
        } catch (error) {
          results.push({
            username: userData.username,
            success: false,
            error: error.message
          });
        }
      }
      
      const successCount = results.filter(r => r.success).length;
      const failCount = results.filter(r => !r.success).length;
      
      console.log(`✅ 批量导入完成: 成功 ${successCount} 条，失败 ${failCount} 条`);
      
      res.json({
        success: true,
        data: results,
        message: `批量导入完成，成功 ${successCount} 条，失败 ${failCount} 条`
      });
    } catch (error) {
      console.error('❌ 批量添加用户失败:', error);
      res.status(500).json({
        success: false,
        message: '批量添加用户失败: ' + error.message
      });
    }
  }

  // 编辑用户
  async editUser(req, res) {
    try {
      console.log('📝 收到编辑用户请求:', req.body);
      
      const { id, ...updateData } = req.body;
      
      if (!id) {
        return res.status(400).json({
          success: false,
          message: '用户ID是必填项'
        });
      }
      
      // 检查用户是否存在
      const checkSql = 'SELECT id FROM users WHERE id = ?';
      const existing = await db.query(checkSql, [id]);
      
      if (!existing || existing.length === 0) {
        return res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
      
      // 构建更新字段
      const fields = [];
      const values = [];
      
      Object.keys(updateData).forEach(key => {
        if (updateData[key] !== undefined) {
          fields.push(`${key} = ?`);
          values.push(updateData[key]);
        }
      });
      
      if (fields.length === 0) {
        return res.status(400).json({
          success: false,
          message: '没有提供更新数据'
        });
      }
      
      values.push(id);
      
      const sql = `UPDATE users SET ${fields.join(', ')} WHERE id = ?`;
      const result = await db.query(sql, values);
      
      console.log(`✅ 用户更新成功，ID: ${id}，影响行数: ${result.affectedRows}`);
      
      res.json({
        success: true,
        message: '用户编辑成功'
      });
    } catch (error) {
      console.error('❌ 编辑用户失败:', error);
      res.status(500).json({
        success: false,
        message: '编辑用户失败: ' + error.message
      });
    }
  }

  // 删除用户（批量）
  async deleteUser(req, res) {
    try {
      const { id: ids } = req.body;
      
      console.log('📝 收到删除用户请求:', ids);
      
      if (!ids || !Array.isArray(ids) || ids.length === 0) {
        return res.status(400).json({
          success: false,
          message: '请提供要删除的用户ID数组'
        });
      }
      
      // 检查要删除的用户是否存在
      const placeholders = ids.map(() => '?').join(',');
      const checkSql = `SELECT COUNT(*) as count FROM users WHERE id IN (${placeholders})`;
      const checkResult = await db.query(checkSql, ids);
      
      if (checkResult[0]?.count !== ids.length) {
        return res.status(404).json({
          success: false,
          message: '部分用户不存在'
        });
      }
      
      // 使用 IN 语句批量删除
      const deleteSql = `DELETE FROM users WHERE id IN (${placeholders})`;
      const result = await db.query(deleteSql, ids);
      
      console.log(`✅ 删除成功，影响行数: ${result.affectedRows}`);
      
      res.json({
        success: true,
        data: { deletedCount: result.affectedRows },
        message: `删除完成，成功删除 ${result.affectedRows} 个用户`
      });
    } catch (error) {
      console.error('❌ 删除用户失败:', error);
      res.status(500).json({
        success: false,
        message: '删除用户失败: ' + error.message
      });
    }
  }

  // 切换用户状态
  async changeUserStatus(req, res) {
    try {
      const { id, status } = req.body;
      
      console.log(`📝 收到切换用户状态请求: id=${id}, status=${status}`);
      
      if (!id || status === undefined) {
        return res.status(400).json({
          success: false,
          message: '用户ID和状态都是必填项'
        });
      }
      
      // 检查用户是否存在
      const checkSql = 'SELECT id FROM users WHERE id = ?';
      const existing = await db.query(checkSql, [id]);
      
      if (!existing || existing.length === 0) {
        return res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
      
      const sql = 'UPDATE users SET status = ? WHERE id = ?';
      const result = await db.query(sql, [status, id]);
      
      console.log(`✅ 用户状态更新成功，ID: ${id}，新状态: ${status}`);
      
      res.json({
        success: true,
        message: '用户状态更新成功'
      });
    } catch (error) {
      console.error('❌ 切换用户状态失败:', error);
      res.status(500).json({
        success: false,
        message: '切换用户状态失败: ' + error.message
      });
    }
  }

  // 重置用户密码
  async resetUserPassWord(req, res) {
    try {
      const { id } = req.body;
      
      console.log(`📝 收到重置用户密码请求: id=${id}`);
      
      if (!id) {
        return res.status(400).json({
          success: false,
          message: '用户ID是必填项'
        });
      }
      
      // 检查用户是否存在
      const checkSql = 'SELECT id FROM users WHERE id = ?';
      const existing = await db.query(checkSql, [id]);
      
      if (!existing || existing.length === 0) {
        return res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
      
      // 重置密码为默认密码
      const defaultPassword = '123456';
      const sql = 'UPDATE users SET password = ? WHERE id = ?';
      const result = await db.query(sql, [defaultPassword, id]);
      
      console.log(`✅ 用户密码重置成功，ID: ${id}`);
      
      res.json({
        success: true,
        message: '用户密码重置成功，新密码为：123456'
      });
    } catch (error) {
      console.error('❌ 重置用户密码失败:', error);
      res.status(500).json({
        success: false,
        message: '重置用户密码失败: ' + error.message
      });
    }
  }

  // 导出用户数据
  async exportUserInfo(req, res) {
    try {
      console.log('📝 收到导出用户数据请求');
      
      const queryParams = req.body || {};
      
      // 构建查询条件
      let whereClause = 'WHERE 1=1';
      const params = [];
      
      if (queryParams.username) {
        whereClause += ' AND username LIKE ?';
        params.push(`%${queryParams.username}%`);
      }
      
      if (queryParams.status !== undefined && queryParams.status !== '') {
        whereClause += ' AND status = ?';
        params.push(parseInt(queryParams.status));
      }
      
      // 获取所有用户数据
      const sql = `
        SELECT 
          u.*,
          r.name as role_name,
          d.name as department_name,
          s.label as status_label,
          g.label as gender_label
        FROM users u
        LEFT JOIN roles r ON u.role_id = r.id
        LEFT JOIN departments d ON u.department_id = d.id
        LEFT JOIN user_status s ON u.status = s.value
        LEFT JOIN user_gender g ON u.gender = g.value
        ${whereClause}
        ORDER BY u.id DESC
      `;
      
      const users = await db.query(sql, params);
      
      console.log(`✅ 导出数据查询成功: ${users.length} 条`);
      
      // 设置响应头
      res.setHeader('Content-Type', 'application/json');
      res.setHeader('Content-Disposition', 'attachment; filename=users_export.json');
      
      // 发送数据
      res.json({
        success: true,
        data: users,
        message: '导出成功'
      });
    } catch (error) {
      console.error('❌ 导出用户数据失败:', error);
      res.status(500).json({
        success: false,
        message: '导出用户数据失败: ' + error.message
      });
    }
  }

  // ============ 原有的兼容方法 ============

  // 创建用户（保持兼容）
  async createUser(req, res) {
    return this.addUser(req, res);
  }

  // 获取所有用户（保持兼容）
  async getAllUsers(req, res) {
    try {
      const users = await db.query(`
        SELECT 
          u.*,
          r.name as role_name,
          d.name as department_name
        FROM users u
        LEFT JOIN roles r ON u.role_id = r.id
        LEFT JOIN departments d ON u.department_id = d.id
        ORDER BY u.id DESC
      `);
      
      res.json({
        success: true,
        data: users
      });
    } catch (error) {
      console.error('获取所有用户失败:', error);
      res.status(500).json({
        success: false,
        message: '服务器内部错误'
      });
    }
  }

  // 获取单个用户（保持兼容）
  async getUserById(req, res) {
    try {
      const userId = parseInt(req.params.id);
      
      if (isNaN(userId)) {
        return res.status(400).json({
          success: false,
          message: '无效的用户ID'
        });
      }

      const user = await db.query(`
        SELECT 
          u.*,
          r.name as role_name,
          d.name as department_name
        FROM users u
        LEFT JOIN roles r ON u.role_id = r.id
        LEFT JOIN departments d ON u.department_id = d.id
        WHERE u.id = ?
      `, [userId]);
      
      if (!user || user.length === 0) {
        return res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }

      res.json({
        success: true,
        data: user[0]
      });
    } catch (error) {
      console.error('获取用户失败:', error);
      res.status(500).json({
        success: false,
        message: '服务器内部错误'
      });
    }
  }

  // 更新用户（保持兼容）
  async updateUser(req, res) {
    try {
      const userId = parseInt(req.params.id);
      const { username, email, password } = req.body;

      if (isNaN(userId)) {
        return res.status(400).json({
          success: false,
          message: '无效的用户ID'
        });
      }

      const updateData = {};
      if (username !== undefined) updateData.username = username;
      if (email !== undefined) updateData.email = email;
      if (password !== undefined) updateData.password = password;

      const fields = Object.keys(updateData).map(key => `${key} = ?`);
      const values = Object.values(updateData);
      
      if (fields.length === 0) {
        return res.status(400).json({
          success: false,
          message: '没有提供更新数据'
        });
      }
      
      values.push(userId);
      
      const sql = `UPDATE users SET ${fields.join(', ')} WHERE id = ?`;
      const result = await db.query(sql, values);

      if (result.affectedRows === 0) {
        return res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }

      res.json({
        success: true,
        message: '用户更新成功'
      });
    } catch (error) {
      console.error('更新用户失败:', error);
      res.status(500).json({
        success: false,
        message: '服务器内部错误'
      });
    }
  }

  // 删除用户（单个，保持兼容）
  async deleteUserSingle(req, res) {
    try {
      const userId = parseInt(req.params.id);

      if (isNaN(userId)) {
        return res.status(400).json({
          success: false,
          message: '无效的用户ID'
        });
      }

      const result = await db.query('DELETE FROM users WHERE id = ?', [userId]);

      if (result.affectedRows === 0) {
        return res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }

      res.json({
        success: true,
        message: '用户删除成功'
      });
    } catch (error) {
      console.error('删除用户失败:', error);
      res.status(500).json({
        success: false,
        message: '服务器内部错误'
      });
    }
  }
}

// 创建实例并导出
const userController = new UserController();
module.exports = userController;

7、创建路由

// routes/users.js
const express = require('express');
const router = express.Router();
const userController = require('./userController');
const multer = require('multer');

// 配置multer用于文件上传
const storage = multer.memoryStorage();
const upload = multer({ 
  storage: storage,
  limits: {
    fileSize: 5 * 1024 * 1024 // 限制5MB
  }
});

// 1. 获取用户列表（POST方式，支持分页查询）
router.post('/list', userController.getUserList);

// 2. 获取树形用户列表
router.post('/tree/list', userController.getUserTreeList);

// 3. 新增用户
router.post('/add', userController.addUser);

// 4. 批量添加用户（文件上传）
router.post('/import', upload.single('file'), userController.batchAddUser);

// 5. 编辑用户
router.post('/edit', userController.editUser);

// 6. 删除用户（批量）
router.post('/delete', userController.deleteUser);

// 7. 切换用户状态
router.post('/change', userController.changeUserStatus);

// 8. 重置用户密码
router.post('/rest_password', userController.resetUserPassWord);

// 9. 导出用户数据
router.post('/export', userController.exportUserInfo);

// 10. 获取用户状态字典
router.get('/status', userController.getUserStatus);

// 11. 获取用户性别字典
router.get('/gender', userController.getUserGender);

// 12. 获取用户部门列表
router.get('/department', userController.getUserDepartment);

// 13. 获取用户角色字典
router.get('/role', userController.getUserRole);

// 14. 健康检查路由
router.get('/health', async (req, res) => {
  const db = require('../db/connection');
  const isConnected = await db.testConnection();
  
  res.json({
    success: isConnected,
    database: isConnected ? 'connected' : 'disconnected',
    timestamp: new Date().toISOString(),
    service: 'User API'
  });
});

// 15. 原有的RESTful风格接口（兼容性支持）
router.post('/users', userController.createUser);          // 创建用户
router.get('/users', userController.getAllUsers);          // 获取所有用户
router.get('/users/:id', userController.getUserById);      // 获取单个用户
router.put('/users/:id', userController.updateUser);       // 更新用户
router.delete('/users/:id', userController.deleteUserSingle);    // 删除单个用户

module.exports = router;

8、修改app.js

// app.js - 添加数据库相关配置
require('dotenv').config(); // 在文件顶部添加

// 在路由之前添加数据库初始化
const db = require('./db/connection');
const userModel = require('./models/userModel');

// 初始化数据库表（可选）
async function initializeDatabase() {
  try {
    const isConnected = await db.testConnection();
    if (isConnected) {
      await userModel.createTable();
      console.log('✅ 数据库初始化完成');
    }
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error);
  }
}

// 启动时初始化
initializeDatabase();

9、初始化数据库

// scripts/init-tables.js
require('dotenv').config();
const db = require('./connection');

async function initTables() {
  try {
    console.log('🔄 开始初始化数据库表...');
    
    // 1. 创建状态字典表
    await db.query(`
      CREATE TABLE IF NOT EXISTS user_status (
        id INT AUTO_INCREMENT PRIMARY KEY,
        value INT NOT NULL UNIQUE,
        label VARCHAR(20) NOT NULL
      )
    `);
    
    await db.query(`
      INSERT IGNORE INTO user_status (value, label) VALUES
      (0, '禁用'),
      (1, '启用'),
      (2, '锁定')
    `);
    console.log('✅ 状态字典表创建完成');
    
    // 2. 创建性别字典表
    await db.query(`
      CREATE TABLE IF NOT EXISTS user_gender (
        id INT AUTO_INCREMENT PRIMARY KEY,
        value INT NOT NULL UNIQUE,
        label VARCHAR(10) NOT NULL
      )
    `);
    
    await db.query(`
      INSERT IGNORE INTO user_gender (value, label) VALUES
      (0, '未知'),
      (1, '男'),
      (2, '女')
    `);
    console.log('✅ 性别字典表创建完成');
    
    // 3. 创建部门表
    await db.query(`
      CREATE TABLE IF NOT EXISTS departments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        parent_id INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
    
    await db.query(`
      INSERT IGNORE INTO departments (id, name, parent_id) VALUES
      (1, '技术部', 0),
      (2, '市场部', 0),
      (3, '人事部', 0),
      (4, '前端开发组', 1),
      (5, '后端开发组', 1)
    `);
    console.log('✅ 部门表创建完成');
    
    // 4. 创建角色表
    await db.query(`
      CREATE TABLE IF NOT EXISTS roles (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        code VARCHAR(50) NOT NULL UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
    
    await db.query(`
      INSERT IGNORE INTO roles (id, name, code) VALUES
      (1, '超级管理员', 'super_admin'),
      (2, '管理员', 'admin'),
      (3, '普通用户', 'user'),
      (4, '访客', 'guest')
    `);
    console.log('✅ 角色表创建完成');
    
    // 5. 创建用户表
    await db.query(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL UNIQUE,
        email VARCHAR(100) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        phone VARCHAR(20) DEFAULT NULL,
        status TINYINT DEFAULT 1 COMMENT '0:禁用 1:启用',
        gender TINYINT DEFAULT 0 COMMENT '0:未知 1:男 2:女',
        department_id INT DEFAULT NULL,
        role_id INT DEFAULT NULL,
        avatar VARCHAR(255) DEFAULT NULL,
        remark TEXT DEFAULT NULL,
        last_login_time TIMESTAMP NULL DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);
    console.log('✅ 用户表创建完成');
    
    // 6. 插入测试用户数据
    await db.query(`
      INSERT IGNORE INTO users (username, email, password, phone, role_id, department_id, gender, status) VALUES
      ('admin', 'admin@example.com', '123456', '13800138000', 1, 1, 1, 1),
      ('test_user', 'test@example.com', '123456', '13800138001', 3, 4, 1, 1),
      ('zhangsan', 'zhangsan@example.com', '123456', '13800138002', 3, 5, 1, 1),
      ('lisi', 'lisi@example.com', '123456', '13800138003', 3, 2, 2, 0)
    `);
    console.log('✅ 测试用户数据插入完成');
    
    console.log('\n🎉 所有表初始化完成！');
    
  } catch (error) {
    console.error('❌ 初始化失败:', error.message);
  }
}

initTables();

10、 运行数据库初始化脚本并下载依赖

node scripts/init-tables.js
npm i

11、 启动项目

node ./bin/www