今天介紹一個(gè) MyBatis – Plus 官方發(fā)布的神器:mybatis-mate 為 mp 企業(yè)級模塊,支持分庫分表,數(shù)據(jù)審計(jì)、數(shù)據(jù)敏感詞過濾(AC算法),字段加密,字典回寫(數(shù)據(jù)綁定),數(shù)據(jù)權(quán)限,表結(jié)構(gòu)自動生成 SQL 維護(hù)等,旨在更敏捷優(yōu)雅處理數(shù)據(jù)。
1. 主要功能
- 字典綁定
- 字段加密
- 數(shù)據(jù)脫敏
- 表結(jié)構(gòu)動態(tài)維護(hù)
- 數(shù)據(jù)審計(jì)記錄
- 數(shù)據(jù)范圍(數(shù)據(jù)權(quán)限)
- 數(shù)據(jù)庫分庫分表、動態(tài)據(jù)源、讀寫分離、數(shù)- – 據(jù)庫健康檢查自動切換。
2.使用
2.1 依賴導(dǎo)入
Spring Boot 引入自動依賴注解包
com.baomidoumybatis-mate-starter1.0.8
注解(實(shí)體分包使用)
com.baomidoumybatis-mate-annotation1.0.8
2.2 字段數(shù)據(jù)綁定(字典回寫)
例如 user_sex 類型 sex 字典結(jié)果映射到 sexText 屬性
@FieldDict(type = “user_sex”, target = “sexText”)private Integer sex;private String sexText;
實(shí)現(xiàn) IDataDict 接口提供字典數(shù)據(jù)源,注入到 Spring 容器即可。
@Componentpublic class DataDict implements IDataDict {/*** 從數(shù)據(jù)庫或緩存中獲取*/private Map SEX_MAP = new ConcurrentHashMap() {{put(“0”, “女”);put(“1”, “男”);}};@Overridepublic String getNameByCode(FieldDict fieldDict, String code) {System.err.println(“字段類型:” + fieldDict.type() + “,編碼:” + code);return SEX_MAP.get(code);}}
2.3 字段加密
屬性 @FieldEncrypt 注解即可加密存儲,會自動解密查詢結(jié)果,支持全局配置加密密鑰算法,及注解密鑰算法,可以實(shí)現(xiàn) IEncryptor 注入自定義算法。
@FieldEncrypt(algorithm = Algorithm.PBEWithMD5AndDES)private String password;
2.4 字段脫敏
屬性 @FieldSensitive 注解即可自動按照預(yù)設(shè)策略對源數(shù)據(jù)進(jìn)行脫敏處理,默認(rèn) SensitiveType 內(nèi)置 9 種常用脫敏策略。
例如:中文名、銀行卡賬號、手機(jī)號碼等 脫敏策略。也可以自定義策略如下:
@FieldSensitive(type = “testStrategy”)private String username;@FieldSensitive(type = SensitiveType.mobile)private String mobile;
自定義脫敏策略 testStrategy 添加到默認(rèn)策略中注入 Spring 容器即可。
@Configurationpublic class SensitiveStrategyConfig {/*** 注入脫敏策略*/@Beanpublic ISensitiveStrategy sensitiveStrategy() {// 自定義 testStrategy 類型脫敏處理return new SensitiveStrategy().addStrategy(“testStrategy”, t -> t + “***test***”);}}
例如:文章敏感詞過濾
/*** 演示文章敏感詞過濾*/@RestControllerpublic class ArticleController {@Autowiredprivate SensitiveWordsMapper sensitiveWordsMapper;// 測試訪問下面地址觀察請求地址、界面返回?cái)?shù)據(jù)及控制臺( 普通參數(shù) )// 無敏感詞 http://localhost:8080/info?content=tom&see=1&age=18// 英文敏感詞 http://localhost:8080/info?content=my%20content%20is%20tomcat&see=1&age=18// 漢字敏感詞 http://localhost:8080/info?content=%E7%8E%8B%E5%AE%89%E7%9F%B3%E5%94%90%E5%AE%8B%E5%85%AB%E5%A4%A7%E5%AE%B6&see=1// 多個(gè)敏感詞 http://localhost:8080/info?content=%E7%8E%8B%E5%AE%89%E7%9F%B3%E6%9C%89%E4%B8%80%E5%8F%AA%E7%8C%ABtomcat%E6%B1%A4%E5%A7%86%E5%87%AF%E7%89%B9&see=1&size=6// 插入一個(gè)字變成非敏感詞 http://localhost:8080/info?content=%E7%8E%8B%E7%8C%AB%E5%AE%89%E7%9F%B3%E6%9C%89%E4%B8%80%E5%8F%AA%E7%8C%ABtomcat%E6%B1%A4%E5%A7%86%E5%87%AF%E7%89%B9&see=1&size=6@GetMapping(“/info”)public String info(Article article) throws Exception {return ParamsConfig.toJson(article);}// 添加一個(gè)敏感詞然后再去觀察是否生效 http://localhost:8080/add// 觀察【貓】這個(gè)詞被過濾了 http://localhost:8080/info?content=%E7%8E%8B%E5%AE%89%E7%9F%B3%E6%9C%89%E4%B8%80%E5%8F%AA%E7%8C%ABtomcat%E6%B1%A4%E5%A7%86%E5%87%AF%E7%89%B9&see=1&size=6// 嵌套敏感詞處理 http://localhost:8080/info?content=%E7%8E%8B%E7%8C%AB%E5%AE%89%E7%9F%B3%E6%9C%89%E4%B8%80%E5%8F%AA%E7%8C%ABtomcat%E6%B1%A4%E5%A7%86%E5%87%AF%E7%89%B9&see=1&size=6// 多層嵌套敏感詞 http://localhost:8080/info?content=%E7%8E%8B%E7%8E%8B%E7%8C%AB%E5%AE%89%E7%9F%B3%E5%AE%89%E7%9F%B3%E6%9C%89%E4%B8%80%E5%8F%AA%E7%8C%ABtomcat%E6%B1%A4%E5%A7%86%E5%87%AF%E7%89%B9&see=1&size=6@GetMapping(“/add”)public String add() throws Exception {Long id = 3L;if (null == sensitiveWordsMapper.selectById(id)) {System.err.println(“插入一個(gè)敏感詞:” + sensitiveWordsMapper.insert(new SensitiveWords(id, “貓”)));// 插入一個(gè)敏感詞,刷新算法引擎敏感詞SensitiveWordsProcessor.reloadSensitiveWords();}return “ok”;}// 測試訪問下面地址觀察控制臺( 請求json參數(shù) )// idea 執(zhí)行 resources 目錄 TestJson.http 文件測試@PostMapping(“/json”)public String json(@RequestBody Article article) throws Exception {return ParamsConfig.toJson(article);}}
2.5 DDL 數(shù)據(jù)結(jié)構(gòu)自動維護(hù)
解決升級表結(jié)構(gòu)初始化,版本發(fā)布更新 SQL 維護(hù)問題,目前支持 MySql、PostgreSQL。
@Componentpublic class PostgresDdl implements IDdl {/*** 執(zhí)行 SQL 腳本方式*/@Overridepublic List getSqlFiles() {return Arrays.asList(// 內(nèi)置包方式”db/tag-schema.sql”,// 文件絕對路徑方式”D:dbtag-data.sql”);}}
不僅僅可以固定執(zhí)行,也可以動態(tài)執(zhí)行?。?/p>
ddlScript.run(new StringReader(“DELETE FROM user;” +”INSERT INTO user (id, username, password, sex, email) VALUES” +”(20, ‘Duo’, ‘123456’, 0, ‘Duo@baomidou.com’);”));
它還支持多數(shù)據(jù)源執(zhí)行?。?!
@Componentpublic class MysqlDdl implements IDdl {@Overridepublic void sharding(Consumer consumer) {// 多數(shù)據(jù)源指定,主庫初始化從庫自動同步String group = “mysql”;ShardingGroupProperty sgp = ShardingKey.getDbGroupProperty(group);if (null != sgp) {// 主庫sgp.getMasterKeys().forEach(key -> {ShardingKey.change(group + key);consumer.accept(this);});// 從庫sgp.getSlaveKeys().forEach(key -> {ShardingKey.change(group + key);consumer.accept(this);});}}/*** 執(zhí)行 SQL 腳本方式*/@Overridepublic List getSqlFiles() {return Arrays.asList(“db/user-mysql.sql”);}}
2.6 動態(tài)多數(shù)據(jù)源主從自由切換
@Sharding 注解使數(shù)據(jù)源不限制隨意使用切換,你可以在 mapper 層添加注解,按需求指哪打哪?。?/p>
@Mapper@Sharding(“mysql”)public interface UserMapper extends BaseMapper {@Sharding(“postgres”)Long selectByUsername(String username);}
你也可以自定義策略統(tǒng)一調(diào)兵遣將
@Componentpublic class MyShardingStrategy extends RandomShardingStrategy {/*** 決定切換數(shù)據(jù)源 key {@link ShardingDatasource}** @param group 動態(tài)數(shù)據(jù)庫組* @param invocation {@link Invocation}* @param sqlCommandType {@link SqlCommandType}*/@Overridepublic void determineDatasourceKey(String group, Invocation invocation, SqlCommandType sqlCommandType) {// 數(shù)據(jù)源組 group 自定義選擇即可, keys 為數(shù)據(jù)源組內(nèi)主從多節(jié)點(diǎn),可隨機(jī)選擇或者自己控制this.changeDatabaseKey(group, sqlCommandType, keys -> chooseKey(keys, invocation));}}
可以開啟主從策略,當(dāng)然也是可以開啟健康檢查!具體配置:
mybatis-mate:sharding:health: true # 健康檢測primary: mysql # 默認(rèn)選擇數(shù)據(jù)源datasource:mysql: # 數(shù)據(jù)庫組- key: node1…- key: node2cluster: slave # 從庫讀寫分離時(shí)候負(fù)責(zé) sql 查詢操作,主庫 master 默認(rèn)可以不寫…postgres:- key: node1 # 數(shù)據(jù)節(jié)點(diǎn)…
2.7 分布式事務(wù)日志打印
部分配置如下:
/***
* 性能分析攔截器,用于輸出每條 SQL 語句及其執(zhí)行時(shí)間*
*/@Slf4j@Component@Intercepts({@Signature(type = StatementHandler.class, method = “query”, args = {Statement.class, ResultHandler.class}),@Signature(type = StatementHandler.class, method = “update”, args = {Statement.class}),@Signature(type = StatementHandler.class, method = “batch”, args = {Statement.class})})public class PerformanceInterceptor implements Interceptor {/*** SQL 執(zhí)行最大時(shí)長,超過自動停止運(yùn)行,有助于發(fā)現(xiàn)問題。*/private long maxTime = 0;/*** SQL 是否格式化*/private boolean format = false;/*** 是否寫入日志文件* true 寫入日志文件,不阻斷程序執(zhí)行!* 超過設(shè)定的最大執(zhí)行時(shí)長異常提示!*/private boolean writeInLog = false;@Overridepublic Object intercept(Invocation invocation) throws Throwable {Statement statement;Object firstArg = invocation.getArgs()[0];if (Proxy.isProxyClass(firstArg.getClass())) {statement = (Statement) SystemMetaObject.forObject(firstArg).getValue(“h.statement”);} else {statement = (Statement) firstArg;}MetaObject stmtMetaObj = SystemMetaObject.forObject(statement);try {statement = (Statement) stmtMetaObj.getValue(“stmt.statement”);} catch (Exception e) {// do nothing}if (stmtMetaObj.hasGetter(“delegate”)) {//Hikaritry {statement = (Statement) stmtMetaObj.getValue(“delegate”);} catch (Exception e) {}}String originalSql = null;if (originalSql == null) {originalSql = statement.toString();}originalSql = originalSql.replaceAll(“[s]+”, ” “);int index = indexOfSqlStart(originalSql);if (index > 0) {originalSql = originalSql.substring(index);}// 計(jì)算執(zhí)行 SQL 耗時(shí)long start = SystemClock.now();Object result = invocation.proceed();long timing = SystemClock.now() – start;// 格式化 SQL 打印執(zhí)行結(jié)果Object target = PluginUtils.realTarget(invocation.getTarget());MetaObject metaObject = SystemMetaObject.forObject(target);MappedStatement ms = (MappedStatement) metaObject.getValue(“delegate.mappedStatement”);StringBuilder formatSql = new StringBuilder();formatSql.append(” Time:”).append(timing);formatSql.append(” ms – ID:”).append(ms.getId());formatSql.append(” Execute SQL:”).append(sqlFormat(originalSql, format)).append(“”);if (this.isWriteInLog()) {if (this.getMaxTime() >= 1 && timing > this.getMaxTime()) {log.error(formatSql.toString());} else {log.debug(formatSql.toString());}} else {System.err.println(formatSql);if (this.getMaxTime() >= 1 && timing > this.getMaxTime()) {throw new RuntimeException(” The SQL execution time is too large, please optimize ! “);}}return result;}@Overridepublic Object plugin(Object target) {if (target instanceof StatementHandler) {return Plugin.wrap(target, this);}return target;}@Overridepublic void setProperties(Properties prop) {String maxTime = prop.getProperty(“maxTime”);String format = prop.getProperty(“format”);if (StringUtils.isNotEmpty(maxTime)) {this.maxTime = Long.parseLong(maxTime);}if (StringUtils.isNotEmpty(format)) {this.format = Boolean.valueOf(format);}}public long getMaxTime() {return maxTime;}public PerformanceInterceptor setMaxTime(long maxTime) {this.maxTime = maxTime;return this;}public boolean isFormat() {return format;}public PerformanceInterceptor setFormat(boolean format) {this.format = format;return this;}public boolean isWriteInLog() {return writeInLog;}public PerformanceInterceptor setWriteInLog(boolean writeInLog) {this.writeInLog = writeInLog;return this;}public Method getMethodRegular(Class clazz, String methodName) {if (Object.class.equals(clazz)) {return null;}for (Method method : clazz.getDeclaredMethods()) {if (method.getName().equals(methodName)) {return method;}}return getMethodRegular(clazz.getSuperclass(), methodName);}/*** 獲取sql語句開頭部分** @param sql* @return*/private int indexOfSqlStart(String sql) {String upperCaseSql = sql.toUpperCase();Set set = new HashSet();set.add(upperCaseSql.indexOf(“SELECT “));set.add(upperCaseSql.indexOf(“UPDATE “));set.add(upperCaseSql.indexOf(“INSERT “));set.add(upperCaseSql.indexOf(“DELETE “));set.remove(-1);if (CollectionUtils.isEmpty(set)) {return -1;}List list = new ArrayList(set);Collections.sort(list, Integer::compareTo);return list.get(0);}private final static SqlFormatter sqlFormatter = new SqlFormatter();/*** 格式sql** @param boundSql* @param format* @return*/public static String sqlFormat(String boundSql, boolean format) {if (format) {try {return sqlFormatter.format(boundSql);} catch (Exception ignored) {}}return boundSql;}}
使用:
@RestController@AllArgsConstructorpublic class TestController {private BuyService buyService;// 數(shù)據(jù)庫 test 表 t_order 在事務(wù)一致情況無法插入數(shù)據(jù),能夠插入說明多數(shù)據(jù)源事務(wù)無效// 測試訪問 http://localhost:8080/test// 制造事務(wù)回滾 http://localhost:8080/test?error=true 也可通過修改表結(jié)構(gòu)制造錯(cuò)誤// 注釋 ShardingConfig 注入 dataSourceProvider 可測試事務(wù)無效情況@GetMapping(“/test”)public String test(Boolean error) {return buyService.buy(null != error && error);}}
2.8 數(shù)據(jù)權(quán)限
mapper 層添加注解:
// 測試 test 類型數(shù)據(jù)權(quán)限范圍,混合分頁模式@DataScope(type = “test”, value = {// 關(guān)聯(lián)表 user 別名 u 指定部門字段權(quán)限@DataColumn(alias = “u”, name = “department_id”),// 關(guān)聯(lián)表 user 別名 u 指定手機(jī)號字段(自己判斷處理)@DataColumn(alias = “u”, name = “mobile”)})@Select(“select u.* from user u”)List selectTestList(IPage page, Long id, @Param(“name”) String username);
模擬業(yè)務(wù)處理邏輯:
@Beanpublic IDataScopeProvider dataScopeProvider() {return new AbstractDataScopeProvider() {@Overrideprotected void setWhere(PlainSelect plainSelect, Object[] args, DataScopeProperty dataScopeProperty) {// args 中包含 mapper 方法的請求參數(shù),需要使用可以自行獲取/*// 測試數(shù)據(jù)權(quán)限,最終執(zhí)行 SQL 語句SELECT u.* FROM user u WHERE (u.department_id IN (‘1’, ‘2’, ‘3’, ‘5’))AND u.mobile LIKE ‘%1533%’*/if (“test”.equals(dataScopeProperty.getType())) {// 業(yè)務(wù) test 類型List dataColumns = dataScopeProperty.getColumns();for (DataColumnProperty dataColumn : dataColumns) {if (“department_id”.equals(dataColumn.getName())) {// 追加部門字段 IN 條件,也可以是 SQL 語句Set deptIds = new HashSet();deptIds.add(“1”);deptIds.add(“2”);deptIds.add(“3”);deptIds.add(“5”);ItemsList itemsList = new ExpressionList(deptIds.stream().map(StringValue::new).collect(Collectors.toList()));InExpression inExpression = new InExpression(new Column(dataColumn.getAliasDotName()), itemsList);if (null == plainSelect.getWhere()) {// 不存在 where 條件plainSelect.setWhere(new Parenthesis(inExpression));} else {// 存在 where 條件 and 處理plainSelect.setWhere(new AndExpression(plainSelect.getWhere(), inExpression));}} else if (“mobile”.equals(dataColumn.getName())) {// 支持一個(gè)自定義條件LikeExpression likeExpression = new LikeExpression();likeExpression.setLeftExpression(new Column(dataColumn.getAliasDotName()));likeExpression.setRightExpression(new StringValue(“%1533%”));plainSelect.setWhere(new AndExpression(plainSelect.getWhere(), likeExpression));}}}}};}
最終執(zhí)行 SQL 輸出:
SELECT u.* FROM user uWHERE (u.department_id IN (‘1’, ‘2’, ‘3’, ‘5’))AND u.mobile LIKE ‘%1533%’ LIMIT 1, 10
目前僅有付費(fèi)版本,了解更多 mybatis-mate 使用示例詳見:
https://gitee.com/baomidou/mybatis-mate-example
原文鏈接:https://mp.weixin.qq.com/s/3Uim4i5YK4QWL4GHiNpjdA