By

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

1
2
3
4
5
6
7
8
// 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

1
2
3
4
5
6
7
8
9
10
11
12
// 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

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

Basic Query

An example

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

from

1
new QueryWrapper<Entity>()

select

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

where

1
2
3
4
5
6
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

1
2
3
// where type = 1 or type = 2
wrapper.and(wrapper -> wrapper.eq("type", 1)
                .or().eq("type", 2))
1
2
3
4
5
6
// 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

1
wrapper.orderByDesc("id")

limit

1
wrapper.last("limit 10")

Page Query

  1. Request URL

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

  1. Page Query API
1
IPage<T> selectPage(IPage<T> page, Wrapper<T> queryWrapper)
  1. 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

1
2
3
4
5
6
7
8
9
10
11
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()
1
2
3
4
List<Map<String, Object>> mapList = userMapper.selectMaps(
    new QueryWrapper<SysUser>()
    .select("type as name, count(*) as typeCount")
    .groupBy("type"));
  • selectObjs()
1
2
3
4
List<Object> mapList = userMapper.selectObjs(
    new QueryWrapper<SysUser>()
    .select("sum(num) as typeTotal")
    .groupBy("type"));
  • selectCount()
1
userMapper.selectCount(queryWrapper);

select

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

group by

1
queryWrapper.groupBy("type"));

having

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

Others

Non-query fields

Use @TableField(exist = false)

1
2
@TableField(exist = false)
private IdName creator;

Use @TableName(excludeProperty={})

1
2
3
4
@TableName(value = "my_table_name", excludeProperty = {"creator"})
public class User {
    private IdName creator;
}

Using MySQL keyword as a column name

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

DML

update it to null when it value is null

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

logical delete

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

Update

1
2
3
userService.update(new UpdateWrapper<User>()
    .set("name", "updateName")
    .eq("id", 1));
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