java获取数据库的所有表结构和索引等元数据

表的POJO：

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.ArrayList;
import java.util.List;

/**
 * @author: jinyang
 * @date: 2020-06-28 12:33
 **/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Table {

    String tableName;

    String tableComment;

    List<Column> columnList = new ArrayList<>();

}

表中列的POJO：

import lombok.Data;

/**
 * @author: jinyang
 * @date: 2020-06-28 14:40
 **/
@Data
public class Column {

    String name;

    String type;

    String length;

    String decimalDigits;

    boolean notNull;

    boolean primary;

    String comment;

}

索引的POJO：

import lombok.Data;
import org.apache.commons.lang3.StringUtils;

/**
 * @author: jinyang
 * @date: 2020-06-28 15:22
 **/
@Data
public class Index {

    String tableName;

    String indexName;

    boolean unique;

    String params;

    public void addParams(String param) {
        if (StringUtils.isBlank(params)) {
            params = param;
        } else {
            params = params + "," + param;
        }
    }
}

一个抽象接口，实现了2种获取数据库schema元数据的方式，一种直接连库导入，另一种通过sql脚本批量导入（大家按需取用）

import java.util.ArrayList;
import java.util.List;

/**
 * @author: jinyang assembled
 * @date: 2020-06-29 20:24
 **/
public interface AssembledDatabaseMetadata {

    List<Table> tables = new ArrayList<>();

    List<Index> indices = new ArrayList<>();

    default List<Table> getTables() {
        return tables;
    }

    default List<Index> getIndices() {
        return indices;
    }

}

第一种方式，连数据库直接获取所有表结构和索引信息

import lombok.SneakyThrows;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.stream.Collectors;

/**
 * @author: jinyang
 * @date: 2020-06-28 19:51
 **/
public class AssembledFromDatabase implements AssembledDatabaseMetadata {

    private String driver = "com.mysql.cj.jdbc.Driver";

    @SneakyThrows
    public AssembledFromDatabase(String url, String user, String pwd) {
        Class.forName(driver);
        Properties props = new Properties();
        props.setProperty("user", user);
        props.setProperty("password", pwd);
        props.setProperty("remarks", "true");
        props.setProperty("useInformationSchema", "true");
        Connection connection = DriverManager.getConnection(url, props);
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet tableRs = metaData.getTables(connection.getCatalog(), connection.getCatalog(), "%", new String[]{"TABLE"});
        while (tableRs.next()) {
            String tableName = tableRs.getString("TABLE_NAME");

            List<Column> columns = new ArrayList<>();
            Table table = new Table(tableName, tableRs.getString("REMARKS"), columns);
            String primaryColumn = null;
            ResultSet idxRs = metaData.getIndexInfo(connection.getCatalog(), connection.getCatalog(), tableName, false, false);
            while (idxRs.next()) {
                String indexName = idxRs.getString("INDEX_NAME");
                if ("PRIMARY".equals(indexName)) {
                    primaryColumn = idxRs.getString("COLUMN_NAME");
                    continue;
                }
                List<Index> exist = indices.stream().filter(o -> o.getTableName().equals(tableName) && o.getIndexName().equals(indexName)).collect(Collectors.toList());
                Index index;
                if (exist != null && exist.size() > 0) {
                    index = exist.get(0);
                    index.setParams(index.getParams() + "," + idxRs.getString("COLUMN_NAME"));
                } else {
                    index = new Index();
                    index.setTableName(tableName);
                    index.setIndexName(indexName);
                    index.setUnique(!idxRs.getBoolean("NON_UNIQUE"));
                    index.setParams(idxRs.getString("COLUMN_NAME"));
                    indices.add(index);
                }
            }
            ResultSet columnRs = metaData.getColumns(connection.getCatalog(), connection.getCatalog(), tableName, "%");
            while (columnRs.next()) {
                Column column = new Column();
                column.setName(columnRs.getString("COLUMN_NAME"));
                column.setType(columnRs.getString("TYPE_NAME"));
                column.setLength(columnRs.getString("COLUMN_SIZE"));
                column.setDecimalDigits(columnRs.getString("DECIMAL_DIGITS"));
                column.setNotNull(!columnRs.getBoolean("NULLABLE"));
                column.setPrimary(column.getName().equals(primaryColumn));
                column.setComment(columnRs.getString("REMARKS"));
                columns.add(column);
            }
            tables.add(table);
        }
    }
}

第二种方式，通过SQL脚本获取所有表结构和索引信息（第二种方式还是有瑕疵，适合datagrip导出的sql脚本，就当给大家提供思路了，主要还是推荐第一种，省力方便）

import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLName;
import com.alibaba.druid.sql.ast.SQLObject;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLCharExpr;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLIntegerExpr;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import jinyang.project.work.util.FileUtil;
import org.springframework.util.CollectionUtils;

import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author: jinyang
 * @date: 2020-06-29 20:23
 **/
public class AssembledFromSqlFile implements AssembledDatabaseMetadata {

    static Pattern idxPattern = Pattern.compile("CREATE INDEX.*?;");

    static Pattern uniqueIdxPattern = Pattern.compile("CREATE UNIQUE INDEX.*?;");

    public AssembledFromSqlFile(String sqlFiles) {
        List<String> sqls = FileUtil.readFiles(sqlFiles);

        // 解析SQL
        for (String sql : sqls) {
            assembleTable(tables, sql);
            assembleIndex(indices, idxPattern.matcher(sql));
            assembleIndex(indices, uniqueIdxPattern.matcher(sql));
        }
    }

    private static void assembleTable(List<Table> tables, String sql) {
        Table table = new Table();
        SQLStatementParser parser = new MySqlStatementParser(sql);
        SQLStatement sqlStatement = parser.parseStatement();
        List<SQLObject> sqlObjects = sqlStatement.getChildren();

        for (SQLObject sqlObject : sqlObjects) {

            if (sqlObject instanceof SQLExprTableSource) {
                SQLName sqlName = ((SQLExprTableSource) sqlObject).getName();
                table.setTableName(sqlName.getSimpleName());
            } else if (sqlObject instanceof SQLColumnDefinition) {
                SQLColumnDefinition columnDefinition = ((SQLColumnDefinition) sqlObject);
                Column column = new Column();
                column.setName(columnDefinition.getNameAsString());
                column.setType(columnDefinition.getDataType().getName());
                List<SQLExpr> arguments = columnDefinition.getDataType().getArguments();
                if (!CollectionUtils.isEmpty(arguments)) {
                    column.setLength(((SQLIntegerExpr) arguments.get(0)).getNumber().toString());
                }
                column.setNotNull(columnDefinition.getConstraints().stream().anyMatch(o->o instanceof SQLNotNullConstraint));
                column.setPrimary(columnDefinition.getConstraints().stream().anyMatch(o->o instanceof SQLColumnPrimaryKey));
                if (columnDefinition.getComment() != null) {
                    column.setComment((String) ((SQLCharExpr) columnDefinition.getComment()).getValue());
                }
                table.getColumnList().add(column);
            }
        }
        tables.add(table);
    }

    private static void assembleIndex(List<Index> indices, Matcher matcher) {
        while (matcher.find()) {
            Index index = new Index();
            String indexSql = matcher.group();
            SQLStatementParser indexSqlParser = new MySqlStatementParser(indexSql);
            SQLStatement indexSqlStatement = indexSqlParser.parseStatement();
            List<SQLObject> indexSqlObjects = indexSqlStatement.getChildren();
            for (SQLObject indexSqlObject : indexSqlObjects) {
                if (indexSqlObject instanceof SQLIdentifierExpr) {
                    index.setIndexName(((SQLIdentifierExpr) indexSqlObject).getName());
                } else if (indexSqlObject instanceof SQLExprTableSource) {
                    index.setTableName(((SQLExprTableSource) indexSqlObject).getName().getSimpleName());
                } else if (indexSqlObject instanceof SQLSelectOrderByItem) {
                    index.addParams(((SQLIdentifierExpr) ((SQLSelectOrderByItem) indexSqlObject).getExpr()).getName());
                }
            }
            if (matcher.pattern() == uniqueIdxPattern) {
                index.setUnique(true);
            }
            indices.add(index);
        }
    }
}