通过内容反向查找MySQL数据库的列名和表名

通过内容查找在哪个表哪个字段-MySQL

万不得已和太大的数据库 建议不要使用会遍历全部的表和全部的字段耗时可能会比较久

package com.ygsoft.jt.mapp.sample.impl.cloneObject;

import java.sql.*;
import java.util.Arrays;
import java.util.List;
/**
 * @author HH287 <br>
 * @version 1.0.0 2023/11/20 16:28 <br>
 * @since JDK 1.8.0
 * 通过内容模糊匹配指定数据库的全部表名和字段名-遍历的方式
 */
public class SearchDatabase {
    public static void main(String[] args) {
        // 要查找的数据库
        String database = "ecp810_9999domain";
        // 要查找的内容
        String search = "10.51.150.172";


        String url = "jdbc:mysql://10.58.21.71:3306/" + database;
        String user = "root";
        String password = "1a2b.Mysql";
        String tableName = null;
        try {
            Connection conn = DriverManager.getConnection(url, user, password);
            Statement stmt = conn.createStatement();
            ResultSet tables = stmt.executeQuery("SHOW TABLES");

            // 不用查找的即排除的表
            List<String> excludeTables = Arrays.asList("bm_ratio_type");
            // 不用查找的即排除的列
            List<String> excludeColumns = Arrays.asList("DESCRIBE", "CONDITION", "MAXVALUE", "RANK", "SEPARATOR", "BEFORE");

            while (tables.next()) {
                tableName = tables.getString(1);

                if (!excludeTables.contains(tableName)) {
                    Statement stmt2 = conn.createStatement();
                    ResultSet columns = stmt2.executeQuery("SHOW COLUMNS FROM `" + tableName + "`");

                    while (columns.next()) {
                        String columnName = columns.getString("Field");

                        if (!excludeColumns.contains(columnName)) {
                            Statement stmt3 = conn.createStatement();
                            ResultSet rs = stmt3.executeQuery("SELECT `" + columnName + "` FROM `" + tableName + "` WHERE `" + columnName + "` LIKE '%" + search + "%'");

                            if (rs.next()) {
                                System.out.println("sql-->" + "SELECT " + columnName + " FROM `" + tableName + "` WHERE `" + columnName + "` LIKE '%" + search + "%';");
                                System.out.println("Table: " + tableName);
                                System.out.println("Column: " + columnName);
                            }

                            rs.close();
                            stmt3.close();
                        }
                    }

                    columns.close();
                    stmt2.close();
                }
            }

            tables.close();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            System.out.println(tableName);
            e.printStackTrace();
        }
    }
}