MyBatis-Plus Common Usage

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("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

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

// find_in_set(?, category_id) or find_in_set(?, category_id)
queryWrapper.and(wrapper -> {
	for (int i = 0; i < categoryIds.size(); i++) {
		if (i > 0) {
			wrapper.or();
		}
		wrapper.apply("find_in_set({0}, category_id)", categoryIds.get(i));
	}
	return wrapper;
});

// 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")

Query Conditions

find_in_set

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

Date functions

date equal

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

Recent 7 days

wrapper.apply("create_time > DATE_SUB(NOW(), INTERVAL 7 DAY)"));

Page Query

1. Request query string parameters

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

• current
• size
• orders[].column
• orders[].asc

2. Page Entity

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

Fields for Request parameters

• long size
• long current
• List<OrderItem> orders
  • String column
  • boolean asc

Fields for response

• List<T> records
• long total

3. Page Query Methods

MyBatis Plus page query method

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

Custom page query in Mapper XML

IPage<MyResult> myPageQuery(@Param("page") Page page, @Param("param") MyParam param);

• Pass com.baomidou.mybatisplus.extension.plugins.pagination.Page object as a parameter.
• Using the IPage class as the return type.
• Don’t need to add limit start, size in mapper XML. The SQL is query all rows. But MyBatis Plus automatically add limit at the end of SQL. If you want to query all, update to List<MyResult> queryAll(@Param("param") MyParam param);

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

Use @TableField(exist = false)

@TableField(exist = false)
private IdName creator;

Use @TableName(excludeProperty={})

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

Using @TableField(condition = SqlCondition.LIKE)

@TableField(condition = SqlCondition.LIKE)  
private String name;

Using @TableField(whereStrategy = FieldStrategy.NOT_EMPTY)

• IGNORED: 不判断
• NOT_NULL: 非NULL判断
• NOT_EMPTY: 非空判断

Using MySQL keyword as a column name

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

Using another entity name

@Alias("UserV2")
public class User {
}

DML

update it to null when it value is null

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

logical delete

// Note that the field for logical deletion cannot be modified by update() method
@TableLogic(value="0",delval="1")
private Boolean delFlag;

Update

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

MyBatis Plus Configurations

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使用配置