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

Use mybatis to CRUD a table, but we find that the SQL statements written are relatively simple, if there is a more complex business, we need to write complex SQL statements, often need splicing, and splicing SQL, slightly careless, due to quotation marks, spaces and other missing may lead to errors.

So how to solve this problem? This is what this article talks about using mybatis dynamic SQL, through if, choose, when, otherwise, trim, where, set, foreach and other tags, can be combined into very flexible SQL statements, thus improving the accuracy of SQL statements, but also greatly improve the efficiency of developers.

Let's take the User table as an example:

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}

Zhengzhou Infertility Hospital: http://www.zzchyy110.com/

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}

Http://www.chacha8.cn/detail/1132398241.html

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=?

In the end, don't delete it automatically.

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 where username=?

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

Select * from user

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 {

/ / encapsulate the id of multiple users

Private List ids

Public List getIds () {

Return ids

}

Public void setIds (List ids) {

This.ids = ids

}

}

②, 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 data according to id collection

@ Test

Public 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)

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.

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.