Continue to output original articles, click on the blue word to follow me
This is why Technology’s 14th original article
In the actual development process, I stepped on a pit of Mybatis, I think it is worth recording and sharing.
Let’s start with what this pit is. If you’ve stepped in this hole and know exactly why, this article will reinforce your impression. If you haven’t, you should take a good look, because you will.
In mybatis, ognlops.equal (0,””) returns true.
This article will be in accordance with the problems encountered -> analysis of problems -> solve the problem of the writing ideas, with the method of tracking the source code, to analyze the problem.
At the same time, share how I used the method of reverse investigation to find the most critical line of source code through the Debug mode, and then understand the cause and effect, and finally solve the problem.
Mybatis 3.5.3 version
Background introduction, requirements analysis
To set the scene, simulate a requirement.
There is an order table with the following structure:
To simplify things, let’s assume that there are only two values in the table:
The status of the order no. 1234 is 0. [Closed] The status of the order No. 4321 is 1.
The developed function is to query the OrderName vaguely with the following interface (OrderName should be a string in the figure) :
Mapper.xml (mapper.xml)
Now you need to add a feature that filters orders by status to the existing feature:
Suppose a page has a drop-down box that filters order data based on order status.
When the user selects “paid”, the background receives the number 1 in Byte type.
When the user selects “unpaid”, the background receives the number 0 as Byte type.
Ready to develop
The requirements are now identified and filtered based on order status.
Very simple, the most important modification place is the modification of mapper. XML, as for how to transmit XML from the front end I will not elaborate, I believe that mybatis friends know.
Add an input parameter to the interface orderName:
Then modify the corresponding XML:
You can simply press CTRL + C to change the original if tag in the XML file and CTRL + V to change the name inside.
Start self-testing and encounter problems
Unit testing, even if it’s simple, obvious, and confident, is part of a programmer’s job ethic.
The unit tests are as follows: pass in states 0 and 1 respectively
Based on the data in our table, we expect each query to have one data.
Let’s take a look at the result:
If status is 0, the number of queried items is 2
If status is 1, the number of queried items is 1
This result is not what we expected! What’s going on?
When I had this problem, I knew there was something wrong.
Two years ago, I would have been programming for search engines right away. The problem encountered a search, according to the advice of net friends, very soon solved. However, it was soon forgotten. Also, I wasn’t connected to the Internet when I encountered this problem.
Don’t be so quick to ask the search engines. Do not panic, to analyze, calm analysis after the harvest.
To analyze problems
The first step in the analysis is to simply print out the SQL, see what the final SQL is, and see why the results are not as expected.
So we add this configuration to application.properties:
logging.level.com.xxxx.xxxx.mapper = debug
Note: replace XXXX with mapper package path
With the SQL print, we find that Mybatis does not concatenate the WHERE keyword when status is 0.
Here it is natural to think of the next step: why did Mybatis not join where keyword for us?
Or another question: where is mybatis using what logic to concatenate SQL?
The normal method is to add breakpoints to trace, but I want to share a “SAO” operation that I checked at the time, locating the problem very quickly. That’s a reverse sweep.
Reverse screening
Now we have identified the problem of SQL splicing. I also got the complete SQL through the log.
The log configuration looks like this:
logging.pattern.console=%date{yyyy-MM-dd HH:mm:ss.SSS} %-5level[%thread]%C{56}.%method:%L -%msg%n
The printed log reads something like this (with a partial intercept) :
In the org. Apache. Ibatis. Logging. JDBC. BaseJdbcLogger 143 lines, the debug method to print the log, this line of log is my breach.
At this point, I’ve got my entire SQL, and if I go back, I can quickly figure out where my SQL was generated.
So I set a breakpoint on line 143 of BaseJdbcLogger and run it.
With the Debug mode of IDEA, we can get the entire call link from the start of the program to the breakpoint. (If the image below is not clear, click on it to see a larger image) :
By calling the chain, three steps down, we can see that the SQL is retrieved from boundSql:
So where does boundSql come from? We continued on our way back.
Going back 11 steps, we can see the boundSql fetch process:
To make it easy for you to find the source code, I put the name of the corresponding method here: org. Apache. The ibatis. Executor. CachingExecutor# query (org. Apache. Ibatis. Mapping. MappedStatement, java.lang.Object, org.apache.ibatis.session.RowBounds, org.apache.ibatis.session.ResultHandler)
Remember when we started, we didn’t know where the SQL was concatenated by what logic.
And that’s the answer to the first part. SQL is through org. Apache. Ibatis. Executor. CachingExecutor produced 81 lines of code:
BoundSql boundSql = ms.getBoundSql(parameterObject);
So all we need to do is put a breakpoint at the beginning of this line of code, and then we know the answer to the second part of the question. What is the logical splicing?
If you are not familiar with Mybatis, it will take some time for you to find this line of code through the Debug mode, while the reverse investigation I mentioned above can save a lot of time.
Key source
The following is the normal forward lookup process, and eventually you will reach the key point of the whole article:
org.apache.ibatis.ognl.ASTNotEq#getValueBody
Why does mybatis return true between the number 0 and the empty string “”? There are no secrets under source code, continue to Debug and you will find this place:
org.apache.ibatis.ognl.OgnlOps#doubleValue
After returning here, the real comparison is here:
V1 and v2 both end up at 0.0. So it returns true.
Because ognlops.equal (0,””) returns true, the entire expression [ognlops.equal (0,””)? Boolean.FALSE: Boolean. True] returns FALSE.
Then there are the three questions to answer:
Where did v1=0 come from? Where does v2=”” come from? What is the problem with returning FALSE?
So the one in the diagram is the value of v1, and this 0 is the query condition that I passed in.
The number two in the figure is the value of v2, and the source of this “” is the expression I wrote inside the if tag in mapper.xml.
The reason the number three in the figure is false is because this expression [ognlops.equal (0,””)? Boolean. False: Boolean.TRUE] returns false. If it returns false, it doesn’t go into the following code: contents. Apply (context).
And this line of code, is to answer the second half of the question we raised before, mybatis through what logic splicing SQL?
Parsing the test condition that we wrote in the if tag in mapper.xml, and then concatenating the content of the condition, which is SQL, if true is returned.
Since the if tag here looks like this:
<if test=”orderStatus ! = null and orderStatus ! = “” >
The orderStatus! =null returns true,orderStatus! = “returns false, so if the whole expression returns false, then the SQL in the if tag is not concatenated.
At this point, we combine the source code, for why there will be problems to finish the analysis.
To solve the problem
Mapper.xml: mapper.xml: mapper.xml: mapper.xml: mapper.xml: mapper.xml: mapper.xml
Or change it to this:
Take a look at the execution result:
So that’s what we expected.
But think back to how I originally modified Mapper.xml:
You can simply press CTRL + C to change the original if tag in the XML file and CTRL + V to change the name inside.
Yes, I mindlessly used the CV method. I wrote the bug in the laughter. Does it make any sense that the type I pass in as orderStatus is a Byte and “”?
But I also thank this brainless CV, let me step on this pit, and research clear. Get new knowledge points.
At the same time, I also thank myself for doing the unit test. Otherwise, I would think that the test student would not be able to use such a question when testing, and he would think that I was a weak chicken.
A few last words
After solving this problem, I searched online and found that some people also encountered this problem, but the first article I searched was a wrong description, he said that 0 would be treated as null in Mybatis? Did you read the source code? Or are we talking about different things?
And then there are a lot of other articles that just throw you a solution, not why it solves the problem. And that’s not perfect, in my opinion, because it’s hard to impress, and you or your colleagues are going to say, “Oh, that’s a problem I’ve seen before.” How? I forget.
Don’t you talk nonsense?
My thoughts on interviewing 15 2020 graduate students from 211/985 universities are also appropriate here:
Later I share this problem in the group, a friend in the group also shared an article to me, fat toward the big guy wrote “and this kind of operation? Analysis why to see the source code”. The article gives another solution, well founded, concise, is a very good article, we can have a look.
The end of the
This is the end of the article. If you can get this knowledge in this article, or when you encounter this problem can think of this article, this is the greatest appreciation of this article, the highest embodiment of the value of the article.
What I hope more is that when you encounter this problem, you finish your analysis and see my article when you search online. Because of their analysis, is always the most impressive, other articles just play an embellishment role.
If you find something wrong, please leave a message and point it out to me so that I can modify it.
If you think the article is good, your likes, comments, forwarding, sharing, appreciation is the biggest encouragement to me.
Thank you for reading and for your attention.
The above.
Click here to leave a message
Continuous output of original
Long press to identify concerns
Past wonderful
【 recommendation 】 I interviewed 15 postgraduates from 985/211 universities and their thoughts after the class of 2020.
Some threads died and it became an interview question! [recommendation] Ordinary 2, graduation three years, after north drift, I am how to become a program ape.
After 77 days of subscription, I earned XXX yuan.
After participating in Dubbo community Developer Day chengdu station, it brought me some thoughts. This Java basic question really has a pit! I beg you, think before you answer.
Java foundation questions really pit! I didn’t think there was a sequel.
To be honest, I found a mistake in the art of Concurrent Programming in Java!
What do I see as a programmer when Jay Chou does QQ music?
From the source point of view -Dubbo2.7 new features of asynchronous transformation!
Transaction not rolled back? Come, we analyze a wave together from phenomenon to principle!
Original isn’t easy. Give it a thumbs up
If you find something wrong, please leave a message and point it out to me so that I can modify it.