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); } } }