java获取Mysql 数据库表字段

添加Mysql的maven依赖

     <!-- 选择适合自己的版本 -->     
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>

 

工具类

import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class DbConfig {

    /**
     * 获取连接
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306", "root", "mysql@123");
        return conn;
    }


    /**
     * 获取所有的数据库
     * @param connection
     * @return
     * @throws Exception
     */
    public static List<String> getDataBases(Connection connection) throws Exception {

        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet catalogs = metaData.getCatalogs();
        ArrayList<String> dbs = new ArrayList<>();
        while (catalogs.next()) {
            String db = catalogs.getString(".TABLE_CAT");
            dbs.add(db);
        }

        return dbs;
    }

    /**
     * 获取当前数据库的所有表
     * @param connection 数据库连接对象
     * @param dataBase 数据库名称
     * @return
     * @throws SQLException
     */
    public static List<String> getTables(Connection connection, String dataBase) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
//        最后一个参数TABLE 表示用户表 见 DatabaseMetaData.getTableTypes()方法
        ResultSet resultSet = metaData.getTables(dataBase, null, null, new String[]{"TABLE"});
        ArrayList<String> tables = new ArrayList<>();
        while (resultSet.next()) {
            String table = resultSet.getString("TABLE_NAME");
            tables.add(table);
        }
        return tables;
    }

    /**
     * 获取数据库表的所有字段
     * @param connection 数据库连接对象
     * @param dataBase 数据库名
     * @param tableName 表名
     * @return
     * @throws SQLException
     * @throws IOException
     */
    public static List<String> getTableField(Connection connection, String dataBase, String tableName) throws SQLException, IOException {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet resultSet = metaData.getColumns(dataBase, null, tableName, null);
        ArrayList<String> columns = new ArrayList<>();
        while (resultSet.next()) {
//            数据库名
            String db_name = resultSet.getString(".TABLE_CAT");
//            表名
            String table_name = resultSet.getString(".TABLE_NAME");
//            获取字段名
            String field = resultSet.getString(".COLUMN_NAME");
//            获取字段类型
            String fieldType = resultSet.getString(".TYPE_NAME");
            String fieldLength = resultSet.getString(".COLUMN_SIZE");
            String fieldDESC = resultSet.getString(".REMARKS");
            String info = String.format("[%s->%s->%s->%s->%s->%s]", db_name, table_name, field, fieldType, fieldLength, fieldDESC);
            System.out.println(info);
            columns.add(field);
        }
        return columns;
    }
}

测试类

 

import java.sql.Connection;
import java.util.List;

public class App {

    public static void main(String[] args) throws Exception {
        Connection connection = DbConfig.getConnection();
        System.out.println(connection);
        List<String> dataBases = DbConfig.getDataBases(connection);
        System.out.println("数据库个数：" + dataBases.size());
        for (String dataBase : dataBases) {
            List<String> tables = DbConfig.getTables(connection, dataBase);
            String log = "数据库[%s]中共有[%s]张表";
            System.out.println(String.format(log, dataBase, tables.size()));
            for (String table : tables) {
                List<String> tableField = DbConfig.getTableField(connection, dataBase, table);
                String log2 = "数据库[%s]->[%s]表中共有[%s]个字段";
                System.out.println(String.format(log2, dataBase, table, tableField.size()));
            }
        }
    }
}