The Proper Use of the Where Tag in Mybatis | by Dwen | May, 2022

And some methods to not use it

Photograph by Joanna Kosinska on Unsplash

This text will summarize and kind out the fundamental utilization types, ideas, and issues which can be simple to come across within the the placetag in Mybatis so you possibly can higher observe and use it.

When the the placetag of Mybatis is just not used, we normally do guide splicing in keeping with the question circumstances. Listed below are some examples:

<choose id="selectSelective" resultType="com.secbro.entity.Person">
choose * from t_user
the place 1=1
<if take a look at="username != null and username != ''">
and username = #username
<if take a look at="idNo != null and idNo != ''">
and id_no = #idNo

This methodology primarily avoids assertion splicing errors, that are just like the next error SQL:

choose * from t_user the place and username = 'Tom' and id = '1001';
choose * from t_user the place and id = '1001';

When including 1=1, the SQL assertion is appropriate.

choose * from t_user the place 1=1 and username = 'Tom' and id = '1001';
choose * from t_user the place 1=1 and id = '1001';

It ought to be famous that the optimization filtering of the 1=1 situation must be executed by MySQL.

If this half could be executed within the utility, it should cut back MySQL stress. In spite of everything, purposes can simply scale horizontally.

To attain the tuning of MySQL efficiency, we are able to implement it primarily based on the the placetag of Mybatis.

The the placetag is the top-level traversal tag and must be used along side the iftag. It’s meaningless to make use of it alone. Normally, there are the next two implementation types:

Methodology one

Methodology two

Should you look intently, you will see that the distinction between these two strategies is whether or not the SQL assertion within the first ifsituation has and.

Listed below are two options of the The place tag:

  • First, the the placeclause is inserted provided that the if tag has content material.
  • Second, if the opening of the clause is andor or, the the placetag will substitute it and take away it.

So, the above two methods of writing are OK, and the the placetag of Mybatis will do one thing for us.

Nevertheless it ought to be famous that the the place tag will solely intelligently take away (ignore) the primary prefix that satisfies the conditional assertion.

Subsequently, it is suggested that when utilizing the the placetag, every assertion ought to ideally be prefixed with andor orprefix. In any other case, there will probably be issues with the next:

The generated SQL assertion is as follows:

choose * from t_user      WHERE username = ?  id_no = ?

Clearly, the syntax is flawed.

Subsequently, when utilizing the the placetag, it is suggested so as to add andor orto all circumstances.

Utilizing the the place tag above can routinely take away the andor orof the primary situation when splicing the conditional assertion, so if there are different customized key phrases, can additionally they be eliminated?

At this level, the the placetag is powerless. The trimtag is on the sector, and it may additionally implement the perform of the the placetag.

Rewrite the above writing primarily based on the the placetag into the trimtag, and discover that the execution impact is precisely the identical. And trim tags have extra versatile customization.

As well as, make sure to take note of one place when utilizing the the place the assertion or different statements, and use feedback.

Let’s have a look at an instance:

The remark of /**/is added to the above SQL assertion, and the generated SQL assertion is:

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

When executed, an error is reported straight.

Let’s see one other instance:

The generated SQL assertion is:

choose * from t_user WHERE -- and username = ? and username = ? and id_no = ?

It’s going to additionally trigger an error.

It is because we used XML to configure SQL when a remark was added after the the placetag. When there are subelements that meet the circumstances, aside from the <!-- -->, which will probably be ignored and parsed by the place, different feedback corresponding to // Or /**/ or and so forth. will probably be handled by the placeas the primary clause aspect.

This leads to the failure of the next actual first AND clause aspect or OR clause aspect to interchange the prefix, leading to a syntax error efficiently.

On the similar time, people typically discover that SQL syntax errors or execution outcomes are incorrect attributable to improper use of XML feedback. It’s strongly really helpful, however not essential, to not remark out the SQL within the XML. You’ll be able to hint the historical past and modifications by the model administration instrument.

More Posts