使用shardingsphere分表后，如何正确使用xml实体映射和mp提供的api

自定义分页实现

分表后分页插件失效问题，原因是我们有分片键，然后分页插件并不知情，所以需要我们重写分页：

分页接参实体：

@Data
public class PageEntity {

    /**
     * 分页页码
     */
    @NotNull(message = "分页页码不能为空")
    @Min(1)
    private Integer pageNum;

    /**
     * 分页页面大小
     */
    @NotNull(message = "分页页面大小不能为空")
    @Min(10)
    private Integer pageSize;

    /**
     * 开始时间
     */
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime startTime;

    /**
     * 结束时间
     */
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime endTime;

}

分页Vo封装，此为模仿mp的Page类，借用了糊涂工具

/**
 * @ClassName: ResultPageVo
 * @Date: 2021/8/13 14:16
 * @Author: lvxy
 * @Description: 返回给前端的page基础Vo
 */
@ToString
public class ResultPageVo<T> {

    /**
     * 返回前端数据列表
     */
    private List<T> records;

    /**
     * 当前页码
     */
    private Integer current;

    /**
     * 分页页面大小
     */
    private Integer size;

    /**
     * 多少页 需要计算
     */
    private Integer pages;

    /**
     * 一共多少条数据
     */
    private Long total;

    public void setCurrent(Integer current) {
        this.current = current;
    }

    // 重写set方法
    public void setSize(Integer pageSize) {
        this.size = pageSize;
        if (this.total != null){
            this.pages = PageUtil.totalPage(Math.toIntExact(this.total),this.size);
            // 如果不使用hutool工具，使用下面注释的方式即可，将上面一行注释掉
            //if (this.size <= 0 || this.total == 0) {
            //    this.pages = 0;
            //} else {
            //    this.pages = (int) Math.ceil(this.total / (double) this.size);
            //}
        }
    }

    // 重写set方法
    public void setTotal(Long totalCount) {
        this.total = totalCount;
        if (this.size != null){
            this.pages = PageUtil.totalPage(Math.toIntExact(this.total),this.size);
            // 如果不使用hutool工具，使用下面注释的方式即可，将上面一行注释掉
            //if (this.size <= 0 || this.total == 0) {
            //    this.pages = 0;
            //} else {
            //    this.pages = (int) Math.ceil(this.total / (double) this.size);
            //}
        }
    }

    public Integer getCurrent() {
        return current;
    }

    public Integer getSize() {
        return size;
    }

    public Integer getPages() {
        return pages;
    }

    public Long getTotal() {
        return total;
    }

    public List<T> getRecords() {
        return records;
    }

    public void setRecords(List<T> records) {
        this.records = records;
    }
}

mapper：

    @Select("select count(*) from document_trans where is_delete=0 and enterprise_id=#{enterpriseId} and user_id=#{userId}")
    Long getDocumentTransCountByUserId(@Param("enterpriseId") Long enterpriseId,@Param("userId") Long userId);
    
    List<DocumentTrans> getDocumentTransByPage(@Param("userId") Long userId,
                                               @Param("enterpriseId") Long enterpriseId,
                                               @Param("neFileStatus") Integer neFileStatus,
                                               @Param("pageEntity") PageEntity pageEntity);

    <select id="getDocumentTransByPage" resultType="com.xxx.DocumentTrans"
            resultMap="BaseResultMap">
        select id,parent_file_id,...
        from document_trans where is_delete=0
        and user_id = #{userId}
        and enterprise_id = #{enterpriseId}
        and file_status != #{neFileStatus}
        <if test="pageEntity.startTime != null">
            and start_time = #{pageEntity.startTime}
        </if>
        <if test="pageEntity.endTime != null">
            and end_time = #{pageEntity.endTime}
        </if>
        order by create_time desc
        limit #{pageEntity.pageNum}, #{pageEntity.pageSize}
    </select>

具体实现：

    // ok
    @Override
    public ResultPageVo<DocumentTrans> getDocumentTransByPage(PageEntity pageEntity) {
        pageEntity.setPageNum(pageEntity.getPageNum() <= 0 ? 0 : (pageEntity.getPageNum() - 1));
        pageEntity.setPageSize(pageEntity.getPageSize() <= 0 ? 0 : pageEntity.getPageSize());
        List<DocumentTrans> res = baseMapper.getDocumentTransByPage(
                MyServletContextUtil.getUserId(),
                MyServletContextUtil.getEnterpriseId(),
                DocumentInfoStatusConstant.WAIT_TRANSLATION,
                pageEntity
        );

        final long count = baseMapper.getDocumentTransCountByUserId(
                MyServletContextUtil.getEnterpriseId(),
                MyServletContextUtil.getUserId()
        );
        return generateMyPage(pageEntity, res, count);
    }

    @NotNull
    public static ResultPageVo<DocumentTrans> generateMyPage(PageEntity pageEntity, List<DocumentTrans> res, long count) {
        ResultPageVo<DocumentTrans> page = new ResultPageVo<>();
        page.setCurrent(pageEntity.getPageNum());
        page.setSize(pageEntity.getPageSize());
        page.setTotal(count);
        page.setRecords(res);
        return page;
    }

xml实体映射失效解决

xml实体映射失效解决：
增加resultMap="XXXMap"即可，如果是select * 则需要改为select 具体字段，比如下面这个分页查询的xml：

    <resultMap id="BaseResultMap" type="com.xxx.DocumentTrans">
        <id property="id" column="id" jdbcType="BIGINT"/>
        <result property="parentFileId" column="parent_file_id" jdbcType="BIGINT"/>
        <result property="fileUuid" column="file_uuid" jdbcType="VARCHAR"/>
		...
    </resultMap>

    <select id="getDocumentTrashByPage" resultType="com.xxx.DocumentTrans"
            resultMap="BaseResultMap">
        select id,parent_file_id,file_uuid,is_delete,...
        from document_trans where is_delete=1 and is_trashed=1 and is_trashed_delete=0
        and user_id = #{userId}
        and enterprise_id = #{enterpriseId}
        <if test="pageEntity.startTime != null">
            and start_time = #{pageEntity.startTime}
        </if>
        <if test="pageEntity.endTime != null">
            and end_time = #{pageEntity.endTime}
        </if>
        limit #{pageEntity.pageNum}, #{pageEntity.pageSize}
    </select>

mp提供的部分api失效问题解决

mp提供的部分api失效问题解决，IService<>接口部分方法如下,可以看到都是xxxById：

public interface IService<T> {
	default boolean updateById(T entity) {
	   return SqlHelper.retBool(this.getBaseMapper().updateById(entity));
	}
	   default List<T> listByIds(Collection<? extends Serializable> idList) {
	   return this.getBaseMapper().selectBatchIds(idList);
	}
	default T getById(Serializable id) {
	   return this.getBaseMapper().selectById(id);
	}
}

在我们使用shardingsphere后，就不能只以id来进行查询或者更新，我们还需要添加分片键，比如我的分片键是企业id，每个企业id一张表，那么就应该使用T entity+Wrapper<T> updateWrapper的这种形式来进行查询和更新：

1. 查询示例：

DocumentTrans dbEntity = this.getOne(new LambdaQueryWrapper<DocumentTrans>()
        .eq(DocumentTrans::getEnterpriseId, MyServletContextUtil.getEnterpriseId())
        .eq(DocumentTrans::getId, documentTrans.getId())
        .select(DocumentTrans::getId,
                DocumentTrans::getFileStatus,
                DocumentTrans::getEnterpriseId,
                DocumentTrans::getFileUuid
        )
);

2. 更新示例：

this.update(documentTrans, new LambdaQueryWrapper<DocumentTrans>()
        .eq(DocumentTrans::getEnterpriseId, documentInfo.getEnterpriseId())
        .eq(DocumentTrans::getFileUuid, documentInfo.getFileUuid()));