专业表操作鉴权增、删、改、id查、全查、名字模糊查

第一版已上线，开始丰富项目功能，给专业表写了几个基础接口

总结：

1、一般这种对数据库直接操作的接口尽量对用户权限以角色进行约束,本项目和负责人对接后，只有当前用户有超级管理员角色才能对专业表操作

2、新增时，要拿入参专业名查重

3、修改、删除、查询时，最好对入参id进行合法性判断，防穿透（非空非负）

4、修改、删除时，要拿入参id查一下这条数据是否存在于数据库

5、修改时，还要拿入参专业名查重；专业名和已存在的所有班组比较，不能重复；注意：这里比较之前先剔除自己，因为有可能只是改本专业的描述，是不修改专业名的，所以可以传当前id对应的的专业名

6、id查、全查、专业名模糊查都可以在控制层中返回，直接用lambdaQuery操作即可

7、最重要的一点：这么简单的接口在一开始的时候，无论怎么调试，都进不了数据库，一开始以为是新增的字段属性问题，一直在调试实体类、DTO、专业表的属性字段和包装类型，但是一直报这个错：

 "errorMsg": "\r\n

### Error updating database.  Cause: java.sql.SQLException: Connection is read-only. 

Queries leading to data modification are not allowed\r\n### The error may exist in cn/esky/backend/authority/dao/team/TeamSpecialityMapper.java (best guess)\r\n### The error may involve cn.esky.backend.authority.dao.team.TeamSpecialityMapper.insert-Inline\r\n### The error occurred while setting parameters\r\n### SQL: INSERT INTO c_speciality  ( id, speciality_name, description_,  update_time, updated_by, create_time, created_by )  VALUES  ( ?, ?, ?,  ?, ?, ?, ? )\r\n### Cause: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed\n; Connection is read-only. Queries leading to data modification are not allowed; nested exception is java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed",

其实从###后面就已经提示的很明显了，只读

在查资料后在serviceImpl里每一个方法开头都加上：

@Transactional(rollbackFor = Exception.class)

即可解决

新增专业addSpeciality

@Override
@Transactional(rollbackFor = Exception.class)
public R<String> addSpeciality(SpecialitySaveDTO saveDTO) {
    String msg;
    Long userId = ContextUtil.getUserId();
    //userId=1452475321122029568L;

    List<Role> roleList = roleService.findRoleByUserId(userId);
    当前用户需要在<用户_角色表>配置角色
    if (null == roleList || roleList.isEmpty()) {
        msg = "通过用户id查找所属角色信息失败,userId:"   userId;
        log.error(msg);
        return R.fail(msg);
    }
    Set<String> roleCodeSet = new HashSet<>();
    roleList.forEach(role -> roleCodeSet.add(role.getCode()));
    判断当前用户是不是内置管理员
    if (!roleCodeSet.contains(SUPER_ADMIN)) {
        msg = "当前用户非内置管理员，无权进行新增专业操作";
        log.error(msg);
        return R.fail(msg);
    }

    String specialityName = saveDTO.getSpecialityName();

    专业名不能为空
    if(null==specialityName||specialityName.isEmpty()){
        msg = "专业名不能为空！";
        log.error(msg);
        return R.fail(msg);
    }

    专业名不能和现有的重复
    List<TeamSpeciality> specialityList = teamSpecialityService.lambdaQuery().eq(TeamSpeciality::getSpecialityName, specialityName).list();
    if (specialityList.size() > 0) {
        msg = "此专业名已存在，请重新输入！";
        log.error(msg);
        return R.fail(msg);
    }

    TeamSpeciality teamSpeciality = new TeamSpeciality();

    BeanUtils.copyProperties(saveDTO, teamSpeciality);
    状态设置为可用
    teamSpeciality.setStates(VALID_STATUS);

    if (!teamSpecialityService.save(teamSpeciality)) {
        msg = "新增失败，请稍后重试！";
        log.error(msg);
        return R.fail(msg);
    }
    msg = "新增专业成功！";
    return R.success(msg);
    
}

修改专业updateSpeciality

@Override
@Transactional(rollbackFor = Exception.class)
public R<String> updateSpeciality(SpecialityUpdateDTO updateDTO) {
    String msg;
    Long userId = ContextUtil.getUserId();
    //模拟管理员操作
    //userId=1452475321122029568L;
    List<Role> roleList = roleService.findRoleByUserId(userId);
    判断当前用户是不是内置管理员
    if (null == roleList || roleList.isEmpty()) {
        msg = "通过用户id查找所属角色信息失败,userId:"   userId;
        log.error(msg);
        return R.fail(msg);
    }
    Set<String> roleCodeSet = new HashSet<>();
    roleList.forEach(role -> roleCodeSet.add(role.getCode()));
    判断当前用户是不是内置管理员
    if (!roleCodeSet.contains(SUPER_ADMIN)) {
        msg = "当前用户非内置管理员，无权进行新增专业操作";
        log.error(msg);
        return R.fail(msg);
    }

    Long id = updateDTO.getId();
    String specialityName = updateDTO.getSpecialityName();
    先判断id合法性 防穿透
    if (null == id || id < 0) {
        msg = "请输入有效的id！";
        log.error(msg);
        return R.fail(msg);
    }
    有效性验证
    TeamSpeciality oldSpeciality = teamSpecialityService.getById(id);
    if (null == oldSpeciality) {
        msg = "请输入有效的id！";
        log.error(msg);
        return R.fail(msg);
    }

    专业名不能为空
    if(null==specialityName||specialityName.isEmpty()){
        msg = "专业名不能为空！";
        log.error(msg);
        return R.fail(msg);
    }

    专业名不能重复(除自己以外，因为有可能只是改自己专业的描述)
    List<TeamSpeciality> specialityList = teamSpecialityService.lambdaQuery().eq(TeamSpeciality::getStates, VALID_STATUS).list();

    Iterator<TeamSpeciality> it = specialityList.iterator();
    while (it.hasNext()) {
        TeamSpeciality eachSpeciality = it.next();
        if (oldSpeciality.getId() == eachSpeciality.getId()) {
            it.remove();
        }
    }

    for (int i = 0; i < specialityList.size(); i  ) {
        if (specialityName.equals(specialityList.get(i).getSpecialityName())) {
            msg = "此专业名已存在，请重新输入！";
            log.error(msg);
            return R.fail(msg);
        }
    }

    BeanUtils.copyProperties(updateDTO, oldSpeciality);
    if (!teamSpecialityService.updateById(oldSpeciality)) {
        msg = "修改失败，请稍后重试！";
        log.error(msg);
        return R.fail(msg);
    }

    msg = "修改专业成功！";
    return R.success(msg);

}

删除专业deleteSpeciality

@Override
@Transactional(rollbackFor = Exception.class)
public R<String> deleteSpeciality(SpecialityUpdateDTO updateDTO) {
    String msg;
    Long userId = ContextUtil.getUserId();
    //模拟管理员操作
    //userId=1452475321122029568L;
    List<Role> roleList = roleService.findRoleByUserId(userId);
    当前用户需要在<用户_角色表>配置角色
    if (null == roleList || roleList.isEmpty()) {
        msg = "通过用户id查找所属角色信息失败,userId:"   userId;
        log.error(msg);
        return R.fail(msg);
    }
    Set<String> roleCodeSet = new HashSet<>();
    roleList.forEach(role -> roleCodeSet.add(role.getCode()));
    判断当前用户是不是内置管理员
    if (!roleCodeSet.contains(SUPER_ADMIN)) {
        msg = "当前用户非内置管理员，无权进行新增专业操作";
        log.error(msg);
        return R.fail(msg);
    }

    Long id = updateDTO.getId();
    id合法性 穿透
    if (null == id || id < 0) {
        msg = "请输入有效的id！";
        log.error(msg);
        return R.fail(msg);
    }
    有效性验证
    TeamSpeciality oldSpeciality = teamSpecialityService.getById(id);
    if (null == oldSpeciality) {
        msg = "请输入有效的id！";
        log.error(msg);
        return R.fail(msg);
    }

     如果还有其他关联信息，就不能删除
    List<Team> teamList = teamService.lambdaQuery().eq(Team::getSpecialityId, id).list();
    if (null != teamList) {
        msg = "该专业在班组详情中尚有关联信息，不可删除！";
        log.error(msg);
        return R.fail(msg);
    }


    if (!teamSpecialityService.removeById(id)) {
        msg = "删除失败，请稍后重试！";
        log.error(msg);
        return R.fail(msg);
    }

    return R.success("删除专业信息成功！");


}

用于新增的入参类：SaveDTO

1.  
   @Data
2.  
   @NoArgsConstructor
3.  
   @AllArgsConstructor
4.  
   @Accessors(chain = true)
5.  
   @ToString(callSuper = true)
6.  
   @EqualsAndHashCode(callSuper = false)
7.  
   @Builder
8.  
   @ApiModel(value = "SpecialitySaveDTO", description = "专业信息实体")
9.  
   public class SpecialitySaveDTO implements Serializable {
10.  
    private static final long serialVersionUID = 1L;
11.  
     
12.  
    @ApiModelProperty(value = "专业名名称")
13.  
    @NotEmpty(message = "专业名不能为空")
14.  
    @Size(max = 255, message = "专业名称长度不能超过255")
15.  
    protected String specialityName;
16.  
     
17.  
     
18.  
    @ApiModelProperty(value = "专业描述")
19.  
    @Size(max = 255, message = "专业描述长度不能超过255")
20.  
    protected String description;
21.  
     
22.  
     
23.  
     
24.  
    }

用于删除、修改、查询时的入参类：SpecialityUpdateDTO

1.  
   @Data
2.  
   @NoArgsConstructor
3.  
   @AllArgsConstructor
4.  
   @Accessors(chain = true)
5.  
   @ToString(callSuper = true)
6.  
   @EqualsAndHashCode(callSuper = false)
7.  
   @Builder
8.  
   @ApiModel(value = "SpecialityUpdateDTO", description = "专业信息实体")
9.  
   public class SpecialityUpdateDTO implements Serializable {
10.  
    private static final long serialVersionUID = 1L;
11.  
     
12.  
     
13.  
    @ApiModelProperty(value = "主键")
14.  
    @NotNull(message = "请填写主键", groups = SuperEntity.Update.class)
15.  
    private Long id;
16.  
     
17.  
    @ApiModelProperty(value = "专业名名称")
18.  
    @Size(max = 255, message = "专业名称长度不能超过255")
19.  
    protected String specialityName;
20.  
     
21.  
     
22.  
    @ApiModelProperty(value = "专业描述")
23.  
    @Size(max = 255, message = "专业描述长度不能超过255")
24.  
    protected String description;
25.  
     
26.  
     
27.  
     
28.  
    }

控制层

1.  
   @ApiOperation(value = "新增专业", notes = "新增专业")
2.  
   @PostMapping("/addSpeciality")
3.  
   @SysLog("新增专业")
4.  
   public R<String> addSpeciality(@RequestBody SpecialitySaveDTO saveDTO) {
5.  
   return baseService.addSpeciality(saveDTO);
6.  
   }
7.  
    
8.  
   @ApiOperation(value = "删除专业", notes = "删除专业")
9.  
   @PostMapping("/deleteSpeciality")
10.  
    @SysLog("删除专业")
11.  
    public R<String> deleteSpeciality(@RequestBody SpecialityUpdateDTO updateDTO) {
12.  
    return baseService.deleteSpeciality(updateDTO);
13.  
    }
14.  
     
15.  
    @ApiOperation(value = "修改专业", notes = "修改专业")
16.  
    @PostMapping("/updateSpeciality")
17.  
    @SysLog("修改专业")
18.  
    public R<String> updateSpeciality(@RequestBody SpecialityUpdateDTO updateDTO) {
19.  
    return baseService.updateSpeciality(updateDTO);
20.  
    }
21.  
     
22.  
    @ApiOperation(value = "根据id查对应专业", notes = "根据id查对应专业")
23.  
    @PostMapping("/selectSpecialityById")
24.  
    @SysLog("根据id查对应专业")
25.  
    public R<TeamSpeciality> selectSpecialityById(@RequestBody SpecialityUpdateDTO updateDTO) {
26.  
    TeamSpeciality teamSpeciality = teamSpecialityService.getById(updateDTO.getId());
27.  
    CommonUtil.notNull(teamSpeciality, "该专业不存在,请联系管理员!");
28.  
    return R.success(teamSpeciality);
29.  
    }
30.  
     
31.  
    @ApiOperation(value = "查专业(有专业名则模糊查，无则全查)", notes = "查专业(有专业名则模糊查，无则全查)")
32.  
    @PostMapping("/selectAllSpecialityOrList")
33.  
    @SysLog("查专业(有专业名则模糊查，无则全查)")
34.  
    public R<List<TeamSpeciality>> selectSpecialityListOrList(@RequestBody SpecialityUpdateDTO updateDTO) {
35.  
    List<TeamSpeciality> teamSpecialities;
36.  
    if (null != updateDTO.getSpecialityName() && !updateDTO.getSpecialityName().isEmpty()) {
37.  
    teamSpecialities = teamSpecialityService.lambdaQuery()
38.  
    .like(TeamSpeciality::getSpecialityName, updateDTO.getSpecialityName())
39.  
    .eq(TeamSpeciality::getStates, VALID_STATUS).list();
40.  
    } else {
41.  
    teamSpecialities = teamSpecialityService.lambdaQuery().eq(TeamSpeciality::getStates, VALID_STATUS).list();
42.  
    }
43.  
    return R.success(teamSpecialities);
44.  
    }

Service (三个查询的接口都在控制层使用lambdaQuery查询，直接返回即可)

1.  
   * 新增专业
2.  
   */
3.  
   R<String> addSpeciality(SpecialitySaveDTO saveDTO);
4.  
    
5.  
   /**
6.  
   * 修改专业
7.  
   */
8.  
   R<String> updateSpeciality(SpecialityUpdateDTO updateDTO);
9.  
    
10.  
    /**
11.  
    * 删除专业
12.  
    */
13.  
    R<String> deleteSpeciality(SpecialityUpdateDTO updateDTO);

ServiceImpl

1.  
   @Override
2.  
   @Transactional(rollbackFor = Exception.class)
3.  
   public R<String> addSpeciality(SpecialitySaveDTO saveDTO) {
4.  
   String msg;
5.  
   Long userId = ContextUtil.getUserId();
6.  
   //userId=1452475321122029568L;
7.  
   List<Role> roleList = roleService.findRoleByUserId(userId);
8.  
   if (null == roleList || roleList.isEmpty()) {
9.  
   msg = "通过用户id查找所属角色信息失败,userId:" userId;
10.  
    log.error(msg);
11.  
    return R.fail(msg);
12.  
    }
13.  
    Set<String> roleCodeSet = new HashSet<>();
14.  
    roleList.forEach(role -> roleCodeSet.add(role.getCode()));
15.  
    //判断当前用户是不是内置管理员
16.  
    if (!roleCodeSet.contains(SUPER_ADMIN)) {
17.  
    msg = "当前用户非内置管理员，无权进行新增专业操作";
18.  
    log.error(msg);
19.  
    return R.fail(msg);
20.  
    }
21.  
     
22.  
    String specialityName = saveDTO.getSpecialityName();
23.  
     
24.  
    //专业名不能为空
25.  
    if(null==specialityName||specialityName.isEmpty()){
26.  
    msg = "专业名不能为空！";
27.  
    log.error(msg);
28.  
    return R.fail(msg);
29.  
    }
30.  
     
31.  
    //专业名不能重复
32.  
    List<TeamSpeciality> specialityList = teamSpecialityService.lambdaQuery().eq(TeamSpeciality::getSpecialityName, specialityName).list();
33.  
    if (specialityList.size() > 0) {
34.  
    msg = "此专业名已存在，请重新输入！";
35.  
    log.error(msg);
36.  
    return R.fail(msg);
37.  
    }
38.  
     
39.  
    TeamSpeciality teamSpeciality = new TeamSpeciality();
40.  
     
41.  
    BeanUtils.copyProperties(saveDTO, teamSpeciality);
42.  
    teamSpeciality.setStates(VALID_STATUS);
43.  
     
44.  
    if (!teamSpecialityService.save(teamSpeciality)) {
45.  
    msg = "新增失败，请稍后重试！";
46.  
    log.error(msg);
47.  
    return R.fail(msg);
48.  
    }
49.  
    msg = "新增专业成功！";
50.  
    return R.success(msg);
51.  
     
52.  
    }
53.  
     
54.  
    @Override
55.  
    @Transactional(rollbackFor = Exception.class)
56.  
    public R<String> updateSpeciality(SpecialityUpdateDTO updateDTO) {
57.  
    String msg;
58.  
    Long userId = ContextUtil.getUserId();
59.  
    //模拟管理员操作
60.  
    //userId=1452475321122029568L;
61.  
    List<Role> roleList = roleService.findRoleByUserId(userId);
62.  
    if (null == roleList || roleList.isEmpty()) {
63.  
    msg = "通过用户id查找所属角色信息失败,userId:" userId;
64.  
    log.error(msg);
65.  
    return R.fail(msg);
66.  
    }
67.  
    Set<String> roleCodeSet = new HashSet<>();
68.  
    roleList.forEach(role -> roleCodeSet.add(role.getCode()));
69.  
    //判断当前用户是不是内置管理员
70.  
    if (!roleCodeSet.contains(SUPER_ADMIN)) {
71.  
    msg = "当前用户非内置管理员，无权进行新增专业操作";
72.  
    log.error(msg);
73.  
    return R.fail(msg);
74.  
    }
75.  
     
76.  
    Long id = updateDTO.getId();
77.  
    String specialityName = updateDTO.getSpecialityName();
78.  
    //id合法性 穿透
79.  
    if (null == id || id < 0) {
80.  
    msg = "请输入有效的id！";
81.  
    log.error(msg);
82.  
    return R.fail(msg);
83.  
    }
84.  
    //有效性验证
85.  
    TeamSpeciality oldSpeciality = teamSpecialityService.getById(id);
86.  
    if (null == oldSpeciality) {
87.  
    msg = "请输入有效的id！";
88.  
    log.error(msg);
89.  
    return R.fail(msg);
90.  
    }
91.  
     
92.  
    //专业名不能为空
93.  
    if(null==specialityName||specialityName.isEmpty()){
94.  
    msg = "专业名不能为空！";
95.  
    log.error(msg);
96.  
    return R.fail(msg);
97.  
    }
98.  
     
99.  
    //专业名不能重复(除自己以外，因为有可能只是改自己专业的描述)
100.  
     List<TeamSpeciality> specialityList = teamSpecialityService.lambdaQuery().eq(TeamSpeciality::getStates, VALID_STATUS).list();
101.  
      
102.  
     Iterator<TeamSpeciality> it = specialityList.iterator();
103.  
     while (it.hasNext()) {
104.  
     TeamSpeciality eachSpeciality = it.next();
105.  
     if (oldSpeciality.getId() == eachSpeciality.getId()) {
106.  
     it.remove();
107.  
     }
108.  
     }
109.  
      
110.  
     for (int i = 0; i < specialityList.size(); i ) {
111.  
     if (specialityName.equals(specialityList.get(i).getSpecialityName())) {
112.  
     msg = "此专业名已存在，请重新输入！";
113.  
     log.error(msg);
114.  
     return R.fail(msg);
115.  
     }
116.  
     }
117.  
      
118.  
     BeanUtils.copyProperties(updateDTO, oldSpeciality);
119.  
     if (!teamSpecialityService.updateById(oldSpeciality)) {
120.  
     msg = "修改失败，请稍后重试！";
121.  
     log.error(msg);
122.  
     return R.fail(msg);
123.  
     }
124.  
      
125.  
     msg = "修改专业成功！";
126.  
     return R.success(msg);
127.  
      
128.  
     }
129.  
      
130.  
     @Override
131.  
     @Transactional(rollbackFor = Exception.class)
132.  
     public R<String> deleteSpeciality(SpecialityUpdateDTO updateDTO) {
133.  
     String msg;
134.  
     Long userId = ContextUtil.getUserId();
135.  
     //模拟管理员操作
136.  
     //userId=1452475321122029568L;
137.  
     List<Role> roleList = roleService.findRoleByUserId(userId);
138.  
     if (null == roleList || roleList.isEmpty()) {
139.  
     msg = "通过用户id查找所属角色信息失败,userId:" userId;
140.  
     log.error(msg);
141.  
     return R.fail(msg);
142.  
     }
143.  
     Set<String> roleCodeSet = new HashSet<>();
144.  
     roleList.forEach(role -> roleCodeSet.add(role.getCode()));
145.  
     //判断当前用户是不是内置管理员
146.  
     if (!roleCodeSet.contains(SUPER_ADMIN)) {
147.  
     msg = "当前用户非内置管理员，无权进行新增专业操作";
148.  
     log.error(msg);
149.  
     return R.fail(msg);
150.  
     }
151.  
      
152.  
     Long id = updateDTO.getId();
153.  
     //id合法性 穿透
154.  
     if (null == id || id < 0) {
155.  
     msg = "请输入有效的id！";
156.  
     log.error(msg);
157.  
     return R.fail(msg);
158.  
     }
159.  
     //有效性验证
160.  
     TeamSpeciality oldSpeciality = teamSpecialityService.getById(id);
161.  
     if (null == oldSpeciality) {
162.  
     msg = "请输入有效的id！";
163.  
     log.error(msg);
164.  
     return R.fail(msg);
165.  
     }
166.  
      
167.  
     List<Team> teamList = teamService.lambdaQuery().eq(Team::getSpecialityId, id).list();
168.  
     if (null != teamList&&!teamList.isEmpty()) {
169.  
     msg = "该专业在班组详情中尚有关联信息，不可删除！";
170.  
     log.error(msg);
171.  
     return R.fail(msg);
172.  
     }
173.  
      
174.  
      
175.  
     if (!teamSpecialityService.removeById(id)) {
176.  
     msg = "删除失败，请稍后重试！";
177.  
     log.error(msg);
178.  
     return R.fail(msg);
179.  
     }
180.  
      
181.  
     return R.success("删除专业信息成功！");
182.  
      
183.  
      
184.  
     }

这篇好文章是转载于：学新通技术网