一步步解决mybatis使用FORCE INDEX的坑

Love The Way You Lie 2022-04-13 07:25 1069阅读 0赞

由于项目中需要使用mysql的强制索引，于是乎就在mapper.xml中添加一个，结果运行后报错：

> Caused by: com.kxtx.security.data.exception.DataPermissionException: SQL 语句解析失败,请检查：**SELECT id, order\_no, order\_type, waybill\_id, waybill\_no, extend\_waybill\_id FROM TMS\_ORDER FORCE INDEX (idx\_to\_exwaybill\_id) WHERE extend\_waybill\_id IN (?) AND order\_type = ?** at com.kxtx.security.data.handler.HandlerUtil.handlerSql(HandlerUtil.java:34) ~\[gillion-web-quick-2.1.6.17.RELEASE-pg.jar:na\] at com.kxtx.security.data.DataPermissionInterceptor.intercept(DataPermissionInterceptor.java:89) ~\[gillion-web-quick-2.1.6.17.RELEASE-pg.jar:na\] at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:57) ~\[mybatis-3.1.1.jar:3.1.1\] at com.sun.proxy.$Proxy212.prepare(Unknown Source) ~\[na:na\] at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:70) ~\[mybatis-3.1.1.jar:3.1.1\] at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:56) ~\[mybatis-3.1.1.jar:3.1.1\] at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267) ~\[mybatis-3.1.1.jar:3.1.1\] at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:141) ~\[mybatis-3.1.1.jar:3.1.1\] at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105) ~\[mybatis-3.1.1.jar:3.1.1\] at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81) ~\[mybatis-3.1.1.jar:3.1.1\] at sun.reflect.GeneratedMethodAccessor139.invoke(Unknown Source) ~\[na:na\] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~\[na:1.8.0\_151\] at java.lang.reflect.Method.invoke(Method.java:498) ~\[na:1.8.0\_151\] at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59) ~\[mybatis-3.1.1.jar:3.1.1\] ... 124 common frames omitted

这里分享一个快速定位及解决bug的经验：

1.  首先，尽可能的认真读故障现场迹象，了解症状
2.  对正在症状有一个初步的判断，不要急着立马度娘，要有自己的判断（低价代码不严谨问题 or API用法问题 or 高级用法不支持问题）
3.  可以运用排除法，定位属于哪一类，很显然它属于高级用法不支持问题（之前一直是好好的）
4.  需要到该组件或类库的社区中找答案

这里有一点要知道：jdbc的PreparedStatement，它本质上是不解析sql的，所谓客户端预编译是假的，其实还是通过服务端预编译(mysql 引擎)完成的。

如果对mybatis源码足够了解的同学，可能知道sql的执行链中有个自定义的plugin（DataPermissionInterceptor）引起的，继续追踪，看它干了什么呢

/**
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
         <property name="plugins">
             <list>     
                 
                 <bean class="com.kxtx.security.data.DataPermissionInterceptor">
                     <property name="excludes">
                     <list>
                         <value>*NoAcl*</value>
                     </list>
                     </property>
                 </bean>
             </list>
         </property>
     */
    @Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
    public class DataPermissionInterceptor implements Interceptor {
    
        private Logger       LOGGER   = LoggerFactory.getLogger(DataPermissionInterceptor.class);
        private boolean      showSql  = false;
        //排除不进行权限控制的Mapper方法，使用正则来匹配
        private List<String> excludes = Lists.newArrayList();
    
    
        @Override
        public Object intercept(Invocation invocation) throws InvocationTargetException, IllegalAccessException, DataPermissionException {
            if (invocation.getTarget() instanceof RoutingStatementHandler) {
                RoutingStatementHandler statementHandler = (RoutingStatementHandler) invocation.getTarget();
                MetaObject metaStatementHandler = MetaObject.forObject(statementHandler);
                BoundSql boundSql = statementHandler.getBoundSql();
                MappedStatement mappedStatement = loadMapperStatement(statementHandler);
                String mapperId = mappedStatement.getId();
                if (isNeedPermission(mapperId)) {
                    String sql = boundSql.getSql();
                    List<ParametersNode> parametersNodes = loadParamsNode(boundSql);
                    sql = HandlerUtil.handlerSql(sql, parametersNodes);
                    metaStatementHandler.setValue("delegate.boundSql.sql", sql);
                    if (showSql) {
                        LOGGER.info("Current execute SQL is: \n" + boundSql.getSql());
                    }
                }
            }
            return invocation.proceed();
        }
    
    
        /**
         * 检查是否需要权限验证
         *
         * @param mapperId
         *
         * @return
         */
        private boolean isNeedPermission(String mapperId) {
            for (String exclude : excludes) {
                Pattern pattern = Pattern.compile(exclude);
                Matcher matcher = pattern.matcher(mapperId);
                if (matcher.matches()) {
                    return false;
                }
            }
            return true;
        }
    
    
        private List<ParametersNode> loadParamsNode(BoundSql boundSql) {
            List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
            List<ParametersNode> parametersNodes = Lists.newArrayList();
            if (parameterMappings.size() > 0) {
                if (boundSql.getParameterObject() instanceof String) {
                    String name = parameterMappings.get(0).getProperty();
                    Object value = boundSql.getParameterObject();
                    parametersNodes.add(new ParametersNode(name, value));
                }
                else {
                    for (ParameterMapping parameterMapping : parameterMappings) {
                        String name = parameterMapping.getProperty();
                        Object value = boundSql.getAdditionalParameter(name);
                        if (value == null) {
                            Object objectValue = boundSql.getAdditionalParameter("_parameter");
                            if (objectValue != null && !(objectValue instanceof Map) && !(objectValue instanceof List)) {
                                try {
                                    value = FieldUtils.getField(objectValue.getClass(), name, true).get(objectValue);
                                } catch (IllegalAccessException e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                        ParametersNode node = new ParametersNode(name, value);
                        parametersNodes.add(node);
                    }
                }
            }
            return parametersNodes;
        }
    
        private MappedStatement loadMapperStatement(RoutingStatementHandler routingStatementHandler) throws DataPermissionException {
    
            Field field = FieldUtils.getField(routingStatementHandler.getClass(), "mappedStatement", true);
            try {
                if (field == null) {
                    Field delegate = FieldUtils.getField(routingStatementHandler.getClass(), "delegate", true);
                    if (delegate != null) {
                        StatementHandler delegateHandler = (StatementHandler) delegate.get(routingStatementHandler);
                        field = FieldUtils.getField(delegateHandler.getClass(), "mappedStatement", true);
                        if (field != null) {
                            return (MappedStatement) field.get(delegateHandler);
                        }
                    }
                }
                return (MappedStatement) field.get(routingStatementHandler);
            } catch (IllegalAccessException e) {
                throw new DataPermissionException("无法获取对应的Mapper Statement");
            }
        }
    
        @Override
        public Object plugin(Object target) {
            if (target instanceof StatementHandler) {
                return Plugin.wrap(target, this);
            }
            else {
                return target;
            }
        }
    
        @Override
        public void setProperties(Properties properties) {
    
        }
    
        public List<String> getExcludes() {
            return excludes;
        }
    
        public void setExcludes(List<String> excludes) {
            if (!excludes.contains("*NoAcl*")) {
                excludes.add("*NoAcl*");
            }
            for (String exclude : excludes) {
                exclude = exclude.replaceAll("\\*", "[\\\\w|.]*[\\\\w|.]");
                this.excludes.add(exclude);
            }
        }
    
        public boolean isShowSql() {
            return showSql;
        }
    
        public void setShowSql(boolean showSql) {
            this.showSql = showSql;
        }
    }
    
    public class HandlerUtil {
        private final static Logger LOGGER = LoggerFactory.getLogger(HandlerUtil.class);
    
        /**
         * 处理进行权限判断的SQL操作语句
         * @param sql
         * @return
         * @throws com.gfa4j.security.data.exception.DataPermissionException
         */
        public static String handlerSql(String sql,List<ParametersNode> nodes) throws DataPermissionException{
            Statement statement = null;
            try {
                statement = new CCJSqlParserManager().parse(new StringReader(sql));
                HandlerFactory.buildHandler(statement).handler(statement,nodes);
                return statement.toString();
            } catch (JSQLParserException e) {
               LOGGER.error("SQL 语句解析失败,请检查："+sql);
                throw new DataPermissionException("SQL 语句解析失败,请检查："+sql);
            }
        }
    
    }

找到了，有个类库 **jsqlparser** 报错了，but？exception呢，异常被吃掉了（这样处理就很不专业，增加排查难度），真实的异常是：

> net.sf.jsqlparser.JSQLParserException  
>     at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:40)  
>     at com.example.driver.TmsSqlParser\_test.sqlParse(TmsSqlParser\_test.java:94)  
>     at com.example.driver.TmsSqlParser\_test.go\_hint(TmsSqlParser\_test.java:17)  
>     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
>     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)  
>     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)  
>     at java.lang.reflect.Method.invoke(Method.java:497)  
>     at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)  
>     at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)  
>     at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)  
>     at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)  
>     at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)  
>     at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)  
>     at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)  
>     at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)  
>     at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)  
>     at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)  
>     at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)  
>     at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)  
>     at org.junit.runners.ParentRunner.run(ParentRunner.java:363)  
>     at org.junit.runner.JUnitCore.run(JUnitCore.java:137)  
>     at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)  
>     at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:51)  
>     at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237)  
>     at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)  
>     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
>     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)  
>     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)  
>     at java.lang.reflect.Method.invoke(Method.java:497)  
>     at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)  
> Caused by: net.sf.jsqlparser.parser.ParseException: Encountered " "INDEX" "INDEX "" at line 1, column 36.  
> Was expecting one of:  
>     <EOF>   
>     "JOIN" ...  
>     "LEFT" ...  
>     "CROSS" ...  
>     "FULL" ...  
>     "WHERE" ...  
>     "GROUP" ...  
>     "INNER" ...  
>     "OUTER" ...  
>     "RIGHT" ...  
>     "HAVING" ...  
>     "NATURAL" ...  
>     "START" ...  
>     "CONNECT" ...  
>     ";" ...  
>     "," ...  
>     "LEFT" ...  
>     "RIGHT" ...  
>     "FULL" ...  
>     "NATURAL" ...  
>     "CROSS" ...  
>     "OUTER" ...  
>     "INNER" ...  
>     "JOIN" ...  
>     "," ...  
>     "WHERE" ...  
>     "START" ...  
>     "CONNECT" ...  
>     "GROUP" ...  
>     "HAVING" ...  
>       
>     at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:9257)  
>     at net.sf.jsqlparser.parser.CCJSqlParser.jj\_consume\_token(CCJSqlParser.java:9130)  
>     at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:63)  
>     at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:38)  
>     ... 29 more  
> **Caused by: net.sf.jsqlparser.parser.ParseException: Encountered " "INDEX" "INDEX "" at line 1, column 36**.  
> Was expecting one of:  
>     <EOF>   
>     "JOIN" ...  
>     "LEFT" ...  
>     "CROSS" ...  
>     "FULL" ...  
>     "WHERE" ...  
>     "GROUP" ...  
>     "INNER" ...  
>     "OUTER" ...  
>     "RIGHT" ...  
>     "HAVING" ...  
>     "NATURAL" ...  
>     "START" ...  
>     "CONNECT" ...  
>     ";" ...  
>     "," ...  
>     "LEFT" ...  
>     "RIGHT" ...  
>     "FULL" ...  
>     "NATURAL" ...  
>     "CROSS" ...  
>     "OUTER" ...  
>     "INNER" ...  
>     "JOIN" ...  
>     "," ...  
>     "WHERE" ...  
>     "START" ...  
>     "CONNECT" ...  
>     "GROUP" ...  
>     "HAVING" ...  
>     at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:9257)  
>     at net.sf.jsqlparser.parser.CCJSqlParser.jj\_consume\_token(CCJSqlParser.java:9130)  
>     at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:63)  
>     at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:38)

看红色部分，到官方找答案：[https://github.com/JSQLParser/JSqlParser/pull/429][https_github.com_JSQLParser_JSqlParser_pull_429]

看截图，在1.1之后的版本是支持的，确认下项目中使用的是0.9的版本。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0c29mdGNoZW5mZWk_size_16_color_FFFFFF_t_70][]

最后，升级下版本，问题得到解决？升级之后发现，0.9的有些功能之前好的，1.1之后就报错了。

怎么破？

这里就要强调一点：**所有的关键组件一定可插拔的，所有关键功能点一定是可监听的，类设计上可拓展性很多时候要具备一定的前瞻性！**

那这个问题就简单了，在执行该插件是把该sql排查出去就可以了（把mapper.xml中的该方法的通配规则添加到excludes中）。![82818753][]

[https_github.com_JSQLParser_JSqlParser_pull_429]: https://github.com/JSQLParser/JSqlParser/pull/429
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l0c29mdGNoZW5mZWk_size_16_color_FFFFFF_t_70]: /images/20220413/a93357fbe49747d3b363112207df641a.png
[82818753]: /images/20220413/74229757b1f2427aa70442001e4814eb.png