Today, we will introduce an artifact officially released by MyBatis - Plus: mybatis mate is an mp enterprise level module that supports database and table separation, data audit, data sensitive word filtering (AC algorithm), field encryption, dictionary write back (data binding), data permissions, automatic SQL maintenance of table structure, etc., aiming at more agile and elegant data processing.
major function
- Dictionary binding
- Field encryption
- Data desensitization
- Table structure dynamic maintenance
- Data audit records
- Data range (Data permission)
- The database is divided into databases and tables, dynamic data sources, read-write separation, and automatic switching of data database health check.
2. Use
2.1 dependency import
Spring Boot introduces automatic dependency annotation package
<dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-mate-starter</artifactId> <version>1.0.8</version> </dependency>
Notes (for entity subcontracting)
<dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-mate-annotation</artifactId> <version>1.0.8</version> </dependency>
2.2 field data binding (Dictionary write back)
For example, user_sex type sex dictionary result mapped to sexText property
@FieldDict(type = "user_sex", target = "sexText") private Integer sex; private String sexText;
Implement the IDataDict interface to provide the dictionary data source and inject it into the Spring container.
@Component
public class DataDict implements IDataDict {
/**
* Get from database or cache
*/
private Map<String, String> SEX_MAP = new ConcurrentHashMap<String, String>() {{
put("0", "female");
put("1", "male");
}};
@Override
public String getNameByCode(FieldDict fieldDict, String code) {
System.err.println("Field type:" + fieldDict.type() + "，code:" + code);
return SEX_MAP.get(code);
}
}2.3 field encryption
The attribute @FieldEncrypt annotation can be encrypted and stored, and the query results will be automatically decrypted. It supports the global configuration of encryption key algorithm and annotation key algorithm, and can implement the IEncryptor injection custom algorithm.
@FieldEncrypt(algorithm = Algorithm.PBEWithMD5AndDES) private String password;
2.4 field desensitization
Attribute @FieldSensitive annotation can automatically desensitize the source data according to the preset policy. By default, 9 common desensitization policies are built into the sentitivetype.
For example, desensitization strategies such as Chinese name, bank card account number and mobile phone number. You can also customize the policy as follows:
@FieldSensitive(type = "testStrategy") private String username; @FieldSensitive(type = SensitiveType.mobile) private String mobile;
Add the custom desensitization policy testStrategy to the default policy and inject it into the Spring container.
@Configuration
public class SensitiveStrategyConfig {
/**
* Injection desensitization strategy
*/
@Bean
public ISensitiveStrategy sensitiveStrategy() {
// Custom testStrategy type desensitization
return new SensitiveStrategy().addStrategy("testStrategy", t -> t + "***test***");
}
}For example, article sensitive word filtering
/**
* Demonstration Article sensitive word filtering
*/
@RestController
public class ArticleController {
@Autowired
private SensitiveWordsMapper sensitiveWordsMapper;
// Test access to the following address to observe the request address, interface return data and console (common parameters)
// No sensitive words http://localhost:8080/info?content=tom&see=1&age=18
// English sensitive words http://localhost:8080/info?content=my%20content%20is%20tomcat&see=1&age=18
// Chinese sensitive words 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
// Multiple sensitive words 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
// Insert a word to make it insensitive 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);
}
// Add a sensitive word and see if it works http://localhost:8080/add
// Observe that the word "cat" is filtered 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
// Nested sensitive word processing 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
// Multi nested sensitive words 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("Insert a sensitive word:" + sensitiveWordsMapper.insert(new SensitiveWords(id, "cat")));
// Insert a sensitive word and refresh the sensitive word of the algorithm engine
SensitiveWordsProcessor.reloadSensitiveWords();
}
return "ok";
}
// Test access the following address observation console (request json parameters)
// idea executes the resources directory testjson Http file test
@PostMapping("/json")
public String json(@RequestBody Article article) throws Exception {
return ParamsConfig.toJson(article);
}
}2.5 automatic maintenance of DDL data structure
Solve the problem of upgrading table structure initialization and updating SQL maintenance after version release. Currently, MySql and PostgreSQL are supported.
@Component
public class PostgresDdl implements IDdl {
/**
* Execute SQL script mode
*/
@Override
public List<String> getSqlFiles() {
return Arrays.asList(
// Built in package method
"db/tag-schema.sql",
// File absolute path mode
"D:\\db\\tag-data.sql"
);
}
}Not only can it be executed in a fixed way, but also it can be executed dynamically!!
ddlScript.run(new StringReader("DELETE FROM user;\n" +
"INSERT INTO user (id, username, password, sex, email) VALUES\n" +
"(20, 'Duo', '123456', 0, 'Duo@baomidou.com');"));It also supports multi data source execution!!!
@Component
public class MysqlDdl implements IDdl {
@Override
public void sharding(Consumer<IDdl> consumer) {
// Multi data source specification, master library initialization, slave library automatic synchronization
String group = "mysql";
ShardingGroupProperty sgp = ShardingKey.getDbGroupProperty(group);
if (null != sgp) {
// Main library
sgp.getMasterKeys().forEach(key -> {
ShardingKey.change(group + key);
consumer.accept(this);
});
// From library
sgp.getSlaveKeys().forEach(key -> {
ShardingKey.change(group + key);
consumer.accept(this);
});
}
}
/**
* Execute SQL script mode
*/
@Override
public List<String> getSqlFiles() {
return Arrays.asList("db/user-mysql.sql");
}
}2.6 dynamic multi data source master-slave free switching
@Sharding annotations enable the data source to be used and switched freely. You can add annotations in the mapper layer and hit whatever you want!!
@Mapper
@Sharding("mysql")
public interface UserMapper extends BaseMapper<User> {
@Sharding("postgres")
Long selectByUsername(String username);
}You can also customize the strategy for unified deployment
@Component
public class MyShardingStrategy extends RandomShardingStrategy {
/**
* Decide to switch data source key {@link ShardingDatasource}
*
* @param group Dynamic database group
* @param invocation {@link Invocation}
* @param sqlCommandType {@link SqlCommandType}
*/
@Override
public void determineDatasourceKey(String group, Invocation invocation, SqlCommandType sqlCommandType) {
// The data source group can be selected by user. The keys are the master-slave nodes in the data source group, which can be selected randomly or controlled by yourself
this.changeDatabaseKey(group, sqlCommandType, keys -> chooseKey(keys, invocation));
}
}You can start the master-slave strategy, and of course, you can start the health check! Specific configuration:
mybatis-mate:
sharding:
health: true # Health testing
primary: mysql # Default select data source
datasource:
mysql: # Database group
- key: node1
...
- key: node2
cluster: slave # The master database is responsible for sql query operations during the separation of read and write from the database. The master database may not write by default
...
postgres:
- key: node1 # Data node
...2.7 distributed transaction log printing
Some configurations are as follows:
/**
* <p>
* Performance analysis interceptor, used to output each SQL statement and its execution time
* </p>
*/
@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 The maximum execution time exceeds the automatic shutdown, which is helpful to find problems.
*/
private long maxTime = 0;
/**
* SQL Format or not
*/
private boolean format = false;
/**
* Whether to write log file <br>
* true Write the log file without blocking the program execution< br>
* Abnormal prompt for exceeding the set maximum execution time!
*/
private boolean writeInLog = false;
@Override
public 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")) {//Hikari
try {
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);
}
// Calculate SQL execution time
long start = SystemClock.now();
Object result = invocation.proceed();
long timing = SystemClock.now() - start;
// Format SQL print execution results
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("\n Execute SQL: ").append(sqlFormat(originalSql, format)).append("\n");
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;
}
@Override
public Object plugin(Object target) {
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
}
return target;
}
@Override
public 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);
}
/**
* Get the beginning of sql statement
*
* @param sql
* @return
*/
private int indexOfSqlStart(String sql) {
String upperCaseSql = sql.toUpperCase();
Set<Integer> 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<Integer> list = new ArrayList<>(set);
Collections.sort(list, Integer::compareTo);
return list.get(0);
}
private final static SqlFormatter sqlFormatter = new SqlFormatter();
/**
* Format 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;
}
}use:
@RestController
@AllArgsConstructor
public class TestController {
private BuyService buyService;
// Database test table t_order cannot insert data when the transaction is consistent. If it can, it means that the multi data source transaction is invalid
// Test access http://localhost:8080/test
// Manufacturing transaction rollback http://localhost:8080/test?error=true You can also make errors by modifying the table structure
// Note ShardingConfig injects dataSourceProvider to test invalid transaction
@GetMapping("/test")
public String test(Boolean error) {
return buyService.buy(null != error && error);
}
}2.8 Data permission
Add annotations to the mapper layer:
// test type data permission range, mixed paging mode
@DataScope(type = "test", value = {
// Association table user alias u specifies Department field permissions
@DataColumn(alias = "u", name = "department_id"),
// The user alias u in the association table specifies the mobile phone number field (handle it by yourself)
@DataColumn(alias = "u", name = "mobile")
})
@Select("select u.* from user u")
List<User> selectTestList(IPage<User> page, Long id, @Param("name") String username);Simulate business processing logic:
@Bean
public IDataScopeProvider dataScopeProvider() {
return new AbstractDataScopeProvider() {
@Override
protected void setWhere(PlainSelect plainSelect, Object[] args, DataScopeProperty dataScopeProperty) {
// args contains the request parameters of the mapper method, which can be obtained by itself
/*
// Test data permissions and finally execute SQL statements
SELECT u.* FROM user u WHERE (u.department_id IN ('1', '2', '3', '5'))
AND u.mobile LIKE '%1533%'
*/
if ("test".equals(dataScopeProperty.getType())) {
// Business test type
List<DataColumnProperty> dataColumns = dataScopeProperty.getColumns();
for (DataColumnProperty dataColumn : dataColumns) {
if ("department_id".equals(dataColumn.getName())) {
// Append Department field IN condition, or SQL statement
Set<String> 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()) {
// No where condition exists
plainSelect.setWhere(new Parenthesis(inExpression));
} else {
// where conditions exist and processing
plainSelect.setWhere(new AndExpression(plainSelect.getWhere(), inExpression));
}
} else if ("mobile".equals(dataColumn.getName())) {
// Support a custom condition
LikeExpression likeExpression = new LikeExpression();
likeExpression.setLeftExpression(new Column(dataColumn.getAliasDotName()));
likeExpression.setRightExpression(new StringValue("%1533%"));
plainSelect.setWhere(new AndExpression(plainSelect.getWhere(), likeExpression));
}
}
}
}
};
}Final execute SQL output:
SELECT u.* FROM user u
WHERE (u.department_id IN ('1', '2', '3', '5'))
AND u.mobile LIKE '%1533%' LIMIT 1, 10Currently, there is only a paid version. For more mybatis mate usage examples, see:
https://gitee.com/baomidou/mybatis-mate-examples
< END >