0. Introduction
Mybatis mate is an mp enterprise level module. It supports sub database and sub table, data audit, data sensitive word filtering (AC algorithm), field encryption, dictionary writeback (data binding), data permissions, automatic generation of table structure, SQL maintenance, etc. It aims to process data more quickly and gracefully.
1. Main functions
- Dictionary binding
- Field encryption
- Data desensitization
- Table structure dynamic maintenance
- Data audit record
- Data range (Data permission)
- The database is divided into database and table, dynamic data source, read-write separation, and automatic switching of 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 writeback)
For example, user_ The result of the sex Dictionary of type sex is mapped to the sexText property
@FieldDict(type = "user_sex", target = "sexText") private Integer sex; private String sexText;
Implement IDataDict interface, provide dictionary data source, and inject it into 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 decrypted automatically. It supports the global configuration of encryption key algorithm and annotation key algorithm, and can realize the user-defined algorithm of IEncryptor injection.
@FieldEncrypt(algorithm = Algorithm.PBEWithMD5AndDES) private String password;
2.4 field desensitization
The attribute @ FieldSensitive annotation can automatically desensitize the source data according to the preset policy. By default, nine common desensitization policies are built in the sentivetype.
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
/**
* Demo article sensitive word filtering
*/
@RestController
public class ArticleController {
@Autowired
private SensitiveWordsMapper sensitiveWordsMapper;
// Test access the following address, 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";
}
// The test accesses the following address to observe the console (request json parameters)
// idea executes the TestJson.http file test in the resources directory
@PostMapping("/json")
public String json(@RequestBody Article article) throws Exception {
return ParamsConfig.toJson(article);
}
}2.5 automatic maintenance of DDL data structure
Solve the problems of upgrading table structure initialization, version release and SQL maintenance. At present, 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 mode
"db/tag-schema.sql",
// File absolute path mode
"D:\\db\\tag-data.sql"
);
}
}It can be executed not only fixedly, but also 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) {
// Multiple data sources are specified, the master library is initialized, and the slave library is automatically synchronized
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 annotation enables the data source to be used and switched freely. You can add annotations in the mapper layer and hit where you want!!
@Mapper
@Sharding("mysql")
public interface UserMapper extends BaseMapper<User> {
@Sharding("postgres")
Long selectByUsername(String username);
}You can also customize the strategy and deploy troops uniformly
@Component
public class MyShardingStrategy extends RandomShardingStrategy {
/**
* Decide to switch the 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) {
// You can customize the data source group. 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, of course, you can also start the health check!!!
Specific configuration:
mybatis-mate:
sharding:
health: true # Health testing
primary: mysql # Select data source by default
datasource:
mysql: # Database group
- key: node1
...
- key: node2
cluster: slave # The master database is responsible for sql query operation when reading and writing from the database are separated. The master database can not write by default
...
postgres:
- key: node1 # Data node
...2.7 distributed transaction log printing
Some configurations are as follows:
/**
* <p>
* Performance analysis interceptor, which is 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 stop, which helps to find problems.
*/
private long maxTime = 0;
/**
* SQL Format
*/
private boolean format = false;
/**
* Write log file or not < br >
* true Write log files without blocking program execution< br>
* Exception 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 and 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 the 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, which indicates 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 create errors by modifying the table structure
// Note ShardingConfig injects dataSourceProvider to test the invalid transaction
@GetMapping("/test")
public String test(Boolean error) {
return buyService.buy(null != error && error);
}
}2.8 data authority
Add comments to the mapper layer:
// test type, Data permission range, mixed paging mode
@DataScope(type = "test", value = {
// Associated table user alias u specifies Department field permissions
@DataColumn(alias = "u", name = "department_id"),
// Specify the mobile phone number field in the user alias u of the association table (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 yourself
/*
// 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()) {
// There is no where condition
plainSelect.setWhere(new Parenthesis(inExpression));
} else {
// where condition exists and processing
plainSelect.setWhere(new AndExpression(plainSelect.getWhere(), inExpression));
}
} else if ("mobile".equals(dataColumn.getName())) {
// A custom condition is supported
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, 10At present, there is only a paid version. For more examples of mybatis mate, see:
https://gitee.com/baomidou/mybatis-mate-examples