删除程序一:删除id=1的记录
package com.hy.multidelete; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import org.apache.log4j.Logger; public class Deleter { private static Logger log = Logger.getLogger(Deleter.class); public void doDelete() { Connection conn = null; Statement stmt = null; try{ Class.forName(DBParam.Driver).newInstance(); conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd); conn.setAutoCommit(false); stmt = conn.createStatement(); String sql="delete from TestTB17 where id=1"; int deleted=stmt.executeUpdate(sql); // 在此处停住断点,另一个删除程序CleanExpiredMocker会执行不下去 log.info("Deleter deleted "+deleted+" records."); // 直到接下来回滚或提交CleanExpiredMocker才可以执行 conn.rollback(); log.info("Rollbacked."); } catch (Exception e) { System.out.print(e.getMessage()); } finally { try { stmt.close(); conn.close(); } catch (SQLException e) { System.out.print("Can't close stmt/conn because of " + e.getMessage()); } } } public static void main(String[] args) { Deleter d=new Deleter(); d.doDelete(); } }
删除程序二:删除id>250的记录,由于与第一个程序不存在交集,因此不会受行锁的影响,无论第一个程序是否提交回滚都会正常运行。
package com.hy.multidelete; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import org.apache.log4j.Logger; public class CleanExpiredMocker { private static Logger log = Logger.getLogger(CleanExpiredMocker.class); public void doClean() { Connection conn = null; Statement stmt = null; try{ Class.forName(DBParam.Driver).newInstance(); conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd); stmt = conn.createStatement(); String sql="delete from TestTB17 where id>250"; int deleted=stmt.executeUpdate(sql); log.info("CleanExpiredMocker deleted "+deleted+" records.");; } catch (Exception e) { System.out.print(e.getMessage()); } finally { try { stmt.close(); conn.close(); } catch (SQLException e) { System.out.print("Can't close stmt/conn because of " + e.getMessage()); } } } public static void main(String[] args) { CleanExpiredMocker c=new CleanExpiredMocker(); c.doClean(); } }
完整程序下载(拿下去请自行修改sql语句): https://files.cnblogs.com/files/xiandedanteng/multidelete20191128.rar
--END-- 2019年11月28日22:13:22