Mybatis-代码检测是否缺表，缺字段，缺索引-并自动补齐-添加-补表-补字段-补索引-InputStream输入流中读取文本-split忽略引号中的分隔符
@RestController
@RequestMapping
@Slf4j
public class TestController{
@Autowired
private HandleTableService handleTableService;
@GetMapping("/isLackOf")
public Object isLackOf() {
//示例1
handleTableService.checkTableOrSyncData202210();
//示例2
handleTableService.checkTableOrSyncData202211();
return 1;
}
}
@Service
@Slf4j
public class HandleTableService extends DataBaseCheckService {
//示例1
public void checkTableOrSyncData202210() {
String addIndexSql = "ALTER TABLE `edu_user_learning_analysis` ADD INDEX `u_idx_updatetime`(`update_time`) USING BTREE";
addLackOfIndex("edu_user_learning_analysis", "u_idx_updatetime", addIndexSql);
}
//示例2
//版本一旦发布（包括项目上），就不要修改这里的内容，直接补充新方法
public void checkTableOrSyncData202211() {
//;分号分割sql列表
execSqlFromResource(isLackOfTable("edu_matrix_manage"), "/db/v2.1.11.sql");
}
package cn.jiangjiesheng.edu.service.common;
import cn.jiangjiesheng.edu.mapper.BaseMapper;
import cn.jiangjiesheng.edu.utils.StreamUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import java.io.InputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@Service
@Slf4j
public class DataBaseCheckService {
@Autowired
protected BaseMapper baseMapper;
//todo 这里不同的项目，这个配置的key应该是不同的，尤其是edu部分
@Value("${spring.datasource.edu.one.url}")
private String databaseUrl;
private static String INVALID_SYMBOL = "[\r\n\t]";
public boolean isLackOfTable(String tableId) {
try {
baseMapper.isLackOfTable(tableId);
} catch (Exception e) {
String message = e.getMessage();
if (message != null
&& message.toLowerCase().contains("sqlsyntaxerrorexception")
&& message.contains("exist")
&& message.contains(tableId)) {
log.warn("缺少{}表", tableId);
return true;
}
}
return false;
}
public boolean isLackOfField(String tableId, String field) {
try {
baseMapper.isLackOfField(tableId, field);
} catch (Exception e) {
String message = e.getMessage();
if (message != null
&& message.toLowerCase().contains("sqlsyntaxerrorexception")
// && message.contains("Unknown column")
&& message.contains(field)) {
log.warn("{}表缺少{}字段", tableId, field);
return true;
}
}
return false;
}
public boolean isLackOfIndex(String tableId, String idx) {
try {
List<String> result = baseMapper.isLackOfIndex(tableId, idx);
if (CollectionUtils.isEmpty(result)) {
log.warn("{}表缺少{}索引 ", tableId, idx);
return true;
}
} catch (Exception e) {
log.warn("检查{}是否缺少{}索引出现异常,请手动检查并添加索引：{},异常：{}", tableId, idx, idx, e);
}
return false;
}
//todo 字段默认值 暂时只能让直接执行 或 放在一个迭代sql文件中 execSqlFromResource(true, "/db/v2.2.0.sql");
//后期尝试代码解析：打印下 columnResultSet1.next() 对象，看看能不能再找到默认值
// private void printTableStructure(String driver, String url, String user, String password) throws Exception {
// Class.forName(driver);
// Connection connection = DriverManager.getConnection(url, user, password);
//
// DatabaseMetaData metaData = connection.getMetaData();
// // 获取所有表 tableNamePattern 可以模糊查询 "order%"
// ResultSet columnResultSet1 = metaData.getColumns(null, "safetyeducationandtrainingsystem", "edu_train_manager", "train_hour");
//
// //https://blog.51cto.com/u_15127538/4402434
//
// while (columnResultSet1.next()) {
// // 字段名称
// String columnName = columnResultSet1.getString("COLUMN_NAME");
// // 数据类型
// String columnType = columnResultSet1.getString("TYPE_NAME");
// // 字段长度
// int datasize = columnResultSet1.getInt("COLUMN_SIZE");
// // 小数部分位数
// int digits = columnResultSet1.getInt("DECIMAL_DIGITS");
// // 是否可为空 1代表可空 0代表不可为空
// int nullable = columnResultSet1.getInt("NULLABLE");
// // 描述
// String remarks = columnResultSet1.getString("REMARKS");
// System.out.println("当前字段："+columnName + " " + columnType + " " + datasize + " " + digits + " " + nullable + " " + remarks);
//
//// 当前字段：train_hour INT 10 0 0 培训课时
//// 当前字段：train_hour FLOAT 11 1 0 培训课时 这个是最新的
// }
//
// }
/**
*
* @param databaseNameOrUrlOrDefaultUrl
* @param tableId
* @param field
* @param columnType 带长度，但不要空格，例如varchar(13)；如果tinyint(2) unsigned ,unsigned不能少；那如果是datetime(0)，直接填写datetime
* @param isNullable
* @param columnComment
* @return
*/
public boolean isNeedModifyFieldProperty(String databaseNameOrUrlOrDefaultUrl, String tableId, String field,
String columnType, Boolean isNullable, String columnComment) {
try {
if(StringUtils.isBlank(databaseNameOrUrlOrDefaultUrl)){
databaseNameOrUrlOrDefaultUrl = databaseUrl;
}
if (databaseNameOrUrlOrDefaultUrl.contains("?")) {
databaseNameOrUrlOrDefaultUrl = databaseNameOrUrlOrDefaultUrl.substring(0, databaseNameOrUrlOrDefaultUrl.indexOf("?"));
}
if (databaseNameOrUrlOrDefaultUrl.contains("/")) {
databaseNameOrUrlOrDefaultUrl = databaseNameOrUrlOrDefaultUrl.substring(databaseNameOrUrlOrDefaultUrl.lastIndexOf("/") + 1);
}
Map<String, Object> modifyField = baseMapper.isNeedModifyFieldProperty(databaseNameOrUrlOrDefaultUrl, tableId, field);
if (modifyField != null) {
//float(11,1)
Object column_type = modifyField.get("COLUMN_TYPE");
//YES NO
Object is_nullable = modifyField.get("IS_NULLABLE");
Object column_comment = modifyField.get("COLUMN_COMMENT");
if (!StringUtils.equals(getValueFromObject(columnType), getValueFromObject(column_type))) {
return true;
}
if (!StringUtils.equals(isNullable != null && isNullable ? "yes" : "no", getValueFromObject(is_nullable))) {
return true;
}
if (!StringUtils.equals(getValueFromObject(columnComment), getValueFromObject(column_comment))) {
return true;
}
}
return false;
} catch (Exception e) {
log.warn("检查{}是否缺少需要修改{}字段属性出现异常,请手动检查并执行,异常：{}", tableId, field, e);
}
return false;
}
public boolean isNeedChangeFieldName(String tableId, String oldFieldName, String newFieldName) {
return !isLackOfField(tableId, oldFieldName) && isLackOfField(tableId, newFieldName);
}
private String getValueFromObject(Object object){
if(object != null){
return String.valueOf(object).toLowerCase().replaceAll(" ","");
}
return null;
}
private void addLackOfTable(String tableId, String addTableSql) {
try {
baseMapper.isLackOfTable(tableId);
} catch (Exception e) {
String message = e.getMessage();
if (message != null
&& message.toLowerCase().contains("sqlsyntaxerrorexception")
&& message.contains("exist")
&& message.contains(tableId)) {
log.warn("缺少{}表,开始自动添加{}表,addTableSql:{},", tableId, tableId, addTableSql);
try {
baseMapper.executeSql(addTableSql);
} catch (Exception e1) {
log.warn("executeSql 出现异常:{}", e1);
}
}
}
}
public void addLackOfField(String tableId, String field, String addFieldSql) {
if (isLackOfField(tableId, field)) {
log.warn("{}缺少{}字段,开始自动添加字段,addFieldSql:{},", tableId, field, addFieldSql);
try {
baseMapper.executeSql(addFieldSql);;
} catch (Exception e) {
log.warn("executeSql 出现异常:{}", e);
}
}
}
public void addLackOfIndex(String tableId, String idx, String addIdxSql) {
if (isLackOfIndex(tableId, idx)) {
log.warn("{}缺少{}索引,开始自动添加索引，addIdxSql：{}", tableId, idx, addIdxSql);
try {
baseMapper.executeSql(addIdxSql);
} catch (Exception e) {
log.warn("executeSql 出现异常:{}", e);
}
}
}
public void modifyFieldProperty(String databaseNameOrUrlOrDefaultUrl, String tableId, String field,
String columnType, Boolean isNullable, String columnComment, String modifyFieldSql) {
//columnType: float(11,1)
//TODO 后期从 modifyFieldSql 中解析出 columnType isNullable columnComment，但是isNeedModifyFieldProperty()方法的参数必传
if (isNeedModifyFieldProperty(databaseNameOrUrlOrDefaultUrl, tableId, field, columnType, isNullable, columnComment)) {
log.warn("{}修改{}字段属性,开始自动修改字段属性，modifyFieldSql：{}", tableId, field, modifyFieldSql);
try {
baseMapper.executeSql(columnComment);
} catch (Exception e) {
log.warn("executeSql 出现异常:{}", e);
}
}
}
public void changeFieldName(String tableId,String oldFieldName, String newFieldName, String changeFieldNameSql) {
//旧的字段必须存在
//TODO 后期从 changeFieldNameSql 中解析出 oldFieldName newFieldName
if (isNeedChangeFieldName(tableId, oldFieldName, newFieldName)) {
log.warn("{}修改{}字段名称,开始自动改变字段名称，newFieldName：{}，changeFieldNameSql：{}", tableId, oldFieldName, newFieldName, changeFieldNameSql);
// ALTER TABLE `edu_train_manager `CHANGE COLUMN `test` `test2` float(3, 2) NOT NULL DEFAULT 1.10 COMMENT '11222' AFTER `handle_user_orgs_status`;
try {
baseMapper.executeSql(changeFieldNameSql);
} catch (Exception e) {
log.warn("executeSql 出现异常:{}", e);
}
}
}
//注意：如果是一条sql修改多个字段的话要拆分出来，避免一个字段修改失败，其他的也没执行
public void execSqlFromResource(boolean isNeedExec, String path) {
if (isNeedExec) {
try {
String[] sqlList = getSqlFromResource(path);
for (String sql : sqlList) {
try {
if(StringUtils.isBlank(sql)){
log.warn("execSqlFromResource解析到空sql");
continue;
}
baseMapper.executeSql(sql);
} catch (Exception e) {
String message = e.getMessage();
if (message == null) {
return;
}
message = message.toLowerCase();
if (!(message.contains("duplicate") || message.contains("exists"))) {
log.info("execSqlFromResource执行表结构修改出现异常，sql来源路径:{},当前sql：{}，异常：{}", path, sql, e.getMessage());
}
}
}
} catch (Exception ignore) {
}
}
}
public String[] getSqlFromResource(String path) {
try {
InputStream inputStream = this.getClass().getResourceAsStream(path);
String sqlStr = StreamUtil.readText(inputStream, "utf-8").replaceAll(INVALID_SYMBOL,"");
//sqlStr.length()/1024
//int kb = sqlStr.getBytes().length / 1024;
//String[] sqlList = null;
// if (kb > 200) {
// sqlList = splitIgnoreQuotaNotUsingRegex(sqlStr, ";");
//} else {
// sqlList = splitIgnoreQuotaUsingRegex(sqlStr, ";");
// }
// 实测上面还是内存溢出
String[] sqlList = splitIgnoreQuotaNotUsingRegex(sqlStr, ";");
return sqlList;
} catch (Exception e) {
log.error("读取刷表结构变更的sql出现异常：sql名称：{}，异常：{}", path, e);
return new String[0];
}
}
///////////////////////////////////////////////下面都是私有方法/////////////////////////////////////////////
// public static void main(String[] args) {
// String str = "数据分析师,14k-25k,1-3年,北京,花生米富,150-500人,朝阳区,B轮,\'移动互联网,金融\',数据分析,2,100";
// // str = "数据分析师,14k-25k,1-3年,北京,花生米富,150-500人,朝阳区,B轮,\'移动互联网,金融\',数据分析,2,100";
// System.out.println("通过正则的结果");
// // 使用正则表达式匹配
// String[] fields = splitIgnoreQuotaUsingRegex(str,",");
// for (String s:fields){
// System.out.println(s);
// }
//
// fields = splitIgnoreQuotaNotUsingRegex(str,",");
//
// // 遍历输出拆分的结果
// for (String s:fields){
// System.out.println(s);
// }
// }
/**
* 使用正则表达式的方法来实现 `根据指定分隔符分割字符串---忽略在[单双（配对出现）]引号里面的分隔符`
* 相关参考：
* https://blog.csdn.net/qq_45797116/article/details/115445901
* https://www.cnblogs.com/zhazhaacmer/p/12357949.html
* @param text
* @param delimiter 分隔符
* @return
*/
public static String[] splitIgnoreQuotaUsingRegex(String text, String delimiter) {
//带-1参数最后会有一个空字符串
//return text.split(delimiter + "(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)(?=(?:[^']*'[^']*')*[^']*$)", -1);
return text.split(delimiter + "(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)(?=(?:[^']*'[^']*')*[^']*$)");
}
/**
* 使用非正则表达式的方法来实现 `根据指定分隔符分割字符串---忽略在[单双（配对出现）]引号里面的分隔符`
* 数据量超过200k就使用这个代码
* 相关参考：
* https://blog.csdn.net/qq_45797116/article/details/115445901
* https://www.cnblogs.com/zhazhaacmer/p/12357949.html
* @param text
* @param delimiter 分隔符
* @return
*/
public static String[] splitIgnoreQuotaNotUsingRegex(String text, String delimiter) {
//trim
text = text.trim();
// 遍历出成对的双引号的位置区间,排除转义的双引号
List<CopyPair<Integer, Integer>> doubleQuotas = getQuotaIndexPairs(text, '\"');
// 遍历出成对的单引号的位置区间,排除转义的单引号
List<CopyPair<Integer, Integer>> singleQuotas = getQuotaIndexPairs(text, '\'');
// 遍历出所有的delimiter的位置,排除掉在上述两个区间中的,排除掉转义的,按该delimiter位置拆分字符串
List<String> splitList = new ArrayList<>(128);
// index 表示目前搜索指针下标
// beforeIndex 表示目前已经成功匹配到的指针下标
int index = 0, beforeIndex = -1;
while ((index = text.indexOf(delimiter, Math.max(beforeIndex + 1, index))) != -1) {
// 排除转义
if (index == 0 || text.charAt(index - 1) != '\\') {
boolean flag = false;
// 排除双引号内的
for (CopyPair<Integer, Integer> p : doubleQuotas) {
if (p.getKey() <= index && p.getValue() >= index) {
flag = true;
break;
}
}
// 排除单引号内的
for (int i = 0; !flag && i < singleQuotas.size(); i++) {
CopyPair<Integer, Integer> p = singleQuotas.get(i);
if (p.getKey() <= index && p.getValue() >= index) {
flag = true;
break;
}
}
// flag = true, 表示该字符串在匹配的成对引号,跳过
if(flag){
index++;
continue;
}
// 这里的substring只取到分隔符的前一位,分隔符不加进来
splitList.add(text.substring(beforeIndex + 1, index));
beforeIndex = index;
} else {
index++;
}
}
// 收尾串【这个会返回一个空字符串】，和text.split()的第2个参数相同
if (beforeIndex != text.length()) {
String lastOne = text.substring(beforeIndex + 1);
if (StringUtils.isNotBlank(lastOne)) {
splitList.add(lastOne);
}
}
return splitList.toArray(new String[0]);
}
/**
* 遍历出成对的双/单引号的位置区间,排除转义的双引号
* @param str
* @param quotaChar
* @return
*/
private static List<CopyPair<Integer, Integer>> getQuotaIndexPairs(String str, char quotaChar) {
List<CopyPair<Integer, Integer>> quotaPairs = new ArrayList<>(64);
List<Integer> posList = new ArrayList<>(128);
for (int idx = 0; idx < str.length(); idx++) {
if (str.charAt(idx) == quotaChar) {
if (idx == 0 || str.charAt(idx - 1) != '\\') {
posList.add(idx);
}
}
}
// 每两个装进Pair中,总数为奇数的话最后一个舍掉
for (int idx = 0; idx <= posList.size() - 2; idx += 2) {
quotaPairs.add(new CopyPair<>(posList.get(idx), posList.get(idx + 1)));
}
return quotaPairs;
}
/**
* 原来 import javafx.util.Pair;
*/
private static class CopyPair<K, V> implements Serializable {
private K key;
public K getKey() {
return key;
}
private V value;
public V getValue() {
return value;
}
private CopyPair(K key, V value) {
this.key = key;
this.value = value;
}
@Override
public String toString() {
return key + "=" + value;
}
@Override
public int hashCode() {
// name's hashCode is multiplied by an arbitrary prime number (13)
// in order to make sure there is a difference in the hashCode between
// these two parameters:
// name: a value: aa
// name: aa value: a
return key.hashCode() * 13 + (value == null ? 0 : value.hashCode());
}
/**
* <p>Test this <code>CopyPair</code> for equality with another
* <code>Object</code>.</p>
*
* <p>If the <code>Object</code> to be tested is not a
* <code>CopyPair</code> or is <code>null</code>, then this method
* returns <code>false</code>.</p>
*
* <p>Two <code>CopyPair</code>s are considered equal if and only if
* both the names and values are equal.</p>
*
* @param o the <code>Object</code> to test for
* equality with this <code>CopyPair</code>
* @return <code>true</code> if the given <code>Object</code> is
* equal to this <code>CopyPair</code> else <code>false</code>
*/
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o instanceof CopyPair) {
CopyPair pair = (CopyPair) o;
if (key != null ? !key.equals(pair.key) : pair.key != null) return false;
if (value != null ? !value.equals(pair.value) : pair.value != null) return false;
return true;
}
return false;
}
}
}
BaseMapper.java
package cn.jiangjiesheng.edu.mapper;
import org.apache.ibatis.annotations.Param;
public interface BaseMapper {
//或者引入版本的概念
Object isLackOfTable(@Param("tableId") String tableId);
Object isLackOfField(@Param("tableId") String tableId, @Param("field") String field);
List<String> isLackOfIndex(@Param("tableId") String tableId, @Param("idx") String idx);
//MODIFY COLUMN
Map<String,Object> isNeedModifyFieldProperty(@Param("databaseName") String databaseName, @Param("tableId") String tableId, @Param("field") String field);
void executeSql(@Param("sql") String sql);
}
BaseMapper.xml
<select id="isLackOfTable" resultType="integer">
select 1 from ${tableId} limit 1
</select>
<select id="isLackOfField" resultType="object" >
select ${field} from ${tableId} limit 1
</select>
<select id="isLackOfIndex" resultType="string">
show index from ${tableId} WHERE key_name = #{idx}
</select>
<!--https://www.jb51.net/article/206936.htm -->
<!--修改字段属性，不包括字段本身 -->
<select id="isNeedModifyFieldProperty" resultType="java.util.HashMap">
SELECT
*
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = #{databaseName}
AND TABLE_NAME = #{tableId}
AND COLUMN_NAME = #{field}
LIMIT 1
</select>
<update id="executeSql">
<![CDATA[ ${sql} ]]>
</update>
// 一次性初始化基础版本的代码
@Transactional(timeout = 50)
public void checkTableOrSyncData_V_1_0_1_202210() {
if (isLackOfTable("eduinp_attachment")) {
try {
InputStream inputStream = this.getClass().getResourceAsStream("/database/eduinp_v1_0_1.sql");
String sql = StreamUtil.readText(inputStream, "utf-8");
String[] tables = sql.split(";");
for (String table : tables) {
if (!table.toLowerCase().replaceAll(" ", "").contains("createtable")) {
continue;
}
//允许报错后回滚
baseMapper.executeSql(table);
}
} catch (Exception ignore) {
}
}
}
import com.alibaba.fastjson.util.IOUtils;
import lombok.extern.slf4j.Slf4j;
import lombok.var;
import java.io.*;
/**
*
* InputStream输入流中读取文本
*
*/
@Slf4j
public class StreamUtil {
/**
* @deprecated IOUtils.readAll
*/
static String readText_ali(InputStream inputStream) {
try {
InputStreamReader dataReader = new InputStreamReader(inputStream);
String result = IOUtils.readAll(dataReader);
dataReader.close();
inputStream.close();
return result;
} catch (Exception ex) {
log.error("Stream2Text error! readText_ali,ex:{}", ex);
return ex.toString();
}
}
/**
* @deprecated java.io.EOFException
*/
static String readText_readUtf(InputStream inputStream) {
try {
var dataInputStream = new DataInputStream(inputStream);
var result = dataInputStream.readUTF();
dataInputStream.close();
inputStream.close();
return result;
} catch (Exception ex) {
log.error("Stream2Text error! readText_readUtf,ex:{}", ex);
return ex.toString();
}
}
/**
* @deprecated 这个是有缺陷的，如果换行符是\n不是\r\n那么文本就变了
*/
static String readText_bufferedReader(InputStream inputStream) {
try {
var inputStreamReader = new InputStreamReader(inputStream);
var bufferedReader = new BufferedReader(inputStreamReader);
String line = null;
StringBuilder stringBuilder = new StringBuilder();
while ((line = bufferedReader.readLine()) != null)
stringBuilder.append(line + "\r\n");
bufferedReader.close();
inputStreamReader.close();
inputStream.close();
return stringBuilder.toString();
} catch (Exception ex) {
log.error("Stream2Text error! getText_bufferedReader,ex:{}", ex);
return ex.toString();
}
}
static String readText_bytesOut(InputStream inputStream, String charset) {
try {
var byteArrayOutputStream = new ByteArrayOutputStream();
int _byte;
while ((_byte = inputStream.read()) != -1)
byteArrayOutputStream.write(_byte);
var result = byteArrayOutputStream.toString(charset);
byteArrayOutputStream.close();
inputStream.close();
return result;
} catch (Exception ex) {
log.error("Stream2Text error! readText_bytesOut,ex:{}", ex);
return ex.toString();
}
}
public static String readText(InputStream inputStream, String charset) {
return readText_bytesOut(inputStream, charset);
}
public static byte[] readBytes(InputStream inputStream) {
try {
var byteArrayOutputStream = new ByteArrayOutputStream();
int _byte;
while ((_byte = inputStream.read()) != -1)
byteArrayOutputStream.write(_byte);
var result = byteArrayOutputStream.toByteArray();
byteArrayOutputStream.close();
inputStream.close();
return result;
} catch (Exception ex) {
ex.printStackTrace();
return null;
}
}
}
正文到此结束
热门推荐
相关文章
近期评论
-
来自： 留言板
-
来自： 留言板
-
来自： js-处理网页图片宽度自适应屏幕宽度
-
来自： 留言板
-
来自： SpringBoot-线上服务卡死卡顿分析-http请求进不去服务-调整tomcat-undertow线程数
