123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115 |
- import express from "express";
- import mysql from "mysql2/promise";
- import timeout from "connect-timeout";
- import bodyParser from "body-parser";
- const app = express();
- const PORT = 3000;
- // 设置请求超时,单位为毫秒(比如:设置 30 秒超时)
- app.use(timeout("30s"));
- app.use(bodyParser.json()); // 在请求处理之前,先解析JSON
- // 处理超时请求
- app.use((req, res, next) => {
- if (req.timedout) {
- return res.status(408).send("Request Timeout");
- }
- next();
- });
- // 获取表和字段信息
- app.get("/tables", async (req, res) => {
- let pool;
- try {
- const { IP, host, baseName, username, password } = req.query;
- // 数据库连接配置
- pool = mysql.createPool({
- host: IP,
- user: username,
- password: password,
- database: baseName,
- port: host,
- });
- const connection = await pool.getConnection();
- const [tables] = await connection.query("SHOW TABLES");
- const tableFieldMap = {};
- for (const table of tables) {
- const tableName = Object.values(table)[0];
- const [fields] = await connection.query(`DESCRIBE ${tableName}`);
- tableFieldMap[tableName] = fields.map((field) => field.Field);
- }
- connection.release();
- res.json(tableFieldMap);
- } catch (error) {
- // console.error("Error fetching database structure:", error);
- res.status(500).send("请检查填写的数据库数据是否有误" + error);
- } finally {
- // 确保关闭数据库连接池
- if (pool) {
- await pool.end();
- }
- }
- });
- app.post("/filterTablesData", async (req, res) => {
- try {
- console.log(req.params, req.body, "req.query.filterData");
- const { IP, host, baseName, username, password } = req.body.database;
- const filterData = req.body.filterData;
- // 数据库连接配置
- const pool = mysql.createPool({
- host: IP,
- user: username,
- password: password,
- database: baseName,
- port: host,
- });
- // 获取数据库连接;
- const connection = await pool.getConnection();
- // 用于存储最终查询结果
- const resultData = {};
- // 遍历 filterData
- for (const item of filterData) {
- const parentNode = item.parentNode; // 数据表名
- const checkChildNode = item.checkChildNode; // 要查询的字段
- // 检查表是否存在
- const [tables] = await connection.query("SHOW TABLES");
- const tableNames = tables.map((row) => Object.values(row)[0]);
- if (!tableNames.includes(parentNode)) {
- continue; // 如果表不存在,跳过
- }
- // 获取表字段信息
- const [columns] = await connection.query(
- `SHOW COLUMNS FROM ${parentNode}`
- );
- const tableFields = columns.map((column) => column.Field);
- // 过滤存在的字段
- const validFields = checkChildNode.filter((field) =>
- tableFields.includes(field)
- );
- if (validFields.length === 0) {
- continue; // 如果没有匹配的字段,跳过
- }
- // 动态查询字段的数据
- const sqlQuery = `SELECT ${validFields.join(
- ", "
- )} FROM ${parentNode} LIMIT 100`; // 限制查询 100 条数据
- const [rows] = await connection.query(sqlQuery);
- // 保存结果
- resultData[parentNode] = rows;
- }
- // 释放连接
- connection.release();
- // 返回最终数据
- res.json(resultData);
- } catch (error) {
- console.error(error);
- res.status(500).send("Error fetching data from database");
- }
- });
- app.listen(PORT, () => {
- console.log(`Server is running on http://localhost:${PORT}`);
- });
|