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

This article will explain in detail the example analysis of the dynamic SQL of Mybatis. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Preface

One of the powerful features of MyBatis is its dynamic SQL.

Dynamic SQL is a powerful feature of Mybatis. When using JDBC to operate data, if there are many query conditions, it is very painful to concatenate the conditions into SQL strings. The usual solution is to write a lot of if-else conditional statements to judge and concatenate, and make sure that you can not forget the space or omit the comma at the end of the field. Mybatis uses a powerful dynamic SQL language to improve the situation

Dynamic SQL's OGNL-based expressions make it easy to implement some logic in SQL statements, so what elements support this functionality? That's what I'm going to say next.

If: using if to realize simple condition selection

Choose (when,otherwise): equivalent to the switch statement in java, usually with when and otherwise

Where: simplify the conditional judgment of where in SQL statements

Set: resolve dynamic update statements

Trim: can flexibly remove redundant keywords

Foreach: iterates over a set, usually used for in conditions

The elements mentioned by the editor above are used in the mapper.xml file, and it is also a tag, double tag. In fact, the usage is similar to our common use of writing java. Next, the editor will explain the individual elements one by one. Let's talk about combination tags.

Using if+where to realize Multi-conditional query

Select * from userand userName like CONCAT ('%', # {userName},'%')

The above code is the simplest if+where SQL mapping statement, the where element tag will automatically identify whether there is a return value within the tag, if so, insert a where keyword, in addition, if the tag returns the content beginning with and or or, the where element will automatically remove it, the main attribute in the if element tag is the test attribute, and test is followed by an expression that returns true or false to judge.

Using if+trim to realize conditional query

Select * from userand userName like CONCAT ('%', # {userName},'%')

You can see from the above code that the use of trim and where element tags is similar, just a few more elements in the trim tag, so what more elements?

Prefix: prefix, which is used to prefix the content contained in trim.

Suffix: suffix, which is used to add a suffix to the content contained in trim.

PrefixOverride: omits the specified content (such as "and | or") for the first part of the trim containing content.

SuffixOverride: the specified content is ignored at the beginning and end of the content contained in the trim.

Next, let's look at using dynamic SQL to implement update operations.

Use if+set to implement update operation

Update app_infologoPicPath=# {logoPicPath}, logoLocPath=# {logoLocPath}, modifyBy=# {modifyBy}, modifyDate=# {modifyDate}, where id=# {id}

The above code is the simplest if+set dynamic SQL, from the above code we can see that the update operation is dynamic, meaning that your value is not empty, if not empty, it will be updated to you, if it is empty, ignore it, emmmm, it seems that its design is very user-friendly.

The above operation can also be implemented with trim.

If+trim implements update operation

Update app_infologoPicPath=# {logoPicPath}, logoLocPath=# {logoLocPath}, modifyBy=# {modifyBy}, modifyDate=# {modifyDate}

The editor has introduced the usage of trim before, so I won't repeat it here. Serious children's shoes should be understood.

The next step is to use foreach to complete complex queries. Let's first talk about the foreach iteration with Mybatis input parameters as an array.

The editor first introduces you to the basic usage and properties of foreach. Foreach is mainly used in building in conditions, which can iterate over a collection in a SQL statement. Its main attributes are: item, index, collection, separator, close, open. Let's take a look at the code.

Select * from user_info where userRole in# {roleIds}

For the SQL conditional loop (in statement), you need to use the foreach tag, so the editor will use the above code to introduce the basic properties of foreach.

Item: an alias (roleIds at this point) that represents the iteration of each element in the collection.

Index: specifies a name that represents the location of each iteration during the iteration.

Open: indicates what the statement starts with (since it is an in conditional statement, it must start with "(")

Separator: indicates what symbol is used as the delimiter between each iteration (since it is an in conditional statement, it must be delimited by ",).

Close: indicates what the statement ends with (since it is an in conditional statement, it must end with ")".

Collection: the most critical and error-prone attribute, it is important to note that this attribute must be specified, and the value of this attribute is different in different cases. There are three main situations.

If the input parameter is a single parameter and the parameter type is a list, the value of the collection attribute is list.

If the input parameter is a single parameter and the parameter type is an array, the value of the collection attribute is array (here the parameter Integer [] roleIds is the array type, so the value of the collection attribute is set to "array").

If the incoming parameters are multiple parameters, they need to be encapsulated as a Map for processing.

This is the end of this article on "sample Analysis of dynamic SQL of Mybatis". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.