spring数据库校准功能（仅增量）V1.0

背景：部署版本多，库结构更新麻烦
参考：ofbiz的实体引擎，启动时校准表结构
预期：
1、应用启动时可以校准库结构；
2、对缺表进行新增；
3、对缺字段进行新增；
4、对字段类型不同则进行更新；
5、对库中多出的表不进行处理;
6、和项目解耦，使其移动至其他项目仍然可以使用。

package com.common.DatabaseCheck;

import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 数据库结构校验工具类
 * 项目启动时，将根据tableList，对代码中存在而库中不存在的表进行新增;对代码和库中已存在的表，则对表字段进行增量更新和类型修改；
 * 注意：1、对多余的表和字段不进行处理；2、无法修改库中的字段名
 */
@Component
public class MysqlCheckUtil {

    @Resource
    private DataSource dataSource;

    private static List<TableInfo> tableList = new ArrayList<>();

    {
        tableList.add(new TableInfo(null, "target_output_job", "，目标输出", Arrays.asList(
                new Column("id", "int(11)", true, false, true,null, "id"),
                new Column("target_id", "int(11)", false, true, false, null, "目标id"))));
    }

    //mysql表结构
    @Data
    @AllArgsConstructor
    public class TableInfo {
        String dbName;
        String name;
        String comment;
        List<Column> cList;

        String mysqlCreatIfNotExistSQL() {
            String sqlTemplate = "CREATE TABLE IF NOT EXISTS %s (  %s  %s ) COMMENT='%s' ";

            String tableName = "`" + name + "`";
            if (StringUtils.isNotEmpty(dbName)) {
                tableName = "`" + dbName + "`." + tableName;
            }

            String tableComment = this.comment;

            String fieldsSql = null;
            StringBuilder fieldsSqlBuilder = new StringBuilder();
            for (Column column : cList) {
                fieldsSqlBuilder.append(", ").append(column.mysqlColumnDefine());
            }
            fieldsSql = fieldsSqlBuilder.substring(1);

            String keysSql = null;
            List<String> keyList = cList.stream()
                    .filter(Column::getIsKey)
                    .map(Column::getName)
                    .collect(Collectors.toList());

            if (CollectionUtils.isEmpty(keyList)) {
                keysSql = "";
            } else {
                keysSql = ",  PRIMARY KEY (`" + String.join("`, `", keyList) + "`) ";
            }

            return String.format(sqlTemplate, tableName, fieldsSql, keysSql, tableComment);
        }
    }

    //mysql字段结构
    @Data
    @AllArgsConstructor
    public class Column {
        String name;
        //字段类型，如varchar(30)
        String type;
        Boolean isKey;
        Boolean isNull;
        Boolean isAutoIncrement;
        String defaultVal;
        String comment;

        public String mysqlColumnDefine() {
            String mysqlDefine = "`" + name + "` " + type + ( isNull ? " NULL " : " NOT NULL ");
            mysqlDefine += StringUtils.isNotEmpty(comment) ? " COMMENT '" + comment + "'" : " ";
            mysqlDefine += StringUtils.isNotEmpty(defaultVal) ? " DEFAULT '" + defaultVal + "'" : " ";
            mysqlDefine += isAutoIncrement ? " AUTO_INCREMENT ": "";
            return mysqlDefine;
        }

        public boolean isSameType(Column c){
            return equalsObj(this.type, c.getType())
                    && equalsObj(this.isNull, c.getIsNull())
                    && equalsObj(this.isAutoIncrement, c.getIsAutoIncrement())
                    && equalsObj(this.defaultVal, c.getDefaultVal())
                    && equalsObj(this.comment, c.getComment());
        }

        private boolean equalsObj(Object s1, Object s2){
            if(s1 == null) return s2 == null;
            return s1.equals(s2);
        }
    }


    private List<Map> execQuerySQL(String sql) throws SQLException {

        Connection conn = dataSource.getConnection();
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        List<Map> selectResult = new ArrayList();
        String[] colNames = getColumnNames(rs);
        int columnCount = rs.getMetaData().getColumnCount();
        while (rs.next()) {
            Map<String, String> recordInfo = new HashMap();
            for (int i = 0; i < columnCount; ++i)
                recordInfo.put(colNames[i], rs.getString(colNames[i]));
            selectResult.add(recordInfo);
        }

        return selectResult;
    }

    private boolean executeSQL(String sql) throws SQLException {
        Connection conn = dataSource.getConnection();
        Statement statement = conn.createStatement();
        return statement.execute(sql);
    }

    private String[] getColumnNames(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        String[] columnNames = new String[columnCount];
        for (int i = 0; i < columnCount; i++) {
            columnNames[i] = metaData.getColumnName(i + 1);
        }
        return columnNames;
    }

    private String getCurrDatabaseName() throws SQLException {
        return execQuerySQL("select database() as db").get(0).get("db").toString();
    }

    private void addTableIfNotExist() throws SQLException {
        String currDatabaseName = getCurrDatabaseName();
        List<Map> dbAllTables = execQuerySQL(" SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES ");
        List<String> tableNames = dbAllTables.stream().map(i -> i.get("TABLE_SCHEMA") + "." + i.get("TABLE_NAME")).collect(Collectors.toList());
        for (TableInfo table : tableList) {
            String dbName = StringUtils.isEmpty(table.getDbName()) ? currDatabaseName : table.getDbName();
            String tableName = table.getName();
            if(!tableNames.contains(dbName +"." +tableName)){
                String tableCreateSQL = table.mysqlCreatIfNotExistSQL();
                System.out.println("新增表:" + tableCreateSQL);
                executeSQL(tableCreateSQL);
            }
        }
    }

    private Map<String, Column> getTableColumnsNameMap(String dbName, String tableName) throws SQLException {
        HashMap<String, Column> result = new HashMap<>();
        List<Map> dbAllCurrColumns = execQuerySQL(" SELECT COLUMN_NAME,COLUMN_TYPE,COLUMN_KEY,IS_NULLABLE,EXTRA,COLUMN_DEFAULT,COLUMN_COMMENT,TABLE_SCHEMA,TABLE_NAME " +
                " FROM information_schema.columns WHERE TABLE_SCHEMA='" + dbName + "' AND TABLE_NAME='" + tableName + "' ");

        for(Map colMap: dbAllCurrColumns){
            String name =(String) colMap.get("COLUMN_NAME");
            String type =(String) colMap.get("COLUMN_TYPE");
            Boolean isKey = colMap.get("COLUMN_KEY").toString().contains("PRI");
            Boolean isNull = colMap.get("IS_NULLABLE").toString().contains("YES");
            Boolean isAutoIncrement = colMap.get("EXTRA").toString().contains("auto_increment");
            String defaultVal =(String) colMap.get("COLUMN_DEFAULT");;
            String comment =(String) colMap.get("COLUMN_COMMENT");
            result.put(name, new Column(name, type, isKey, isNull, isAutoIncrement, defaultVal, comment));
        }
        return result;
    }

    private void handleColumn() throws SQLException {
        String currDatabaseName = getCurrDatabaseName();

        for (TableInfo table : tableList) {
            String dbName = StringUtils.isEmpty(table.getDbName()) ? currDatabaseName : table.getDbName();
            String tableName = table.getName();
            Map<String, Column> name2ColumnMap = getTableColumnsNameMap(dbName, tableName);

            for (Column column : table.getCList()) {
                if (name2ColumnMap.containsKey(column.getName())) {
                    //同名字段校验类型是否一致
                    if (column.isSameType(name2ColumnMap.get(column.getName()))) {
                        //字段类型一致，不做任何操作
                    } else {
                        //字段类型不一致，更新类型
                        modifyColumn(dbName, tableName, column);
                        System.out.println(dbName + "." + tableName +"表，更新字段" + column.getName() + "的类型。旧类型" + name2ColumnMap.get(column.getName()).getType() + " >> 新类型" + column.getType());
                    }
                } else {
                    //不存在字段，需要新增
                    addColumn(dbName, tableName, column);
                    System.out.println(dbName + "." + tableName +"表，的新增字段" + column.getName() + "，类型为" + column.getType());
                }
            }
        }
    }

    private void modifyColumn(String dbName, String tableName, Column column) throws SQLException {
        executeSQL(String.format(" ALTER TABLE `%s`.`%s` MODIFY  %s ", dbName, tableName, column.mysqlColumnDefine()));
    }

    private void addColumn(String dbName, String tableName, Column column) throws SQLException {
        executeSQL(String.format(" ALTER TABLE `%s`.`%s` add  %s ", dbName, tableName, column.mysqlColumnDefine()));
    }

    @PostConstruct
    public void check() {
        try {
            System.out.println("开始校验数据库");
            addTableIfNotExist();
            handleColumn();
            System.out.println("结束校验数据库");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}