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

This article mainly explains how to use the where tag of mybatis. Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use the where tag of mybatis.

When we construct sql dynamically, we often use where 1room1 in order to prevent injection or improper statements.

Select * from zc_chat_group WHERE 1 id= # {id} and leader_num = # {leaderNum} and group_type = # {groupType}

But using the where tag can simplify this statement.

Select * from zc_chat_group id= # {id} and leader_num = # {leaderNum} and group_type = # {groupType}

When this sql is executed, if the parameter id is null, the execution result of this statement is

Select * from zc_chat_group where leader_num = 'xx' and group_type =' xx'

The 'where' tag knows that if there is a return value in the tag it contains, it will insert a' where'. In addition, if the content returned by the tag begins with AND or OR, it will be removed.

The use of Mybatis where tags

In order to achieve the tuning of MySQL performance, we can implement it based on Mybatis's where tag. The where tag is the top-level traversal tag, which needs to be used with the if tag, and it makes no sense to use it alone. There are usually two forms of implementation.

Method 1:

Select * from t_user username = # {username} and id_no = # {idNo}

Method 2:

Select * from t_user and username = # {username} and id_no = # {idNo}

If you look closely, you will find that the difference between the two methods lies in whether the SQL statement in the first if condition has and.

Here are two features of the where tag:

First, the where clause is inserted only if the if tag has content

Second, if the clause is enabled as "AND" or "OR", the where tag will replace it.

So, both of the above are fine, and Mybatis's where tag will do something for us.

However, it is important to note that the where tag only intelligently removes (ignores) the prefix of the first conditional statement. Therefore, it is recommended that when using where tags, it is best to write an and prefix or or prefix on each statement, otherwise there will be problems like the following:

Select * from t_user username = # {username} id_no = # {idNo}

The generated SQL statement is as follows:

Select * from t_user WHERE username =? Id_no =?

Obviously, the grammar is wrong.

Therefore, when using where tags, it is recommended that all conditions be added with and or or

Advanced: custom trim tags

The above use of the where tag can achieve the stitching condition statement, automatically remove the first condition of the and or or, then if it is other custom keywords can also be removed?

At this point, the where tag is powerless, the trim tag is on the stage, it can also achieve the function of the where tag.

Select * from t_user and username = # {username} and id_no = # {idNo}

Rewrite the above write based on the where tag to the trim tag and find that the execution effect is exactly the same. And trim tags have a more flexible customization.

The pit of where statement

In addition, when using where statements or other statements, we must pay attention to one place, and that is: the use of annotations.

Let's start with an example:

Select * from t_user and username = # {username} / * and id_no = # {idNo} * / and id_no = # {idNo}

The comment / * * / is added to the above SQL statement, and the resulting SQL statement is:

Select * from t_user WHERE username =? / * and id_no =? * / and id_no =?

When executing, report an error directly.

There is another example:

Select * from t_user-- and username = # {username} and username = # {username} and id_no = # {idNo}

The generated SQL statement is:

Select * from t_user WHERE-and username =? And username =? And id_no =?

It can also lead to false positives.

This is because when we configure SQL using XML, if we add comments after the where tag, then when there are child elements that meet the criteria, except

Comments will be ignored by where except parsing, other comments such as / / or / * * / or-- will be treated by where as the first clause element, resulting in subsequent real first AND clause elements or OR clause elements can not be successfully replaced by the prefix, resulting in syntax errors.

At the same time, in practice, individuals often find that improper use of annotations in XML leads to SQL syntax errors or execution errors. It is strongly recommended that, not necessarily, do not comment out SQL in XML, and that you can use version management tools to trace history and changes.

At this point, I believe you have a deeper understanding of "how to use the where tag of mybatis". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.