MyBatisPlus常用操作

# MyBatis-Plus

MyBatis-Plus（简称 MP）是一个 MyBatis的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。

# 分页查询

查询参数赋值到queryWrapper中，在xml中使用 ${ew.customSqlSegment}

@Slf4j
@RestController
@RequestMapping(value = "/api")
public class UserController {

    @Resource
    private UserService userService;
    
    /**
     * 分页查询用户列表
     */
    @PostMapping(value = "/users/page", params = {"pageNo", "pageSize"})
    public Result<?> findUsers(@RequestBody UserDTO userDTO, Integer pageNo, Integer pageSize) {
        log.debug("请求分页查询用户列表,参数:{},分页参数:[{},{}]", userDTO, pageNo, pageSize);
        return Result.success(this.userService.findPage(userDTO, pageNo, pageSize));
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

public interface UserService {
  /**
   * 分页查询用户列表
   */
  IPage findPageV2(UserDTO userDTO, Integer pageNo, Integer pageSize);
}

1
2
3
4
5
6

@Slf4j
@Service
public class UserServiceImpl implements UserService {

  @Resource
  private UserRepository userRepository;
  @Resource
  private UserMapper userMapper;

  @Override
  public IPage findPageV2(UserDTO userDTO, Integer pageNo, Integer pageSize) {
    log.debug("分页查询用户列表:参数:{},分页参数[{},{}]", userDTO, pageNo, pageSize);
    QueryWrapper<User> qw = new QueryWrapper<>(this.userMapper.toEntity(userDTO));
    qw.orderByDesc("create_time");
    Page<User> pageUser = new Page<>(pageNo, pageSize);
    IPage iPage = userRepository.pageJoin(pageUser, qw);
    List<UserDTO> userDTOList = this.userMapper.toDto(iPage.getRecords());
    iPage.setRecords(userDTOList);
    return iPage;
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

@Mapper
public interface UserRepository extends BaseMapper<User> {
    IPage pageJoin(Page<User> pageUser, @Param("ew") QueryWrapper<User> qw);
}

1
2
3
4

<mapper namespace="com.bitouyun.Respository.UserRepository">
    <select id="pageJoin" resultType="com.bitouyun.entity.User">
        SELECT DISTINCT u.*, r.role_name AS roleName
        FROM sys_user u
        left JOIN sys_user_role ur ON u.id = ur.user_id
        left JOIN sys_role r ON ur.role_id = r.id
        ${ew.customSqlSegment}
    </select>
</mapper>

1
2
3
4
5
6
7
8
9

@Data
@TableName("sys_user")
public class User extends BaseEntity {
    @TableId(type = IdType.AUTO)
    private Integer id;
    private String username;
    @TableField(condition = SqlCondition.LIKE)
    private String name;
    @JsonProperty(access = JsonProperty.Access.WRITE_ONLY)
    private String password;
    private Integer gender;
    private String avatar;
    private String phone;
    private String email;
    private Integer delFlag;
    @TableField(exist = false)
    private String roleName;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

// Make sure to add code blocks to your code group

# 设置字段为空值

# 1. 使用LambdaUpdateWrapper (推荐)
LambdaUpdateWrapper<> updateWrapper = new LambdaUpdateWrapper<>().set(User::getName, null);
UserRepository.update(updateWrapper);

# 2. 修改字段更新策略
@TableField(updateStrategy= FieldStrategy.IGNORED)
private String name;

#  3. 修改全局更新策略
mybatis-plus:
  global-config:
    # 字段策略 IGNORED:忽略判断 NOT_NULL:非NULL判断 NOT_EMPTY:非空判断
    field-strategy: IGNORED

1
2
3
4
5
6
7
8
9
10
11
12
13

# 主键

主键一般设置无符号bigint、int类型,自增或设置数字.

    # @TableId(type = IdType.AUTO) # 数据库自增
    @TableId(type = IdType.ASSIGN_ID) # 雪花算法(对应代码里Long、Integer或String)
    private Long id;

1
2
3

#SQL教程

上次更新: 2023/12/19, 14:28:41

← MySQL常用查询