I am using Mybatis-Plus recently, MMM, it smells good!! Here are some tips for using Mybatis-Plus

! [](https://pic2.zhimg.com/80/v2-56f497aa018a37e5cc071e1fcff62577_720w.png)

1. Conditional query (QueryWrapper)

The SQL statement we need to query is as follows: 

SELECT * FROM user_info WHERE age = 20
Copy the code
Then the corresponding code can be: 

QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("age", 20);
List<UserInfo> list = userInfoMapper.selectList(queryWrapper );
Copy the code
Above is the query user table, the user age is equal to 20 user information

Conditional query (QueryWrapper lambda)

QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<>();
queryWrapper.lambda().eq(UserInfo::getAge, 20);
List<UserInfo> list = userInfoMapper.selectList(queryWrapper );
Copy the code
This is the QueryWrapper lambda expression to avoid writing field names incorrectly or SQL errors due to unconverted hump writing

3. Conditional query (LambdaQueryWrapper)

LambdaQueryWrapper<UserInfo> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(UserInfo::getAge, 20);
List<UserInfo> list = userInfoMapper.selectList(queryWrapper );
Copy the code
LambdaQueryWrapper is written essentially the same as QueryWrapper

4, paging query

LambdaQueryWrapper<UserInfo> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.eq(UserInfo::getAge, 20); Page<UserInfo> queryPage = new Page<>(Page, limit); / / paging query IPage < the UserInfo > IPage = userInfoMapper. SelectPage (queryPage queryWrapper); Long total = iPage.getTotal(); List<UserInfo> List = iPage.getRecords();Copy the code
This is a paging query and queryWrapper can be null if no conditional query is required

5, paging query (table)

When we need associative table paging query, step 3 no longer meets our requirements, so we need to do associative table paging query

Suppose we need the following SQL statement: 

SELECT
	a.*,
	b.`name` AS sex_text 
FROM
	user_info a
	LEFT JOIN user_sex b ON ( a.sex = b.id ) 
WHERE
	1 = 1 
	AND a.age > 20
Copy the code
Then we need to do the following:

  • 1. Create UserInfoVO. Java

    import com.zyxx.entity.UserInfo; import lombok.Data;

    @Data public class UserInfoVO extends UserInfo {

    // Private String sexText;Copy the code

    } 123456789

  • 2, UserInfoMapper. In Java

    IPage list(Page page, @Param(Constants.WRAPPER) Wrapper queryWrapper);

  • 3, UserInfoMapper. XML

    SELECT a.*, b.`name` AS sex_text FROM user_info a LEFT JOIN user_sex b ON ( a.sex = b.id ) ${ew.customSqlSegment}

  • 4, UserInfoServiceImpl. In Java

    LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.eq(UserInfo::getAge, 20); Page queryPage = new Page<>(Page, limit); IPage = userInfomapper. list(queryPage, queryWrapper); Long total = iPage.getTotal(); // user data List List = iPage.getRecords();

The above is the operation of paging query (syntable), this should be used more

6, AND AND OR

QueryWrapper is connected to and by default, but in our business requirements we will definitely use OR to write SQL

  • 1. Assume that we need the following SQL statement:

    SELECT a.* FROM user_info a WHERE 1 = 1 AND a.id <> 1 AND ( a.name = ‘jack’ OR a.phone = ‘13888888888’ )

So we can write this: 

LambdaQueryWrapper<UserInfo> queryWrapper = new LambdaQueryWrapper<>(); // AND a.id <> 1 queryWrapper.ne(UserInfo::getId, "1"); // AND ( a.`name` = 'jack' OR a.phone = '13888888888' ) queryWrapper.and(i -> i.eq(UserInfo::getName, "jack").or().eq(UserInfo::getPhone, "13888888888")); / / query results List < the UserInfo > List = userInfoMapper. The selectList (queryWrapper);Copy the code
This completes the initial and and or case

  • 2, complex suppose we need the following SQL statement:

    SELECT a.* FROM user_info a WHERE 1 = 1 AND a.id <> 1 AND ( (a.name = ‘jack’ AND a.category = 1) OR (a.phone = ‘13888888888’ OR a.category = 2) )

So we can write this: 

LambdaQueryWrapper<UserInfo> queryWrapper = new LambdaQueryWrapper<>(); // AND a.id <> 1 queryWrapper.ne(UserInfo::getId, "1"); // AND ( (a.`name` = 'jack' AND a.category = 1) OR (a.phone = '13888888888' OR a.category = 2) ) queryWrapper.and(i -> (i.and(j -> j.eq(UserInfo::getName, "jack").eq(UserInfo::getCategory, 1))).or(j -> j.eq(UserInfo::getPhone, "13888888888").eq(UserInfo::getCategory, 2))); / / query results List < the UserInfo > List = userInfoMapper. The selectList (queryWrapper);Copy the code
This completes the complex case of and and or

You can follow this rule when you encounter more complex SQL

7, @ TableLogic

This annotation is the logical deletion flag of Mybatis-Plus. Generally, the annotation is on the attributes of entities. If MY user information table uses a field (del_flag) to indicate the deletion status of data, as follows: 

@APIModelProperty (value = "Delete status (0-- not deleted 1-- deleted) ") @tableField ("del_flag") @TABLelogic private Integer delFlag;Copy the code
By default, Mybatis-Plus uses 0 to indicate valid and 1 to indicate invalid. Of course, it can also be customized, there are two ways:

  • 1. Notes

    @APIModelProperty (value = “delete status (0– not deleted 1– deleted) “) @tableField (“del_flag”) @TABLelogic (value = “1”, delval = “0”) private Integer delFlag;

Value = “” Default value, delval =” “deleted value

  • 2. Configuration files

    Mybatis configuration

    mybatis-plus:

    Global configuration

    Global-config: db-config: # logic-delete-field: DelFlag # Logic-delete-value: 1 # Logic-not delete-value: 0

So, when we query data, we will automatically add query criteria for us 

AND del_flag = 0
Copy the code
Logical deletion at this time, when we perform userInfoMapper. RemoveById (id), also won’t physically deleted, actually execute SQL is as follows: 

UPDATE user_info set del_flag = 1 where id = #{id}
Copy the code
Note: SQL you write in an XML file does not automatically apply logical delete conditions

Select * from ‘select’ where ‘select’

If you want to query only a few fields in a table, you should not query all the fields in the table if the table is large. 

SELECT
	id,
	`name`,
	phone 
FROM
	user_info 
WHERE
	1 = 1 
	AND age = 20
Copy the code
We only need to query the id, name, phone of the user whose age is equal to 20 

LambdaQueryWrapper<UserInfo> queryWrapper = new LambdaQueryWrapper<>(); // only query id, name, phone queryWrapper. Select (UserInfo::getId, UserInfo::getName, UserInfo::getPhone); // Age = 20 querywrapper. eq(UserInfo::getAge, 20); List<UserInfo> list = userInfoMapper.selectList(queryWrapper );Copy the code
Thus, we need to query the data for all the fields in the table, greatly reducing the query time

Select * from getOne; select * from getOne;

GetOne is a method in service. When we use getOne to query data, we usually do not use primary key ID to query data. If we use primary key ID to query data, we should use: 

UserInfo userInfo = userInfoService.getById(id);
Copy the code
If there is an openId field in our user table, such as wechat user, this must be unique. We need to use openId to query a user’s information. We usually use: 

LambdaQueryWrapper<UserInfo> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(UserInfo::getOpenId, openId);
UserInfo userInfo = userInfoService.getOne(queryWrapper);
Copy the code
However, there is a certain risk, that is, if there are two or more data of the same openId in the table, it will throw an exception, so we need to do this: 

LambdaQueryWrapper<UserInfo> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(UserInfo::getOpenId, openId);
UserInfo userInfo = userInfoService.getOne(queryWrapper, false);
Copy the code
As you can see, a false argument is passed with the meaning of:

  • According to Wrapper, query a record
  • Whether more than one result throws an exception
By viewing the source code: 

@Override
public T getOne(Wrapper<T> queryWrapper, boolean throwEx) {
    if (throwEx) {
        return baseMapper.selectOne(queryWrapper);
    }
    return SqlHelper.getObject(log, baseMapper.selectList(queryWrapper));
}
Copy the code
If true (the default) is passed in, the query is left unprocessed, otherwise paging is performed, and look at the getObject() method: 

@param list ignore * @param <E> Ignore * @return ignore */ public static <E> E getObject(Log log, List<E> list) { if (CollectionUtils.isNotEmpty(list)) { int size = list.size(); if (size > 1) { log.warn(String.format("Warn: execute Method There are %s results.", size)); } return list.get(0); } return null; }Copy the code
The first data is returned by default. Therefore, when multiple data is queried, the first data is returned by default