众所周知,mybatisplus提供了强大的代码生成能力,它默认生成的常用的CRUD方法(例如插入、更新、删除、查询等)的定义,能够帮助我们节省很多体力劳动。他的BaseMapper中定义了这些常用的CRUD方法,我们在使用时,继承这个BaseMapper类就默认拥有了这些能力。如果我们的业务中,需要类似的通用Sql时,该如何实现呢?是每个Mapper中都定义一遍类似的Sql吗?显然这是最笨的一种方法。此时我们可以借助mybatisplus这个成熟框架,来实现我们想要的通用Sql。比如有一个这样的需求,项目中所有表或某一些表,都要执行一个类似的查询,如`SelectByErp`,那么可以这样实现。(这是一个最简单的sql实现,使用时可以根据业务需求实现更为复杂的sql:比如多租户系统自动增加租户id参数、分库分表系统增加分库分表字段条件判断)1.定义一个SelectByErp类,继承AbstractMethod 类,并实现injectMappedStatement方法2.定义sql方法名、sql模板、实现sql的拼接组装
public class SelectByErp extends AbstractMethod {
private final String erpColumn = "erp";
private final String method = "selectByErp";
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;
}
3.定义一个sql注入器 GyhSqlInjector,添加SelectByErp对象
@Component
public class GyhSqlInjector extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass);
methodList.add(new SelectByErp());
return methodList;
}
}
4.定义一个基础Mapper GyhBaseMapper,添加selectByErp方法
public interface GyhBaseMapper<T> extends BaseMapper<T> {
List<T> selectByErp(String erp);
}
5.应用中需要使用该SelectByErp方法的表,都继承GyhBaseMapper,那么这些表将都拥有了selectByErp这个查询方法,程序启动后会自动为这些表生成该sql。public interface XXXMapper extends GyhBaseMapper<XXXTable>
添加一个mybatisplus已有sql
1.mybatisplus 常用CRUD方法如最上图,这些方法已经默认会自动生成,但mybatisplus其实提供了更多的方法,如下图,只要我们在启动时添加进去,就可以使用了。2.比如我想使用AlwaysUpdateSomeColumnById方法,该方法可以在更新时可以将字段设置为null。添加步骤如下。3.定义一个sql注入器 ,如GyhSqlInjector,添加AlwaysUpdateSomeColumnById对象@Component
public class GyhSqlInjector extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass);
methodList.add(new AlwaysUpdateSomeColumnById());
return methodList;
}
}
4.定义一个基础Mapper 如GyhBaseMapper,添加alwaysUpdateSomeColumnById方法
public interface GyhBaseMapper<T> extends BaseMapper<T> {
int alwaysUpdateSomeColumnById(@Param(Constants.ENTITY) T entity);
}
5.继承GyhBaseMapper的其他Mapper,将自动拥有alwaysUpdateSomeColumnById方法编辑一个mybatisplus已有sql
1.如果想编辑一个mybatisplus已有sql,比如分库分表系统,执行updateById操作时,虽然主键Id已确定,但目标表不确定,此时可能导致该sql在多张表上执行,造成资源浪费,并且分库分表字段不可修改,默认的updateById不能用,需要改造。以下以shardingsphere分库分表为例。2.定义一个UpdateByIdWithSharding类,继承UpdateById 类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(joining(NEWLINE));
return rmShardingColumnSet;
}
}
3.定义一个sql注入器 GyhSqlInjector,添加UpdateByIdWithSharding对象
@Component
public class GyhSqlInjector extends DefaultSqlInjector {
@Autowired
private YamlShardingRuleConfiguration yamlShardingRuleConfiguration;
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass);
methodList.add(new UpdateByIdWithSharding(yamlShardingRuleConfiguration));
return methodList;
}
}
4.定义一个基础Mapper GyhBaseMapper,添加新的selectById方法
public interface GyhBaseMapper<T> extends BaseMapper<T> {
int updateById(@Param(Constants.ENTITY) T entity);
}
5.所有参与分表的表,在定义Mapper时继承GyhBaseMapper,那么在使用他的updateById方法时,将自动增加分库分表判断,准确命中目标表,减少其他分表查询的资源浪费。以上是针对mybatisplus的一些简单改造,希望能为你提供一点点帮助~