Mybatis-Plus入门系列(5)- MybatisPlus之动态表名插件DynamicTableNameInnerInterceptor

今天药忘吃喽~ 2023-10-10 12:35 64阅读 0赞

### 动态表名 ###

**描述**： Sql执行时，动态的修改表名  
**简单业务场景**： 日志或者其他数据量大的表，通过日期进行了水平分表，需要通过日期参数，动态的查询数据。

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    @SuppressWarnings({
        "rawtypes"})
    public class DynamicTableNameInnerInterceptor implements InnerInterceptor {
        
    
        private Map<String, TableNameHandler> tableNameHandlerMap;
    
        @Override
        public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        
            PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
            if (InterceptorIgnoreHelper.willIgnoreDynamicTableName(ms.getId())) return;
            mpBs.sql(this.changeTable(mpBs.sql()));
        }
    
        @Override
        public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
        
            PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);
            MappedStatement ms = mpSh.mappedStatement();
            SqlCommandType sct = ms.getSqlCommandType();
            if (sct == SqlCommandType.INSERT || sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) {
        
                if (InterceptorIgnoreHelper.willIgnoreDynamicTableName(ms.getId())) return;
                PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();
                mpBs.sql(this.changeTable(mpBs.sql()));
            }
        }
    
        protected String changeTable(String sql) {
        
            TableNameParser parser = new TableNameParser(sql);
            List<TableNameParser.SqlToken> names = new ArrayList<>();
            parser.accept(names::add);
            StringBuilder builder = new StringBuilder();
            int last = 0;
            for (TableNameParser.SqlToken name : names) {
        
                int start = name.getStart();
                if (start != last) {
        
                    builder.append(sql, last, start);
                    String value = name.getValue();
                    TableNameHandler handler = tableNameHandlerMap.get(value);
                    if (handler != null) {
        
                        builder.append(handler.dynamicTableName(sql, value));
                    } else {
        
                        builder.append(value);
                    }
                }
                last = name.getEnd();
            }
            if (last != sql.length()) {
        
                builder.append(sql.substring(last));
            }
            return builder.toString();
        }
    }

### 测试案例 ###

案例目标：根据传入的月份参数，动态的查询xx\_月份的表

1.  复制几张表，并插入一些测试数据  
    ![在这里插入图片描述][20210315155310452.png]
2.  实现TableNameHandler接口

public class MyTableNameHandler implements TableNameHandler {
        
        /**
         * @param sql       原始SQL
         * @param tableName 表名
         * @return 动态表名
         */
        @Override
        public String dynamicTableName(String sql, String tableName) {
        
            // 模拟获取月份参数，实际应该从参数中获取
            String[] month = {
        "", "_03", "_04"};
            // 随机获取
            int nextInt = new Random().nextInt(2);
            String dynamicTableName = "order_tbl" + month[nextInt];
            System.err.println("动态查询表：" + dynamicTableName);
            return dynamicTableName;
        }
    }

1.  配置类添加插件

// 添加动态表名插件
            DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor=new DynamicTableNameInnerInterceptor();
            TableNameHandler tableNameHandler=new MyTableNameHandler();
            Map<String, TableNameHandler> tableNameHandlerMap=new HashMap<>();
            tableNameHandlerMap.put("order_tbl",tableNameHandler); // order_tbl表配置动态表名插件
            dynamicTableNameInnerInterceptor.setTableNameHandlerMap(tableNameHandlerMap);
            interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);

1.  测试：后台SQL已经实现，不同的参数查询不同表的功能  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0_size_16_color_FFFFFF_t_70]

[20210315155310452.png]: https://img-blog.csdnimg.cn/20210315155310452.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210315163718732.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0,size_16,color_FFFFFF,t_70