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

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

// where status = 0 and (type = 1 or (type > 10 and type < 20))
wrapper.eq("status", 0)
    .and(wrapper -> wrapper.eq("type", 1)
         .or(wrapper2 -> 
             wrapper2.gt("type", 10)
             .lt("type", 20));

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

Aggregation Query methods

• selectMaps()

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

• selectObjs()

List<Object> mapList = userMapper.selectObjs(
    new QueryWrapper<SysUser>()
    .select("sum(num) as typeTotal")
    .groupBy("type"));

• selectCount()

userMapper.selectCount(queryWrapper);

select

queryWrapper.select("count(*) as typeCount");
queryWrapper.select("submit_content_type as name, count(*) as value")

group by

queryWrapper.groupBy("type"));

having

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

Others

non-query fields

@TableField(exist = false)
private IdName creator;

Using MySQL keyword as a column name

@TableField(value = "`type`")
private Integer type;

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;

Update

userService.update(new UpdateWrapper<User>()
    .set("name", "updateName")
    .eq("id", 1));