获取数据库下的所有表信息,表注释,字段信息,字段注释

public class TableInfoVo {
    private String tableName;//表名称
    private String tableType;//表或视图
    private Integer tableCount;//表格的数据量
    private String tableRemarks;//表注释
    private List<ColumnVo> columnVos;//表所包含的字段信息
}

public class ColumnVo {
    private String columnName;//字段名称
    private String columnType;//字段类型
    private Integer columnSize;//字段大小
    private Boolean isNullable;//是否可为空
    private Boolean isPrimary;//是否主键
    private String remarks;//字段注释
}

 /**
     * Description : 获取数据库下的表信息(包含字段信息)
     * @param type    数据库类型  如MySQL oracle SqlServer
     * @param dbId
     * @date : 2018-10-12   10:39
     * @author : PZG
     */
    public  List<TableInfoVo> getTableInfo(Connection connection,String type) throws Exception {
        if (connection== null) {
            DatabaseMetaData meta = connection.getMetaData();
           //表格和视图一并查询  若只查询表格meta.getTables(null, null, null, new String[]{"TABLE"});  
            ResultSet rs = meta.getTables(null, null, null, new String[]{"TABLE", "VIEW"});
            List<TableInfoVo> list = new ArrayList<>();
            while (rs.next()) {
                TableInfoVo tableInfoVo = new TableInfoVo();
                //获取数据库名称
//                String databaseName = rs.getString(1);
                //获取表名称(包含视图)
                String tableName = rs.getString(3);
                tableInfoVo.setTableName(tableName);
                //调用下方的获取表注释的接口
                Map<String, String> tableRemarks = getTableRemarks(connection, type, tableName);
                String tableCount = tableRemarks.get("tableCount");
                String tableComment = tableRemarks.get("tableComment");
                //表的记录数
                Integer count = tableCount == null ? 0 : Integer.valueOf(tableCount);
                tableInfoVo.setTableCount(count);
                //表的注释
                tableInfoVo.setTableRemarks(tableComment);
                //获取表类型
                String tableType = rs.getString(4);
                tableInfoVo.setTableType(tableType);
                ResultSet resultSet = null;
                ResultSet primaryKeys = null;
                String keyColName = null;
                if ("ORACLE".equals(type.toUpperCase())) {
                    //其他数据库不需要这个方法的，直接传null，这个是oracle这么用
                    resultSet = meta.getColumns(null, meta.getUserName().toUpperCase(), tableName.toUpperCase(), "%");
                    //获取主键
                    primaryKeys = meta.getPrimaryKeys(null, null, tableName.toUpperCase());
                } else {
                    resultSet = meta.getColumns(null, "%", tableName, "%");
                    primaryKeys = meta.getPrimaryKeys(null, null, tableName);
                }
                while (primaryKeys.next()) {
                    // 主键字段
                    keyColName = primaryKeys.getString(4);
                }
                primaryKeys.close();
                List<ColumnVo> columnVos = new ArrayList<>();
                //获取单个表下的字段信息,包含字段注释
                while (resultSet.next()) {
                    ColumnVo columnVo = new ColumnVo();
                    String columnName = resultSet.getString("COLUMN_NAME");
                    columnVo.setColumnName(columnName);
                    if (keyColName != null && keyColName.equals(columnName)) {
                        columnVo.setPrimary(true);
                    } else {
                        columnVo.setPrimary(false);
                    }
                    String remarks = resultSet.getString("REMARKS");
                    columnVo.setRemarks(remarks);
                    String typeName = resultSet.getString("TYPE_NAME");
                    columnVo.setColumnType(typeName);
                    String isNullable = resultSet.getString("IS_NULLABLE");
                    Boolean nullable = "YES".equals(isNullable) ? true : false;
                    columnVo.setNullable(nullable);
                    String columnSize = resultSet.getString("COLUMN_SIZE");
                    Integer size = columnSize == null ? 0 : Integer.valueOf(columnSize);
                    columnVo.setColumnSize(size);
                    columnVos.add(columnVo);
                }
                tableInfoVo.setColumnVos(columnVos);
                resultSet.close();
                list.add(tableInfoVo);
            }
            rs.close();
            connection.close();
            return list;
        } else {
            throw new CustomException("获取表信息参数空值异常");
        }
    }

/**
 * Description : 获取表的注释
 *
 * @param connection
 * @param type    数据库类型  如MySQL oracle SqlServer
 * @param tableName  数据表名称
 * @return : java.lang.String
 * @date : 2018-10-17   14:20
 * @author : PZG
 */
Map<String, String> getTableRemarks(Connection connection, String type, String tableName) throws Exception {
    if (type != null && tableName != null) {
        Statement statement = connection.createStatement();
        Map<String, String> map = new HashMap<>();
        String sql = null;
        if ("MYSQL".equals(type.toUpperCase())) {
            sql = "Select TABLE_COMMENT COMMENT from INFORMATION_SCHEMA.TABLES Where table_name = '" + tableName + "'";
        }
        if ("ORACLE".equals(type.toUpperCase())) {
            sql = "select comments COMMENT from user_tab_comments Where table_name='" + tableName + "'";
        }
        if ("SQLSERVER".equals(type.toUpperCase())) {
            sql = "SELECT COMMENT = case when a.colorder=1 then isnull(f.value,'') else '' end FROM syscolumns a " +
                    "left join systypes b on a.xusertype=b.xusertype " +
                    "inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties' " +
                    "left join syscomments e on a.cdefault=e.id " +
                    "left join sys.extended_properties   g on a.id=G.major_id and a.colid=g.minor_id " +
                    "left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 " +
                    "where d.name='" + tableName + "'";
        }
        ResultSet resultSet = statement.executeQuery(" SELECT COUNT(1) COUNT FROM " + tableName);
        while (resultSet.next()) {
            String tableCount = resultSet.getString("COUNT");
            map.put("tableCount", tableCount);
        }
        ResultSet resultSet1 = statement.executeQuery(sql);
        while (resultSet1.next()) {
            String tableComment = resultSet1.getString("COMMENT");
            map.put("tableComment", tableComment);
        }
        resultSet1.close();
        resultSet.close();
        return map;
    }
    return null;
}