如何较方便给上百张数据库表添加表字段

前言

年前和业务部门的研发小伙伴聊天,他说由于之前表设计考虑不周全,导致业务表缺少了一些字段,他老大就把这个加表字段的任务给他,咋一听挺简单的,不就加些字段,但小伙伴烦恼的地方在于需要加这些字段的表大概有100多张,如果单靠手动添加,那效率太低了。于是他问我有没有啥方法,能比较方便的实现这个需求,今天就来水一下这个话题

需求分析

小伙伴的诉求在于方便的实现,那怎么实现方便这个诉求。答案的本质就是这个实现不要让他自己做,让别人或者其他东西实现他诉求

方案思路

方案一: 把这个需求安排给其他人做,哈哈

方案二:写存储过程或者函数

方案三:通过写sql脚本执行

本文重点讲解方案三写sql脚本执行

如何实现这个sql脚本

实现这个sql脚本的方式有很多种,本文就讲下利用java程序生成这个sql脚本。生成这个sql脚本的关键在于DatabaseMetaData这个类,这个类是啥?它是java.sql包中的类,利用它可以获取我们连接到的数据库的结构、存储等很多信息
其更多详细介绍可以查看如下链接
https://www.apiref.com/java11-zh/java.sql/java/sql/DatabaseMetaData.html

核心代码实现

1、获取数据库下的所有数据库表名
 /**
     * 获取数据库下的所有数据库表名
     *
     * @return Map> key为数据库名称,value为该数据库下的所有表名
     */
    public Map> getDbTablesNamesMap(){
        Map> dbTableNamesMap = new HashMap<>();
        try {
            //获取数据库的元数据
            DatabaseMetaData dbMetaData = getConnection().getMetaData();
            //从元数据中获取到所有的表名
            ResultSet rs = dbMetaData.getTables(null, null, null,new String[] { "TABLE" });
            List tableNames;
            while(rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                String curTableDbName = rs.getString("TABLE_CAT");
                String tableNameType = rs.getString("TABLE_TYPE");
                //表模式(可能为空),在oracle中获取的是命名空间
                String tableNameSchema = rs.getString("TABLE_SCHEM");
                String tableNameRemark = rs.getString("REMARKS");
                System.out.println("表名: " + tableName + ",表所属数据库: " + curTableDbName + ",表类型: " + tableNameType + ",表模式: " + tableNameSchema + ",表备注: " + tableNameRemark);
                //跳过mysql自带的系统库
                if("sys".equalsIgnoreCase(curTableDbName)){
                    continue;
                }
                if(dbTableNamesMap.containsKey(curTableDbName)){
                    tableNames = dbTableNamesMap.get(curTableDbName);
                }else{
                    tableNames = new ArrayList<>();
                }
                tableNames.add(tableName);
                dbTableNamesMap.put(curTableDbName,tableNames);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        close();
        return dbTableNamesMap;
    }
2、拼凑要执行的sql语句
public static List prepareSqlStatementWithStringFormat(boolean isForFiles) {
        String sql = "alter table %s ADD COLUMN create_time DATETIME COMMENT '创建时间',ADD COLUMN created_by_id BIGINT(20) DEFAULT NULL COMMENT '创建人id', ADD COLUMN update_time DATETIME COMMENT '修改时间', ADD COLUMN last_updated_by_id BIGINT(20) DEFAULT NULL COMMENT '修改人id'";
        List sqlStatements = new ArrayList<>();
        Map> dbTableNamesMap = DbUtils.INSTANCE.getDbTablesNamesMap();
        dbTableNamesMap.forEach((dbName,tableNames) -> {
            for (String tableName : tableNames) {
                String record = dbName + "." + tableName;
                String sqlStatement = String.format(sql, record);
                //如果是要写入文件,则每条生成的sql语句,需追加分号
                if(isForFiles){
                    sqlStatement = sqlStatement + ";";
                }
                System.out.println(sqlStatement);
                sqlStatements.add(sqlStatement);

            }

        });
        return sqlStatements;

    }
3、将生成的sql语句写入文件
public static void writeSqlStatement2File(List sqlStatements,String filePath){

        try {
            File file = new File(filePath);
            if(!file.exists()){
                file.createNewFile();
            }
            FileUtil.writeUtf8Lines(sqlStatements,file);
            System.out.println("数据库脚本写入"+filePath+"成功");
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("数据库脚本写入"+filePath+"失败");
        }


    }

总结

小伙伴并没有采用方案三的实现方法,而是采取了方案一,通过一顿饭的代价,让我帮他实现了这个需求。最后我帮他实现整体方案是以flyway+java程序实现的sql脚本的方式实现

demo链接

https://github.com/lyb-geek/springboot-learning/tree/master/springboot-generate-sqlscript

你可能感兴趣的