作训处后端

2021-01-14 本文已影响0人弹钢琴的崽崽

一. 数据库设计

业务比较简单，五张表的增删改查

1. 数据库设计中遇到的问题

列名不能用关键字如：NUMBER,LEVEL,NATIVE

人员编成表中添加sort排序字段，需求里有升序降序的业务，需要根据这个字段排序，每个表中要添加sort_no字段，表示机构表中机构的排序，列表需要按照部门排序，在每次添加对象的时候，根据deptCode去机构表中查出该机构的sort_no存入该对象中。

民族码表

create table DM_MZ
(
  bm  VARCHAR2(50) not null,
  mc  VARCHAR2(50) not null,
  pyt VARCHAR2(50),
  wbt VARCHAR2(50)
);
comment on table DM_MZ is '民族码表';
insert into DM_MZ (bm, mc, pyt, wbt)
values ('01', '汉族', 'HZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('02', '蒙古族', 'MGZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('03', '回族', 'HZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('04', '藏族', 'CZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('05', '维吾尔族', 'WWEZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('06', '苗族', 'MZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('07', '彝族', 'YZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('08', '壮族', 'ZZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('09', '布依族', 'BYZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('10', '朝鲜族', 'CXZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('11', '满族', 'MZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('12', '侗族', 'DZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('13', '瑶族', 'YZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('14', '白族', 'BZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('15', '土家族', 'TJZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('16', '哈尼族', 'HNZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('17', '哈萨克族', 'HSKZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('18', '傣族', 'DZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('19', '黎族', 'LZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('20', '傈僳族', 'LSZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('21', '佤族', 'ZZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('22', '畲族', 'ZZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('23', '高山族', 'GSZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('24', '拉祜族', 'LZZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('25', '水族', 'SZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('26', '东乡族', 'DXZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('27', '纳西族', 'NXZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('28', '景颇族', 'JPZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('29', '柯尔克孜族', 'KEKZZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('30', '土族', 'TZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('31', '达翰尔族', 'DHEZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('32', '么佬族', 'MLZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('33', '羌族', 'QZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('34', '布朗族', 'BLZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('35', '撒拉族', 'SLZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('36', '毛难族', 'MNZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('37', '仡佬族', 'ZLZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('38', '锡伯族', 'XBZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('39', '阿昌族', 'ACZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('40', '普米族', 'PMZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('41', '塔吉克族', 'TJKZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('42', '怒族', 'NZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('43', '乌孜别克族', 'WZBKZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('44', '俄罗斯族', 'ELSZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('45', '鄂温克族', 'EWKZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('46', '崩龙族', 'BLZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('47', '保安族', 'BAZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('48', '裕固族', 'YGZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('49', '京族', 'JZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('50', '塔塔尔族', 'TTEZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('51', '独龙族', 'DLZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('52', '鄂伦春族', 'ELCZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('53', '赫哲族', 'HZZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('54', '门巴族', 'MBZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('55', '珞巴族', 'ZBZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('56', '基诺族', 'JNZ', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('98', '外国血统', 'WG血T', null);
insert into DM_MZ (bm, mc, pyt, wbt)
values ('99', '其它', 'QT', null);

2. 不登录如何测接口

application.properties文件修改

#无须登录过滤的资源（如js，css等静态资源），多个用“|”隔开
duceap.security.dcuc.ignorePattern=duceap.security.dcuc.ignorePattern=/js/*|/img/*|/css/*|/dcuclogin/*|/authorizationPage.html|/importAuthorizationFile.html|/license/LicenseManagerServlet|/authorizationFile/|/v2/|/v1/home/|/v1/txry/|/v1/txzb/|/v1/dict/|/api/file/|/v1/

3. 契约平台测试API

输入地址即可测试

二. 后端常用代码

1. 逻辑删除方法

实体类继承CurdEntity
持久层调用xxxRepository.deleteById(id)方法就是逻辑删除

2. 业务与图片关联

在保存/编辑的时候，前端传过来图片id
首先删除业务与图片的关联，用于编辑操作
获取图片id，将业务id和图片类型存入附件表中
在查看详情的时候通过业务id从附件表中查出图片id
拼接成路径返回给前端http://20.20.32.126:8088/slxfwz/api/file/view/{图片id}

代码如下

2.1 Service层保存/详情操作

a. 保存时与图片关联

b. 查询详情时拼接图片url返回

@Service
@Transactional
public class SlxfCombatFireexpertService extends BaseService<CombatFireexpert, String> implements ISlxfCombatFireexpertService{

    private SlxfCombatFireexpertRepository slxfCombatFireexpertRepository;

    @Autowired
    private SlxfCombatFaculyRepository slxfCombatFaculyRepository;

    @Autowired
    private GeneralConvertor generalConvertor;

    @Autowired
    private ErticleRepository erticleRepository;

    public SlxfCombatFireexpertService(SlxfCombatFireexpertRepository slxfCombatFireexpertRepository){
            super(slxfCombatFireexpertRepository);
            this.slxfCombatFireexpertRepository=slxfCombatFireexpertRepository;
    }
    /**
     * 新增/编辑灭火专家
     * @param tSlxfCombatFireexpert
     * @return
     */
    @Override
    public CombatFireexpert saveFire(TSlxfCombatFireexpert tSlxfCombatFireexpert) {
        List<Map<String,Object>> map = slxfCombatFaculyRepository.findSortNoByCode(tSlxfCombatFireexpert.getDept());
        tSlxfCombatFireexpert.setSortNo(Integer.parseInt(map.get(0).get("SORT_NO").toString()));
        CombatFireexpert convertor = generalConvertor.convertor(tSlxfCombatFireexpert, CombatFireexpert.class);
        CombatFireexpert save = slxfCombatFireexpertRepository.save(convertor);
        /**
         * 将图片与业务关联
         */
        //先删除业务与图片的关联，用于编辑操作
        erticleRepository.updateDelFile(save.getId());
        String pictureId = tSlxfCombatFireexpert.getPictureId();
        erticleRepository.updateFile(save.getId(), CombatPersonnelEnum.MHZJ_IMG.getCode(),pictureId);
        return save;
    }
    /**
     * 查询灭火专家详情
     * @param s
     * @return
     */
    @Override
    public TSlxfCombatFireexpert findFire(String s) {
        CombatFireexpert one = slxfCombatFireexpertRepository.findOne(s);
        TSlxfCombatFireexpert convertor = generalConvertor.convertor(one, TSlxfCombatFireexpert.class);
        // 组合图片url
        List<UploaderFile> fileByBusinessType = erticleRepository.findFileByBusinessType(s);
        List<CodeRecord> list = CodeInfoUtils.getCodeListByCodeDicId(SysEnum.QQSH.getCode());
        if(CollectionUtils.isNotEmpty(fileByBusinessType)){
            String url = list.get(0).getValue() + ErticleEnum.URL.getDesc() + fileByBusinessType.get(0).getId();
            convertor.setPictureUrl(url);
        }
        return convertor;
    }
}

2.2 对附件表的操作

a. 将业务与附件做关联

b. 删除业务与附件的关联

c. 根据业务id获取业务附件

public interface ErticleRepository extends BaseRepository<Erticle, String> {
    /**
     * 将业务与附件做关联
     * @param
     */
    @Modifying
    @NativeQuery("UPDATE T_UPLOADER_FILE SET BUSINESS_ID = :businessId, BUSINESS_TYPE = :businessType WHERE ID = :fileId")
    void updateFile(@Param("businessId") String businessId, @Param("businessType") String businessType, @Param("fileId") String fileId);

    /**
     * 删除业务与附件的关联
     * @param
     */
    @Modifying
    @NativeQuery("UPDATE T_UPLOADER_FILE SET BUSINESS_ID = '', BUSINESS_TYPE = '' WHERE BUSINESS_ID = :businessId")
    void updateDelFile(@Param("businessId") String businessId);

    /**
     * 根据业务id获取业务附件
     * @return
     */
    @NativeQuery(value = "SELECT * from T_UPLOADER_FILE WHERE BUSINESS_ID =:businessId  ORDER by GENERATE_TIME ASC")
    List<UploaderFile> findFileByBusinessType(@Param("businessId") String businessId);
}

3. 人员编成查在职/借调人数和总人数SQL

简单理解相当于从这个班级里查出总人数、男生人数、女生人数。

传过来deptCode来查出该部门下的相关人数量

3.1 Service

当deptCode不存在时都显示为0

/**
     * 展示该部门人员相关数量
     * @param deptCode
     * @return
     */
@Override
public PersonnelVo showNumber(String deptCode) {
    Map<String,Object> map = slxfCombatPersonnelRepository.selectSumInfoByDeptCode(deptCode);
    PersonnelVo p = new PersonnelVo();
    p.setDeptCode(deptCode);
    p.setPeopleNumber(map.get("PEOPLENUMBER").toString());
    p.setJobNumber(ObjectUtils.isEmpty(map.get("JOBNUMBER")) ? "0" : map.get("JOBNUMBER").toString());
    p.setLoanNumber(ObjectUtils.isEmpty(map.get("LOANNUMBER")) ? "0" : map.get("LOANNUMBER").toString());
    return p;
}

3.2 Repository层SQL

这里涉及到decode的用法，之前的文章里写过

decode参考这篇文章

/**
     * 查找该部门人员相关信息
     * @param deptCode
     * @return
     */
    @NativeQuery("select count(*) as PEOPLENUMBER, sum(decode(JOB,1,1,0)) as JOBNUMBER,sum(decode(JOB,2,1,0)) as LOANNUMBER from T_SLXF_COMBAT_PERSONNEL where dept = :deptCode")
    Map<String,Object> selectSumInfoByDeptCode(@Param("deptCode") String deptCode);

4. 人员排序的需求实现sql

就是根据业务id更新数据库的sort字段的值

update T_SLXF_COMBAT_PERSONNEL set sort = sort + 1 where id = :id

4.1 Service层

/**
     * 上升人员排序
     * @param s
     */
@Override
public void sortUp(String s) {
    slxfCombatPersonnelRepository.sortUp(s);
}

/**
     * 下降人员排序
     * @param s
     */
@Override
public void sortDown(String s) {
    slxfCombatPersonnelRepository.sortDown(s);
}

4.2 Repository层SQL

/**
     * 升高排序
     * @param id
     */
@Modifying
@NativeQuery("update T_SLXF_COMBAT_PERSONNEL set sort = sort + 1 where id = :id")
void sortUp(@Param("id") String id);

/**
     * 下降排序
     * @param id
     */
@Modifying
@NativeQuery("update T_SLXF_COMBAT_PERSONNEL set sort = sort - 1 where id = :id")
void sortDown(@Param("id") String id);

5. 查询机构数接口

5.1 Service层TreeNodeUtils使用

返回类型List<Map<String,Object>>
查询机构表的所有数据
查三个字段CODE,FULL_NAME,UP_GOV_NAME
TreeNodeUtils.generateTree()
SimpleTreeNodeItemResolver("本机构数据库列名","父机构数据库列名")

/**
     * 查询机构树
     * @return
     */
@Override
public List<Map<String, Object>> getDeptTree() {
    List<Map<String,String>> orgInfoList = slxfCombatPersonnelRepository.findAllOrgInfo();
    List<Map<String, Object>> maps = TreeNodeUtils.generateTree(orgInfoList, new SimpleTreeNodeItemResolver("FULL_NAME", "UP_GOV_NAME"));
    return maps;
}

5.2 Repository层查询所有机构

public interface SlxfCombatPersonnelRepository extends BaseRepository<CombatPersonnel, String> {

    @NativeQuery("select CODE,FULL_NAME,UP_GOV_NAME from T_ORG_INFO")
    List<Map<String, String>> findAllOrgInfo();

}

6. 作训处权限判断

6.1 需求

人员编成列表的排序列、创建按钮、删除编辑按钮需要权限控制

其他四个统一叫人才数据，创建按钮、删除编辑按钮需要权限控制

6.2 添加SQL

在权限表中添加这两个权限

INSERT INTO "T_SECURITY_RIGHT" ("ID", "APPLICATION_ID", "PARENT_ID", "CODE", "URL", "SORT_NUM", "SHOW_MODE", "RIGHT_TYPE", "DESCRIPTION", "IS_ENABLED", "CREATE_USER", "CREATE_TIME", "CREATE_DEPT", "UPDATE_USER", "UPDATE_TIME", "UPDATE_DEPT", "IS_DELETED", "DELETE_TIME", "DELETE_USER", "DELETE_DEPT", "OPEN_MODE", "OPEN_FUNCTION", "NAME", "IS_SYSTEM", "ICONCLASS") VALUES ('SLXF_RYBC', 'QX_SLXF_ZX', NULL, 'SLXF_RYBC', NULL, NULL, '1', NULL, '人员编成', '1', NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, NULL, NULL, '0', NULL, '人员编成', '0', NULL);
INSERT INTO "T_SECURITY_RIGHT" ("ID", "APPLICATION_ID", "PARENT_ID", "CODE", "URL", "SORT_NUM", "SHOW_MODE", "RIGHT_TYPE", "DESCRIPTION", "IS_ENABLED", "CREATE_USER", "CREATE_TIME", "CREATE_DEPT", "UPDATE_USER", "UPDATE_TIME", "UPDATE_DEPT", "IS_DELETED", "DELETE_TIME", "DELETE_USER", "DELETE_DEPT", "OPEN_MODE", "OPEN_FUNCTION", "NAME", "IS_SYSTEM", "ICONCLASS") VALUES ('SLXF_RCSJ', 'QX_SLXF_ZX', NULL, 'SLXF_RCSJ', NULL, NULL, '1', NULL, '人才数据', '1', NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, NULL, NULL, '0', NULL, '人才数据', '0', NULL);

在dcuc中同步

dcuc中数据源配置

6.3 编写接口返回权限信息

Controller层

/**
     * 获取作训处登录用户权限
     * @return
     */
@Override
public List<SecurityRightVo> getCombatRights() {
    return slxfCombatPersonnelService.getCombatRights();
}

Service层

/**
     * 获取作训处登录用户权限
     * @return
     */
@Override
public List<SecurityRightVo> getCombatRights() {
    SecurityUser currentUser = (SecurityUser) SessionUtils.getAttribute("securityUserSessionId");
    List<SecurityRight> securityRights = currentUser.getSecurityRightsByApplicationId("QX_SLXF_ZX");
    List<SecurityRightVo> convertor = generalConvertor.convertor(securityRights, SecurityRightVo.class);
    return convertor;
}

三. 后端完整代码

1. 优秀教员

Controller层

@RestController
public class CombatFaculyController implements CombatDataFaculyApi {

    @Autowired
    private ISlxfCombatFaculyService slxfCombatFaculyService;

    /**
     * 分页+条件查询优秀教员
     * @param searchable
     * @return
     */
    @Override
    public Page<TSlxfCombatFaculy> searchFaculy(Searchable searchable) {
        return slxfCombatFaculyService.searchFaculy(searchable);
    }

    /**
     * 保存/编辑优秀教员
     * @param tSlxfCombatFaculy
     * @return
     */
    @Override
    public String saveFaculy(TSlxfCombatFaculy tSlxfCombatFaculy) {
        CombatFaculy combatFaculy= slxfCombatFaculyService.saveFaculy(tSlxfCombatFaculy);
        return combatFaculy.getId();
    }

    /**
     * 逻辑删除优秀教员
     * @param id
     * @return
     */
    @Override
    public String deleteFaculy(String id) {
        slxfCombatFaculyService.deleteFaculy(id);
        return id;
    }

    /**
     * 查询优秀教员详情
     * @param id
     * @return
     */
    @Override
    public TSlxfCombatFaculy findFaculy(String id) {
        return slxfCombatFaculyService.findFaculy(id);
    }

Service层

@Service
@Transactional
public class SlxfCombatFaculyService extends BaseService<CombatFaculy, String> implements ISlxfCombatFaculyService{

    private SlxfCombatFaculyRepository slxfCombatFaculyRepository;

    public SlxfCombatFaculyService(SlxfCombatFaculyRepository slxfCombatFaculyRepository){
            super(slxfCombatFaculyRepository);
            this.slxfCombatFaculyRepository=slxfCombatFaculyRepository;
    }
    @Autowired
    private GeneralConvertor generalConvertor;

    /**
     * 分页+条件查询分页教员
     * @param searchable
     * @return
     */
    @Override
    public Page<TSlxfCombatFaculy> searchFaculy(Searchable searchable) {
        searchable.addSearchFilter("deleted", SearchOperator.eq, "0");
        searchable.addSort(Sort.Direction.ASC, "dept");
        searchable.addSort(Sort.Direction.DESC, "sortNo");
        Page<CombatFaculy> paging = slxfCombatFaculyRepository.paging(searchable);
        List<CombatFaculy> content = paging.getContent();
        List<TSlxfCombatFaculy> convertor = generalConvertor.convertor(content, TSlxfCombatFaculy.class);
        return new PageImpl<>(convertor, paging.getPageable(), paging.getTotalElements());
    }

    /**
     * 创建、编辑优秀教员
     * @param tSlxfCombatFaculy
     * @return
     */
    @Override
    public CombatFaculy saveFaculy(TSlxfCombatFaculy tSlxfCombatFaculy) {
        String dept = tSlxfCombatFaculy.getDept();
        List<Map<String,Object>> map = slxfCombatFaculyRepository.findSortNoByCode(dept);
        tSlxfCombatFaculy.setSortNo(Integer.parseInt(map.get(0).get("SORT_NO").toString()));
        CombatFaculy convertor = generalConvertor.convertor(tSlxfCombatFaculy, CombatFaculy.class);
        return slxfCombatFaculyRepository.save(convertor);
    }

    /**
     * 逻辑删除优秀教员
     * @param s
     */
    @Override
    public void deleteFaculy(String s) {
        slxfCombatFaculyRepository.deleteById(s);
    }

    /**
     * 查询优秀教员详情
     * @param s
     * @return
     */
    @Override
    public TSlxfCombatFaculy findFaculy(String s) {
        CombatFaculy one = slxfCombatFaculyRepository.findOne(s);
        return generalConvertor.convertor(one, TSlxfCombatFaculy.class);
    }
}

Repository层

public interface SlxfCombatFaculyRepository extends BaseRepository<CombatFaculy, String> {
    /**
     * 根据单位code查询单位排序
     * @param dept
     * @return
     */
    @NativeQuery("SELECT SORT_NO FROM T_ORG_INFO WHERE CODE = :dept")
    List<Map<String,Object>> findSortNoByCode(@Param("dept") String dept);
}

2. 防火灭火专家(有图片)

Controller层

@RestController
public class CombatFireexpertController implements CombatDataFireApi {

    @Autowired
    private ISlxfCombatFireexpertService slxfCombatFireexpertService;


    /**
     * 分页+条件查询防火灭火专家集合
     * @param searchable
     * @return
     */
    @Override
    public Page<TSlxfCombatFireexpert> searchFire(Searchable searchable) {
        return slxfCombatFireexpertService.searchFire(searchable);
    }

    /**
     * 保存/编辑防火灭火专家
     * @param tSlxfCombatFireexpert
     * @return
     */
    @Override
    public String saveFire(TSlxfCombatFireexpert tSlxfCombatFireexpert) {
        CombatFireexpert combatFireexpert = slxfCombatFireexpertService.saveFire(tSlxfCombatFireexpert);
        return combatFireexpert.getId();
    }

    /**
     * 逻辑删除防火灭火专家
     * @param id
     * @return
     */
    @Override
    public String deleteFire(String id) {
        slxfCombatFireexpertService.deleteFire(id);
        return id;
    }

    /**
     * 查询防火灭火专家详情
     * @param id
     */
    @Override
    public TSlxfCombatFireexpert findFire(String id) {
        return slxfCombatFireexpertService.findFire(id);
    }
}

Service层

@Service
@Transactional
public class SlxfCombatFireexpertService extends BaseService<CombatFireexpert, String> implements ISlxfCombatFireexpertService{

    private SlxfCombatFireexpertRepository slxfCombatFireexpertRepository;

    @Autowired
    private SlxfCombatFaculyRepository slxfCombatFaculyRepository;

    @Autowired
    private GeneralConvertor generalConvertor;

    @Autowired
    private ErticleRepository erticleRepository;

    public SlxfCombatFireexpertService(SlxfCombatFireexpertRepository slxfCombatFireexpertRepository){
            super(slxfCombatFireexpertRepository);
            this.slxfCombatFireexpertRepository=slxfCombatFireexpertRepository;
    }

    /**
     * 分页+条件查询灭火专家
     * @param searchable
     * @return
     */
    @Override
    public Page<TSlxfCombatFireexpert> searchFire(Searchable searchable) {
        searchable.addSearchFilter("deleted", SearchOperator.eq, "0");
        searchable.addSort(Sort.Direction.ASC, "dept");
        searchable.addSort(Sort.Direction.DESC, "sortNo");
        Page<CombatFireexpert> paging = slxfCombatFireexpertRepository.paging(searchable);
        List<CombatFireexpert> content = paging.getContent();
        List<TSlxfCombatFireexpert> convertor = generalConvertor.convertor(content, TSlxfCombatFireexpert.class);
        return new PageImpl<>(convertor, paging.getPageable(), paging.getTotalElements());
    }

    /**
     * 新增/编辑灭火专家
     * @param tSlxfCombatFireexpert
     * @return
     */
    @Override
    public CombatFireexpert saveFire(TSlxfCombatFireexpert tSlxfCombatFireexpert) {
        List<Map<String,Object>> map = slxfCombatFaculyRepository.findSortNoByCode(tSlxfCombatFireexpert.getDept());
        tSlxfCombatFireexpert.setSortNo(Integer.parseInt(map.get(0).get("SORT_NO").toString()));
        CombatFireexpert convertor = generalConvertor.convertor(tSlxfCombatFireexpert, CombatFireexpert.class);
        CombatFireexpert save = slxfCombatFireexpertRepository.save(convertor);
        /**
         * 将图片与业务关联
         */
        //先删除业务与图片的关联，用于编辑操作
        erticleRepository.updateDelFile(save.getId());
        String pictureId = tSlxfCombatFireexpert.getPictureId();
        erticleRepository.updateFile(save.getId(), CombatPersonnelEnum.MHZJ_IMG.getCode(),pictureId);
        return save;
    }

    /**
     * 逻辑删除灭火专家
     * @param s
     */
    @Override
    public void deleteFire(String s) {
        slxfCombatFireexpertRepository.deleteById(s);
    }

    /**
     * 查询灭火专家详情
     * @param s
     * @return
     */
    @Override
    public TSlxfCombatFireexpert findFire(String s) {
        CombatFireexpert one = slxfCombatFireexpertRepository.findOne(s);
        TSlxfCombatFireexpert convertor = generalConvertor.convertor(one, TSlxfCombatFireexpert.class);
        // 组合图片url
        List<UploaderFile> fileByBusinessType = erticleRepository.findFileByBusinessType(s);
        List<CodeRecord> list = CodeInfoUtils.getCodeListByCodeDicId(SysEnum.QQSH.getCode());
        if(CollectionUtils.isNotEmpty(fileByBusinessType)){
            String url = list.get(0).getValue() + ErticleEnum.URL.getDesc() + fileByBusinessType.get(0).getId();
            convertor.setPictureUrl(url);
        }
        return convertor;
    }
}

3. 评判员

Controller层

@RestController
public class CombatJudgeController implements CombatDataJudgeApi {

    @Autowired
    private ISlxfCombatJudgeService slxfCombatJudgeService;

    /**
     * 分页查询数据
     * @param searchable
     * @return
     */
    @Override
    public Page<TSlxfCombatJudge> searchJudge(Searchable searchable) {
        return slxfCombatJudgeService.searchJudge(searchable);
    }

    /**
     * 新增 | 编辑人员数据
     * @param tSlxfCombatJudge
     * @return
     */
    @Override
    public String saveJudge(TSlxfCombatJudge tSlxfCombatJudge) {
        return slxfCombatJudgeService.saveJudge(tSlxfCombatJudge);
    }

    /**
     * 删除人员数据
     * @param id
     * @return
     */
    @Override
    public String deleteJudge(String id) {
        return slxfCombatJudgeService.deleteJudge(id);
    }

    /**
     * 查询人员数据详情
     * @param id
     * @return
     */
    @Override
    public TSlxfCombatJudge findJudge(String id) {
        return slxfCombatJudgeService.findJudgeById(id);
    }
}

Service层

@Service
@Transactional
public class SlxfCombatJudgeService extends BaseService<CombatJudge, String> implements ISlxfCombatJudgeService {

    @Autowired
    private GeneralConvertor generalConvertor;
    @Autowired
    private SlxfCombatFaculyRepository slxfCombatFaculyRepository;
    private SlxfCombatJudgeRepository slxfCombatJudgeRepository;

    public SlxfCombatJudgeService(SlxfCombatJudgeRepository slxfCombatJudgeRepository) {
        super(slxfCombatJudgeRepository);
        this.slxfCombatJudgeRepository = slxfCombatJudgeRepository;
    }

    @Override
    public Page<TSlxfCombatJudge> searchJudge(Searchable searchable) {
        searchable.addSearchFilter("deleted", SearchOperator.eq, "0");
        searchable.addSort(Sort.Direction.ASC, "dept");
        searchable.addSort(Sort.Direction.DESC, "sortNo");
        Page<CombatJudge> page  = slxfCombatJudgeRepository.paging(searchable);
        List<CombatJudge> list = page.getContent();
        List<TSlxfCombatJudge> combatJudgeList = generalConvertor.convertor(list, TSlxfCombatJudge.class);
        return new PageImpl<>(combatJudgeList, page.getPageable(), page.getTotalElements());
    }

    @Override
    public String saveJudge(TSlxfCombatJudge tSlxfCombatJudge) {
        List<Map<String,Object>> map = slxfCombatFaculyRepository.findSortNoByCode(tSlxfCombatJudge.getDept());
        tSlxfCombatJudge.setSortNo(Integer.parseInt(map.get(0).get("SORT_NO").toString()));
        CombatJudge combatJudge = slxfCombatJudgeRepository.save(generalConvertor.convertor(tSlxfCombatJudge, CombatJudge.class));
        return combatJudge.getId();
    }

    @Override
    public String deleteJudge(String id) {
        slxfCombatJudgeRepository.deleteJudgeById(id);
        return id;
    }

    @Override
    public TSlxfCombatJudge findJudgeById(String id) {
        CombatJudge combatJudge = slxfCombatJudgeRepository.findOne(id);
        return generalConvertor.convertor(combatJudge,TSlxfCombatJudge.class);
    }

}

Repository层

public interface SlxfCombatJudgeRepository extends BaseRepository<CombatJudge, String> {

    @Modifying
    @NativeQuery("UPDATE T_SLXF_COMBAT_JUDGE SET DELETED ='1' WHERE id =:id ")
    void deleteJudgeById(@Param("id") String id);
}

4. 人员编成(树+图+升降序+人数展示)

Controller层

@RestController
public class CombatPersonnelController implements CombatPersonnelApi {

    @Autowired
    private ISlxfCombatPersonnelService slxfCombatPersonnelService;

    /**
     * 分页+条件查询人员编成列表
     * @param searchable
     * @return
     */
    @Override
    public Page<TSlxfCombatPersonnel> searchPersonnel(Searchable searchable) {
        return slxfCombatPersonnelService.searchPersonnel(searchable);
    }

    /**
     * 保存、编辑人员编成信息
     * @param tSlxfCombatPersonnel
     * @return
     */
    @Override
    public String savePersonnel(TSlxfCombatPersonnel tSlxfCombatPersonnel) {
        CombatPersonnel combatPersonnel = slxfCombatPersonnelService.savePersonel(tSlxfCombatPersonnel);
        return combatPersonnel.getId();
    }

    /**
     * 逻辑删除人员编成信息
     * @param id
     * @return
     */
    @Override
    public String deletePersonnel(String id) {
        slxfCombatPersonnelService.deletePersonal(id);
        return id;
    }

    /**
     * 获取机构树
     * @return
     */
    @Override
    public List<Map<String, Object>> getDeptTree() {
        return slxfCombatPersonnelService.getDeptTree();
    }

    /**
     * 查询人员编成详情
     * @param id
     * @return
     */
    @Override
    public TSlxfCombatPersonnel findPersonnel(String id) {
        return slxfCombatPersonnelService.findPersonnel(id);
    }

    /**
     * 人员升序
     * @param id
     * @return
     */
    @Override
    public String sortUp(String id) {
        slxfCombatPersonnelService.sortUp(id);
        return id;
    }

    /**
     * 人员排序下降
     * @param id
     * @return
     */
    @Override
    public String sortDown(String id) {
        slxfCombatPersonnelService.sortDown(id);
        return id;
    }

    /**
     * 展示该人员相关数量
     * @param deptCode
     * @return
     */
    @Override
    public PersonnelVo showNumber(String deptCode) {
        PersonnelVo personnelVo = slxfCombatPersonnelService.showNumber(deptCode);
        personnelVo.setDeptCode(deptCode);
        return personnelVo;
    }
    /**
     * 获取作训处登录用户权限
     * @return
     */
    @Override
    public List<SecurityRightVo> getCombatRights() {
        return slxfCombatPersonnelService.getCombatRights();
    }
}

Service层

@Service
@Transactional
public class SlxfCombatPersonnelService extends BaseService<CombatPersonnel, String> implements ISlxfCombatPersonnelService{

    private SlxfCombatPersonnelRepository slxfCombatPersonnelRepository;

    @Autowired
    private SlxfCombatFaculyRepository slxfCombatFaculyRepository;

    @Autowired
    private GeneralConvertor generalConvertor;

    @Autowired
    private ErticleRepository erticleRepository;

    public SlxfCombatPersonnelService(SlxfCombatPersonnelRepository slxfCombatPersonnelRepository){
            super(slxfCombatPersonnelRepository);
            this.slxfCombatPersonnelRepository=slxfCombatPersonnelRepository;
    }

    /**
     * 查询机构树
     * @return
     */
    @Override
    public List<Map<String, Object>> getDeptTree() {
        List<Map<String,String>> orgInfoList = slxfCombatPersonnelRepository.findAllOrgInfo();
        List<Map<String, Object>> maps = TreeNodeUtils.generateTree(orgInfoList, new SimpleTreeNodeItemResolver("FULL_NAME", "UP_GOV_NAME"));
        return maps;
    }

    /**
     * 分页+条件查询人员编成
     * @param searchable
     * @return
     */
    @Override
    public Page<TSlxfCombatPersonnel> searchPersonnel(Searchable searchable) {
        Condition condition = searchable.getSearchFilter("dept",SearchOperator.eq);
        if(condition.getValue().toString().equals("010000000000")){
            searchable.removeSearchFilter("dept",SearchOperator.eq);
        }
        searchable.addSearchFilter("deleted", SearchOperator.eq, "0");
        searchable.addSort(Sort.Direction.DESC, "sort");
        searchable.addSort(Sort.Direction.ASC, "dept");
        searchable.addSort(Sort.Direction.DESC, "sortNo");
        Page<CombatPersonnel> paging = slxfCombatPersonnelRepository.paging(searchable);
        List<CombatPersonnel> content = paging.getContent();
        List<TSlxfCombatPersonnel> convertor = generalConvertor.convertor(content, TSlxfCombatPersonnel.class);
        return new PageImpl<>(convertor, paging.getPageable(), paging.getTotalElements());
    }

    /**
     * 保存/编辑人员编成信息
     * @param tSlxfCombatPersonnel
     * @return
     */
    @Override
    public CombatPersonnel savePersonel(TSlxfCombatPersonnel tSlxfCombatPersonnel) {
        List<Map<String,Object>> map = slxfCombatFaculyRepository.findSortNoByCode(tSlxfCombatPersonnel.getDept());
        tSlxfCombatPersonnel.setSortNo(Integer.parseInt(map.get(0).get("SORT_NO").toString()));
        // 默认排序字段为0
        tSlxfCombatPersonnel.setSort(0);
        CombatPersonnel convertor = generalConvertor.convertor(tSlxfCombatPersonnel, CombatPersonnel.class);
        CombatPersonnel save = slxfCombatPersonnelRepository.save(convertor);
        /**
         * 将图片与业务关联
         */
        //先删除业务与图片的关联，用于编辑操作
        erticleRepository.updateDelFile(save.getId());
        String pictureId = tSlxfCombatPersonnel.getPictureId();
        erticleRepository.updateFile(save.getId(), CombatPersonnelEnum.RYBC_IMG.getCode(),pictureId);
        return save;
    }

    /**
     * 逻辑删除人员编成信息
     * @param s
     */
    @Override
    public void deletePersonal(String s) {
        slxfCombatPersonnelRepository.deleteById(s);
    }

    /**
     * 查询人员编成详情
     * @param s
     * @return
     */
    @Override
    public TSlxfCombatPersonnel findPersonnel(String s) {
        CombatPersonnel one = slxfCombatPersonnelRepository.findOne(s);
        TSlxfCombatPersonnel convertor = generalConvertor.convertor(one, TSlxfCombatPersonnel.class);
        /*
        生成图片url
         */
        List<UploaderFile> fileByBusinessType = erticleRepository.findFileByBusinessType(s);
        List<CodeRecord> list = CodeInfoUtils.getCodeListByCodeDicId(SysEnum.QQSH.getCode());
        if(CollectionUtils.isNotEmpty(fileByBusinessType)){
            String url = list.get(0).getValue() + ErticleEnum.URL.getDesc() + fileByBusinessType.get(0).getId();
            convertor.setPictureUrl(url);
        }
        return convertor;
    }

    /**
     * 上升人员排序
     * @param s
     */
    @Override
    public void sortUp(String s) {
        slxfCombatPersonnelRepository.sortUp(s);
    }

    /**
     * 下降人员排序
     * @param s
     */
    @Override
    public void sortDown(String s) {
        slxfCombatPersonnelRepository.sortDown(s);
    }

    /**
     * 展示该部门人员相关数量
     * @param deptCode
     * @return
     */
    @Override
    public PersonnelVo showNumber(String deptCode) {
        Map<String,Object> map = slxfCombatPersonnelRepository.selectSumInfoByDeptCode(deptCode);
        PersonnelVo p = new PersonnelVo();
        p.setDeptCode(deptCode);
        p.setPeopleNumber(map.get("PEOPLENUMBER").toString());
        p.setJobNumber(ObjectUtils.isEmpty(map.get("JOBNUMBER")) ? "0" : map.get("JOBNUMBER").toString());
        p.setLoanNumber(ObjectUtils.isEmpty(map.get("LOANNUMBER")) ? "0" : map.get("LOANNUMBER").toString());
        return p;
    }
    /**
     * 获取作训处登录用户权限
     * @return
     */
    @Override
    public List<SecurityRightVo> getCombatRights() {
        SecurityUser currentUser = (SecurityUser) SessionUtils.getAttribute("securityUserSessionId");
        List<SecurityRight> securityRights = currentUser.getSecurityRightsByApplicationId("QX_SLXF_ZX");
        List<SecurityRightVo> convertor = generalConvertor.convertor(securityRights, SecurityRightVo.class);
        return convertor;
    }
}

Repository层

public interface SlxfCombatPersonnelRepository extends BaseRepository<CombatPersonnel, String> {


    @NativeQuery("select CODE,FULL_NAME,UP_GOV_NAME from T_ORG_INFO")
    List<Map<String, String>> findAllOrgInfo();

    /**
     * 升高排序
     * @param id
     */
    @Modifying
    @NativeQuery("update T_SLXF_COMBAT_PERSONNEL set sort = sort + 1 where id = :id")
    void sortUp(@Param("id") String id);

    /**
     * 下降排序
     * @param id
     */
    @Modifying
    @NativeQuery("update T_SLXF_COMBAT_PERSONNEL set sort = sort - 1 where id = :id")
    void sortDown(@Param("id") String id);

    /**
     * 查找该部门人员相关信息
     * @param deptCode
     * @return
     */
    @NativeQuery("select count(*) as PEOPLENUMBER, sum(decode(JOB,1,1,0)) as JOBNUMBER,sum(decode(JOB,2,1,0)) as LOANNUMBER from T_SLXF_COMBAT_PERSONNEL where dept = :deptCode")
    Map<String,Object> selectSumInfoByDeptCode(@Param("deptCode") String deptCode);
}

5. 空中观察指挥培训人员

Controller层

@RestController
public class CombatTrainingController implements CombatDataTrainApi {

    @Autowired
    private ISlxfCombatTrainingService slxfCombatTrainingService;

    /**
     * 分页+条件查询空中观察指挥培训人员列表
     * @param searchable
     * @return
     */
    @Override
    public Page<TSlxfCombatTraining> searchTrain(Searchable searchable) {
        return slxfCombatTrainingService.searchTrain(searchable);
    }

    /**
     * 保存、编辑空中观察指挥培训人员
     * @param tSlxfCombatTraining
     * @return
     */
    @Override
    public String saveTrain(TSlxfCombatTraining tSlxfCombatTraining) {
        CombatTraining combatTraining = slxfCombatTrainingService.saveTrain(tSlxfCombatTraining);
        return combatTraining.getId();
    }

    /**
     * 逻辑删除空中观察指挥培训人员
     * @param id
     * @return
     */
    @Override
    public String deleteTrain(String id) {
        slxfCombatTrainingService.deleteTrain(id);
        return id;
    }

    @Override
    public TSlxfCombatTraining findTrain(String id) {
        return slxfCombatTrainingService.findTrain(id);
    }
}

Service层

@Service
@Transactional
public class SlxfCombatTrainingService extends BaseService<CombatTraining, String> implements ISlxfCombatTrainingService{

    private SlxfCombatTrainingRepository slxfCombatTrainingRepository;

    public SlxfCombatTrainingService(SlxfCombatTrainingRepository slxfCombatTrainingRepository){
            super(slxfCombatTrainingRepository);
            this.slxfCombatTrainingRepository=slxfCombatTrainingRepository;
    }
    @Autowired
    private GeneralConvertor generalConvertor;
    @Autowired
    private SlxfCombatFaculyRepository slxfCombatFaculyRepository;
    /**
     * 分页+条件查询空中观察指挥培训人员列表
     * @param searchable
     * @return
     */
    @Override
    public Page<TSlxfCombatTraining> searchTrain(Searchable searchable) {
        searchable.addSearchFilter("deleted", SearchOperator.eq, "0");
        searchable.addSort(Sort.Direction.ASC, "dept");
        searchable.addSort(Sort.Direction.DESC, "sortNo");
        Page<CombatTraining> paging = slxfCombatTrainingRepository.paging(searchable);
        List<CombatTraining> content = paging.getContent();
        List<TSlxfCombatTraining> convertor = generalConvertor.convertor(content, TSlxfCombatTraining.class);
        return new PageImpl<>(convertor, paging.getPageable(), paging.getTotalElements());
    }

    /**
     * 保存/编辑空中观察指挥培训人员列表
     * @param tSlxfCombatTraining
     * @return
     */
    @Override
    public CombatTraining saveTrain(TSlxfCombatTraining tSlxfCombatTraining) {
        List<Map<String,Object>> map = slxfCombatFaculyRepository.findSortNoByCode(tSlxfCombatTraining.getDept());
        tSlxfCombatTraining.setSortNo(Integer.parseInt(map.get(0).get("SORT_NO").toString()));
        CombatTraining convertor = generalConvertor.convertor(tSlxfCombatTraining, CombatTraining.class);
        return slxfCombatTrainingRepository.save(convertor);
    }

    /**
     * 逻辑删除空中观察指挥培训人员列表
     * @param s
     */
    @Override
    public void deleteTrain(String s) {
        slxfCombatTrainingRepository.deleteById(s);
    }

    /**
     * 查询空中观察指挥培训人员列表详情
     * @param s
     * @return
     */
    @Override
    public TSlxfCombatTraining findTrain(String s) {
        CombatTraining one = slxfCombatTrainingRepository.findOne(s);
        return generalConvertor.convertor(one, TSlxfCombatTraining.class);
    }
}

四. 数据库脚本即建表

1. 码表新增的数据

INSERT INTO "DM_SYS_CODE" ("CODE", "VALUE", "CODE_ID") VALUES ('1', '研究生及以上', 'DM_DEGREE_OF_EDUCATION');
INSERT INTO "DM_SYS_CODE" ("CODE", "VALUE", "CODE_ID") VALUES ('2', '本科', 'DM_DEGREE_OF_EDUCATION');
INSERT INTO "DM_SYS_CODE" ("CODE", "VALUE", "CODE_ID") VALUES ('3', '大专', 'DM_DEGREE_OF_EDUCATION');
INSERT INTO "DM_SYS_CODE" ("CODE", "VALUE", "CODE_ID") VALUES ('4', '高中', 'DM_DEGREE_OF_EDUCATION');
INSERT INTO "DM_SYS_CODE" ("CODE", "VALUE", "CODE_ID") VALUES ('5', '初中', 'DM_DEGREE_OF_EDUCATION');
INSERT INTO "DM_SYS_CODE" ("CODE", "VALUE", "CODE_ID") VALUES ('6', '小学', 'DM_DEGREE_OF_EDUCATION');
INSERT INTO "DM_SYS_CODE" ("CODE", "VALUE", "CODE_ID") VALUES ('7', '其他', 'DM_DEGREE_OF_EDUCATION');

INSERT INTO "T_MD_SYS_CODE_DIC" ("CODE_ID", "DATASOURCE_ID", "NAME", "ENTITY_NAME", "CODE_FIELD", "NAME_FIELD", "PY_FIELD", "WB_FIELD", "ORDER_FIELD", "SEGMENT_RULE", "VIEW_SQL", "LOAD_TYPE", "PARENT_CODE_FIELD") VALUES ('DM_DEGREE_OF_EDUCATION', NULL, '文化程度', 'DM_SYS_CODE', 'CODE', 'VALUE', NULL, NULL, NULL, NULL, 'CODE_ID=''DM_DEGREE_OF_EDUCATION''', '1', NULL);

INSERT INTO "DM_SYS_CODE" ("CODE", "VALUE", "CODE_ID") VALUES ('1', '在岗', 'DM_ON_THE_JOB');
INSERT INTO "DM_SYS_CODE" ("CODE", "VALUE", "CODE_ID") VALUES ('2', '借调', 'DM_ON_THE_JOB');

INSERT INTO "T_MD_SYS_CODE_DIC" ("CODE_ID", "DATASOURCE_ID", "NAME", "ENTITY_NAME", "CODE_FIELD", "NAME_FIELD", "PY_FIELD", "WB_FIELD", "ORDER_FIELD", "SEGMENT_RULE", "VIEW_SQL", "LOAD_TYPE", "PARENT_CODE_FIELD") VALUES ('DM_ON_THE_JOB', NULL, '在岗情况', 'DM_SYS_CODE', 'CODE', 'VALUE', NULL, NULL, NULL, NULL, 'CODE_ID=''DM_ON_THE_JOB''', '1', NULL);


INSERT INTO "T_MD_SYS_CODE_DIC" ("CODE_ID", "DATASOURCE_ID", "NAME", "ENTITY_NAME", "CODE_FIELD", "NAME_FIELD", "PY_FIELD", "WB_FIELD", "ORDER_FIELD", "SEGMENT_RULE", "VIEW_SQL", "LOAD_TYPE", "PARENT_CODE_FIELD") VALUES ('DM_NATION_CODE', NULL, '民族', 'DM_MZ', 'MB', 'MC', NULL, NULL, NULL, NULL, NULL, '1', NULL);

2. 建表语句

/*==============================================================*/
/* DBMS name:      ORACLE Version 11g                           */
/* Created on:     2021/1/11 13:57:09                           */
/*==============================================================*/
/*==============================================================*/
/* Table: T_SLXF_COMBAT_FACULY                                  */
/*==============================================================*/
create table T_SLXF_COMBAT_FACULY
(
   ID                   VARCHAR2(32)         not null,
   NAME                 VARCHAR2(50),
   DEPT                 VARCHAR2(50),
   PHONE                VARCHAR2(50),
   TITLE                VARCHAR2(50),
   BU_RANK              VARCHAR2(50),
   BIRTH                VARCHAR2(50),
   RATING_CONDEITION    VARCHAR2(500),
   SORT_NO              NUMBER,
   SUBJECTS             VARCHAR2(500),
   CREATE_USER          VARCHAR2(50),
   CREATE_DEPT          VARCHAR2(50),
   UPDATE_USER          VARCHAR2(50),
   UPDATE_TIME          VARCHAR2(50),
   UPDATE_DEPT          VARCHAR2(50),
   CREATE_TIME          VARCHAR2(50),
   DELETE_USER          VARCHAR2(50),
   DELETE_TIME          VARCHAR2(50),
   DELETE_DEPT          VARCHAR2(50),
   DELETED              VARCHAR2(2),
   constraint PK_T_SLXF_COMBAT_FACULY primary key (ID)
);

comment on table T_SLXF_COMBAT_FACULY is
'作训处-优秀教员';

comment on column T_SLXF_COMBAT_FACULY.ID is
'ID';

comment on column T_SLXF_COMBAT_FACULY.NAME is
'姓名';

comment on column T_SLXF_COMBAT_FACULY.DEPT is
'单位';

comment on column T_SLXF_COMBAT_FACULY.PHONE is
'联系电话';

comment on column T_SLXF_COMBAT_FACULY.TITLE is
'衔级';

comment on column T_SLXF_COMBAT_FACULY.BU_RANK is
'部 职 别';

comment on column T_SLXF_COMBAT_FACULY.BIRTH is
'出生年月';

comment on column T_SLXF_COMBAT_FACULY.RATING_CONDEITION is
'评级情况';

comment on column T_SLXF_COMBAT_FACULY.SORT_NO is
'机构排序号';

comment on column T_SLXF_COMBAT_FACULY.SUBJECTS is
'评审科目';

comment on column T_SLXF_COMBAT_FACULY.CREATE_USER is
'CREATE_USER';

comment on column T_SLXF_COMBAT_FACULY.CREATE_DEPT is
'CREATE_DEPT';

comment on column T_SLXF_COMBAT_FACULY.UPDATE_USER is
'UPDATE_USER';

comment on column T_SLXF_COMBAT_FACULY.UPDATE_TIME is
'UPDATE_TIME';

comment on column T_SLXF_COMBAT_FACULY.UPDATE_DEPT is
'UPDATE_DEPT';

comment on column T_SLXF_COMBAT_FACULY.CREATE_TIME is
'CREATE_TIME';

comment on column T_SLXF_COMBAT_FACULY.DELETE_USER is
'DELETE_USER';

comment on column T_SLXF_COMBAT_FACULY.DELETE_TIME is
'DELETE_TIME';

comment on column T_SLXF_COMBAT_FACULY.DELETE_DEPT is
'DELETE_DEPT';

comment on column T_SLXF_COMBAT_FACULY.DELETED is
'DELETED';

/*==============================================================*/
/* Table: T_SLXF_COMBAT_FIREEXPERT                              */
/*==============================================================*/
create table T_SLXF_COMBAT_FIREEXPERT
(
   ID                   VARCHAR2(32)         not null,
   NAME                 VARCHAR2(50),
   SEX                  VARCHAR2(20),
   DEPT                 VARCHAR2(50),
   IDCARD               VARCHAR2(50),
   PHONE                VARCHAR2(50),
   EMAIL                VARCHAR2(50),
   EDUCATION            VARCHAR2(20),
   PICTURE_ID           VARCHAR2(32),
   INDUSTRY             VARCHAR2(50),
   PROFESSIONAL         VARCHAR2(50),
   JOB                  VARCHAR2(50),
   CERTIFICATE_NO       VARCHAR2(50),
   ADDRESS              VARCHAR2(100),
   SORT_NO              NUMBER,
   CREATE_USER          VARCHAR2(50),
   CREATE_DEPT          VARCHAR2(50),
   UPDATE_USER          VARCHAR2(50),
   UPDATE_TIME          VARCHAR2(50),
   UPDATE_DEPT          VARCHAR2(50),
   CREATE_TIME          VARCHAR2(50),
   DELETE_USER          VARCHAR2(50),
   DELETE_TIME          VARCHAR2(50),
   DELETE_DEPT          VARCHAR2(50),
   DELETED              VARCHAR2(2),
   constraint PK_T_SLXF_COMBAT_FIREEXPERT primary key (ID)
);

comment on table T_SLXF_COMBAT_FIREEXPERT is
'作训处-防火灭火专家';

comment on column T_SLXF_COMBAT_FIREEXPERT.ID is
'ID';

comment on column T_SLXF_COMBAT_FIREEXPERT.NAME is
'姓名';

comment on column T_SLXF_COMBAT_FIREEXPERT.SEX is
'性别';

comment on column T_SLXF_COMBAT_FIREEXPERT.DEPT is
'单位';

comment on column T_SLXF_COMBAT_FIREEXPERT.IDCARD is
'身份证号码';

comment on column T_SLXF_COMBAT_FIREEXPERT.PHONE is
'联系电话';

comment on column T_SLXF_COMBAT_FIREEXPERT.EMAIL is
'邮箱';

comment on column T_SLXF_COMBAT_FIREEXPERT.EDUCATION is
'文化程度';

comment on column T_SLXF_COMBAT_FIREEXPERT.PICTURE_ID is
'图片id';

comment on column T_SLXF_COMBAT_FIREEXPERT.INDUSTRY is
'行业领域';

comment on column T_SLXF_COMBAT_FIREEXPERT.PROFESSIONAL is
'从事专业';

comment on column T_SLXF_COMBAT_FIREEXPERT.JOB is
'职务/职称/职 业';

comment on column T_SLXF_COMBAT_FIREEXPERT.CERTIFICATE_NO is
'岗位证书编号';

comment on column T_SLXF_COMBAT_FIREEXPERT.ADDRESS is
'通讯地址';

comment on column T_SLXF_COMBAT_FIREEXPERT.SORT_NO is
'机构排序号';

comment on column T_SLXF_COMBAT_FIREEXPERT.CREATE_USER is
'CREATE_USER';

comment on column T_SLXF_COMBAT_FIREEXPERT.CREATE_DEPT is
'CREATE_DEPT';

comment on column T_SLXF_COMBAT_FIREEXPERT.UPDATE_USER is
'UPDATE_USER';

comment on column T_SLXF_COMBAT_FIREEXPERT.UPDATE_TIME is
'UPDATE_TIME';

comment on column T_SLXF_COMBAT_FIREEXPERT.UPDATE_DEPT is
'UPDATE_DEPT';

comment on column T_SLXF_COMBAT_FIREEXPERT.CREATE_TIME is
'CREATE_TIME';

comment on column T_SLXF_COMBAT_FIREEXPERT.DELETE_USER is
'DELETE_USER';

comment on column T_SLXF_COMBAT_FIREEXPERT.DELETE_TIME is
'DELETE_TIME';

comment on column T_SLXF_COMBAT_FIREEXPERT.DELETE_DEPT is
'DELETE_DEPT';

comment on column T_SLXF_COMBAT_FIREEXPERT.DELETED is
'DELETED';

/*==============================================================*/
/* Table: T_SLXF_COMBAT_JUDGE                                   */
/*==============================================================*/
create table T_SLXF_COMBAT_JUDGE
(
   ID                   VARCHAR2(32)         not null,
   NAME                 VARCHAR2(50),
   DEPT                 VARCHAR2(50),
   PHONE                VARCHAR2(50),
   TITLE                VARCHAR2(50),
   BU_RANK              VARCHAR2(50),
   JOB_RESUME           VARCHAR2(2000),
   SORT_NO              NUMBER,
   EXPERIENCE           VARCHAR2(2000),
   CREATE_USER          VARCHAR2(50),
   CREATE_DEPT          VARCHAR2(50),
   UPDATE_USER          VARCHAR2(50),
   UPDATE_TIME          VARCHAR2(50),
   UPDATE_DEPT          VARCHAR2(50),
   CREATE_TIME          VARCHAR2(50),
   DELETE_USER          VARCHAR2(50),
   DELETE_TIME          VARCHAR2(50),
   DELETE_DEPT          VARCHAR2(50),
   DELETED              VARCHAR2(2),
   constraint PK_T_SLXF_COMBAT_JUDGE primary key (ID)
);

comment on table T_SLXF_COMBAT_JUDGE is
'作训处-评判员';

comment on column T_SLXF_COMBAT_JUDGE.ID is
'ID';

comment on column T_SLXF_COMBAT_JUDGE.NAME is
'姓名';

comment on column T_SLXF_COMBAT_JUDGE.DEPT is
'单位';

comment on column T_SLXF_COMBAT_JUDGE.PHONE is
'联系方式';

comment on column T_SLXF_COMBAT_JUDGE.TITLE is
'衔级';

comment on column T_SLXF_COMBAT_JUDGE.BU_RANK is
'部 职 别';

comment on column T_SLXF_COMBAT_JUDGE.JOB_RESUME is
'工作简历';

comment on column T_SLXF_COMBAT_JUDGE.SORT_NO is
'机构排序号';

comment on column T_SLXF_COMBAT_JUDGE.EXPERIENCE is
'培训情况';

comment on column T_SLXF_COMBAT_JUDGE.CREATE_USER is
'CREATE_USER';

comment on column T_SLXF_COMBAT_JUDGE.CREATE_DEPT is
'CREATE_DEPT';

comment on column T_SLXF_COMBAT_JUDGE.UPDATE_USER is
'UPDATE_USER';

comment on column T_SLXF_COMBAT_JUDGE.UPDATE_TIME is
'UPDATE_TIME';

comment on column T_SLXF_COMBAT_JUDGE.UPDATE_DEPT is
'UPDATE_DEPT';

comment on column T_SLXF_COMBAT_JUDGE.CREATE_TIME is
'CREATE_TIME';

comment on column T_SLXF_COMBAT_JUDGE.DELETE_USER is
'DELETE_USER';

comment on column T_SLXF_COMBAT_JUDGE.DELETE_TIME is
'DELETE_TIME';

comment on column T_SLXF_COMBAT_JUDGE.DELETE_DEPT is
'DELETE_DEPT';

comment on column T_SLXF_COMBAT_JUDGE.DELETED is
'DELETED';

/*==============================================================*/
/* Table: T_SLXF_COMBAT_PERSONNEL                               */
/*==============================================================*/
create table T_SLXF_COMBAT_PERSONNEL
(
   ID                   VARCHAR2(32)         not null,
   NAME                 VARCHAR2(50),
   SEX                  VARCHAR2(20),
   BIRTH                VARCHAR2(50),
   DEPT                 VARCHAR2(50),
   PHONE                VARCHAR2(50),
   PICTURE_ID           VARCHAR2(32),
   NATIONAL             VARCHAR2(20),
   EDUCATION            VARCHAR2(50),
   JOB                  VARCHAR2(50),
   NATIVE_PLACE         VARCHAR2(50),
   POSITION             VARCHAR2(52),
   TITLE                VARCHAR2(50),
   RANK                 VARCHAR2(50),
   JOIN_TIME            VARCHAR2(50),
   WORK_TIME            VARCHAR2(50),
   BU_RANK              VARCHAR2(50),
   EXPERIENCE           VARCHAR2(2000),
   ACTIONS              VARCHAR2(2000),
   REWARDS              VARCHAR2(2000),
   RESUME               VARCHAR2(2000),
   SORT                 NUMBER,
   SORT_NO              NUMBER,
   REMARKS              VARCHAR2(2000),
   CREATE_USER          VARCHAR2(50),
   CREATE_DEPT          VARCHAR2(50),
   UPDATE_USER          VARCHAR2(50),
   UPDATE_TIME          VARCHAR2(50),
   UPDATE_DEPT          VARCHAR2(50),
   CREATE_TIME          VARCHAR2(50),
   DELETE_USER          VARCHAR2(50),
   DELETE_TIME          VARCHAR2(50),
   DELETE_DEPT          VARCHAR2(50),
   DELETED              VARCHAR2(2),
   constraint PK_T_SLXF_COMBAT_PERSONNEL primary key (ID)
);

comment on table T_SLXF_COMBAT_PERSONNEL is
'作训处-人员编成';

comment on column T_SLXF_COMBAT_PERSONNEL.ID is
'ID';

comment on column T_SLXF_COMBAT_PERSONNEL.NAME is
'姓名';

comment on column T_SLXF_COMBAT_PERSONNEL.SEX is
'性别';

comment on column T_SLXF_COMBAT_PERSONNEL.BIRTH is
'出生年月';

comment on column T_SLXF_COMBAT_PERSONNEL.DEPT is
'单位';

comment on column T_SLXF_COMBAT_PERSONNEL.PHONE is
'联系方式';

comment on column T_SLXF_COMBAT_PERSONNEL.PICTURE_ID is
'图片id';

comment on column T_SLXF_COMBAT_PERSONNEL.NATIONAL is
'民族';

comment on column T_SLXF_COMBAT_PERSONNEL.EDUCATION is
'文化程度';

comment on column T_SLXF_COMBAT_PERSONNEL.JOB is
'在岗情况';

comment on column T_SLXF_COMBAT_PERSONNEL.NATIVE_PLACE is
'籍贯';

comment on column T_SLXF_COMBAT_PERSONNEL.POSITION is
'职务';

comment on column T_SLXF_COMBAT_PERSONNEL.TITLE is
'消防衔/年月';

comment on column T_SLXF_COMBAT_PERSONNEL.RANK is
'职级/年月';

comment on column T_SLXF_COMBAT_PERSONNEL.JOIN_TIME is
'入党(团)时间';

comment on column T_SLXF_COMBAT_PERSONNEL.WORK_TIME is
'工作时间';

comment on column T_SLXF_COMBAT_PERSONNEL.BU_RANK is
'现部职别';

comment on column T_SLXF_COMBAT_PERSONNEL.EXPERIENCE is
'培训经历';

comment on column T_SLXF_COMBAT_PERSONNEL.ACTIONS is
'参加重大救援行动情况';

comment on column T_SLXF_COMBAT_PERSONNEL.REWARDS is
'奖惩情况';

comment on column T_SLXF_COMBAT_PERSONNEL.RESUME is
'简历';

comment on column T_SLXF_COMBAT_PERSONNEL.SORT is
'人员排序';

comment on column T_SLXF_COMBAT_PERSONNEL.SORT_NO is
'机构排序号';

comment on column T_SLXF_COMBAT_PERSONNEL.REMARKS is
'备注';

comment on column T_SLXF_COMBAT_PERSONNEL.CREATE_USER is
'CREATE_USER';

comment on column T_SLXF_COMBAT_PERSONNEL.CREATE_DEPT is
'CREATE_DEPT';

comment on column T_SLXF_COMBAT_PERSONNEL.UPDATE_USER is
'UPDATE_USER';

comment on column T_SLXF_COMBAT_PERSONNEL.UPDATE_TIME is
'UPDATE_TIME';

comment on column T_SLXF_COMBAT_PERSONNEL.UPDATE_DEPT is
'UPDATE_DEPT';

comment on column T_SLXF_COMBAT_PERSONNEL.CREATE_TIME is
'CREATE_TIME';

comment on column T_SLXF_COMBAT_PERSONNEL.DELETE_USER is
'DELETE_USER';

comment on column T_SLXF_COMBAT_PERSONNEL.DELETE_TIME is
'DELETE_TIME';

comment on column T_SLXF_COMBAT_PERSONNEL.DELETE_DEPT is
'DELETE_DEPT';

comment on column T_SLXF_COMBAT_PERSONNEL.DELETED is
'DELETED';


/*==============================================================*/
/* Table: T_SLXF_COMBAT_TRAINING                                */
/*==============================================================*/
create table T_SLXF_COMBAT_TRAINING
(
   ID                   VARCHAR2(32)         not null,
   NAME                 VARCHAR2(50),
   DEPT                 VARCHAR2(50),
   PHONE                VARCHAR2(50),
   BU_RANK              VARCHAR2(50),
   TRAIN_YEAR           VARCHAR2(50),
   TRAIN_TERMIMAL       VARCHAR2(50),
   GRADE                VARCHAR2(50),
   SORT_NO              NUMBER,
   HOURS                VARCHAR2(20),
   CREATE_USER          VARCHAR2(50),
   CREATE_DEPT          VARCHAR2(50),
   UPDATE_USER          VARCHAR2(50),
   UPDATE_TIME          VARCHAR2(50),
   UPDATE_DEPT          VARCHAR2(50),
   CREATE_TIME          VARCHAR2(50),
   DELETE_USER          VARCHAR2(50),
   DELETE_TIME          VARCHAR2(50),
   DELETE_DEPT          VARCHAR2(50),
   DELETED              VARCHAR2(2),
   constraint PK_T_SLXF_COMBAT_TRAINING primary key (ID)
);

comment on table T_SLXF_COMBAT_TRAINING is
'作训处-空中观察指挥培训人员';

comment on column T_SLXF_COMBAT_TRAINING.ID is
'ID';

comment on column T_SLXF_COMBAT_TRAINING.NAME is
'姓名';

comment on column T_SLXF_COMBAT_TRAINING.DEPT is
'单位';

comment on column T_SLXF_COMBAT_TRAINING.PHONE is
'联系方式';

comment on column T_SLXF_COMBAT_TRAINING.BU_RANK is
'部职别';

comment on column T_SLXF_COMBAT_TRAINING.TRAIN_YEAR is
'培训年份';

comment on column T_SLXF_COMBAT_TRAINING.TRAIN_TERMIMAL is
'培训航站';

comment on column T_SLXF_COMBAT_TRAINING.GRADE is
'观察员等级';

comment on column T_SLXF_COMBAT_TRAINING.SORT_NO is
'机构排序号';

comment on column T_SLXF_COMBAT_TRAINING.HOURS is
'空中观察小时';

comment on column T_SLXF_COMBAT_TRAINING.CREATE_USER is
'CREATE_USER';

comment on column T_SLXF_COMBAT_TRAINING.CREATE_DEPT is
'CREATE_DEPT';

comment on column T_SLXF_COMBAT_TRAINING.UPDATE_USER is
'UPDATE_USER';

comment on column T_SLXF_COMBAT_TRAINING.UPDATE_TIME is
'UPDATE_TIME';

comment on column T_SLXF_COMBAT_TRAINING.UPDATE_DEPT is
'UPDATE_DEPT';

comment on column T_SLXF_COMBAT_TRAINING.CREATE_TIME is
'CREATE_TIME';

comment on column T_SLXF_COMBAT_TRAINING.DELETE_USER is
'DELETE_USER';

comment on column T_SLXF_COMBAT_TRAINING.DELETE_TIME is
'DELETE_TIME';

comment on column T_SLXF_COMBAT_TRAINING.DELETE_DEPT is
'DELETE_DEPT';

comment on column T_SLXF_COMBAT_TRAINING.DELETED is
'DELETED';

作训处后端

一. 数据库设计

1. 数据库设计中遇到的问题

2. 不登录如何测接口

3. 契约平台测试API

二. 后端常用代码

1. 逻辑删除方法

2. 业务与图片关联

2.1 Service层保存/详情操作

a. 保存时与图片关联

b. 查询详情时拼接图片url返回

2.2 对附件表的操作

a. 将业务与附件做关联

b. 删除业务与附件的关联

c. 根据业务id获取业务附件

3. 人员编成查在职/借调人数和总人数SQL

3.1 Service

3.2 Repository层SQL

4. 人员排序的需求实现sql

4.1 Service层

4.2 Repository层SQL

5. 查询机构数接口

5.1 Service层TreeNodeUtils使用

5.2 Repository层查询所有机构

6. 作训处权限判断

6.1 需求

6.2 添加SQL

6.3 编写接口返回权限信息

三. 后端完整代码

1. 优秀教员

2. 防火灭火专家(有图片)

3. 评判员

4. 人员编成(树+图+升降序+人数展示)

5. 空中观察指挥培训人员

四. 数据库脚本即建表

1. 码表新增的数据

2. 建表语句

猜你喜欢

热点阅读