一对多关系的数据库记录添加时通过中间表存储关系的具体写法

最新推荐文章于 2024-07-21 11:47:23 发布

天又热了

最新推荐文章于 2024-07-21 11:47:23 发布

阅读量705

点赞数 1

本文链接：https://blog.csdn.net/weixin_43404791/article/details/104738133

版权

案例：通过用户注册与修改为例，用户需要有配置的角色
1.用户注册
具体代码实现：

@ApiOperation(value = "用户注册",notes = "用户注册" +
            "参数包括：        @ApiImplicitParam(type = \"query\", name = \"name\",value = \"用户名\",required = true),\n" +
            "            @ApiImplicitParam(type = \"query\", name = \"password\",value = \"密码\",required = true),\n" +
            "            @ApiImplicitParam(type = \"query\", name = \"deptId\",value = \"所属方向ID\",required = true),\n" +
            "            @ApiImplicitParam(type = \"query\", name = \"grade\",value = \"年级，比如2018\",required = true),\n" +
            "            @ApiImplicitParam(type = \"query\", name = \"email\",value = \"邮箱，确保格式正确\",required = true),\n" +
            "            @ApiImplicitParam(type = \"query\", name = \"mobile\",value = \"手机，确保格式正确\",required = true),\n" +
            "            @ApiImplicitParam(type = \"query\", name = \"createBy\",value = \"创建者\",required = true),")
    @ApiImplicitParams({
            @ApiImplicitParam(type = "query", name = "name",value = "用户名",required = true),
            @ApiImplicitParam(type = "query", name = "password",value = "密码",required = true),
            @ApiImplicitParam(type = "query", name = "deptId",value = "所属方向ID",required = true),
            @ApiImplicitParam(type = "query", name = "grade",value = "年级，比如2018",required = true),
            @ApiImplicitParam(type = "query", name = "email",value = "邮箱，确保格式正确",required = true),
            @ApiImplicitParam(type = "query", name = "mobile",value = "手机，确保格式正确",required = true),
    })
    @PostMapping("/register")
    //@PreAuthorize("hasAuthority('ROLE_USER')")
    public HttpResult register(String name, String password, Long deptId, String grade, String email, String mobile, @RequestParam List<Long> roleList, MultipartFile uploadFile  ) throws FileNotFoundException {
        //MultipartFile uploadFile = null;
        SysUser sysUser = new SysUser();

        Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
        //新建暂时缓存目录,该目录一定存在
        String url = ResourceUtils.getURL("").getPath()+uploadFile.getOriginalFilename();
        System.out.println(url);
        File folder = new File(url);
        try{
            //转义文件到服务器
            uploadFile.transferTo(folder);
            //从服务器获取文件传递到阿里云OSS.返回下载链接地址
            String avator_url = OSSUtils.upload(folder,sysUser.getName()+".jpg");
            //删除服务器缓存文件
            folder.delete();
            //设置属性
            sysUser.setName(name);
            sysUser.setPassword(password);
            sysUser.setDeptId(deptId);
            sysUser.setEmail(email);
            sysUser.setMobile(mobile);
            sysUser.setGrade(grade);
            sysUser.setCreateBy(authentication.getName());
            //设置用户头像
            sysUser.setAvator(avator_url);
            //设置创建时间
            sysUser.setCreateTime(new Date());
            //设置更新时间
            sysUser.setLastUpdateTime(new Date());
            //设置创建者
            sysUser.setLastUpdateBy(sysUser.getCreateBy());
            //删除标志
            sysUser.setDelFlag((byte)0);
            //密码加密
            sysUser.setPassword(PassWordEncoderUtils.BCryptPassword(sysUser.getPassword()));
            //保存
            System.out.println("time:"+sdf.format(new Date()));
            sysUserService.save(sysUser);
            for(Long role: roleList){
                SysUserRole sysUserRole = new SysUserRole();
                sysUserRole.setUserId(sysUser.getId());
                sysUserRole.setRoleId(role);
                sysUserRole.setId(UUID.randomUUID().toString());
                sysUserRole.setCreateBy(authentication.getName());
                sysUserRole.setLastUpdateBy(authentication.getName());
                sysUserRole.setCreateTime(new Date());
                sysUserRole.setLastUpdateTime(new Date());
                sysUserRole.setDelFlag((byte)0);
                sysUserService.saveUserAndRole(sysUserRole);
            }
            return HttpResult.ok(sysUser);
        }catch (DuplicateKeyException e){
            return HttpResult.error("重复注册");
        }catch (IOException e){
            e.printStackTrace();
            return HttpResult.error("注册失败");
        }

    }

2.用户修改

   @ApiOperation(value = "用户修改",notes = "用户修改")
    @ApiImplicitParams({
            @ApiImplicitParam(type = "query", name="id",value = "用户编号",required = true),
            @ApiImplicitParam(type = "query", name = "name",value = "用户名"),
            @ApiImplicitParam(type = "query", name = "password",value = "密码"),
            @ApiImplicitParam(type = "query", name = "deptId",value = "所属方向ID"),
            @ApiImplicitParam(type = "query", name = "grade",value = "年级，比如2018"),
            @ApiImplicitParam(type = "query", name = "email",value = "邮箱，确保格式正确"),
            @ApiImplicitParam(type = "query", name = "mobile",value = "手机，确保格式正确"),
            @ApiImplicitParam(type = "query", name = "lastUpdateBy",value = "修改者"),
            @ApiImplicitParam(type = "query", name = "delFlag",value = "删除标志，-1删除，0正常")
            //@ApiImplicitParam(type = "query", name = "createTime",value = "创建时间",required = true)
    })
    @PutMapping("/update")
    public HttpResult update(Long id, String name, String password, Long deptId, String grade, String email, String mobile,
                             @RequestParam List<Long> roleList, @ApiParam(value = "uploadFile",required = false) MultipartFile uploadFile) throws IOException,NullPointerException {
        try{
            Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
            SysUser sysUser = new SysUser();
            //设置属性
            sysUser.setId(id);
            sysUser.setName(name);
            sysUser.setPassword(password);
            sysUser.setDeptId(deptId);
            sysUser.setEmail(email);
            sysUser.setMobile(mobile);
            sysUser.setGrade(grade);
            sysUser.setCreateBy(authentication.getName());
            if (uploadFile !=null){
                String url = ResourceUtils.getURL("").getPath()+uploadFile.getOriginalFilename();
                File folder = new File(url);
                uploadFile.transferTo(folder);
                String avator_url = OSSUtils.upload(folder, UUID.randomUUID().toString() +".jpg");
                folder.delete();
                sysUser.setAvator(avator_url);
            }
            sysUser.setLastUpdateTime(new Date());
            sysUserService.update(sysUser);
            sysUserService.deleteUserAndRole(sysUser.getId());
            System.out.println(sysUser.getId());
            for(Long role: roleList){
                SysUserRole sysUserRole = new SysUserRole();
                sysUserRole.setUserId(sysUser.getId());
                sysUserRole.setRoleId(role);
                sysUserRole.setId(UUID.randomUUID().toString());
                sysUserRole.setCreateBy(authentication.getName());
                sysUserRole.setLastUpdateBy(authentication.getName());
                sysUserRole.setCreateTime(new Date());
                sysUserRole.setLastUpdateTime(new Date());
                sysUserRole.setDelFlag((byte)0);
                sysUserService.saveUserAndRole(sysUserRole);
            }
            return HttpResult.ok(sysUser);
        }catch (Exception e) {
            e.printStackTrace();
            return HttpResult.error("用户修改失败");
        }
    }

3.因为涉及到大量的NULL所以数据库这样设计，也是为了分属性修改

<update id="updateByPrimaryKey" useGeneratedKeys="true" keyProperty="id" parameterType="cn.hcnet2006.blog.hcnetwebsite.bean.SysUser">
    update sys_user
    set
        <if test="name != null and name != ''">
          name = #{name,jdbcType=VARCHAR},
        </if>
        <if test="password != null and name != ''">
          password = #{password,jdbcType=VARCHAR},
        </if>
        <if test="avator != null and avator != ''">
          avator = #{avator,jdbcType=VARCHAR},
        </if>
        <if test="grade != null and avator != ''">
          grade = #{grade,jdbcType=VARCHAR},
        </if>
        <if test="email != null and email != ''">
          email = #{email,jdbcType=VARCHAR},
        </if>
        <if test="mobile != null and email != ''">
          mobile = #{mobile,jdbcType=VARCHAR},
        </if>
        <if test="deptId != null and deptId != ''">
          dept_id = #{deptId,jdbcType=BIGINT},
        </if>
        <if test="createBy != null and createBy != ''">
          create_by = #{createBy,jdbcType=VARCHAR},
        </if>
        <if test="createTime != null ">
          create_time = #{createTime,jdbcType=TIMESTAMP},
        </if>
        <if test="lastUpdateTime != null ">
          last_update_time = #{lastUpdateTime,jdbcType=TIMESTAMP},
        </if>
        <if test="lastUpdateBy != null and lastUpdateBy != ''">
          last_update_by = #{lastUpdateBy,jdbcType=VARCHAR},
        </if>
        <if test="delFlag != null and delFlag != ''">
          del_flag = #{delFlag,jdbcType=TINYINT},
        </if>
        id = #{id}
    where id = #{id,jdbcType=BIGINT}
  </update>