如何获取数据源下所有的表信息及表下所有的字段信息

数据源之DataSource遇见MetaData

最近有个需求,需要获取一个库下所有的表或者根据条件模糊匹配表,同时还需要获取某张表下所有的字段信息,于是乎研究了数据源DataSource对象,发现了一些小秘密,在这里给大家分享一下,如有不对的地方欢迎评论指正或者关于DataSource的其他小秘密也欢迎在评论区留言,如在开发中有用的我将同步采纳并更新博客,谢谢!

环境准备

maven依赖如下:

	<dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.12</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
    </dependencies>

1、从DataBaseMetaData中获取所有表及表的所有字段信息

	public static void main(String[] args) {
        try (Connection connection = getDataSource().getConnection()) {
            List<String> allTables = getAllTables(connection, null);
            List<String> primaryKeys = getPrimaryKeys(connection, "tb_user_info");
            List<Map<String, String>> allColumns = getAllColumns(connection, "tb_user_info");

            System.out.println("all tables:");
            allTables.stream().forEach(table -> System.out.println(table + "\n"));
            System.out.println("table's primaryKeys:");
            primaryKeys.stream().forEach(primaryKey -> System.out.println(primaryKey + "\n"));
            System.out.println("table's all columns:");
            allColumns.stream().forEach(map -> {
                map.forEach((k, v) -> {
                    System.out.println(k + ":" + v + "\t");
                });
                System.out.println("\n");
            });
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

	public static DataSource getDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        return dataSource;
    }

输出结果:

all tables:
tb_user_info

table's primaryKeys:
auto_id

login_name

user_id

table's all columns:
isAutoIncrement:YES	
isNullAble:NO	
dataType:4	
columnSize:10	
nullAble:0	
tableCat:test	
typeName:INT	
columnDef:null	
ordinalPosition:1	
remarks:	
tableName:tb_user_info	
columnName:auto_id	

isAutoIncrement:NO	
isNullAble:NO	
dataType:4	
columnSize:10	
nullAble:0	
tableCat:test	
typeName:INT	
columnDef:null	
ordinalPosition:2	
remarks:用户id	
tableName:tb_user_info	
columnName:user_id	
...

1.1、如何获取一个库下的所有表

获取某个库下的所有表:ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[]) throws SQLException;

	/**
     * get all tables from DataSource - Connection - DatabaseMetaData
     * use %tableNamePattern% to get fuzzy matching all tables
     * eg: ResultSet tables2 = metaData.getTables(null,null,"%tableNamePattern%",new String[]{"TABLE"});
     *
     * @param connection
     * @param tableNamePattern
     * @return
     * @throws SQLException
     */
    public static List<String> getAllTables(Connection connection, String tableNamePattern) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet tables = metaData.getTables(null, null, tableNamePattern, new String[]{"TABLE"});
        List<String> tableList = new ArrayList<>();
        while (tables.next()) {
            String tableName = tables.getString(3);
            tableList.add(tableName);
        }
        return tableList;
    }

1.2、如何获取一个表中的所有主键

获取某张表下的所有主键:ResultSet getPrimaryKeys(String catalog, String schema,String table) throws SQLException;

	/**
     * get all PrimaryKeys from DataSource - Connection - DatabaseMetaData
     *
     * @param connection
     * @param tableNamePattern
     * @return
     * @throws SQLException
     */
    public static List<String> getPrimaryKeys(Connection connection, String tableNamePattern) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet primaryKeys = metaData.getPrimaryKeys(null, null, tableNamePattern);
        List<String> primaryKeyList = new ArrayList<>();
        while (primaryKeys.next()) {
            String columnName = primaryKeys.getString("COLUMN_NAME");
            primaryKeyList.add(columnName);
        }
        return primaryKeyList;
    }

1.3、如何获取一个表中所有的字段及字段定义信息

获取某张表下的所有column信息:ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)throws SQLException;

    /**
     * get all columns from table by DataSource - Connection - DatabaseMetaData
     *
     * @param connection
     * @param tableNamePattern
     * @return
     * @throws SQLException
     */
    public static List<Map<String, String>> getAllColumns(Connection connection, String tableNamePattern) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet columns = metaData.getColumns(null, null, tableNamePattern, null);
        List<Map<String, String>> columnList = new ArrayList<>();
        while (columns.next()) {
            // 可自定义column对象用于存储列信息
            Map<String, String> columnMap = new HashMap<>();
            String tableCat = columns.getString("TABLE_CAT"); // 库名 schema
            columnMap.put("tableCat", tableCat);
            String tableName = columns.getString("TABLE_NAME"); // 表名
            columnMap.put("tableName", tableName);
            String columnName = columns.getString("COLUMN_NAME"); // 列名
            columnMap.put("columnName", columnName);
            String dataType = columns.getString("DATA_TYPE"); // 数据类型
            columnMap.put("dataType", dataType);
            String typeName = columns.getString("TYPE_NAME"); // 列的数据类型:INT、VARCHAR
            columnMap.put("typeName", typeName);
            String columnSize = columns.getString("COLUMN_SIZE"); // 列的数据类型的大小: 1 10 32
            columnMap.put("columnSize", columnSize);
            String nullAble = columns.getString("NULLABLE"); // 允许为空:0 不允许 1 允许
            columnMap.put("nullAble", nullAble);
            String remarks = columns.getString("REMARKS"); // 备注
            columnMap.put("remarks", remarks);
            String columnDef = columns.getString("COLUMN_DEF"); // 列的默认值
            columnMap.put("columnDef", columnDef);
            String ordinalPosition = columns.getString("ORDINAL_POSITION"); // 表字段创建的顺序
            columnMap.put("ordinalPosition", ordinalPosition);
            String isNullAble = columns.getString("IS_NULLABLE"); // 表字段是否允许为空:"YES":允许为null "NO":不允许为null
            columnMap.put("isNullAble", isNullAble);
            String isAutoIncrement = columns.getString("IS_AUTOINCREMENT"); // 是否是自增列 "YES":是 "NO":不是
            columnMap.put("isAutoIncrement", isAutoIncrement);
            columnList.add(columnMap);
        }
        return columnList;
    }
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页