批量更新更新多个记录在春天mvc与mysql

问题描述：

我有一个问题，假设我有最初说100条记录，并且我在UI上显示它们作为用户列表。现在我已经提供了通过单击针对每条记录放置的“取消激活”按钮来停用号码用户的规定，然后我将所有“取消激活”的用户捕获到列表中并将其发送到DAO层[取消激活用户的逻辑只是将'isDeleted'标志设置为true，iesoft delete因此，它与我更新已放入列表的多个记录一样好]，有一个简单的解决方案，写一个for循环 - >遍历列表 - >和每条记录触发一个查询来更新isDeleted标志为true，但是如果我有5000条记录要一次删除，那么它不可行。我已经听到并实施了批量更新概念“插入”多个记录一次，但我不明白我怎么才能使用批量更新来更新多个记录只有一个数据库调用，请大家帮忙，插入的批量更新代码是下面，批量更新更新多个记录在春天mvc与mysql

private static final String INSERT_USER_PERMISSION = 
      "INSERT INTO permission_transaction(permissionId,userId,isDeleted) " 
      + "VALUES(?,?,?)"; 

@Transactional 
    public void addPermission(final UserVO userVo, final List<PermissionVO> permissionVoList) 
      throws Exception { 
     logger.info("Adding user permission, for userId: "+userVo.getUserId()); 

     try { 
      jdbc.batchUpdate(INSERT_USER_PERMISSION, new BatchPreparedStatementSetter() { 
       @Override 
       public void setValues(PreparedStatement ps, int i) throws SQLException { 

        PermissionVO permissionVo = permissionVoList.get(i); 
        ps.setInt(1, permissionVo.getPermissionId()); 
        ps.setInt(2, userVo.getUserId()); 
        ps.setBoolean(3, false); 
       } 
       @Override 
       public int getBatchSize() { 
        return permissionVoList.size(); 
       } 
      }); 
      logger.info("Exiting addPermission, for userId: "+userVo.getUserId()); 
     }catch (Exception e) { 
      logger.error("Error in adding user permission: " + e.getMessage(), e); 
      throw e; 
     } 

    }

答

嘿，我找到了解决办法，这是我做什么，

private static final String UPDATE_CLIENT_OWNER = 
      "UPDATE clientowner SET " 
      + "clientOwnerName=?," 
      + "clientOwnerPhone=?," 
      + "clientOwnerEmail=?," 
      + "lastUpdatedOn=NOW() " 
      + "WHERE clientOwnerId=?"; 
@Transactional 
    public void updateClientOwner(int clientId, List<ClientOwnerVO> clientOwnerVoList) throws Exception { 
     logger.info("Updating client Owner(s)"); 
     try{ 
      int[] count = jdbc.batchUpdate(UPDATE_CLIENT_OWNER, new BatchPreparedStatementSetter() { 
       @Override 
       public void setValues(PreparedStatement ps, int i) throws SQLException { 

        ClientOwnerVO clientOwnerVO = clientOwnerVoList.get(i); 
        ps.setString(1, clientOwnerVO.getClientOwnerName()); 
        ps.setString(2, VariableEncryption.encrypt(clientOwnerVO.getClientOwnerPhone(), clientOwnerVO.getCreatedOn())); 
        ps.setString(3, VariableEncryption.encrypt(clientOwnerVO.getClientOwnerEmail(), clientOwnerVO.getCreatedOn())); 
        ps.setInt(4, clientOwnerVO.getClientOwnerID()); 

       } 
       @Override 
       public int getBatchSize() { 
        return clientOwnerVoList.size(); 
       } 
      }); 
      logger.info("Exiting After successfully updating "+count.toString()+" client owners"); 


     }catch (Exception e) { 
      logger.error("Error in updating client owners: " + e.getMessage(), e); 
      throw e; 
     }

批量更新更新多个记录在春天mvc与mysql

相关推荐