使用MyBatis-Plus的XML自定义分页进阶版——通过数据库字符串数组字段嵌套集合

概要

遇到的场景是数据库中的一个字段存放了一段id的集合，无法使用join来进行表的链接，所以只能靠java代码来实现了。
然后我一开始没弄懂如果要添加嵌套的话，分页应该怎么弄。
几天之后，突然灵感来了。当然也有待后续改进。。

解决方案

1、数据库表结构

notice表

role表

2、pojo

NoticePageVO

@Data
public class NoticePageVO {

    private Integer current;
    private Integer size;
    private String title;
    private String name;
}

NoticeVO

@Data
public class NoticeVO {
    private Integer id;
    private String title;
    private String name;
    private String createdAt;
    private Integer shouldReadNum;
    private Integer readNum;
    private List<String> roleIds;
    private String content;
}

NoticeRoleVO

@Data
public class NoticeRoleVO {
    private Integer id;
    private String title;
    private String name;
    private String createdAt;
    private Integer shouldReadNum;
    private Integer readNum;
    private List<Role> roleIds;
    private String content;
}

3、xml代码

其中的JsonTypeHandler参考这篇博客springboot+mybatis+mysql 存储单字段为数组格式的解决方案

    <resultMap id="roleList" type="com.operative.core.notice.entity.VO.NoticeVO">
        <result column="role_ids" property="roleIds" typeHandler="com.operative.base.handler.JsonTypeHandler"/>
    </resultMap>

    <select id="getAllNotice" resultMap="roleList">
        SELECT t1.*,t2.name FROM notice t1 LEFT JOIN `user` t2 on t1.user_id = t2.id
        where
        <if test="noticePageVO.title!=null and noticePageVO.title!=''">
            t1.title like concat('%',#{noticePageVO.title},'%') and
        </if>
        <if test="noticePageVO.name!=null and noticePageVO.name!=''">
            t2.name like concat('%',#{noticePageVO.name},'%') and
        </if>
        t1.is_deleted = 0
    </select>

4、mapper层

使用mybatis-plus自带的自定义分页参考博客使用MyBatis-Plus的XML自定义分页

public interface NoticeMapper extends BaseMapper<Notice> {
    IPage<NoticeVO> getAllNotice(Page<NoticeVO> page, NoticePageVO noticePageVO);
}

5、ServiceImpl层 （主要逻辑）

@Service
public class NoticeServiceImpl extends ServiceImpl<NoticeMapper, Notice> implements NoticeService {
    @Autowired
    private NoticeMapper noticeMapper;
    @Autowired
    private RoleMapper roleMapper;

    @Override
    public ResponseData<Object> getAll(NoticePageVO noticePageVO){
        Page<NoticeVO> page = new Page<>();
        if (noticePageVO.getCurrent()!=null&&noticePageVO.getSize()!=null){
            page = new Page<>(noticePageVO.getCurrent(),noticePageVO.getSize());
        }
        final IPage<NoticeVO> allNotice = noticeMapper.getAllNotice(page,noticePageVO);//获取notice中的数据和分页信息

        //提取其中的records，然后对其进行处理，嵌套上role
        final List<NoticeVO> records = allNotice.getRecords();
        List<NoticeRoleVO> noticeRoleVOS = new ArrayList<>();
        for (NoticeVO noticeVO : records) {
            NoticeRoleVO noticeRoleVO = new NoticeRoleVO();
            final List<String> roleIds = noticeVO.getRoleIds();
            QueryWrapper<Role> queryWrapper = new QueryWrapper<>();
            if (roleIds!=null&&roleIds.size()!=0){
                queryWrapper.in("id",roleIds);
            }
            final List<Role> roles = roleMapper.selectList(queryWrapper);
            noticeRoleVO.setRoleIds(roles);
            BeanUtils.copyProperties(noticeVO,noticeRoleVO);
            noticeRoleVOS.add(noticeRoleVO);
        }

        //新建一个分页对象，加入处理后的notice集合和分页相关信息即可
        Page<NoticeRoleVO> page1 = new Page<>();
        BeanUtils.copyProperties(allNotice,page1);
        page1.setRecords(noticeRoleVOS);

        return Result.success(page1);
    }



}

6、Service接口

public interface NoticeService extends IService<Notice> {
    ResponseData<Object> getAll(NoticePageVO noticePageVO);
}

7、Controller层

@CrossOrigin
@RestController
@RequestMapping("/notice")
public class NoticeController {
    @Autowired
    private NoticeService noticeService;
    @GetMapping
    private ResponseData<Object> getAllNotice(NoticePageVO noticePageVO){
        return noticeService.getAll(noticePageVO);
    }
}

8、最后返回数据

end