第一版已上线,开始丰富项目功能,给专业表写了几个基础接口
总结:
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
@Data
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = false)
@Builder
@ApiModel(value = "SpecialitySaveDTO", description = "专业信息实体")
public class SpecialitySaveDTO implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "专业名名称")
@NotEmpty(message = "专业名不能为空")
@Size(max = 255, message = "专业名称长度不能超过255")
protected String specialityName;
@ApiModelProperty(value = "专业描述")
@Size(max = 255, message = "专业描述长度不能超过255")
protected String description;
}
用于删除、修改、查询时的入参类:SpecialityUpdateDTO
@Data
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = false)
@Builder
@ApiModel(value = "SpecialityUpdateDTO", description = "专业信息实体")
public class SpecialityUpdateDTO implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "主键")
@NotNull(message = "请填写主键", groups = SuperEntity.Update.class)
private Long id;
@ApiModelProperty(value = "专业名名称")
@Size(max = 255, message = "专业名称长度不能超过255")
protected String specialityName;
@ApiModelProperty(value = "专业描述")
@Size(max = 255, message = "专业描述长度不能超过255")
protected String description;
}
控制层
@ApiOperation(value = "新增专业", notes = "新增专业")
@PostMapping("/addSpeciality")
@SysLog("新增专业")
public R<String> addSpeciality(@RequestBody SpecialitySaveDTO saveDTO) {
return baseService.addSpeciality(saveDTO);
}
@ApiOperation(value = "删除专业", notes = "删除专业")
@PostMapping("/deleteSpeciality")
@SysLog("删除专业")
public R<String> deleteSpeciality(@RequestBody SpecialityUpdateDTO updateDTO) {
return baseService.deleteSpeciality(updateDTO);
}
@ApiOperation(value = "修改专业", notes = "修改专业")
@PostMapping("/updateSpeciality")
@SysLog("修改专业")
public R<String> updateSpeciality(@RequestBody SpecialityUpdateDTO updateDTO) {
return baseService.updateSpeciality(updateDTO);
}
@ApiOperation(value = "根据id查对应专业", notes = "根据id查对应专业")
@PostMapping("/selectSpecialityById")
@SysLog("根据id查对应专业")
public R<TeamSpeciality> selectSpecialityById(@RequestBody SpecialityUpdateDTO updateDTO) {
TeamSpeciality teamSpeciality = teamSpecialityService.getById(updateDTO.getId());
CommonUtil.notNull(teamSpeciality, "该专业不存在,请联系管理员!");
return R.success(teamSpeciality);
}
@ApiOperation(value = "查专业(有专业名则模糊查,无则全查)", notes = "查专业(有专业名则模糊查,无则全查)")
@PostMapping("/selectAllSpecialityOrList")
@SysLog("查专业(有专业名则模糊查,无则全查)")
public R<List<TeamSpeciality>> selectSpecialityListOrList(@RequestBody SpecialityUpdateDTO updateDTO) {
List<TeamSpeciality> teamSpecialities;
if (null != updateDTO.getSpecialityName() && !updateDTO.getSpecialityName().isEmpty()) {
teamSpecialities = teamSpecialityService.lambdaQuery()
.like(TeamSpeciality::getSpecialityName, updateDTO.getSpecialityName())
.eq(TeamSpeciality::getStates, VALID_STATUS).list();
} else {
teamSpecialities = teamSpecialityService.lambdaQuery().eq(TeamSpeciality::getStates, VALID_STATUS).list();
}
return R.success(teamSpecialities);
}
Service (三个查询的接口都在控制层使用lambdaQuery查询,直接返回即可)
* 新增专业
*/
R<String> addSpeciality(SpecialitySaveDTO saveDTO);
/**
* 修改专业
*/
R<String> updateSpeciality(SpecialityUpdateDTO updateDTO);
/**
* 删除专业
*/
R<String> deleteSpeciality(SpecialityUpdateDTO updateDTO);
ServiceImpl
@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);
}
@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);
}
@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&&!teamList.isEmpty()) {
msg = "该专业在班组详情中尚有关联信息,不可删除!";
log.error(msg);
return R.fail(msg);
}
if (!teamSpecialityService.removeById(id)) {
msg = "删除失败,请稍后重试!";
log.error(msg);
return R.fail(msg);
}
return R.success("删除专业信息成功!");
}