1, Background

The artifact officially released by MyBatis - Plus: mybatis mate is an mp enterprise level module that supports database and table splitting, data audit, data sensitive word filtering (AC algorithm), field encryption, dictionary writeback (data binding), data permissions, automatic generation of table structure, SQL maintenance, etc., aiming at more agile and elegant data processing.

2, Main functions

Function introduction

• Dictionary binding
• Field encryption
• Data desensitization
• Table structure dynamic maintenance
• Data audit record
• Data range (Data permission)
• Database sub database and sub table, dynamic data source, read-write separation, and automatic switching of data database health check.

Η dependent 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>

Η 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);
    }
}

Η 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;

 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);
    }
}

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');"));

ⅶ 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 turn on the master-slave strategy, and of course, you can turn on 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
          ...

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);
    }
}

For more examples of using mybatis mate, see:

https://gitee.com/baomidou/mybatis-mate-examples