數(shù)據(jù)庫設(shè)計(jì)采用行擴(kuò)展，實(shí)現(xiàn)系統(tǒng)設(shè)置功能

一、平時(shí)我們實(shí)現(xiàn)系統(tǒng)設(shè)置功能的方式

在我們開發(fā)的系統(tǒng)功能中，大部分都有設(shè)置功能，比如APP端的用戶推送開關(guān)設(shè)置，平時(shí)我們是這樣設(shè)計(jì)的：

public class AppSettingPush {

    @ApiModelProperty(value = "會(huì)員ID")
    private Long memberId;

    @ApiModelProperty(value = "獲贊推送")
    private Boolean belike;
    
    @ApiModelProperty(value = "評(píng)論推送")
    private Boolean com;
    
    @ApiModelProperty(value = "收藏推送")
    private Boolean coll;
    
    @ApiModelProperty(value = "轉(zhuǎn)發(fā)推送")
    private Boolean tran;
    
    @ApiModelProperty(value = "關(guān)注推送")
    private Boolean focus;
    
    @ApiModelProperty(value = "@我的推送")
    private Boolean at;
}

采用這種方式有一個(gè)弊端，那就是將來如果要加一個(gè)推送設(shè)置開關(guān)，比如聊天推送，就需要增加字段，修改表結(jié)構(gòu)。另外，如果隨著業(yè)務(wù)功能的增加，還需要增加其他模塊設(shè)置 ，比如用戶空間數(shù)據(jù)設(shè)置（是否顯示關(guān)注列表、是否顯示粉絲列表、是否顯示被點(diǎn)贊列表等等），又需要增加一個(gè)設(shè)置表，且重新需要實(shí)現(xiàn)一套CURD代碼。

public class AppSettingSpaceData{

    @ApiModelProperty(value = "會(huì)員ID")
    private Long memberId;

    @ApiModelProperty(value = "是否顯示關(guān)注列表")
    private Boolean lfouce;

    @ApiModelProperty(value = "是否顯示粉絲列表")
    private Boolean lfans;

    @ApiModelProperty(value = "是否顯示被點(diǎn)贊列表")
    private Boolean lbelike;

    @ApiModelProperty(value = "是否顯示訪客列表")
    private Boolean lvisit;

    @ApiModelProperty(value = "是否顯示點(diǎn)贊列表")
    private Boolean llike;

    @ApiModelProperty(value = "是否顯示收藏列表")
    private Boolean lcoll;

    @ApiModelProperty(value = "是否顯示我評(píng)論的動(dòng)態(tài)列表")
    private Boolean lcd;

    @ApiModelProperty(value = "是否顯示@我的動(dòng)態(tài)列表")
    private Boolean lad;
}

二、使用行擴(kuò)展實(shí)現(xiàn)通用設(shè)置功能

2.1 表結(jié)構(gòu)
CREATE TABLE `t_mem_member_settings` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `member_id` bigint NOT NULL COMMENT '會(huì)員id',
  `setting_module` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '設(shè)置模塊',
  `setting_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '設(shè)置類型',
  `setting_value` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '設(shè)置值',
  `order_num` int DEFAULT '0' COMMENT '顯示排序字段',
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `IDX_deleted` (`deleted`) USING BTREE,
  KEY `idx_member_module` (`member_id`,`deleted`,`setting_module`) USING BTREE,
  KEY `idx_member_type` (`member_id`,`deleted`,`setting_type`,`setting_module`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=210391324491786 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=2048 ROW_FORMAT=DYNAMIC COMMENT='會(huì)員APP設(shè)置'
2.2 實(shí)體對(duì)象
public class MemberSettings{

    @ApiModelProperty(value = "會(huì)員ID")
    private Long memberId;

    @ApiModelProperty(value = "設(shè)置模塊")
    private MemberSettingModuleEnum settingModule;

    @ApiModelProperty(value = "設(shè)置類型")
    private MemberSettingTypeEnum settingType;

    @ApiModelProperty(value = "設(shè)置值")
    private String settingValue;

    @ApiModelProperty(value = "顯示排序字段")
    private int orderNum;
}
2.3 模塊枚舉
@Getter
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(description = "會(huì)員APP設(shè)置模塊")
public enum MemberSettingModuleEnum {
    PUSH("PUSH", "推送"),
    SPACE_DATA("SPACE_DATA", "空間數(shù)據(jù)")
    ;

    @EnumValue
    @JsonValue
    private String code;
    private String name;
}
2.4 設(shè)置項(xiàng)枚舉
@Getter
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(description = "會(huì)員APP設(shè)置類型")
public enum MemberSettingTypeEnum {
    PUSH_BE_LIKE("PUSH_BE_LIKE", "獲贊推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,1),
    PUSH_COM("PUSH_COM", "評(píng)論推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,2),
    PUSH_COLL("PUSH_COLL", "收藏推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,3),
    PUSH_TRAN("PUSH_TRAN", "轉(zhuǎn)發(fā)推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,4),
    PUSH_FOCUS("PUSH_FOCUS", "關(guān)注推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,5),
    PUSH_AT("PUSH_AT", "@我的推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,6),
    PUSH_IM_CHAT("PUSH_IM_CHAT", "@IM聊天推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,7),
    PUSH_IM_APPLY("PUSH_IM_APPLY", "IM申請(qǐng)推送", MemberSettingModuleEnum.PUSH,"1",Boolean.class,8),

    SPACE_DATA_LFOUCE("SPACE_DATA_LFOUCE", "是否顯示關(guān)注列表", MemberSettingModuleEnum.SPACE_DATA,"1",Boolean.class,1),
    SPACE_DATA_LFANS("SPACE_DATA_LFANS", "是否顯示粉絲列表", MemberSettingModuleEnum.SPACE_DATA,"1",Boolean.class,2),
    SPACE_DATA_LBELIKE("SPACE_DATA_LBELIKE", "是否顯示被點(diǎn)贊列表", MemberSettingModuleEnum.SPACE_DATA,"1",Boolean.class,3),
    SPACE_DATA_LVISIT("SPACE_DATA_LVISIT", "是否顯示訪客列表", MemberSettingModuleEnum.SPACE_DATA,"1",Boolean.class,4),
    SPACE_DATA_LLIKE("SPACE_DATA_LLIKE", "是否顯示點(diǎn)贊列表", MemberSettingModuleEnum.SPACE_DATA,"1",Boolean.class,5),
    SPACE_DATA_LCOLL("SPACE_DATA_LCOLL", "是否顯示收藏列表", MemberSettingModuleEnum.SPACE_DATA,"0",Boolean.class,6),
    SPACE_DATA_LCD("SPACE_DATA_LCD", "是否顯示我評(píng)論的動(dòng)態(tài)列表", MemberSettingModuleEnum.SPACE_DATA,"0",Boolean.class,7),
    SPACE_DATA_LAD("SPACE_DATA_LAD", "是否顯示@我的動(dòng)態(tài)列表", MemberSettingModuleEnum.SPACE_DATA,"0",Boolean.class,8);

    @EnumValue
    @JsonValue
    private String code;
    private String name;
    private MemberSettingModuleEnum settingModule;
    private String defaultValue;
    private Class valueType;
    private int orderNum;

    public static List<MemberSettingTypeEnum> listMemberSetting(MemberSettingModuleEnum module){
        MemberSettingTypeEnum[] arry = MemberSettingTypeEnum.values();
        List<MemberSettingTypeEnum> list = new ArrayList<>();

        for(MemberSettingTypeEnum item : arry){
            if(item.getSettingModule() == module){
                list.add(item);
            }
        }
        return list;
    }
}
2.5 contoller類
@Slf4j
@Api(tags="會(huì)員APP通用設(shè)置")
@RestController
@RequestMapping("membersettings")
public class MemberSettingsController{

    @Resource
    private MemberSettingsService memberSettingsService;

    @PutMapping("list")
    @ApiOperation(value="按模塊查詢會(huì)員APP設(shè)置數(shù)據(jù)")
    public Result<MemberSettingsVO> listMemberSettings(@RequestBody QueryMemberSettingsVO queryVO){
        return Result.success(memberSettingsService.listMemberSettings(queryVO));
    }

    @PutMapping("update")
    @ApiOperation(value="更新設(shè)置")
    public Result<Boolean> update(@RequestBody @Valid MemberSettingsUpdateVO updateVO){
        return Result.success(memberSettingsService.update(updateVO));
    }

    @PutMapping("updatebatch")
    @ApiOperation(value="批量更新設(shè)置")
    public Result<Boolean> updateBatch(@RequestBody @Valid MemberSettingsUpdateBatchVO updateBatchVO){
        return Result.success(memberSettingsService.updateBatch(updateBatchVO));
    }

    @PostMapping("boolsetting")
    @ApiOperation(value="獲取用戶Bool類型設(shè)置值")
    public Result<Boolean> getMemberBoolSetting(@RequestBody SearchMemberSettingsVO search){
        return Result.success(memberSettingsService.getMemberBoolSetting(search));
    }

    @PostMapping("settingvalue")
    @ApiOperation(value="獲取用戶設(shè)置的Value值")
    public Result<String> getMemberSettingValue(@RequestBody SearchMemberSettingsVO search){
        return Result.success(memberSettingsService.getMemberSettingValue(search));
    }
}
2.6 service實(shí)現(xiàn)類
@Slf4j
@Service
public class MemberSettingsServiceImpl implements MemberSettingsService {

    @Override
    public MemberSettingsVO listMemberSettings(QueryMemberSettingsVO queryVO) {
        log.info("listMemberSettings queryVO:{}",queryVO);
        MemberSettingsVO memberSettingsVO = memberSettingsRdsHelper.get(queryVO.getMemberId());

        // Redis沒數(shù)據(jù),則從數(shù)據(jù)庫取數(shù)據(jù)
        if (memberSettingsVO == null) {
            memberSettingsVO = new MemberSettingsVO();
            memberSettingsVO.setMemberId(queryVO.getMemberId());

            List<MemberSettingsItemVO> dbList = mapper.listMemberSettings(queryVO);
            if (dbList == null || dbList.size() == 0) {
                //數(shù)據(jù)庫無數(shù)據(jù)，則生成默認(rèn)記錄
                saveDefaultValue(queryVO);
                dbList = mapper.listMemberSettings(queryVO);
            }else{
                // 檢查是否有配置新的枚舉項(xiàng)
                List<MemberSettingTypeEnum> typeList = MemberSettingTypeEnum.listMemberSetting(queryVO.getSettingModule());
                if(dbList.size()!=typeList.size()){
                    dbList = dealNewTypes(dbList,typeList,queryVO);
                }
            }

            memberSettingsVO.setSettingItems(dbList);
            memberSettingsRdsHelper.add(memberSettingsVO);
        }else{
            // 檢查是否有配置新的枚舉項(xiàng)
            List<MemberSettingsItemVO> dbList = memberSettingsVO.getSettingItems();
            List<MemberSettingTypeEnum> typeList = MemberSettingTypeEnum.listMemberSetting(queryVO.getSettingModule());
            if(dbList.size()!=typeList.size()){
                memberSettingsRdsHelper.del(queryVO.getMemberId());

                // 為了防止Redis數(shù)據(jù)出錯(cuò)，從數(shù)據(jù)庫取數(shù)據(jù)
                dbList = mapper.listMemberSettings(queryVO);
                dbList = dealNewTypes(dbList,typeList,queryVO);

                memberSettingsVO.setSettingItems(dbList);
                memberSettingsRdsHelper.add(memberSettingsVO);
            }
        }
        return memberSettingsVO;
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public boolean update(MemberSettingsUpdateVO updateVO) {
        memberSettingsRdsHelper.del(updateVO.getMemberId());
        memberSettingsItemRdsHelper.del(updateVO.getMemberId(),updateVO.getSettingType().getCode());
        mapper.update(updateVO);
        return true;
    }

    @Override
    public boolean updateBatch(MemberSettingsUpdateBatchVO updateBatchVO) {
        return updateBatch(updateBatchVO.getUpdateList());
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public boolean updateBatch(List<MemberSettingsUpdateVO> updateVOList) {
        if (updateVOList != null && updateVOList.size() > 0) {
            for (MemberSettingsUpdateVO item : updateVOList) {
                update(item);
            }
        }
        return true;
    }

    @Override
    public boolean insertBatch(List<MemberSettingsUpdateVO> updateVOList) {
        if (updateVOList != null && updateVOList.size() > 0) {
            List<MemberSettings> entityList = new ArrayList<>();
            for (MemberSettingsUpdateVO item : updateVOList) {
                MemberSettings entity = new MemberSettings();
                BeanUtil.copyProperties(item, entity);

                entity.setOrderNum(entity.getSettingType().getOrderNum());
                entity.setSettingModule(entity.getSettingType().getSettingModule());

                entityList.add(entity);
            }
            saveBatch(entityList);
        }
        return true;
    }

    @Override
    public String getMemberSettingValue(SearchMemberSettingsVO search) {
        MemberSettings memberSettings = memberSettingsItemRdsHelper.get(search.getMemberId(),search.getSettingType());

        // Redis沒數(shù)據(jù),則從數(shù)據(jù)庫取數(shù)據(jù)
        if(memberSettings == null){
            memberSettings = mapper.getMemberSettings(search);

            // 數(shù)據(jù)庫沒數(shù)據(jù)，則表示是新配置的枚舉項(xiàng)
            if(memberSettings == null){
                MemberSettingTypeEnum item = MemberSettingTypeEnum.valueOf(search.getSettingType());
                if(item!=null){
                    memberSettings =  packageEntity(search.getMemberId(),item);
                    save(memberSettings);
                }
            }

            if(memberSettings!=null){
                memberSettingsItemRdsHelper.add(memberSettings);
            }
        }

        if(memberSettings!=null){
            return memberSettings.getSettingValue();
        }else{
            return null;
        }
    }

    @Override
    public Boolean getMemberBoolSetting(SearchMemberSettingsVO search) {
        Member member = memberService.getById(search.getMemberId());
        if(member == null || member.getForbiddenStatus() == MemberForbiddenStatusEnum.FORBIDDEN){
            return false;
        }
        String value = getMemberSettingValue(search);
        boolean result = "1".equals(value) ? true : false;
        return result;
    }

    /**
     * 生成默認(rèn)記錄
     *
     * @param queryVO
     */
    private void saveDefaultValue(QueryMemberSettingsVO queryVO) {
        List<MemberSettingTypeEnum> list = MemberSettingTypeEnum.listMemberSetting(queryVO.getSettingModule());
        Long memberId = queryVO.getMemberId();

        List<MemberSettings> entityList = packageSettings(memberId,list);
        saveBatch(entityList);
    }

    private List<MemberSettings> packageSettings(Long memberId,List<MemberSettingTypeEnum> list){
        List<MemberSettings> entityList = new ArrayList<>();
        for (MemberSettingTypeEnum item : list) {
            MemberSettings entity = packageEntity(memberId,item);
            entityList.add(entity);
        }
        return entityList;
    }

    private MemberSettings packageEntity(Long memberId,MemberSettingTypeEnum item){
        MemberSettings entity = new MemberSettings();
        entity.setMemberId(memberId);
        entity.setSettingType(item);
        entity.setSettingModule(item.getSettingModule());
        entity.setSettingValue(item.getDefaultValue());
        entity.setOrderNum(item.getOrderNum());
        return entity;
    }

    private List<MemberSettingsItemVO> dealNewTypes(List<MemberSettingsItemVO> dbList,List<MemberSettingTypeEnum> typeList,QueryMemberSettingsVO queryVO){
        List<MemberSettingTypeEnum> dbTypeList = new ArrayList<>();
        for(MemberSettingsItemVO dbItem : dbList){
            dbTypeList.add(dbItem.getSettingType());
        }

        // 差集 (typeList - dbTypeList) = 新配置的枚舉類型
        List<MemberSettingTypeEnum> newTypeList = typeList.stream().filter(item -> !dbTypeList.contains(item)).collect(Collectors.toList());
        List<MemberSettings> entityList = packageSettings(queryVO.getMemberId(),newTypeList);

        //將新的枚舉項(xiàng)存入數(shù)據(jù)庫
        saveBatch(entityList);
        //重新查詢數(shù)據(jù)
        dbList = mapper.listMemberSettings(queryVO);

        return dbList;
    }
}
2.7 擴(kuò)展說明

如果有新的設(shè)置項(xiàng)或設(shè)置模塊，新增枚舉類型即可，不需要修改其他代碼。

2.8 優(yōu)化說明

將排序字段由int類型改成float類型擴(kuò)展性更好一點(diǎn)。例如，現(xiàn)在只有設(shè)置項(xiàng)：A:1、B:2、C:3、D:4，由于需求變化，需要在B后面增加一個(gè)設(shè)置項(xiàng)，則可以設(shè)置成E:2.01

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容