java 导出数据库表结构信息、列字段、约束等

👑 博主简介：知名开发工程师
👣 出没地点：北京
💊 2023年目标：成为一个大佬
———————————————————————————————————————————
版权声明：本文为原创文章，如需转载须注明出处，喜欢可收藏！

一. 前言

由于编写项目相关文档，需要导出开发库所有的表结构信息，因此写了个程序，首先导出json格式，然后将json转成excel。

@RestController
public class UserController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @GetMapping("/hello")
    public String hello() {

        //查到所有表，列的信息
        List<Map<String, Object>> list1 = jdbcTemplate.queryForList("SELECT\n" +
                "\tt1.table_name,t1.column_name,t1.column_type,t1.column_comment\n" +
                "FROM\n" +
                "\tinformation_schema.`COLUMNS` t1\n" +
                "WHERE\n" +
                "\tt1.table_schema = 'dtmg' \n" +
                "\tAND t1.table_name LIKE 'di%' \n" +
                "\t\n" +
                "union all \n" +
                "\n" +
                "SELECT\n" +
                "\tt1.table_name,t1.column_name,t1.column_type,t1.column_comment\n" +
                "FROM\n" +
                "\tinformation_schema.`COLUMNS` t1\n" +
                "WHERE\n" +
                "\tt1.table_schema = 'dtmg' \n" +
                "\tAND t1.table_name LIKE 'sys%'");

        System.out.println(list1.size());


        //查询所有的有约束的表，列，约束名
        List<Map<String, Object>> list2 = jdbcTemplate.queryForList("SELECT\n" +
                "\ttable_name,\n" +
                "\tcolumn_name,\n" +
                "\tconstraint_name \n" +
                "FROM\n" +
                "\tinformation_schema.KEY_COLUMN_USAGE \n" +
                "WHERE\n" +
                "\ttable_schema = 'dtmg' \n" +
                "\tAND table_name LIKE 'di%'\n" +
                "union all \n" +
                "SELECT\n" +
                "\ttable_name,\n" +
                "\tcolumn_name,\n" +
                "\tconstraint_name \n" +
                "FROM\n" +
                "\tinformation_schema.KEY_COLUMN_USAGE \n" +
                "WHERE\n" +
                "\ttable_schema = 'dtmg' \n" +
                "\tAND table_name LIKE 'sys%'");

        System.out.println(list2.size());

        for (Map<String, Object> item : list1) {
            for (Map<String, Object> son : list2) {
                if ((item.get("table_name")+""+item.get("column_name")).equals(son.get("table_name")+""+son.get("column_name"))){
                    item.put("constraint_name",son.get("constraint_name"));
                }
            }
        }


        List<Map<String, Object>> list3 = jdbcTemplate.queryForList("select table_name,table_comment from information_schema.`TABLES`\n" +
                "where \n" +
                "table_schema = 'dtmg' \n" +
                "\tAND table_name LIKE 'di%' \n" +
                "\tOR table_name LIKE 'sys%' ");

        for (Map<String, Object> x : list1) {
            for (Map<String, Object> y : list3) {
                if (x.get("table_name").equals(y.get("table_name"))){
                    x.put("table_comment",y.get("table_comment"));
                }
            }
        }

        String str = JSON.toJSONString(list1);
        System.out.println();

        this.write(str);


        return "ok";
    }

    public void write(String str){
        BufferedWriter writer = null;
        File file = new File("E://aa.json");
        //如果文件不存在则新建
        if (!file.exists()){
            try {
                file.createNewFile();
            }catch (IOException e){
               e.printStackTrace();
            }
        }
        //如果多次执行同一个流程，会导致json文件内容不断追加，在写入之前清空文件
        try {
            writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file,false),"UTF-8"));
            writer.write("");
            writer.write(str);
        }catch (IOException e){
            e.printStackTrace();
        }finally {
            try{
                if (writer != null){
                    writer.close();
                }
            }catch (IOException e){
                e.printStackTrace();
            }
        }
    }

}

二. 结果

运行程序，生成 json 文件

然后借助网上一个在线工具，json 转 excel : 在线工具

转成excel后如图：