数据库查询所有表和字段

查询所有表和字段

介绍

有时候可能要展示所有的表以及所有的字段，下面介绍oracle和mysql的方法以及代码

oracle

sql

查询所有表
select Table_NAME from all_tables where owner = '表空间'
查询字段
SELECT * FROM user_tab_columns where table_name = upper('表名');

代码

    @Test
    public void test1() throws IOException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
        dataSource.setUrl("jdbc:oracle:thin:@127.0.0.1/orcl");
        dataSource.setUsername("admin");
        dataSource.setPassword("123456");
        dataSource.setMaxActive(1);
        dataSource.setInitialSize(1);
        dataSource.setMinIdle(1);
        dataSource.setMaxWait(10000l);
        dataSource.setConnectionErrorRetryAttempts(0);
        dataSource.setBreakAfterAcquireFailure(true);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select Table_NAME from all_tables where owner = 'admin'");
        Map<String, List<String>> map = new HashMap<>();
        list.forEach(e -> {
            String tableName = String.valueOf(e.get("TABLE_NAME"));
            List<String> _list = search(jdbcTemplate, tableName);
            if(!_list.isEmpty()){
                map.put(tableName,_list);
            }

        });
        StringBuilder builder = new StringBuilder();
        map.forEach((k,v)->{
            builder.append("表：\n").append(k).append("\n")
                    .append("字段:\n").append(v).append("\n");
        });
        FileOutputStream out = new FileOutputStream("表和字段.txt");
        out.write(builder.toString().getBytes(StandardCharsets.UTF_8));
        out.flush();
        out.close();


    }

    public List<String> search(JdbcTemplate jdbcTemplate, String tableName) {

        StringBuilder builder = new StringBuilder()
                .append("SELECT column_name FROM user_tab_columns where table_name = upper('")
                .append(tableName).append("')");
        List<Map<String, Object>> list = jdbcTemplate.queryForList(builder.toString());
        return list.stream()
                .map(e -> String.valueOf(e.get("COLUMN_NAME")))
                .collect(Collectors.toList());

    }

mysql

sql

-- 查询所有表
SELECT 
    * 
FROM
    information_schema.TABLES 
where TABLE_NAME = '表名';



-- 查询所有字段
SELECT
    *
FROM
    information_schema.COLUMNS
WHERE
    table_schema = '库名'
    AND table_name = '表名'
	
	

代码

    @Test
    public void test2() throws IOException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        dataSource.setMaxActive(1);
        dataSource.setInitialSize(1);
        dataSource.setMinIdle(1);
        dataSource.setMaxWait(10000l);
        dataSource.setConnectionErrorRetryAttempts(0);
        dataSource.setBreakAfterAcquireFailure(true);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA = '库名'");
        Map<String, List<String>> map = new HashMap<>();
        list.forEach(e -> {
            String tableName = String.valueOf(e.get("TABLE_NAME"));
            List<String> _list = searchMysql(jdbcTemplate, tableName);
            if (!_list.isEmpty()) {
                map.put(tableName, _list);
            }

        });
        StringBuilder builder = new StringBuilder();
        map.forEach((k, v) -> {
            builder.append("表：\n").append(k).append("\n")
                    .append("字段:\n").append(v).append("\n");
        });
        FileOutputStream out = new FileOutputStream("表和字段.txt");
        out.write(builder.toString().getBytes(StandardCharsets.UTF_8));
        out.flush();
        out.close();
    }

    public List<String> searchMysql(JdbcTemplate jdbcTemplate, String tableName) {

        StringBuilder builder = new StringBuilder()
                .append("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_schema = '库名' AND table_name = '")
                .append(tableName).append("'");
        List<Map<String, Object>> list = jdbcTemplate.queryForList(builder.toString());
        return list.stream()
                .map(e -> String.valueOf(e.get("COLUMN_NAME")))
                .collect(Collectors.toList());

    }