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

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/itsoftchenfei/article/details/84579989

由于项目中需要使用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

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

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

怎么破?

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

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

展开阅读全文

force index 没有效果

04-21

[code=sql]CREATE TABLE nbk_payment.smartpay_payments (rn id INT(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',rn code VARCHAR(8) NOT NULL COMMENT '交易码',rn subCode VARCHAR(4) NOT NULL COMMENT '子交易码',rn txnCode VARCHAR(16) DEFAULT NULL COMMENT '交易码=code+subcode',rn cardId VARCHAR(32) NOT NULL COMMENT '卡号',rn amount BIGINT(20) NOT NULL COMMENT '交易金额 单位分',rn txnTime DATETIME NOT NULL COMMENT '交易时间',rn sysSeqId VARCHAR(32) NOT NULL COMMENT '系统流水号',rn custPhone VARCHAR(32) NOT NULL COMMENT '客户电话',rn custName VARCHAR(64) NOT NULL COMMENT '客户名',rn merId VARCHAR(64) DEFAULT NULL COMMENT '商户号',rn merName VARCHAR(64) NOT NULL COMMENT '商户名',rn termId VARCHAR(32) NOT NULL COMMENT '终端id',rn note VARCHAR(255) DEFAULT NULL COMMENT '备注',rn created_user_id INT(11) DEFAULT NULL COMMENT '创建人',rn created_at DATETIME DEFAULT NULL COMMENT '创建时间',rn updated_user_id INT(11) DEFAULT NULL COMMENT '更新人',rn updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',rn oriSeqId VARCHAR(32) NOT NULL,rn sourceOrderNumber VARCHAR(64) DEFAULT NULL COMMENT '订单号',rn order_source INT(11) DEFAULT NULL COMMENT '单据来源',rn stat TINYINT(4) DEFAULT 0 COMMENT '0:原始状态;1:撤销状态',rn machineNumber VARCHAR(32) DEFAULT NULL,rn PRIMARY KEY (id),rn INDEX idx_merId_txnTime (merId, txnTime),rn INDEX IDX_sysSeqId_txnCode_txnTime (sysSeqId, txnCode, txnTime)rn)rnENGINE = INNODBrnAUTO_INCREMENT = 510642rnAVG_ROW_LENGTH = 209rnCHARACTER SET utf8rnCOLLATE utf8_general_ci;[/code]rnrnrn[code=sql]rn explain SELECT rn *rn FROM rn nbk_payment.smartpay_payments sp FORCE INDEX (idx_merId_txnTime)rn INNER JOIN nbk_organ.organ_devices od rn ON sp.machineNumber=od.device_code[/code]rnrn通过explain得到:rn1 SIMPLE sp ALL (null) (null) (null) (null) 507708 Using wherern1 SIMPLE od ref UK_organ_devices UK_organ_devices 153 nbk_payment.sp.machineNumber 1 Using index conditionrnrnnbk_payment.smartpay_payments总是不使用索引,请问怎么办,谢谢!rnrn 论坛

Force close怎么解决

01-18

01-18 20:19:20.266: ERROR/AndroidRuntime(910): FATAL EXCEPTION: mainrn01-18 20:19:20.266: ERROR/AndroidRuntime(910): android.database.CursorIndexOutOfBoundsException: Index 0 requested, with a size of 0rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at android.database.AbstractCursor.checkPosition(AbstractCursor.java:580)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at android.database.AbstractWindowedCursor.checkPosition(AbstractWindowedCursor.java:214)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at android.database.AbstractWindowedCursor.getInt(AbstractWindowedCursor.java:84)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at android.database.CursorWrapper.getInt(CursorWrapper.java:123)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at com.android.hellomiss.Service.isAddOne(registerService.java:270)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at com.android.hellomiss.Service.updateTable(registerService.java:204)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at com.android.hellomiss.Service.access$300(registerService.java:30)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at com.android.hellomiss.Service$5.onChange(registerService.java:164)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at android.database.ContentObserver$NotificationRunnable.run(ContentObserver.java:43)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at android.os.Handler.handleCallback(Handler.java:587)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at android.os.Handler.dispatchMessage(Handler.java:92)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at android.os.Looper.loop(Looper.java:123)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at android.app.ActivityThread.main(ActivityThread.java:4627)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at java.lang.reflect.Method.invokeNative(Native Method)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at java.lang.reflect.Method.invoke(Method.java:521)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:876)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:634)rn01-18 20:19:20.266: ERROR/AndroidRuntime(910): at dalvik.system.NativeStart.main(Native Method) 论坛

没有更多推荐了,返回首页