index.js 3.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115
  1. import express from "express";
  2. import mysql from "mysql2/promise";
  3. import timeout from "connect-timeout";
  4. import bodyParser from "body-parser";
  5. const app = express();
  6. const PORT = 3000;
  7. // 设置请求超时,单位为毫秒(比如:设置 30 秒超时)
  8. app.use(timeout("30s"));
  9. app.use(bodyParser.json()); // 在请求处理之前,先解析JSON
  10. // 处理超时请求
  11. app.use((req, res, next) => {
  12. if (req.timedout) {
  13. return res.status(408).send("Request Timeout");
  14. }
  15. next();
  16. });
  17. // 获取表和字段信息
  18. app.get("/tables", async (req, res) => {
  19. let pool;
  20. try {
  21. const { IP, host, baseName, username, password } = req.query;
  22. // 数据库连接配置
  23. pool = mysql.createPool({
  24. host: IP,
  25. user: username,
  26. password: password,
  27. database: baseName,
  28. port: host,
  29. });
  30. const connection = await pool.getConnection();
  31. const [tables] = await connection.query("SHOW TABLES");
  32. const tableFieldMap = {};
  33. for (const table of tables) {
  34. const tableName = Object.values(table)[0];
  35. const [fields] = await connection.query(`DESCRIBE ${tableName}`);
  36. tableFieldMap[tableName] = fields.map((field) => field.Field);
  37. }
  38. connection.release();
  39. res.json(tableFieldMap);
  40. } catch (error) {
  41. // console.error("Error fetching database structure:", error);
  42. res.status(500).send("请检查填写的数据库数据是否有误" + error);
  43. } finally {
  44. // 确保关闭数据库连接池
  45. if (pool) {
  46. await pool.end();
  47. }
  48. }
  49. });
  50. app.post("/filterTablesData", async (req, res) => {
  51. try {
  52. console.log(req.params, req.body, "req.query.filterData");
  53. const { IP, host, baseName, username, password } = req.body.database;
  54. const filterData = req.body.filterData;
  55. // 数据库连接配置
  56. const pool = mysql.createPool({
  57. host: IP,
  58. user: username,
  59. password: password,
  60. database: baseName,
  61. port: host,
  62. });
  63. // 获取数据库连接;
  64. const connection = await pool.getConnection();
  65. // 用于存储最终查询结果
  66. const resultData = {};
  67. // 遍历 filterData
  68. for (const item of filterData) {
  69. const parentNode = item.parentNode; // 数据表名
  70. const checkChildNode = item.checkChildNode; // 要查询的字段
  71. // 检查表是否存在
  72. const [tables] = await connection.query("SHOW TABLES");
  73. const tableNames = tables.map((row) => Object.values(row)[0]);
  74. if (!tableNames.includes(parentNode)) {
  75. continue; // 如果表不存在,跳过
  76. }
  77. // 获取表字段信息
  78. const [columns] = await connection.query(
  79. `SHOW COLUMNS FROM ${parentNode}`
  80. );
  81. const tableFields = columns.map((column) => column.Field);
  82. // 过滤存在的字段
  83. const validFields = checkChildNode.filter((field) =>
  84. tableFields.includes(field)
  85. );
  86. if (validFields.length === 0) {
  87. continue; // 如果没有匹配的字段,跳过
  88. }
  89. // 动态查询字段的数据
  90. const sqlQuery = `SELECT ${validFields.join(
  91. ", "
  92. )} FROM ${parentNode} LIMIT 100`; // 限制查询 100 条数据
  93. const [rows] = await connection.query(sqlQuery);
  94. // 保存结果
  95. resultData[parentNode] = rows;
  96. }
  97. // 释放连接
  98. connection.release();
  99. // 返回最终数据
  100. res.json(resultData);
  101. } catch (error) {
  102. console.error(error);
  103. res.status(500).send("Error fetching data from database");
  104. }
  105. });
  106. app.listen(PORT, () => {
  107. console.log(`Server is running on http://localhost:${PORT}`);
  108. });