Springboot整合JDBC批量删除修改

最新推荐文章于 2025-05-27 08:00:00 发布

原创最新推荐文章于 2025-05-27 08:00:00 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#批量删除

Java进阶专栏收录该内容

8 篇文章

订阅专栏

本文介绍如何使用Spring框架的JdbcTemplate和BatchPreparedStatementSetter进行批量数据操作，包括插入、更新和删除，通过具体示例展示了批量操作的实现方式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

repository类

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class EquimentModifyBatch {

@Autowired
private JdbcTemplate jdbcTemplate;

/**
* 批量添加数据
*/
public int batchCreateArticle(final List<Article> articles) {
String sql = "INSERT INTO article(title, description) VALUES(?, ?)";
// spring jdbc 帮我们生成了批量插入的 sql 语句, 我们也可以直接使用批量的插入 sql 语句进行批量数据插入
// return jdbcTemplate.batchUpdate(new String[]{sql});
return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
Article article = articles.get(i);
preparedStatement.setString(1, article.getTitle());
preparedStatement.setString(2, article.getDescription());
}

@Override
public int getBatchSize() {
return articles.size();
}
}).length;
}

   /**
   * 批量修改数据
   */
   public int batchModfiyEquipment(final List<Equipment> equipments) {
       if (equipments == null || equipments.size() == 0) {
           return 0;
       }
       String sql = "UPDATE equipment SET status = ? WHERE equ_id = ?";
       // spring jdbc 帮我们生成了批量插入的 sql 语句, 我们也可以直接使用批量的插入 sql 语句进行批量数据插入
       return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
           @Override
           public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
               Equipment equipment = equipments.get(i);
               preparedStatement.setInt(1, equipment.getStatus());
               preparedStatement.setString(2, equipment.getEquId());
           }

           @Override
           public int getBatchSize() {
               return equipments.size();
           }
       }).length;
   }

/**
* 批量删除数据
*/

public int batchDeleteArticle(final List<Integer> ids) {
String sql = "DELETE FROM article WHERE id = ?";
// spring jdbc 帮我们生成了批量插入的 sql 语句, 我们也可以直接使用批量的插入 sql 语句进行批量数据插入
return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
preparedStatement.setInt(1, ids.get(i));
}

@Override
public int getBatchSize() {
return ids.size();
}
}).length;

}

service层

equipments = equipmentService.getEquiByRedis();// 从缓存中获取所有设备
       // 循环处理每一台设备
       for (Equipment equipment : equipments) {
           String equId = equipment.getEquId();
           int equStatus = UnionPayHeartbeatUtil.getEquStatus(equId, configService.getEquipmentHeartbeatTimeout(),
                   client);
           EquipmentOffline equipmentOffline = get(equId, listNotRecovered);
           if (-1 == equStatus) {
               if (null == equipmentOffline) {
                   equipmentOffline = equipmentOfflineService.offline(equId, "", "");
               } else {
                   if (equipment.getStatus() != equStatus) {
                       equipment.setStatus(-1);
                       if (equipment.getUploadStatus() != -1) {
                           equipment.setUploadStatus(-1);
                       }
                       equipmentModifyList.add(equipment);
                   }
               }
           }
       }