MyBatis-Plus Common Usage

Contents

1. 1. Query
   1. 1.1. QueryWrapper
   2. 1.2. Basic Query
   3. 1.3. Page Query
   4. 1.4. One Column Query
   5. 1.5. Aggregation Query
   6. 1.6. Others
2. 2. DML

Query

QueryWrapper

QueryWrapper

// usage 1
QueryWrapper<SysUser> queryWrapper = new QueryWrapper<SysUser>()
    .eq("user_id", userId)
    .like(name != null, "name", name);

// usage 2
QueryWrapper<SysUser> queryWrapper = Wrappers.query(sysUser)
    .eq("user_id", userId);

LambdaQueryWrapper

// usage 1
LambdaQueryWrapper<SysUser> lambdaQueryWrapper = new LambdaQueryWrapper<SysUser>()
    .eq(SysUser::getUserId, userId)
    .like(name != null, SysUser::getName, name);

// usage 2
LambdaQueryWrapper<SysUser> lambdaQueryWrapper = Wrappers.lambdaQuery(sysUser)
    .eq(SysUser::getUserId, userId);

// usage 3
LambdaQueryWrapper<SysUser> lambdaQueryWrapper = Wrappers.lambdaQuery(SysUser.class)
    .eq(SysUser::getUserId, userId);

Convert QueryWrapper to LambdaQueryWrapper

LambdaQueryWrapper<SysUser> lambdaQueryWrapper = new QueryWrapper<SysUser>()
    .eq("user_id", userId)
    .lambda()
    .eq(SysUser::getType, type);

Basic Query

An example

List<SysUser> sysUserList = userService.list(
    new QueryWrapper<SysUser>()
    .select("name")
    .eq("type", type)
    .orderByDesc("id")
    .last("limit 10"));

from

new QueryWrapper<Entity>()

select

wrapper.select("column 1", "column 2")

where

wrapper.eq("type", 1)
    .like("title", "test")
    .in("user_id", userIds)
    .ge("age", 18)
    .apply("date(gmt_create)=date({0})", new Date())
    .apply("date_format(gmt_modified,'%Y-%m-%d') = date({0})", new Date())

and…or

wrapper.and(wrapper -> wrapper.eq("type", 1)
                .or().eq("type", 2))

order by

wrapper.orderByDesc("id")

limit

wrapper.last("limit 10")

Page Query

1. Request URL

http://xxx.xxx/?current=1&size=10&orders[0].column=pubtime&orders[0].asc=true

2. Page Query API

IPage<T> selectPage(IPage<T> page, Wrapper<T> queryWrapper)

3. The Page Class

com.baomidou.mybatisplus.extension.plugins.pagination.Page

• Fields for Request parameters
  • long size
  • long current
  • List orders
    • String column
    • boolean asc
• Fields for response
  • List records
  • long total

One Column Query

List<Object> names = baseMapper.selectObjs(
    new LambdaQueryWrapper<User>()
    .select(User::getName)
    .eq(User::getType, 1));
if (names == null) {
    return Collections.emptySet();
} else {
    return names.stream()
        .map(Object::toString)
        .collect(Collectors.toSet());
}

Aggregation Query

An example

List<Map<String, Object>> mapList = userService.listMaps(new QueryWrapper<SysUser>()
                                                         .select("count(*) as typeCount")
                                                         .groupBy("type"));

group by

wrapper.groupBy("type"));

having

wrapper.having("COUNT(*) > 10"))

Others

non-query fields

@TableField(exist = false)
private IdName creator;

DML

update it to null when it value is null

@TableField(fill = FieldFill.UPDATE)
private String name;

logical delete

@TableLogic(value="0",delval="1")
private Boolean delFlag;