mybatis-plus 动态表名
一:背景
我们在查询数据库设计的时候会按一定的条件分表,我们需要根据条件拼接动态表名
在动态分表的背景下,有时候查询数据的时候需要跨表查询,那此时就需要MP在解析的时候,能够很好的自适应表格名称,进行wrapper条件查询
二:实现
MP中是通过PaginationInterceptor(分页插件)完成动态表名解析的,最终用法如下:
DynamicTableTreadLocal.INSTANCE.setTableName(GcQmUtil.getCartNumberByCartNumber(cartNumber)); QueryWrapper<QmRectifySlave> wrapper = new QueryWrapper<>();
三:pom.xml 依赖
<mybatis-plus-boot-starter.version>3.3.1</mybatis-plus-boot-starter.version> <!-- mybatis-plus begin --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>${mybatis-plus-boot-starter.version}</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-generator</artifactId> <version>${mybatis-plus-boot-starter.version}</version> </dependency>
四:配置类
package com.kexin.common.config; import com.baomidou.mybatisplus.extension.incrementer.OracleKeyGenerator; import com.baomidou.mybatisplus.extension.parsers.DynamicTableNameParser; import com.baomidou.mybatisplus.extension.parsers.ITableNameHandler; import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor; import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringBootConfiguration; import org.springframework.context.annotation.Bean; import java.util.Collections; import java.util.HashMap; @SpringBootConfiguration @MapperScan("com.kexin.admin.mapper*") public class MybatisPlusConfig { private static final String DYNAMIC_TABLE_PRE = "QM_RECTIFY_SLAVE"; @Bean public PaginationInterceptor paginationInterceptor() { PaginationInterceptor paginationInterceptor = new PaginationInterceptor(); // 设置请求的页面大于最大页后操作, true调回到首页,false 继续请求 默认false // paginationInterceptor.setOverflow(false); // 设置最大单页限制数量,默认 500 条,-1 不受限制 // paginationInterceptor.setLimit(500); // 开启 count 的 join 优化,只针对部分 left join paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true)); // paginationInterceptor.setDbType("oracle"); // 设置请求的页面大于最大页后操作, true调回到首页,false 继续请求 默认false paginationInterceptor.setOverflow(false); // 设置最大单页限制数量,默认 500 条,-1 不受限制 paginationInterceptor.setLimit(500); // // 开启 count 的 join 优化,只针对部分 left join paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true)); DynamicTableNameParser dynamicTableNameParser = new DynamicTableNameParser(); dynamicTableNameParser.setTableNameHandlerMap(new HashMap<String, ITableNameHandler>(2) {{ //动态表规则-生成自己需要的动态表名 put(DYNAMIC_TABLE_PRE, (metaObject, sql, tableName) -> DynamicTableTreadLocal.INSTANCE.getTableName()); }}); paginationInterceptor.setSqlParserList(Collections.singletonList(dynamicTableNameParser)); return paginationInterceptor; } /** * Sequence主键自增 * * @return 返回oracle自增类 * @author * @date 2019/1/2 */ @Bean public OracleKeyGenerator oracleKeyGenerator(){ return new OracleKeyGenerator(); } }
五:动态表名存储类
package com.kexin.common.config; /** * @description: 动态表名存储类 * @author: 巫恒强 * @time: 2021/7/22 10:32 */ public enum DynamicTableTreadLocal { INSTANCE; private ThreadLocal<String> tableName = new ThreadLocal<>(); public String getTableName() { return tableName.get(); } public void setTableName(String tableName) { this.tableName.set(tableName); } public void remove() { tableName.remove(); } }
六:工具类 (根据实际情况编写)
package com.kexin.common.util.gc; import com.kexin.admin.entity.tables.QmRectifyMaster; /** * @description: * @author: 巫恒强 * @time: 2021/7/15 14:24 */ public class GcQmUtil { public static String getCartNumberByRectifyMater(QmRectifyMaster master){ return "QM_RECTIFY_SLAVE_"+master.getCartNumber().substring(0,2); } public static String getCartNumberByCartNumber(String cartNumber){ return "QM_RECTIFY_SLAVE_"+cartNumber.substring(0,2); } }
七:具体使用 (分页动态查询)
DynamicTableTreadLocal.INSTANCE.setTableName(GcQmUtil.getCartNumberByCartNumber(cartNumber)); QueryWrapper<QmRectifySlave> wrapper = new QueryWrapper<>(); wrapper.eq("RECTIFYM_ID",rectifymId); IPage<QmRectifySlave> ipage = qmRectifySlaveService.page(new Page<>(page,limit),wrapper);
八:其他 原理
- 以mybatis的query方法作为入口
- 通过动态代理执行到配置的分页插件
- 通过分页插件进行sql解析
- 根据分页插件中配置的tableNameHandler进行目标表格的替换
- 最后形成一个可执行sql,执行查询