使用JDBC的方式
- 获取数据库连接;
- 获取数据库中所有的表名;
- 执行sql删除数据
package com.ttsx.demo.test;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class SqlTest2 {
public static List<String> getTableNameByCon(Connection con) throws SQLException {
DatabaseMetaData meta = con.getMetaData();
ResultSet rs = meta.getTables(null, null, null,
new String[]{"TABLE"});
List<String> strings = new ArrayList<>();
while (rs.next()) {
strings.add(rs.getString(3));
}
return strings;
}
private static long queryCount(Connection conn,String tableName) throws SQLException {
String sql = "SELECT COUNT (*) as cnt FROM ? ";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, tableName);
ResultSet rs = pstmt.executeQuery(sql);
while (rs.next()) {
long count = rs.getInt("cnt");
return count;
}
return 0L;
}
public static void main(String[] args) {
String driverClassName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "root";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Long totalDeleted =0L;
Long expiredCount=0L;
try {
Class.forName(driverClassName);
con = DriverManager.getConnection(url, username, password);
List<String> tableNameByCon = getTableNameByCon(con);
long begin = new Date().getTime();
for (String s : tableNameByCon) {
String sql = "delete from "+s+" limit 100000";
pstmt = con.prepareStatement(sql);
do {
int result = pstmt.executeUpdate();
totalDeleted +=result;
expiredCount = queryCount(con,s);
} while (expiredCount > 0);
}
long end = new Date().getTime();
System.out.println("删除完成,一共删除数据:"+totalDeleted+"条,"+"一共耗时:"+(end - begin) / 1000 + "秒");
} catch (Exception e) {
e.printStackTrace();
System.out.println("删除失败");
}finally {
try {
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
if(con != null) con.close();
} catch (Exception e) {
}
}
}
}