基于Spring包扫描工具和MybatisPlus逆向工程组件的数据表自动同步机制

公司产品产出的项目较多。同步数据库表结构工作很麻烦。一个alter语句要跑到N个客户机上执行脚本。超级费时麻烦。介于此，原有方案是把增量脚本放到一resource包下，项目启动时执行逐行执行一次。但由于模块开发人员较多，总有那么一两个机灵鬼漏写脚本。加上项目分支的原因，导致更新客户表结构后埋雷，炸雷。投诉不断。因此本人开发了一款可靠、自动的表结构更新构件。

原理说明：
1、由于全部表OR映射实体采用了MyBatisPlus的@TableName，TableField注解，所以利用组件（https://blog.csdn.net/qq_37148232/article/details/131821497?spm=1001.2014.3001.5501），扫描出项目中所有带@TableName的实体类。然后解析出表名、字段以及自定义注解上的建表类型，建表长度，注释，默认值等。得到最新的表结构。
2、利用从MyBatisPlus逆向工程中扒出来的表结构查询工具。稍做封装，得到项目数据库当前的表结构。
3、对比最新的表结构和当前表结构。得到缺哪些表、缺哪些字段、哪些字段更新了、
哪些字段删了。
4、将对比结果生成可执行的alter、ceate、drop、modify语句。更新数据库。为保险起见，drop和modify默认不启用。
5、开机自动运行生成的语句集。更新表结构。

代码结构如下：

各类说明：
ColumnInfo:包扫描实体类后得到的字段信息封装。
CreateTableHandler:自动建表处理器。
DBJDBCType:java-jdbcType映射接口。
MySqlJDBCType:Mysql系列数据库类型映射支持
OtherJDBCTYpe:Oracle系列数据库映射支持。
JDBCSupport:类型映射支持
SchemaAnalyserAutoConfiguration:主配置类
SchemaAnalyserExecutor:自动建表执行入口
SchemaDefinition:扫描后得到的表和字段信息
SchemaDefinitionLoader:包扫描及全面目字段信息加载类
SchemaExistedDefinitionLoader：项目库表结构加载类
UpdateTableHandler:Alter语句处理类

主要类代码：

public class ColumnInfo {private Boolean isPrimary;private String columnName;private String type;public String toCreateColumn() {if (isPrimary) {return columnName + " " + type + " " + "primary key";} else {return columnName + " " + type;}}
}

public class CreateTableHandler {@Autowiredprivate JDBCSupport jdbcSupport;public void createTable(SchemaDefinition schemaDefinition) {if (!FrameworkProperties.enableAutoAlterTableCreate) {return;}List<String> columns = schemaDefinition.getColumns();Map<String, ColumnName> columnContainer = schemaDefinition.getColumnContainer();Map<String, Field> propContainer = schemaDefinition.getPropContainer();List<ColumnInfo> columnInfos = new ArrayList<>();TableId tableId = schemaDefinition.getTableId();if (null != tableId) {ColumnInfo columnInfo = new ColumnInfo();columnInfo.setColumnName(tableId.value());columnInfo.setIsPrimary(true);columnInfo.setType(jdbcSupport.decideJDBCPrimaryType());columnInfos.add(columnInfo);}for (String column : columns) {ColumnName columnName = columnContainer.get(column);Field field = propContainer.get(column);String jdbcType = jdbcSupport.decideJDBCType(column, field, columnName);ColumnInfo columnInfo = new ColumnInfo();columnInfo.setType(jdbcType);columnInfo.setIsPrimary(false);columnInfo.setColumnName(column);columnInfos.add(columnInfo);}jdbcSupport.createTable(schemaDefinition.getTableName(), columnInfos);}
}

public interface DBJDBCType {static DBJDBCType matchJDBCType(String databaseType) {return databaseType.toLowerCase().contains("mysql") ? new MySqlJDBCType() : new OtherJDBCType();}String varcharType(int length);String intType();String longType();String booleanType();String dateType();String textType();
}

public class JDBCSupport implements InitializingBean {private final static String SQL_ADD_MYSQL = "alter table %s add %s %s %s comment '%s';";private final static String SQL_ADD_ORACLE = "alter table %s add %s %s %s;";private final static String SQL_COMMENT_ORACLE = "comment on table %s.%s is '%s';";private final static String SQL_MODIFY = "alter table %s modify %s %s;";private final static String SQL_CREATE = "create table %s (%s);";private DataSource dataSource;private String databaseProductName;private boolean isMysql;public JDBCSupport(DataSource dataSource) {this.dataSource = dataSource;}public void createTable(String tableName, List<ColumnInfo> columnInfos) {List<String> createColumns = ListUtils.list2list(columnInfos, ColumnInfo::toCreateColumn);String columnSqlItems = StrUtils.join(createColumns);String createSql = String.format(SQL_CREATE, tableName, columnSqlItems);executeSql(createSql);}public void addColumn(String tableName, String columnName, String type, Object defaultValue, String comment) {String sql;// 默认值String defaultValueSegment = "";if (StrUtils.isNotNull(defaultValue)) {if (defaultValue instanceof String) {defaultValueSegment = "default '" + defaultValue + "'";} else {if (!"-999".equals(String.valueOf(defaultValue))) {defaultValueSegment = "default " + defaultValue;}}}// 注释comment = StrUtils.isNull(comment) ? "" : comment;if (isMysql) {sql = String.format(SQL_ADD_MYSQL, tableName, columnName, type, defaultValueSegment, comment);executeSql(sql);} else {sql = String.format(SQL_ADD_ORACLE, tableName, columnName, type, defaultValueSegment);String commentSql = String.format(SQL_COMMENT_ORACLE, tableName, columnName, comment);executeSql(sql);executeSql(commentSql);}}public void modifyColumn(String tableName, String columnName, String type) {String sql = String.format(SQL_MODIFY, tableName, columnName, type);executeSql(sql);}public String decideJDBCPrimaryType() {DBJDBCType dbjdbcType = DBJDBCType.matchJDBCType(databaseProductName);// 主键用60位字符return dbjdbcType.varcharType(60);}public String decideJDBCType(String columnName, Field field, ColumnName definition) {DBJDBCType dbjdbcType = DBJDBCType.matchJDBCType(databaseProductName);if (null != definition) {return chooseByColumnDefinition(definition, dbjdbcType);} else {return chooseByField(columnName, field, dbjdbcType);}}@Overridepublic void afterPropertiesSet() {try (Connection connection = dataSource.getConnection()) {this.databaseProductName = connection.getMetaData().getDatabaseProductName();this.isMysql = "MySQL".equals(databaseProductName);} catch (Exception e) {e.printStackTrace();}}private String chooseByField(String columnName, Field field, DBJDBCType dbjdbcType) {if (null == field) {return dbjdbcType.varcharType(200);}String name = field.getName();Class<?> fieldType = field.getType();if (String.class.isAssignableFrom(fieldType)) {// 基于经验的一些合理猜测判断if (columnName.endsWith("_id")) {return dbjdbcType.varcharType(60);} else if (columnName.endsWith("_ids")) {return dbjdbcType.varcharType(500);} else if (name.equals("content")) {return dbjdbcType.varcharType(500);} else if (name.equals("createBy")) {return dbjdbcType.varcharType(60);} else if (name.equals("updateBy")) {return dbjdbcType.varcharType(60);} else if (name.equals("areaId")) {return dbjdbcType.varcharType(60);} else {return dbjdbcType.varcharType(200);}}if (Integer.class.isAssignableFrom(fieldType)) {// 基于经验的一些合理猜测判断if (columnName.startsWith("is_") || columnName.startsWith("has_")) {return dbjdbcType.booleanType();} else {return dbjdbcType.intType();}}if (Long.class.isAssignableFrom(fieldType)) {return dbjdbcType.longType();}if (Date.class.isAssignableFrom(fieldType)) {return dbjdbcType.dateType();}return dbjdbcType.varcharType(200);}private String chooseByColumnDefinition(ColumnName definition, DBJDBCType dbjdbcType) {if (definition.varcharColumn()) {return dbjdbcType.varcharType(definition.varcharLength());} else if (definition.booleanColumn()) {return dbjdbcType.booleanType();} else if (definition.intColumn()) {return dbjdbcType.intType();} else if (definition.longColumn()) {return dbjdbcType.longType();} else if (definition.dateColumn()) {return dbjdbcType.dateType();} else if (definition.textColumn()) {return dbjdbcType.textType();} else {return dbjdbcType.varcharType(definition.varcharLength());}}private void executeSql(String sql) {try (Connection connection = dataSource.getConnection();PreparedStatement preparedStatement = connection.prepareStatement(sql)) {preparedStatement.execute();} catch (Exception e) {log.warn("sql[{}]执行异常", sql);}}
}

public class MySqlJDBCType implements DBJDBCType {@Overridepublic String varcharType(int length) {return "varchar(" + length + ")";}@Overridepublic String intType() {return "int";}@Overridepublic String longType() {return "bigint";}@Overridepublic String booleanType() {return "tinyint";}@Overridepublic String dateType() {return "date";}@Overridepublic String textType() {return "text";}
}

public class OtherJDBCType implements DBJDBCType {@Overridepublic String varcharType(int length) {return "varchar2(" + (length * 2) + ")";}@Overridepublic String intType() {return "number(10)";}@Overridepublic String longType() {return "number(19)";}@Overridepublic String booleanType() {return "number(1)";}@Overridepublic String dateType() {return "date";}@Overridepublic String textType() {return "text";}
}

@Configuration
@ConditionalOnProperty(prefix = "com.xxx.framework", name = "enable-auto-alter-table", havingValue = "true")
public class SchemaAnalyserAutoConfiguration {@Beanpublic JDBCSupport jdbcSupport(DataSource dataSource) {return new JDBCSupport(dataSource);}@Beanpublic CreateTableHandler createTableHandler() {return new CreateTableHandler();}@Beanpublic UpdateTableHandler updateTableHandler() {return new UpdateTableHandler();}@Beanpublic SchemaAnalyserExecutor schemaAnalyserRunner() {return new SchemaAnalyserExecutor();}@Bean@DependsOn("frameworkProperties") // 主要是读取系统类型用于一些判断，所以要依赖public SchemaDefinitionLoader schemaDefinitionLoader() {return new SchemaDefinitionLoader();}@Beanpublic SchemaExistedDefinitionLoader schemaExistedDefinitionLoader() {return new SchemaExistedDefinitionLoader();}
}

public class SchemaAnalyserExecutor implements EasySpringListener {@Autowiredprivate CreateTableHandler createTableHandler;@Autowiredprivate UpdateTableHandler updateTableHandler;@Autowiredprivate SchemaDefinitionLoader schemaDefinitionLoader;@Autowiredprivate SchemaExistedDefinitionLoader schemaExistedDefinitionLoader;@Overridepublic void doBusiness(ApplicationContext applicationContext) {List<SchemaDefinition> projectSchemaDefinition = schemaDefinitionLoader.getProjectSchemaDefinition();Map<String, TableInfo> tableContainer = schemaExistedDefinitionLoader.findExistedTableInfo();generateDelete(tableContainer);// 对比已存在的表和字段，更新字段或新建表for (SchemaDefinition schemaDefinition : projectSchemaDefinition) {// 看表里存不存在该表的定义信息TableInfo tableInfo = tableContainer.get(schemaDefinition.getTableName());if (null != tableInfo) {try {updateTableHandler.updateTable(schemaDefinition, tableInfo);} catch (Exception e) {e.printStackTrace();}} else {try {createTableHandler.createTable(schemaDefinition);} catch (Exception e) {e.printStackTrace();}}}}public void generateDelete(Map<String, TableInfo> tableContainer) {if (FrameworkProperties.enableGenerateDeleteScript) {Set<String> strings = tableContainer.keySet();List<String> tableNames = ZYListUtils.set2list(strings);tableNames.sort(Comparator.comparing(a -> a));List<String> deleteSqls = new ArrayList<>();tableNames.forEach(tableName -> {deleteSqls.add("delete from " + tableName + " ;");});FileUtils.writeLines(deleteSqls, "D://clear_" + ZYDateUtils.formart(new Date(), "yyyy-MM-dd-HH-mm-ss") + ".sql", "utf-8", true);}}@Overridepublic int getOrder() {return 0;}
}

public class SchemaDefinition {private String tableName;private String tableComment;// 表格信息private TableName tableNameAnnotation;// 主键信息private TableId tableId;private List<String> columns = new ArrayList<>();// 字段属性定义private Map<String, Field> propContainer = new HashMap<>();// 字段描述信息private Map<String, ColumnName> columnContainer = new HashMap<>();// 字段定义信息private Map<String, TableField> fieldContainer = new HashMap<>();public void joinSchemaDefinition(SchemaDefinition schemaDefinition) {List<String> targetColumns = schemaDefinition.getColumns();Map<String, ColumnName> targetColumnContainer = schemaDefinition.getColumnContainer();Map<String, TableField> targetFieldContainer = schemaDefinition.getFieldContainer();for (String targetColumn : targetColumns) {if (!columns.contains(targetColumn)) {ColumnName columnName = targetColumnContainer.get(targetColumn);TableField tableField = targetFieldContainer.get(targetColumn);if (null != columnName && null != tableField) {columns.add(targetColumn);columnContainer.put(targetColumn, columnName);fieldContainer.put(targetColumn, tableField);}}}}public SchemaDefinition(Class<?> aClass) {this.tableNameAnnotation = aClass.getAnnotation(TableName.class);// 表名this.tableName = tableNameAnnotation.value().toLowerCase();// 收集字段定义信息Field[] fields = ZYReflectUtils.getFields(aClass);for (Field field : fields) {field.setAccessible(true);// 主键字段TableId tableId = field.getAnnotation(TableId.class);if (null != tableId) {this.tableId = tableId;continue;}// 普通字段TableField tableField = field.getAnnotation(TableField.class);if (null == tableField) {continue;}if (!tableField.exist()) {continue;}String column = tableField.value().toLowerCase();// 字段集合columns.add(column);// 表格定义fieldContainer.put(column, tableField);// 字段反射属性propContainer.put(column, field);// 建表描述ColumnName columnName = field.getAnnotation(ColumnName.class);if (null != columnName) {columnContainer.put(column, columnName);}}}
}

public class SchemaDefinitionLoader implements InterestedClassAware {private List<SchemaDefinition> schemaDefinitions = new ArrayList<>();public List<SchemaDefinition> getProjectSchemaDefinition() {return schemaDefinitions;}@Overridepublic boolean match(AnnotationMetadata annotationMetadata) {return annotationMetadata.hasAnnotation(TableName.class.getName());}@Overridepublic void setClasses(Set<Class<?>> classes) {List<SchemaDefinition> definitions = new ArrayList<>();Map<String, TableCondition> tableConditionCache = new HashMap<>();for (Class<?> aClass : classes) {TableExplain tableExplain = aClass.getAnnotation(TableExplain.class);if (isNecessary(tableConditionCache, tableExplain, aClass)) {SchemaDefinition schemaDefinition = new SchemaDefinition(aClass);if (null != tableExplain) {// 表的注释schemaDefinition.setTableComment(tableExplain.value());}definitions.add(schemaDefinition);}}Map<String, List<SchemaDefinition>> schemaContainer = ZYListUtils.groupList(definitions, SchemaDefinition::getTableName);schemaContainer.forEach((schemaName, schemas) -> {if (schemas.size() == 1) {schemaDefinitions.add(schemas.get(GlobalConstant.FIRST));} else if (schemas.size() > 1) {SchemaDefinition schemaDefinition = schemas.get(GlobalConstant.FIRST);// 合并集合for (int i = 1; i < schemas.size(); i++) {schemaDefinition.joinSchemaDefinition(schemas.get(i));}schemaDefinitions.add(schemaDefinition);}});}private boolean isNecessary(Map<String, TableCondition> tableConditionCache, TableExplain tableExplain, Class<?> aClass) {if (null == tableExplain) {return true;}if (tableExplain.exclude()) {return false;}Class<? extends TableCondition> condition = tableExplain.condition();String name = condition.getName();TableCondition tableCondition = tableConditionCache.get(name);if (null == tableCondition) {tableCondition = ReflectUtils.newInstance(condition);tableConditionCache.put(name, tableCondition);}return tableCondition.isNecessary(aClass);}
}

public class SchemaExistedDefinitionLoader {@Autowiredprivate DataSourceProperties dataSourceProperties;@Autowiredprivate DataSource dataSource;@SneakyThrowspublic Map<String, TableInfo> findExistedTableInfo() {DataSourceConfig dataSourceConfig = new DataSourceConfig();dataSourceConfig.setDriverName(dataSourceProperties.getDriverClassName());dataSourceConfig.setPassword(dataSourceProperties.getPassword());dataSourceConfig.setUsername(dataSourceProperties.getUsername());String url = dataSourceProperties.getUrl();dataSourceConfig.setUrl(url);this.connection = dataSourceConfig.getConn();dataSourceConfig.setSchemaName(this.connection.getSchema());this.dataSourceConfig = dataSourceConfig;if (url.contains("kingbase8")) {this.dbQuery = new OracleQuery();} else {this.dbQuery = dataSourceConfig.getDbQuery();}this.strategyConfig = new StrategyConfig();this.globalConfig = new GlobalConfig();List<TableInfo> tablesInfo = getTablesInfo();return ZYListUtils.groupModel(tablesInfo, TableInfo::getName);// 表名全改成小写}private IDbQuery dbQuery;private DataSourceConfig dataSourceConfig;private Connection connection;private GlobalConfig globalConfig;private StrategyConfig strategyConfig;private List<TableInfo> getTablesInfo() {//所有的表信息List<TableInfo> tableList = new ArrayList<>();//不存在的表名PreparedStatement preparedStatement = null;try {String tablesSql = dbQuery.tablesSql();if (DbType.POSTGRE_SQL == dbQuery.dbType()) {String schema = dataSourceConfig.getSchemaName();if (schema == null) {//pg默认schema=publicschema = "public";dataSourceConfig.setSchemaName(schema);}tablesSql = String.format(tablesSql, schema);}//oracle数据库表太多，出现最大游标错误else if (DbType.ORACLE == dbQuery.dbType()) {String schema = dataSourceConfig.getSchemaName();//oracle默认用户的schema=usernameif (schema == null) {schema = dataSourceConfig.getUsername().toUpperCase();dataSourceConfig.setSchemaName(schema);}tablesSql = String.format(tablesSql, schema);}preparedStatement = connection.prepareStatement(tablesSql);ResultSet results = preparedStatement.executeQuery();TableInfo tableInfo;while (results.next()) {String tableName = results.getString(dbQuery.tableName());if (StringUtils.isNotEmpty(tableName)) {String tableComment = results.getString(dbQuery.tableComment());if ("VIEW".equalsIgnoreCase(tableComment)) {// 跳过视图continue;}tableInfo = new TableInfo();tableInfo.setName(tableName.toLowerCase());tableInfo.setComment(tableComment);tableList.add(tableInfo);} else {System.err.println("当前数据库为空！！！");}}tableList.forEach(ti -> convertTableFields(ti, strategyConfig.getColumnNaming()));} catch (SQLException e) {e.printStackTrace();} finally {// 释放资源try {if (preparedStatement != null) {preparedStatement.close();}if (connection != null) {connection.close();}} catch (SQLException e) {e.printStackTrace();}}return tableList;}private TableInfo convertTableFields(TableInfo tableInfo, NamingStrategy strategy) {boolean haveId = false;List<TableField> fieldList = new ArrayList<>();try {String tableFieldsSql = dbQuery.tableFieldsSql();if (DbType.POSTGRE_SQL == dbQuery.dbType()) {tableFieldsSql = String.format(tableFieldsSql, dataSourceConfig.getSchemaName(), tableInfo.getName());} else if (DbType.ORACLE == dbQuery.dbType()) {tableFieldsSql = String.format(tableFieldsSql.replace("#schema", dataSourceConfig.getSchemaName()), tableInfo.getName());} else {tableFieldsSql = String.format(tableFieldsSql, tableInfo.getName());}PreparedStatement preparedStatement = connection.prepareStatement(tableFieldsSql);ResultSet results = preparedStatement.executeQuery();while (results.next()) {TableField field = new TableField();field.setName(results.getString(dbQuery.fieldName().toLowerCase()));field.setType(results.getString(dbQuery.fieldType()));field.setColumnType(dataSourceConfig.getTypeConvert().processTypeConvert(globalConfig, field.getType()));field.setComment(results.getString(dbQuery.fieldComment()));fieldList.add(field);}} catch (SQLException e) {System.err.println("SQL Exception：" + e.getMessage());}tableInfo.setFields(fieldList);return tableInfo;}
}

public class UpdateTableHandler {@Autowiredprivate JDBCSupport jdbcSupport;public void updateTable(SchemaDefinition schemaDefinition, TableInfo tableInfo) {if (!FrameworkProperties.enableAutoAlterTableAddColumn && !FrameworkProperties.enableAutoAlterTableModifyColumn) {return;}List<String> columns = schemaDefinition.getColumns();List<TableField> commonFields = tableInfo.getFields();Map<String, TableField> existsColumnContainer = ZYListUtils.groupModel(commonFields, TableField::getName);// 列的定义Map<String, ColumnName> columnContainer = schemaDefinition.getColumnContainer();// 列的字段描述Map<String, Field> propContainer = schemaDefinition.getPropContainer();String tableName = tableInfo.getName();for (String column : columns) {// 列的定义注解ColumnName columnName = columnContainer.get(column);// 列属性的反射类型Field field = propContainer.get(column);// 决定jdbc现有的类型String jdbcType = jdbcSupport.decideJDBCType(column, field, columnName);if (!existsColumnContainer.containsKey(column)) {// 添加字段if (FrameworkProperties.enableAutoAlterTableAddColumn) {Object defaultValue = null;String comment = "";if (null != columnName && null != field) {boolean isVarchar = String.class.isAssignableFrom(field.getType());defaultValue = isVarchar ? columnName.varcharDefaultValue() : columnName.intDefaultValue();comment = columnName.value();}jdbcSupport.addColumn(tableName, column, jdbcType, defaultValue, comment);}} else {// 更新字段TableField existsTableField = existsColumnContainer.get(column);if (compareAndNecessaryModify(column, columnName, field, existsTableField)) {if (FrameworkProperties.enableAutoAlterTableModifyColumn) {jdbcSupport.modifyColumn(tableName, column, jdbcType);}}}}}// 比对下新旧数据库，看字段是否需要modifyprivate boolean compareAndNecessaryModify(String column, ColumnName columnName, Field field, TableField existsTableField) {// 主要是字段类型跟长度String type = existsTableField.getType();String jdbcType = jdbcSupport.decideJDBCType(column, field, columnName);return !type.equals(jdbcType);}
}