Mybatis Troubleshooter book: ‘#’ failed to pass Boolean values

This is the first day of my participation in the First Challenge 2022

Life is too short to have a dog

First, the scene of the problem

MySQL has been providing JSON type since version 5.7. This problem is that MySQL service can be queried normally when using JSON type, but Mybatis is used to query invalid problems.

Specifically, when using Mybatis (here it should be noted that the author actually uses its enhanced version of Mybatis-Plus) to conduct conditional query according to the specified value of a key in the JSON type field, the result cannot be queried. When transferring parameter values, ‘#’ is used to transfer variable values. The query code is as follows:

    @Select("select * from `task_info` where task_params -> #{fieldName} = #{fieldValue}")
    @ResultMap("mybatis-plus_TaskInfo")
    List<TaskInfo> selectByTaskParams(String fieldName, Object fieldValue);
Copy the code

After testing, it can be found that the above query statement will only fail when it is passed for Boolean variables, and the result can be normally queried when the parameters are passed as string and number types.

Second, explore the problem

1. Print and execute SQL

After finding that the execution result of Mybatis was inconsistent with the execution result of the direct running SQL, the initial guess was that the actual execution of Mybatis was inconsistent with the target SQL, causing the final execution result to be inconsistent with the expected result. In order to verify the conjure, it is necessary to print out the SQL actually executed in Mybatis for comparison and judgment. P6spy open source tool is introduced here to track the database operation (the reason why StdOutImpl is not used here is that when the console prints, SQL execution is not splice processing, and the corresponding parameter is still ‘? ‘Show).

Using the above tools, you can see the actual executed SQL in the console as follows:

select * FROM `task_info` WHERE task_params -> '$.online' = true;
Copy the code

After pasting the corresponding information to the database GRAPHICAL user interface (GUI), you can obtain the corresponding result. Here I can’t help suspecting that p6SPY does not fully follow Mybatis’ splicing logic in the process of splicing SQL, but simply splices the statement text after obtaining SQL execution statements and passing in parameters.

2. Use ‘$’ to pass the value

There was no obvious exception in the SQL statement, so suspicion was directed at the SQL assembly process, which used the ‘#{}’ pass operation to do the SQL assembly. ${} = ‘${}’ = ‘${}’ = ‘${}’ = ‘${}’ = ‘${}’ = ‘${}’ = ‘${}’ = ‘${}’ = ‘${}’ = ‘${}’

    @Select("select * from `task_info` where task_params -> #{fieldName} = ${fieldValue}")
    @ResultMap("mybatis-plus_TaskInfo")
    List<TaskInfo> selectByTaskParams(String fieldName, Object fieldValue);
Copy the code

After testing, it is found that the corresponding results can be correctly queried using the above query statement, indicating that the above conjecture is valid.

Third, reason derivation

As you can see from the above problem exploration, the original query execution exception is indeed caused by ‘#{}’, but what is the specific cause?

1. The difference between ‘#{}’ and ‘${}

From the above test, we can find that the same SQL statement using #{} cannot be queried successfully, while using ${} can be queried correctly, which indicates that the internal processing mechanism of Mybatis is different for the above two different placeholders. #{} Mybatis creates a PreparedStatement for SQL assembly. ${} Mybatis creates a PreparedStatement for SQL assembly. These two classes of the biggest differences between believe you also have to fall back such as flow, namely the former in executing SQL will there be precompiled process, while the latter will not add processing directly execute static SQL and return the corresponding results (the static SQL here refers to the value transfer process will not do any type check, according to the text splicing way of joining together directly from the corresponding SQL).

2. ‘#{}’ precompilation process

Now that we know the root cause of the problem is the precompilation process, let’s take a closer look. PreparedStatement (Mybatis), PreparedStatement (Mybatis), PreparedStatement (Mybatis), PreparedStatement (Mybatis) Since the variable in question above is of a Boolean type, let’s trace the process of setting the variable.

Start with the entry method for setting variables. It needs to be reminded that since the author is using Mybatis-Plus, the entry method of parameter setting is different from the original Mybatis, but the underlying Settings are actually the same, so the analysis is carried out according to Mybatis-Plus. The entry method is MybatisParameterHandler#setParameters, as shown below:

Typehandler. setParameter(ps, I + 1, value, jdbcType); In the second figure, the TypeHandler object instance is UnknownTypeHandler, because the type of the variable passed in was not specified in the original code. To further clarify the actual type of TypeHandler, UnknownTypeHandler (Object Parameter, JdbcType JdbcType) provides the resolveTypeHandler(Object Parameter, JdbcType JdbcType) method to further specify the type of the parameter and the instance type of the processor.

As you can see, the resolveTypeHandler method specifies that the parameter type is Boolean, and the type handler eventually selects BooleanTypeHandler.

After selecting the type handler, we finally begin to approach our target, the setBoolean method, and keep tracking:

Method to track all the way to the ClientPreparedQueryBindings# setBoolean internal finally found the reason of this problem is: SQL = 0; Boolean = 0; Boolean = 1; Boolean = 0; Boolean = 1;

select * FROM `task_info` WHERE task_params -> '$.online' = '1';
Copy the code

Using this SQL, of course, does not produce the expected results, so the problem of passing Boolean values with ‘#’ is solved, and we choose to pass values with ‘$’ instead.

Four,

The use of ‘#{}’ and ‘${}’ is an old question, but sometimes it is only clear in concept but vague in detail, the above question is difficult to find the root cause in the first place.

Of course, the above source code for booleans is not difficult to understand, because in MySQL, booleans are actually converted to tinyint for storage, that is, using 0/1 to identify true/false. However, in SQL statement processing, MySQL can recognize true/false and convert it back to 0/1 for retrieval operations, which to some extent causes our perception of the Boolean value in MySQL and its essence mismatching.