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

Editor to share with you MyBatis dynamic SQL sentence example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Brief introduction of dynamic SQL statement

Dynamic SQL statement is a very powerful function of MyBatis, which allows us to combine different SQL statements according to different requirements. Dynamic SQL statements can be added to select, update, insert and delete tags.

IF tag

The IF tag can insert the contents of the IF tag in the SQL statement when the condition is true. If it is not true, it will not be inserted.

Example:

Select * from tb_user where

U_realname=# {realname}

And u_gender=# {gender}

In the above code, the query is based on the attributes of the User object. If the name is not empty, the name is inserted as a condition, and if the gender is not empty, gender is inserted as a condition.

But we will find that if the name is empty and the gender is not empty, the SQL statement will become: select * from tb_user where and u_gender=# {gender}

Or if the name or gender is empty, it will become: select * from tb_user where

There will be syntax errors, so we need the next label: where

Where tag

The Where tag is used to configure the where condition, which removes the excess and or or. If none of the conditions are true, the where in the sql is automatically removed.

The code is changed to:

Select * from tb_user

U_realname=# {realname}

And u_gender=# {gender}

And u_age=# {age}

So there won't be any possible mistakes above.

Trim tag

The functions of the above where tags can also be achieved using trim tags, and trim can remove redundant prefixes and suffixes.

Syntax:

Example:

...

Set tag

Used to configure the set part of the update statement, you can automatically add the set keyword and remove the extra comma

Example:

Update tb_user

U_name=# {name}

U_password=# {password}

Where u_id=# {id}

You can also use trim to implement:

U_name=# {name}

...

Foreach tag

Values used to iterate through a collection or array, such as querying by multiple person names

Select * from tb_user where u_realname in ('Zhang San','Li Si')

Syntax:

# {variable name}

Note: the collection name needs to be configured using the @ Param annotation in the Mapper interface

Example:

Select * from tb_user where u_realname in

# {name}

Choose tag

In Java, there are if, if-else and switch statements to judge multiple conditions, and MyBatis also has Choose tags to judge multiple conditions.

Example:

SELECT * FROM BLOG WHERE state = 'ACTIVE'

AND title like # {title}

AND author_name like # {author}

AND featured = 1

Here to judge from the first when, if it is established, insert the condition, end the choose tag, if it is not established, and then judge the next when, if all when is not established, insert the statement in the otherwise.

The above is all the contents of the article "sample Analysis of dynamic SQL statements in MyBatis". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.