Contents
  1. 1. Query
    1. 1.1. QueryWrapper
    2. 1.2. Basic Query
    3. 1.3. Query Conditions
    4. 1.4. Page Query
    5. 1.5. One Column Query
    6. 1.6. Aggregation Query
    7. 1.7. Others
  2. 2. DML
  3. 3. MyBatis Plus Configurations

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
7
wrapper.eq("type", 1)
.like("title", "test")
.in("user_id", userIds)
.ge("age", 18)
.apply("find_in_set({0}, type)", type);
.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")

Query Conditions

find_in_set

1
wrapper.apply("find_in_set({0}, type)", type);

Date functions

1
2
wrapper.apply("date(gmt_create)=date({0})", new Date())
.apply("date_format(gmt_modified,'%Y-%m-%d') = date({0})", new Date())

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

MyBatis Plus Configurations

1
2
3
4
5
6
7
8
9
mybatis-plus:
mapper-locations: classpath:mybatis/mapper/*.xml
type-aliases-package: com.example.entity,com.example.modules.**.entity
global-config:
db-config:
where-strategy: not_empty
logic-delete-field: deleteFlag
logic-delete-value: 1
logic-not-delete-value: 0

More configurations reference MyBatis Plus使用配置

Contents
  1. 1. Query
    1. 1.1. QueryWrapper
    2. 1.2. Basic Query
    3. 1.3. Query Conditions
    4. 1.4. Page Query
    5. 1.5. One Column Query
    6. 1.6. Aggregation Query
    7. 1.7. Others
  2. 2. DML
  3. 3. MyBatis Plus Configurations