网站的运营管理方案网站建设建设哪家好

当前位置： 首页 > news >正文

网站的运营管理方案,网站建设建设哪家好,做微网站用哪个平台,软件外包价格一般多少公司产品产出的项目较多。同步数据库表结构工作很麻烦。一个alter语句要跑到N个客户机上执行脚本。超级费时麻烦。介于此#xff0c;原有方案是把增量脚本放到一resource包下#xff0c;项目启动时执行逐行执行一次。但由于模块开发人员较多#xff0c;总有那么一两个机灵鬼…公司产品产出的项目较多。同步数据库表结构工作很麻烦。一个alter语句要跑到N个客户机上执行脚本。超级费时麻烦。介于此原有方案是把增量脚本放到一resource包下项目启动时执行逐行执行一次。但由于模块开发人员较多总有那么一两个机灵鬼漏写脚本。加上项目分支的原因导致更新客户表结构后埋雷炸雷。投诉不断。因此本人开发了一款可靠、自动的表结构更新构件。 原理说明 1、由于全部表OR映射实体采用了MyBatisPlus的TableNameTableField注解所以利用组件https://blog.csdn.net/qq_37148232/article/details/131821497?spm1001.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;}ListString columns schemaDefinition.getColumns();MapString, ColumnName columnContainer schemaDefinition.getColumnContainer();MapString, Field propContainer schemaDefinition.getPropContainer();ListColumnInfo 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, ListColumnInfo columnInfos) {ListString 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();}BeanDependsOn(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) {ListSchemaDefinition projectSchemaDefinition schemaDefinitionLoader.getProjectSchemaDefinition();MapString, 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(MapString, TableInfo tableContainer) {if (FrameworkProperties.enableGenerateDeleteScript) {SetString strings tableContainer.keySet();ListString tableNames ZYListUtils.set2list(strings);tableNames.sort(Comparator.comparing(a - a));ListString 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 ListString columns new ArrayList();// 字段属性定义private MapString, Field propContainer new HashMap();// 字段描述信息private MapString, ColumnName columnContainer new HashMap();// 字段定义信息private MapString, TableField fieldContainer new HashMap();public void joinSchemaDefinition(SchemaDefinition schemaDefinition) {ListString targetColumns schemaDefinition.getColumns();MapString, ColumnName targetColumnContainer schemaDefinition.getColumnContainer();MapString, 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 ListSchemaDefinition schemaDefinitions new ArrayList();public ListSchemaDefinition getProjectSchemaDefinition() {return schemaDefinitions;}Overridepublic boolean match(AnnotationMetadata annotationMetadata) {return annotationMetadata.hasAnnotation(TableName.class.getName());}Overridepublic void setClasses(SetClass? classes) {ListSchemaDefinition definitions new ArrayList();MapString, 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);}}MapString, ListSchemaDefinition 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(MapString, 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 MapString, 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();ListTableInfo tablesInfo getTablesInfo();return ZYListUtils.groupModel(tablesInfo, TableInfo::getName);// 表名全改成小写}private IDbQuery dbQuery;private DataSourceConfig dataSourceConfig;private Connection connection;private GlobalConfig globalConfig;private StrategyConfig strategyConfig;private ListTableInfo getTablesInfo() {//所有的表信息ListTableInfo 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默认schemapublicschema public;dataSourceConfig.setSchemaName(schema);}tablesSql String.format(tablesSql, schema);}//oracle数据库表太多出现最大游标错误else if (DbType.ORACLE dbQuery.dbType()) {String schema dataSourceConfig.getSchemaName();//oracle默认用户的schemausernameif (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;ListTableField 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;}ListString columns schemaDefinition.getColumns();ListTableField commonFields tableInfo.getFields();MapString, TableField existsColumnContainer ZYListUtils.groupModel(commonFields, TableField::getName);// 列的定义MapString, ColumnName columnContainer schemaDefinition.getColumnContainer();// 列的字段描述MapString, 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);} }