不管如何更新数据库,alterscripter.sql都会在启动时被调用一次,
而里里面用varchar2 varchar两条alter语句,
简单了兼容了下mysqlt和oracle(偷懒没根据databaseId做脚本区分,),
而且可以写建表语句,视图语句。
这样,无论是哪个环境,都不会报数据库缺字段缺表,
字段长度什么的精度什么的问题不匹配。
就不用老是去求发版的大佬了,
那谁,麻烦加个字段,那谁,麻烦帮执行下脚本啥的。哈哈。
//排到最前面,避免和其他查询冲突
//EasySpringListener:前面文章改写的spring监听器
@Order(Integer.MIN_VALUE)
public class AlterTablerExcutor implements EasySpringListener, ResourceLoaderAware {
@Autowired
SystemDao systemDao;
private ResourceLoader resourceLoader;
@Override
public void doSome(ApplicationContext applicationContext) {
//只在Linux服务器上执行,
//避免有些没代码更新习贯的人在windows上 运行老脚本导致报错
String systemType = System.getProperty("os.name");
String lowerCase = systemType.toLowerCase();
if (lowerCase.indexOf("linux") == -1) {
return;
}
//读取所有maven业务模块下的alterscripter.sql脚本
List<String> sqlScripts = readClassPathFiles2String("tablealter/alterscripter.sql");
if (null == sqlScripts || sqlScripts.size() == 0) {
return;
}
String ddd = "################################################";
log.info(ddd);
log.info("#开始执行表的完整性更新脚本");
log.info(ddd);
log.info("");
for (String sqlScript : sqlScripts) {
excutorScript(sqlScript);
}
}
private void excutorScript(String sqlScript) {
if (isNotNull(sqlScript)) {
String[] sqls = sqlScript.split(";");
if (sqls.length == 0) {
return;
}
for (String sql : sqls) {
excutorSql(sql);
}
}
}
private void excutorSql(String sql) {
if (isNull(sql)) {
return;
}
//用从mybatis里面扒出来的StringTokenizer 这玩意格式化sql
String styleSql = toStyle(sql);
String separetorLine= "################################################";
log.info(separetorLine);
log.info("#当前执行:{}", styleSql);
try {
log.info("#..........");
// @Select("${sql}")
//List<ImportMap> excutorSql(@Param("sql") String sql);
systemDao.excutorSql(styleSql);
log.info("#更新结果:成功!!!");
} catch (Exception e) {
//与传统的执行整个脚本不同的时,
//对每一条alter语句用try...catch包裹,
//避免一条不成功所有不成功,而且不成功一般是xxx已存在引发的。是可行的
log.info("#更新结果:失败!!!");
log.info("#可能结果:{已执行过的alter操作}--{表或字段已存在}--{语法错误}");
logMessage(e);
log.info(separetorLine);
}
log.info("");
}
private void logMessage(Exception e) {
String message = e.getMessage();
StringTokenizer stringTokenizer = new StringTokenizer(message);
log.info("#异常消息:");
String msg = "#";
while (stringTokenizer.hasMoreTokens()) {
msg += stringTokenizer.nextToken();
if (msg.length() > 100) {
log.info(msg);
msg = "#";
}
}
}
private String toStyle(String sql) {
StringTokenizer stringTokenizer = new StringTokenizer(sql);
List<String> list = new ArrayList<String>();
while (stringTokenizer.hasMoreTokens()) {
list.add(stringTokenizer.nextToken());
}
String styleSql = DrinStringUtils.join(list, " ");
return styleSql;
}
public List<String> readClassPathFiles2String(String filePath) {
List<String> scripts = new ArrayList<>();
try {
ClassLoader classLoader = resourceLoader.getClassLoader();
filePath = "tablealter/alterscripter.sql";
Enumeration<URL> urls = (classLoader != null ? classLoader.getResources(filePath)
: ClassLoader.getSystemResources(filePath));
while (urls.hasMoreElements()) {
URL url = urls.nextElement();
UrlResource resource = new UrlResource(url);
try (InputStream inputStream = resource.getInputStream()) {
scripts.add(IOUtils.toString(inputStream, "UTF-8"));
} catch (Exception e) {
}
}
} catch (IOException ex) {
}
return scripts;
}
@Override
public void setResourceLoader(ResourceLoader resourceLoader) {
this.resourceLoader = resourceLoader;
}
}
sql脚本: