Shulou(Shulou.com)06/01 Report--

This article mainly explains "case analysis of SQL nodes in Mybatis". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "case analysis of SQL nodes in Mybatis".

First, the reasons for the article

One day when I was self-testing after completing a small function in the project, I found that there was a very strange bug-the final SQL was inconsistent with the SQL I expected, and a branch of if was spliced to the final SQL without passing specific parameters.

① SQL statements defined in the XML file

Select * from balance and data_org_code in # {dataOrgCode} and data_org_code = # {dataOrgCode}

Parameters passed in by ②

{"dataOrgCodes": ["6", "2"]}

③ Mybatis print the executed SQL

SELECT * FROM balanceWHERE data_org_code IN (?) AND data_org_code =?

Printed execution parameters

{"dataOrgCodes": ["6", "2"]} II. Existing problems

People who have studied Mybatis should be able to see that there is something wrong with this SQL and there is something else that should not be there. According to our understanding, the final executed SQL should be

SELECT * FROM balanceWHERE data_org_code IN

But mybatis executes SQL with a little more statements-AND data_org_code =?

After this problem, I repeated debug to make sure that there was nothing wrong with the parameters I passed in, and there was no interceptor to add extra parameters.

Third, analyze the SQL generation process.

After determining the content of the if tag that wrote the XML file and the parameters passed in correctly, the problem caused by the parameters was eliminated. So apart from this possibility, the problem may arise in the parsing of SQL, where SQL is generated. So we navigate to the place where SQL is generated, the DynamicSqlSource#getBoundSql (parameter object we query) method

/ / Configuration is the Mybatis core class, and the rootSqlNode root SQL node is the SQL statement we defined in XML. / / (for example, rootSqlNode, the content in the middle of the tag is rootSqlNode) public DynamicSqlSource (Configuration configuration, SqlNode rootSqlNode) {this.configuration = configuration; this.rootSqlNode = rootSqlNode;} public BoundSql getBoundSql (Object parameterObject) {DynamicContext context = new DynamicContext (configuration, parameterObject); rootSqlNode.apply (context);.. BoundSql boundSql = sqlSource.getBoundSql (parameterObject); context.getBindings () .forEach (boundSql::setAdditionalParameter); return boundSql;}

You can see inside the method that a DynamicContext has been created, and this object is used to store the dynamically generated SQL.

(the following is the omission of a lot of code that has nothing to do with this problem, leaving only the relevant code)

Public class DynamicContext {public static final String PARAMETER_OBJECT_KEY = "_ parameter"; public static final String DATABASE_ID_KEY = "_ databaseId"; / / stores dynamically generated SQL, similar to StringBuilder's role private final StringJoiner sqlBuilder = new StringJoiner (""); / / unique numbering value, which will be used to generate the final SQL and parameter value mapping using private int uniqueNumber = 0 / / stitching SQL public void appendSql (String sql) {sqlBuilder.add (sql);} / / get the stitched SQL public String getSql () {return sqlBuilder.toString () .trim ();} / / get the unique number, return and add a public int getUniqueNumber () {return uniqueNumber++;}}

And the next sentence is to parse the SQL we wrote to complete the splicing of the SQL.

RootSqlNode.apply (context)

The rootSqlNode here is the SQL content that we write in the tag, including, tag and so on.

The rootSqlNode object is of type SqlNode. In fact, the SQL statement here is parsed into a tree-level structure similar to the DOM node of HTML. In the test example in this section, the structure is similar to the following (not completely correct, only for reference value, indicating that the rootSqlNode structure is similar to the following structure):

Select * from balance where and data_org_code in # {dataOrgCode} and data_org_code = # {dataOrgCode}

The SqlNode definition is as follows:

Public interface SqlNode {boolean apply (DynamicContext context);}

The apply method is used to evaluate whether to concatenate the content of this SqlNode to the final returned SQL. Different types of SqlNode have different implementations. For example, the relevant SqlNode type in this section is IfSqlNode, corresponding to the if tag of the SQL statement we wrote, and the StaticTextSqlNode type that stores the final sql content.

Public class StaticTextSqlNode implements SqlNode {/ / stores the sql we wrote / / similar to and data_org_code in private final String text; public StaticTextSqlNode (String text) {this.text = text;} @ Override public boolean apply (DynamicContext context) {/ / calls the sqppendSql method of the DynamicContext object to assemble the final sql context.appendSql (text); return true;} public class IfSqlNode implements SqlNode {/ / evaluator private final ExpressionEvaluator evaluator / / the test attribute value used to determine whether this statement is valid or not in the if tag / / corresponds to one of our examples: "dataOrgCodes! = null and dataOrgCodes.size > 0" private final String test; / / if tag. If there are no other tags in the if tag, then the value here is the text attribute of the StaticTextSqlNode node / / StaticTextSqlNode node is the sql statement private final SqlNode contents that we finally need to concatenate. / / contents is the content we defined in the if tag, and test is the content public IfSqlNode (SqlNode contents, String test) defined by the attribute test of the if tag. {this.test = test; this.contents = contents; this.evaluator = new ExpressionEvaluator () } @ Override public boolean apply (DynamicContext context) {/ / use the evaluator to evaluate whether the content in the test defined in the if tag is true if (evaluator.evaluateBoolean (test, context.getBindings () {/ / when the contents is a node of type StaticTextSqlNode, splice the content in the if tag to the sql / / otherwise continue to call the method apply (equivalent to a recursive call Until you find the lowest content node) contents.apply (context) Return true;} return false;}}

We can see it here.

Evaluator.evaluateBoolean (test, context.getBindings ())

This evaluation method is to compare the content of the test statement with the parsed Map of the parameters we pass in. If there is a value in our parameters, and the content is in line with the judgment of the test statement, then the sql statement is spliced. For example, in this example

And data_org_code in # {dataOrgCode}

And the parameters we passed in.

{"dataOrgCodes": ["6", "2"]}

You can see that the parameter returns true when compared to the test statement "dataOrgCodesplaces = null and dataOrgCodes.size > 0".

Fourth, analyze the generation of excess SQL

According to the above steps, we can see that our bug is generated in

Evaluator.evaluateBoolean (test, context.getBindings ()) is generated by this step. That is, there is an attribute in context.getBindings () that satisfies dataOrgCode! = null and dataOrgCode! =''. Debug validates the following

You can see that the Map that stores the parameter mapping has the attribute of dataOrgCode, but the attribute we pass in is only the dataOrgCodes array, but there is no dataOrgCode attribute, so how did this dataOrgCode attribute come from?

Do debug from scratch again and find that the problem occurs in the apply method of ForEachSqlNode.

Public boolean apply (DynamicContext context) {/ / get parameter mapping storage Map Map bindings = context.getBindings () / / get the parameter parameter in bingdings. Key is collectionExpression, that is, what we wrote in the collection value of the tag foreach tag / / get the corresponding value from the parameter mapper according to collectionExpression. This time the value is: ["1", "2"] final Iterable iterable = evaluator.evaluateIterable (collectionExpression, bindings, Optional.ofNullable (nullable) .orElseGet (configuration::isNullableOnForEach)); if (iterable = null | |! iterable.iterator (). HasNext ()) {return true } / / the first parameter boolean first = true; / / add the content applyOpen (context) that we defined in the open value of the foreach tag to the stitching applyOpen; / / the counter int I = 0; / / traversing the array data we passed in ["1", "2"] / / represents the value in our traversal array, such as "1" for (Object o: iterable) {DynamicContext oldContext = context If (first | | separator = = null) {context = new PrefixedContext (context, ");} else {context = new PrefixedContext (context, separator);} int uniqueNumber = context.getUniqueNumber (); / / the contents of the index value of the foreach tag are stored as key, and the counter value I is stored in bingdings as value. / / for example, the first loop is ("index", 0). Note: since the same key will be overwritten, the final storage will be ("index", userIds.length-1) / / and generate a key with the value of ITEM_PREFIX + index + content + "_" + uniqueNumber,value stored in bingdings as uniqueNumber. / / for example, the first loop is ("_ _ frch_index_0", 0) applyIndex (context, I, uniqueNumber); / / the contents of the item value of the foreach tag are stored as key, and the values in the traversal array are stored in bingdings as value. / / for example, the first loop is ("userId", "1"). Note: since the same key will be overwritten, the final stored value is ("index", userIds [userIds.length-1]) / / and a key is generated as ITEM_PREFIX + item value content + "_" + uniqueNumber,value is stored in the bingdings for the values in this traversal array. / / for example, the first loop is ("_ _ frch_userId_0", "1") applyItem (context, o, uniqueNumber); contents.apply (new FilteredDynamicContext (configuration, context, index, item, uniqueNumber)); if (first) {first =! ((PrefixedContext) context). IsPrefixApplied ();} context = oldContext; / / counter plus an iTunes + } / / after foreach traversal, add the close content applyClose (context) defined by the foreach tag; return true;}

As you can see from the source code, the problem lies in traversing the dataOrgCodes array. In executing the apply method, there are

ApplyIndex (context, I, uniqueNumber)

ApplyItem (context, o, uniqueNumber)

# ForEachSqlNodeprivate void applyIndex (DynamicContext context, Object o, int I) {if (index! = null) {context.bind (index, o); context.bind (itemizeItem (index, I), o);}} private void applyItem (DynamicContext context, Object o, int I) {if (item! = null) {context.bind (item, o); context.bind (itemizeItem (item, I), o) } # DynamicContextpublic void bind (String name, Object value) {bindings.put (name, value);}

As you can see from the above logic, when we iterate through the dataOrgCodes array, we are defined in the foreach tag

The values of item and index attributes are stored as key in the bingdings of DynamicContext, that is, the Map corresponding to the query parameter object we passed in. This results in that although we did not pass in the dataOrgCode attribute, the intermediate value dataOrgCode is generated in the process of executing the foreach of dataOrgCodes, resulting in unnecessary conditional statements in the final stitched SQL.

V. Solutions

According to reason, the framework we use is the secondary development of Mybatis (basically Mybatis), so there should not be such a big problem. So after finding the problem, I wrote a demo locally to reproduce it, and found that the local one would not have this problem. I was immediately confused. Then I went to github and pulled down the source code of Mybatis for comparison, and finally found some problems.

Mybatis found the problem in 2017 and fixed it, adding the intermediate value generated by removing this foreach traversal at the end of the method, that is, deleting the key we defined in the tag item and index from the parameter mapping Map, so that the problem in this section will not occur.

However, the framework I use is still not updated, using the 2012 version of the code. So in order to solve this problem, you can only modify the attribute value name of item in the foreach tag to avoid conflicts with the attribute name in the test of the if tag. That is, change to the following SQL code.

Thank you for your reading. The above is the content of "instance Analysis of SQL nodes in Mybatis". After the study of this article, I believe you have a deeper understanding of the problem of instance analysis of SQL nodes in Mybatis, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.