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

In this issue, the editor will bring you a dynamic SQL about how to analyze mybatis. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. Dynamic SQL:if statement

Query data according to username and sex. If username is empty, it will be queried only by sex; otherwise, it will be queried only by username

First of all, do not use dynamic SQL to write

Select * from user where username=# {username} and sex=# {sex}

In the above query statement, we can find that if # {username} is empty, then the query result is also empty, how to solve this problem? Use if to determine

Select * from user where username=# {username} and sex=# {sex}

If you write like this, we can see that if sex equals null, then the query statement is select * from user where username=# {username}, but what if usename is empty? So the query statement is select * from user where and sex=# {sex}, which is the wrong SQL statement, how to solve it? Take a look at the following where statement

2. Dynamic SQL:if+where statement

Select * from user username=# {username} and sex=# {sex}

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

3. Dynamic SQL:if+set statement

Similarly, the above query SQL statement contains the where keyword, if in the update operation, contains the set keyword, how do we deal with it?

Update user u u.username = # {username}, u.sex = # {sex} where id=# {id}

Write like this, if the first condition username is empty, then the sql statement is: update user u set u. Sexuality? Where id=?

If the first condition is not empty, then the sql statement is: update user u set u.username =?, u.sex =? Where id=?

4. Dynamic SQL:choose (when,otherwise) statement

Sometimes, we don't want to use all the query conditions, we just want to choose one of them. One of the query conditions can be satisfied. This kind of problem can be solved by using choose tags, similar to Java's switch statement.

Select * from user id=# {id} and username=# {username} and sex=# {sex}

That is to say, here we have three conditions, id,username,sex, and we can only choose one as the query condition.

If id is not empty, the query statement is: select * from user where id=?

If id is empty, see if username is empty, and if not, then the statement is select * from user whereusername=?

If username is empty, the query statement is select * from user where sex=?

5. Dynamic SQL:trim statement

A trim tag is a formatted tag that can perform the functions of set or where tags.

①, rewrite the if+where statement of the second point above with trim

Select * from user and username=# {username} and sex=# {sex}

Prefix: prefix

Prefixoverride: remove the first and or or

②, rewrite the if+set statement of the third point above with trim

Update user u u.username = # {username}, u.sex = # {sex}, where id=# {id}

Suffix: suffix

Suffixoverride: remove the last comma (it can also be another tag, just like the and in the prefix above)

6. Dynamic SQL: SQL fragment

Sometimes we use a lot of sql statements. In order to increase the reusability of the code and simplify the code, we need to extract the code and call it directly when we use it.

For example, if we often need to do joint queries based on user name and gender, then we extract this code as follows:

AND username = # {username} AND sex = # {sex}

Reference sql snippet

Note:

①, it is best to define sql fragments based on a single table to improve the reusability of fragments.

②, it is best not to include where in the sql fragment

7. Dynamic SQL: foreach statement

Requirements: we need to query the users in the user table whose id is 1JI 2pm 3 respectively.

Sql statement:

Select * from user where id=1 or id=2 or id=3

Select * from user where id in (1, 2, 3)

①, create a UserVo class that encapsulates the properties of a List ids

Package com.ys.vo;import java.util.List;public class UserVo {/ / encapsulates id private List ids; public List getIds () {return ids;} public void setIds (List ids) {this.ids = ids;}} of multiple users

②, we use foreach to rewrite select * from user where id=1 or id=2 or id=3

Select * from user id=# {id}

Test:

/ / query user table data @ Testpublic void testSelectUserByListId () {String statement = "com.ys.po.userMapper.selectUserByListId"; UserVo uv = new UserVo (); List ids = new ArrayList (); ids.add (1); ids.add (2); ids.add (3); uv.setIds (ids) according to id collection; List listUser = session.selectList (statement, uv); for (User u: listUser) {System.out.println (u) } session.close ();}

③, we use foreach to rewrite select * from user where id in (1, 2, 3)

Select * from user # {id} 8, Summary

In fact, the writing of dynamic sql statements is often a splicing problem, in order to ensure accurate splicing, we had better first write the original sql statements, and then compare them with mybatis dynamic sql to prevent errors.

The above is the editor for you to share how to analyze the dynamic SQL of mybatis, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are 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.