优化循环操作数据库批量处理数据mybatis plus

优化循环操作数据库批量处理数据mybatis plus

大家好我是小星，今天在技术交流群里看见有人在循环中修改数据库，觉得不太好 在此总结几种批量修改数据库的方法

未优化前代码：

for(int i=0;i <pojolist.size();i++){
    Pojo pojo = pojolist.get(i);
    pojo.setAge(1);
    mapper.updateById(pojolist);
}

批量处理方法

1. 使用mybatis plus批量处理（数据量较小推荐使用）

   for (Pojo pojo : pojolist) {
        application.setIsDeleted(1);
   }
           
   // 执行批量更新
   pojoService.updateBatchById(pojolist);
   

2. 使用LambdaUpdateWrapper进行批量修改（适合大数据量）

   pojoService.update(Wrappers.lambdaUpdate()
                       .in(Pojo::getIdpojolist)
                       .set(Pojo::setAge, 18));
   

3. 使用jdbc批量更新

   public class UserBatchUpdate {
   
       private static final String URL = "jdbc:mysql://localhost:3306/yourdatabase";
       private static final String USER = "yourusername";
       private static final String PASSWORD = "yourpassword";
       public void batchUpdateUsers(List<User> users) throws SQLException {
           String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
           try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
                PreparedStatement pstmt = conn.prepareStatement(sql)) {
               for (User user : users) {
                   pstmt.setString(1, user.getName());
                   pstmt.setString(2, user.getEmail());
                   pstmt.setInt(3, user.getId());
                   pstmt.addBatch();
               }
               pstmt.executeBatch();
           }
       }
   }
   

4. 使用Hibernate批量更新

   public class UserBatchUpdate {
   
       private static SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
   
       public void batchUpdateUsers(List<User> users) {
           Session session = sessionFactory.openSession();
           Transaction transaction = session.beginTransaction();
           try {
               int batchSize = 50;
               for (int i = 0; i < users.size(); i++) {
                   User user = users.get(i);
                   session.update(user);
   
                   if (i % batchSize == 0 && i > 0) {
                       session.flush();
                       session.clear();
                   }
               }
               transaction.commit();
           } catch (Exception e) {
               if (transaction != null) {
                   transaction.rollback();
               }
               throw e;
           } finally {
               session.close();
           }
       }
   }