How to Extend MyBatis-Plus with Custom CRUD Methods and SQL Injection

MyBatis-Plus Common CRUD Methods

MyBatis-Plus automatically generates common CRUD methods (insert, update, delete, select, etc.) through its BaseMapper, allowing developers to inherit these capabilities without writing repetitive SQL.

Extending CRUD with Custom SQL

To create a reusable custom query such as SelectByErp, define a class that extends AbstractMethod and implements injectMappedStatement. The class stores the column name, method name, and SQL template, builds the final SQL using table metadata, and returns a mapped statement.

/**
 * 新增一个通用sql
 */
public class SelectByErp extends AbstractMethod {
    // 需要查询的列名
    private final String erpColumn = "erp";
    // sql方法名
    private final String method = "selectByErp";
    // sql模板
    private final String sqlTemplate = "SELECT %s FROM %s WHERE %s=#{%s} %s";

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        TableFieldInfo erpFiled = getErpProperty(tableInfo);
        SqlSource sqlSource = new RawSqlSource(configuration, String.format(sqlTemplate,
                sqlSelectColumns(tableInfo, false),
                tableInfo.getTableName(),
                erpFiled.getColumn(), erpFiled.getProperty(),
                tableInfo.getLogicDeleteSql(true, false)), Object.class);
        return this.addSelectMappedStatementForTable(mapperClass, method, sqlSource, tableInfo);
    }

    private TableFieldInfo getErpProperty(TableInfo tableInfo) {
        List<TableFieldInfo> fieldList = tableInfo.getFieldList();
        TableFieldInfo erpField = fieldList.stream().filter(filed -> filed.getColumn().equals(erpColumn)).findFirst().get();
        return erpField;
    }
}

Next, create a GyhSqlInjector that overrides getMethodList to add an instance of SelectByErp. Then define a base mapper interface GyhBaseMapper<T> extending BaseMapper<T> with the method signature List<T> selectByErp(String erp);. Any mapper that extends GyhBaseMapper automatically gains the selectByErp method.

public interface GyhBaseMapper<T> extends BaseMapper<T> {
    List<T> selectByErp(String erp);
}

Adding Existing MyBatis-Plus Methods

MyBatis-Plus also provides advanced methods such as AlwaysUpdateSomeColumnById, which updates a record while allowing null values. Add this method by creating a GyhSqlInjector that registers an AlwaysUpdateSomeColumnById instance, and extend the base mapper with the corresponding method signature.

public interface GyhBaseMapper<T> extends BaseMapper<T> {
    int alwaysUpdateSomeColumnById(@Param(Constants.ENTITY) T entity);
}

Modifying Existing SQL for Sharding Scenarios

When using sharding (e.g., ShardingSphere), the default updateById may target the wrong table. Create a class UpdateByIdWithSharding that extends UpdateById, inject the sharding configuration, and override injectMappedStatement to append sharding column conditions to the generated SQL.

public class UpdateByIdWithSharding extends UpdateById {
    private String columnDot = "`";
    private YamlShardingRuleConfiguration yamlShardingRuleConfiguration;

    public UpdateByIdWithSharding(YamlShardingRuleConfiguration yamlShardingRuleConfiguration) {
        this.yamlShardingRuleConfiguration = yamlShardingRuleConfiguration;
    }

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String tableName = tableInfo.getTableName();
        Map<String, YamlTableRuleConfiguration> tables = yamlShardingRuleConfiguration.getTables();
        if (tables.containsKey(tableName)) {
            YamlTableRuleConfiguration tableRuleConfiguration = tables.get(tableName);
            String shardingColumn = tableRuleConfiguration.getTableStrategy().getStandard().getShardingColumn();
            boolean logicDelete = tableInfo.isLogicDelete();
            SqlMethod sqlMethod = SqlMethod.UPDATE_BY_ID;
            String shardingAdditional = getShardingColumnWhere(tableInfo, shardingColumn);
            final String additional = optlockVersion() + tableInfo.getLogicDeleteSql(true, false);
            shardingAdditional = shardingAdditional + additional;
            String sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(),
                    getSqlSet(logicDelete, tableInfo, shardingColumn),
                    tableInfo.getKeyColumn(), ENTITY_DOT + tableInfo.getKeyProperty(),
                    shardingAdditional);
            SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
            return addUpdateMappedStatement(mapperClass, modelClass, sqlMethod.getMethod(), sqlSource);
        } else {
            return super.injectMappedStatement(mapperClass, modelClass, tableInfo);
        }
    }

    private String getShardingColumnWhere(TableInfo tableInfo, String shardingColumn) {
        StringBuilder shardingWhere = new StringBuilder();
        shardingWhere.append(" AND ").append(shardingColumn).append("=#{");
        shardingWhere.append(ENTITY_DOT);
        TableFieldInfo fieldInfo = tableInfo.getFieldList().stream()
                .filter(f -> f.getColumn().replaceAll(columnDot, StringUtils.EMPTY).equals(shardingColumn))
                .findFirst().get();
        shardingWhere.append(fieldInfo.getEl());
        shardingWhere.append("}");
        return shardingWhere.toString();
    }

    public String getSqlSet(boolean ignoreLogicDelFiled, TableInfo tableInfo, String shardingColumn) {
        List<TableFieldInfo> fieldList = tableInfo.getFieldList();
        String rmShardingColumnSet = fieldList.stream()
                .filter(i -> ignoreLogicDelFiled ? !(tableInfo.isLogicDelete() && i.isLogicDelete()) : true)
                .filter(i -> !i.getColumn().equals(shardingColumn))
                .map(i -> i.getSqlSet(ENTITY_DOT))
                .filter(Objects::nonNull)
                .collect(Collectors.joining(NEWLINE));
        return rmShardingColumnSet;
    }
}

Register this class in a GyhSqlInjector and add an updateById method to GyhBaseMapper. Mappers that inherit this base mapper will now execute sharding‑aware updates, reducing unnecessary queries.

public interface GyhBaseMapper<T> extends BaseMapper<T> {
    int updateById(@Param(Constants.ENTITY) T entity);
}

These examples demonstrate simple extensions to MyBatis-Plus that improve code reuse and adapt to complex requirements such as multi‑tenant or sharding environments.