基于Mybatis-Plus的TypeHandler的附件及附件组处理

需求背景

        在Web开发中，经常会通过设计出单独的附件表用于存储附件信息。这样对于附件表的查询、更新、新增等操作，必然需要额外的处理。以查询为例，需要使用业务表去Left Join附件表；这样会需要大量编写SQL脚本，侵入性太强，当遇到查询附件组的情况，还需要处理如组内附件排序问题。

        本文通过结合Mybatis的TypeHandler机制，巧妙的解决这一样问题。使得附件附件的处理完全解耦，使开发无需关注这些工作。声明，文中的“图片”和“附件”表示的是同一个含义。

步骤

1、创建附件实体类

创建实体类：Pic

@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
public class Pic {
    private String id;

    private String fileId;

    private String url;

    @JsonIgnore
    private int sort;

    @JsonIgnore
    private String groupId;
    
    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (o == null || getClass() != o.getClass()) {
            return false;
        }
        Pic pic = (Pic) o;
        return Objects.equals(id, pic.id) && Objects.equals(url, pic.url);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, url);
    }
}

创建附件组实体类：PicGroup

@Data
public class PicGroup {
    private String groupId;

    private List<Pic> picList;
}

2、创建TypeHandler

PicTypeHandler

@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes(Pic.class)
public class PicTypeHandler implements TypeHandler<Pic> {
    @Override
    public void setParameter(PreparedStatement preparedStatement, int i, Pic pic, JdbcType jdbcType) throws SQLException {
        PicCache picCache = SpringContextHolder.getBean(PicCache.class);
        if (StringUtils.isBlank(pic.getId())) {
            picCache.savePic(pic);
        } else {
            picCache.updatePic(pic);
        }
        preparedStatement.setString(i, pic.getId());
        // TODO发布Spring确认文件上传事件
    }

    @Override
    public Pic getResult(ResultSet resultSet, String s) throws SQLException {
        return this.getById(resultSet.getString(s));
    }

    @Override
    public Pic getResult(ResultSet resultSet, int i) throws SQLException {
        return this.getById(resultSet.getString(i));
    }

    @Override
    public Pic getResult(CallableStatement callableStatement, int i) throws SQLException {
        return this.getById(callableStatement.getString(i));
    }

    private Pic getById(String id) {
        if (StringUtils.isBlank(id)) {
            return null;
        }
        try {
            //PageHelperDialect.skipPage(true);
            PicCache picCache = SpringContextHolder.getBean(PicCache.class);
            ThreadPoolTaskExecutor executor = SpringContextHolder.getBean(ThreadPoolTaskExecutor.class);
            Future<Pic> picFuture = executor.submit(() -> picCache.getPicById(id));
            return picFuture.get();
        } catch (Exception e) {
            throw new BusinessException("获取图片失败！", e);
        } finally {
            //PageHelperDialect.skipPage(false);
        }
    }
}

PicGroupTypeHandler

@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes(PicGroup.class)
public class PicGroupTypeHandler implements TypeHandler<PicGroup> {
    @Override
    public void setParameter(PreparedStatement preparedStatement, int i, PicGroup picGroup, JdbcType jdbcType) throws SQLException {
        String picGroupId = picGroup.getGroupId();
        List<Pic> picList = picGroup.getPicList();
        PicCache picCache = SpringContextHolder.getBean(PicCache.class);
        if (StringUtils.isBlank(picGroup.getGroupId())) {
            if (CollectionUtils.isEmpty(picList)) {
                preparedStatement.setString(i, null);
                return;
            }
            picCache.savePicGroup(picGroup);
            picGroupId = picGroup.getGroupId();
        } else {
            picCache.updatePicGroup(picGroup);
        }
        preparedStatement.setString(i, picGroupId);
        // TODO发布Spring确认文件上传事件
    }

    @Override
    public PicGroup getResult(ResultSet resultSet, String s) throws SQLException {
        return this.getById(resultSet.getString(s));
    }

    @Override
    public PicGroup getResult(ResultSet resultSet, int i) throws SQLException {
        return this.getById(resultSet.getString(i));
    }

    @Override
    public PicGroup getResult(CallableStatement callableStatement, int i) throws SQLException {
        return this.getById(callableStatement.getString(i));
    }

    public PicGroup getById(String id) {
        if (StringUtils.isBlank(id)) {
            return null;
        }

        try {
            //PageHelperDialect.skipPage(true);
            PicCache picCache = SpringContextHolder.getBean(PicCache.class);
            ThreadPoolTaskExecutor executor = SpringContextHolder.getBean(ThreadPoolTaskExecutor.class);
            Future<PicGroup> picGroupFuture = executor.submit(() -> picCache.getPicGroupById(id));
            //Future<PicGroup> picGroupFuture = ThreadUtil.execAsync(() -> picCache.getPicGroupById(id));
            return picGroupFuture.get();
            //return picCache.getPicGroupById(id);
        } catch (Exception e) {
            throw new BusinessException("获取图片组失败！", e);
        } finally {
            //PageHelperDialect.skipPage(false);
        }
    }
}

注意
如果使用了Pagehelper分页组件，由于com.github.pagehelper.PageInterceptor#intercept 会拦截掉Select查询SQL请求，导致Page结果被后面的附件查询SQL结果覆盖，所以上面TypeHandler都是用了异步请求。由于Page是绑定到ThreadLocal里面，所以子线程的查询结果不会影响到原来Page的结果，具体请看Pagehelper的源码。

3、创建数据操作接口

这里只提供接口，需要业务去实现这个接口，并且是一个Spring Bean组件

/**
 * Pic附件数据存储
 *
 * @author jiangxiaowei
 */
public interface PicRepository {
    /**
     * 根据id获取图片
     *
     * @param id id
     * @return 图片
     */
    Pic getById(String id);

    /**
     * 根据图片组获取图片集合
     *
     * @param groupId 组id
     * @return 图片集合
     */
    List<Pic> getByGroupId(String groupId);

    /**
     * 保存图片
     *
     * @param pics 图片
     * @return 是否成功
     */
    boolean save(Pic... pics);

    /**
     * 更新图片
     *
     * @param pic 图片
     * @return 是否成功
     */
    boolean update(Pic pic);

    /**
     * 新增并删除旧图片
     *
     * @param news      新图片
     * @param deleteIds 旧图片id
     * @return 是否成功
     */
    boolean insertAndDelete(List<Pic> news, Set<String> deleteIds);
}

4、创建PicCache

通过集成Spring Cache，提升性能

/**
 * @author ：jiangxiaowei
 */
@CacheConfig(cacheNames = PicCache.CACHE_NAME)
@RequiredArgsConstructor
public class PicCache {
    private final PicRepository picService;
    public static final String CACHE_NAME = "PIC_CACHE";

    @CacheEvict(key = "#id")
    public void evictPicCache(String id) {
    }

    @Cacheable(key = "'group_' + #groupId")
    public PicGroup getPicGroupById(String groupId) {
        List<Pic> picList = picService.getByGroupId(groupId);
        PicGroup group = new PicGroup();
        group.setGroupId(groupId);
        if (!CollectionUtils.isEmpty(picList)) {
            picList.sort(Comparator.comparing(Pic::getSort));
            group.setPicList(picList);
        }
        return group;
    }

    @Cacheable(key = "#id")
    public Pic getPicById(String id) {
        return picService.getById(id);
    }

    @CachePut(key = "#pic.id", unless = "#pic == null")
    public Pic savePic(Pic pic) {
        picService.save(pic);
        return pic;
    }

    @CachePut(key = "'group_' + #picGroup.groupId", unless = "#picGroup == null")
    public PicGroup savePicGroup(PicGroup picGroup) {
        List<Pic> picList = picGroup.getPicList();
        if (CollectionUtils.isEmpty(picList)) {
            return null;
        }
        String groupId = IdUtil.createSnowflake(0, 0).nextIdStr();
        AtomicInteger sorted = new AtomicInteger();
        // 设置排序、组id
        picList.forEach(pic -> {
            pic.setSort(sorted.incrementAndGet());
            pic.setGroupId(groupId);
        });
        picGroup.setPicList(picList);
        picGroup.setGroupId(groupId);
        picService.save(picList.toArray(new Pic[]{}));
        return picGroup;
    }

    @CachePut(key = "'group_' + #picGroup.groupId", unless = "#picGroup == null")
    public PicGroup updatePicGroup(PicGroup picGroup) {
        if (picGroup == null) {
            return null;
        }
        PicCache picCache = (PicCache) AopContext.currentProxy();
        String groupId = picGroup.getGroupId();
        List<Pic> picList = picGroup.getPicList();
        List<Pic> picListCache = null;
        if (!StringUtils.isBlank(groupId)) {
            PicGroup picGroupCache = picCache.getPicGroupById(groupId);
            if (picGroupCache != null) {
                picListCache = picGroupCache.getPicList();
            }
            // 没有变更
            if (Objects.equals(picListCache, picList)) {
                return picGroupCache;
            }
        }

        // 空处理
        picList = Optional.ofNullable(picList).orElseGet(Collections::emptyList);
        picListCache = Optional.ofNullable(picListCache).orElseGet(Collections::emptyList);
        // 新增分组附件
        AtomicInteger sorted = new AtomicInteger();
        picList.forEach(pic -> {
            pic.setSort(sorted.incrementAndGet());
            pic.setGroupId(groupId);
        });

        // 保留的id
        List<String> noDeleteIds = picList.stream().map(Pic::getId).collect(Collectors.toList());
        // 删除的id
        Set<String> picIdListCache = picListCache.stream().map(Pic::getId).collect(Collectors.toSet());
        picIdListCache.removeAll(noDeleteIds);
        picService.insertAndDelete(picList, picIdListCache);
        picGroup.setPicList(picList);
        return picGroup;
    }

    @CachePut(key = "#pic.id", unless = "#pic == null")
    public Pic updatePic(Pic pic) {
        PicCache picCache = (PicCache) AopContext.currentProxy();
        Pic cachePic = picCache.getPicById(pic.getId());
        if (cachePic != null && Objects.equals(cachePic.getUrl(), pic.getUrl())) {
            return cachePic;
        }
        picService.update(pic);
        picCache.evictPicCache(pic.getId());
        return pic;
    }
}

5、示例开发

实体Bean，必须设置@TableName(autoResultMap = true)

@Data
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = true)
@TableName(autoResultMap = true)
public class ZbDepositSetting extends BaseAutoModel<ZbDepositSetting> {
    /** 活动ID */
    @TableField("activity_id")
    private Long activityId;

    /** 封面图 */
    @TableField(value = "cover_pic_id", typeHandler = PicTypeHandler.class)
    private Pic coverPic;

    /** 详情图组ID */
    @TableField(value = "detail_pic_group_id", typeHandler = PicGroupTypeHandler.class)
    private PicGroup detailPicGroup;
	
	// 其他省略代码...
}

VO中使用

@Data
public class ZbDepositSettingDto {
    /** 活动ID */
    private Long activityId;

    /** 封面图 */
    private Pic coverPicId;

    /** 详情图组ID */
    private PicGroup detailPicGroupId;

   // 其他省略代码...
}

至此，新增、修改、查询业务表的同时，都会自动的维护附件表关联记录，事半功倍。

6、示例演示

定义一个Controller，编写测试方法

@GetMapping("/list")
public XCloudResponse<XCloudPageInfo<ZbDepositDto.Setting>> list(@Validated ZbDepositParam param) {
    XCloudPageInfo<ZbDepositDto.Setting> pageInfo = depositSettingService.selectList(param);
    return XCloudResponse.succeed(pageInfo);
}

通过idea的自带rest测试工具
GET http://localhost:9207/deposit/settings/list?activityId=1
Authorization: Bearer {{access-token}}

示例结果：

{
    "code": 200,
    "msg": "成功",
    "payload": {
        "total": 16,
        "pages": 2,
        "records": [
            {
                "id": "1",
                "price": "1",
                "shelfStatus": {
                    "code": "1",
                    "desc": "已上架"
                },
                "sorted": 2,
                "shelfTime": "2020-12-22T15:17:22.000+0800",
                "coverPic": {
                    "id": "124",
                    "url": "https://xxx.com/079d858e-36af-4a63-8550-ce4d725e80ae.png"
                },
                "detailPicGroup": {
                    "groupId": "0f9ba317-1f65-46e9-b0c6-ed46268a1ecb",
                    "picList": [
                        {
                            "id": "372",
                            "url": "https://xxx/7c70542a-2831-4ebd-99d5-8c5d312431b8.pngg"
                        },
                        {
                            "id": "374",
                            "url": "https://xxx/7c70542a-2831-4ebd-99d5-8c5d312431b8.png"
                        }
                    ]
                }
            }
           // 其他省略...
        ]
    }
}

后记

1. 可以考虑在系统启动的过程中先把附件表加载到Redis缓存中
2. 使用J2Cache两级缓存组件，对这种多读少写进一步提升缓存性能
3. 前端上传附件到后台，后台上传的OSS服务器等，如果多次无效上传造成OSS大量垃圾数据，可以在TypeHandler中引入确认机制，通过Spring发布异步确认事件，未确认的由定时器删除OSS附件
4. 通过实现Mybatis的Interceptor，进一步扩展在删除业务表数据记录的同时删除附件