Error selecting key or setting result to parameter object. Cause: java.sql.SQLSyntaxErrorException

created at 08-30-2022 views: 19

general mapper insertSelective method reports a syntax error

The error message is as follows

Error selecting key or setting result to parameter object. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

This problem occurs occasionally in our online projects, and it occurs about once every three or four days. It can be said to be very torturous. This error is very clear, but I have searched the whole network and have not found any related problems. Here I record my thoughts on troubleshooting this problem and the solution.

The error is thrown from the following source code, so here's a look at it

private void processGeneratedKeys(Executor executor, MappedStatement ms, Object parameter) {
        try {
            if (parameter != null && this.keyStatement != null && this.keyStatement.getKeyProperties() != null) {
                String[] keyProperties = this.keyStatement.getKeyProperties();
                Configuration configuration = ms.getConfiguration();
                MetaObject metaParam = configuration.newMetaObject(parameter);
                if (keyProperties != null) {
                    Executor keyExecutor = configuration.newExecutor(executor.getTransaction(), ExecutorType.SIMPLE);
                    //The core method to get the id value
                    List<Object> values ​​= keyExecutor.query(this.keyStatement, parameter, RowBounds.DEFAULT, Executor.NO_RESULT_HANDLER);
                    if (values.size() == 0) {
                        throw new ExecutorException("SelectKey returned no data.");
                    }

                    if (values.size() > 1) {
                        throw new ExecutorException("SelectKey returned more than one value.");
                    }

                    MetaObject metaResult = configuration.newMetaObject(values.get(0));
                    //If there is a result, the following method sets the result to the id
                    if (keyProperties.length == 1) {
                        if (metaResult.hasGetter(keyProperties[0])) {
                            this.setValue(metaParam, keyProperties[0], metaResult.getValue(keyProperties[0]));
                        } else {
                            this.setValue(metaParam, keyProperties[0], values.get(0));
                        }
                    } else {
                        this.handleMultipleProperties(keyProperties, metaParam, metaResult);
                    }
                }
            }

        } catch (ExecutorException var10) {
            throw var10;
        } catch (Exception var11) {
        //The exception printed in the log, where it was thrown
            throw new ExecutorException("Error selecting key or setting result to parameter object. Cause: " + var11, var11);
        }
    }

The source code can't find the problem here at all I carefully compared the logs of the correct execution and the execution error, and found that such a line of logs will appear when there is an error. I think the problem is here.

com.legal.draft.dao.mapper.ContractDraftMapper.insertSelective!selectKey_COUNT

After debugging for a long time, I found the core code block according to _COUNT in the log

// Determine whether to paginate
if (this.dialect.skip(ms, parameter, rowBounds)) {
// Normally, it will be in this if
                 resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
             } else {
             //Wrong will go to this
                 String msId = ms.getId();
                 Configuration configuration = ms.getConfiguration();
                 Map<String, Object> additionalParameters = (Map)this.additionalParametersField.get(boundSql);
                 if (this.dialect.beforeCount(ms, parameter, rowBounds)) {
                 //The error will be spelled _COUNT, a strange log makes me closer and closer to the truth
                     String countMsId = msId + this.countSuffix;

Why enter else? Why does an insert method have paging? The source code of skip (paging or not) is as follows

public boolean skip(MappedStatement ms, Object parameterObject, RowBounds rowBounds) {
         if (ms.getId().endsWith("_COUNT")) {
             throw new RuntimeException("Multiple paging plugins were found in the system, please check the system configuration!");
         } else {
         //get page
             Page page = this.pageParams.getPage(parameterObject, rowBounds);
             if (page == null) {
                 return true;
             } else {
                 if (StringUtil.isEmpty(page.getCountColumn())) {
                     page.setCountColumn(this.pageParams.getCountColumn());
                 }

                 this.autoDialect.initDelegateDialect(ms);
                 return false;
             }
         }
     }

The source code is very simple, it is to determine whether the current thread has a page, which is the following

PageHelper.startPage(pageNum, pageSize);

Certainly not. It's really strange how my insert interface can go to paging, so I went to check the principle of the PageHelper plugin and found such a sentence.

After the PageHelper.startPage method is called, there must be a Mapper query method behind it, which must be consumed. Otherwise, it will be paged when the thread is called by other methods due to ThreadLocal. The usage of the paging plugin is very clearly written in the documentation! ! In addition, this is not a problem caused by ThreadLocal, but improper use!

I suddenly realized, so I wrote a test interface.

/**
      * Change
      * @param
      * @return
      */
     @PostMapping("modifyDraftTemp")
     @EPAroundLog
     public BaseResponse<ContractVO> modifyDraft() {

         // turn on paging
         PageHelper.startPage(1,1);

         //return result
         return BaseResponse.ok();
     }

After dropping this test interface a few times, the problem will recur after calling the insertSelective method to insert it.

The problem here is clear, the reason is that PageHelper.startPage is called somewhere in the project, but it is not consumed, because the page object is placed in the local thread, and ThreadLocal just executes the insertSelective method , will go to the paging method, When processing the id just inserted, the assembled sql looks like this.

SELECT LAST_INSERT_ID() LIMIT ?

If executed, it will report the above sql syntax error

Knowing the cause of the problem is easy to solve.

  • Method 1: Find the place where PageHelper.startPage is not consumed in the project, and optimize the code. (I looked at dozens of our projects, it's horrible)
  • Method 2: Add PageHelper.clearPage() before the insert statement (to cure the symptoms but not the root cause, but it works, I'm lazy, I use 2)
//If other threads have unconsumed paging, it will cause an error to be inserted here
PageHelper.clearPage();
if (contractDraftMapper.insertSelective(contractDraft) != 1) {
return null;
}
created at:08-30-2022
edited at: 08-30-2022: