北京建设银行网站首页,营业执照咨询电话24小时,佛山便宜网站建设,移动端wordpress主题文章目录 概要整体架构流程技术名词解释技术细节小结 概要 对于Doris的 Unique 模型#xff0c;在删除数据的时候只能根据key删除#xff0c;如果使用其他条件就会报错 整体架构流程
先获得表的key#xff0c;然后在通过输入的条件获得key的所有值#xff0c;最后通过key的… 文章目录 概要整体架构流程技术名词解释技术细节小结 概要 对于Doris的 Unique 模型在删除数据的时候只能根据key删除如果使用其他条件就会报错 整体架构流程
先获得表的key然后在通过输入的条件获得key的所有值最后通过key的值拼接删除语句调用线程删除。
技术名词解释
jsqlparser SQL解析工具能将字符串解析出SQL的表值条件等HikariCP 数据库连接池单例模式单例模式调用数据库连接
技术细节
多线程使用线程池开启线程也可以使用分页线程时间显示时间显示使用无符号的时间格式
小结
具体实现代码如下
package org.example;import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;import static org.example.SqlParserExample.getTableName;public class Main {public static void main(String[] args) throws Exception {initLogRecord.initLog();System.out.println();Connection conn HikariConfigs.HikariConn();// Statement stmt conn.createStatement();Scanner scanner new Scanner(System.in);System.out.println(请输入删除的语句);String sql scanner.nextLine();String where ;if (sql.indexOf(where) 0) {where sql.substring(sql.indexOf(where));} else if (sql.indexOf(WHERE) 0) {where sql.substring(sql.indexOf(WHERE));}System.out.println(获取WHERE条件);System.out.println(where);String table getTableName(sql);String getKey select * from information_schema.columns where TABLE_NAME ? ;PreparedStatement ptmt_getKey conn.prepareStatement(getKey);ptmt_getKey.setString(1, table);ResultSet rs ptmt_getKey.executeQuery();ListString key new ArrayList();MapString, String key_type new HashMap();System.out.println(获取KEY);while (rs.next()) {if (rs.getString(column_key).equals(UNI)) {key.add(rs.getString(column_name));key_type.put(rs.getString(column_name), rs.getString(DATA_TYPE));System.out.println(rs.getString(column_name) rs.getString(column_key));}}System.out.println(拼接key);String column ;for (String s : key) {if (!Objects.equals(column, )) {column column , s;} else {column s;}}System.out.println(column);// 拼接SELECT 语句String newSQL SELECT column FROM table where;PreparedStatement pstm_getkeyvalue conn.prepareStatement(newSQL);rs pstm_getkeyvalue.executeQuery();ListMapString, String values new ArrayList();while (rs.next()) {MapString, String map new HashMap();for (String x : key) {map.put(x, rs.getString(x));}values.add(map);}// for (int i 0 ;i values.size();i){// System.out.println(values.get(i));;// }// 循环拼接删除的where 条件StringBuilder delete_where;String delete_sql;ListString delete_sqls new ArrayList();for (MapString, String x : values) {delete_where new StringBuilder();for (String y : x.keySet()) {if (delete_where.length() 0) {// 时间格式的拼接if (key_type.get(y).equals(datetime)) {LocalDateTime date LocalDateUtils.parseLocalDateTime(x.get(y),LocalDateUtils.DATETIME_PATTERN);String str LocalDateUtils.format(date, LocalDateUtils.UNSIGNED_DATETIME_PATTERN);delete_where.append( AND ).append(y).append().append().append(str).append();} else {delete_where.append( AND ).append(y).append().append().append(x.get(y)).append();}} else {if (key_type.get(y).equals(datetime)) {LocalDate date LocalDateUtils.parseLocalDate(x.get(y), LocalDateUtils.DATETIME_PATTERN);String str LocalDateUtils.format(date, LocalDateUtils.UNSIGNED_DATETIME_PATTERN);delete_where new StringBuilder(y str );} else {delete_where new StringBuilder(y x.get(y) );}}}delete_sql ;delete_sql DELETE FROM table WHERE delete_where;delete_sqls.add(delete_sql);// System.out.println(删除语句 delete_sql);// if (dels.executeUpdate(delete_sql)0){// i;// System.out.println(删除成功);// }else{// System.out.println(!!!!!!!!!!!!!!!!!删除失败!!!!!!!!!!!!!!!!!);// j;// }}conn.close();// 使用线程执行sqlSystem.out.println( 需要删除数据的总数为: delete_sqls.size() );System.out.println( 请确认是否删除 yes or no);String is_delete scanner.nextLine();if (!is_delete.equals(yes)) {return;}// ExecutorService executor Executors.newFixedThreadPool(10); // 创建一个固定大小的线程池// AtomicInteger successCount new AtomicInteger(0); // 原子计数器用于记录成功执行的SQL数量// AtomicInteger failureCount new AtomicInteger(0); // 统计失败次数// long start System.currentTimeMillis();// executor.submit(() - {// try (Connection connection HikariConfigs.HikariConn();) {// for (int i 0; i delete_sqls.size(); i) {// Statement statement connection.createStatement();// String deletesql delete_sqls.get(i);// System.out.println(!!!正在删除数据 deletesql);// // int updateCount statement.executeUpdate(deletesql);// // if (updateCount -1) {// // successCount.incrementAndGet(); // 如果删除成功增加本地计数器// // } else {// // failureCount.incrementAndGet();// 如果删除失败// // }// statement.addBatch(deletesql);// successCount.incrementAndGet(); // 如果删除成功增加本地计数器// if (i % 500 0) {// statement.executeBatch();// }// }// } catch (SQLException e) {// e.printStackTrace();// } catch (InterruptedException ex) {// ex.printStackTrace();// }// });// executor.shutdown(); // 关闭线程池// executor.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS); // 等待所有任务完成// long end System.currentTimeMillis();AtomicInteger successCount new AtomicInteger(0); // 原子计数器用于记录成功执行的SQL数量AtomicInteger failureCount new AtomicInteger(0); // 统计失败次数int size delete_sqls.size();int theadCount 10;int splitCount size / theadCount (size % theadCount ! 0 ? 1 : 0); //计算分拆数量向上取整final CountDownLatch cdl new CountDownLatch(theadCount);// 定义线程数量long starttime System.currentTimeMillis();for (int k 1; k theadCount; k) {final int beign (k - 1) * splitCount;final int end (k * splitCount) size ? size : k * splitCount;if(beign end) break;new Thread(new Runnable() {Overridepublic void run() {Connection con JDBCTools.getConnection();try {Statement st con.createStatement();for (int i 0; i delete_sqls.size(); i) {String deletesql delete_sqls.get(i);System.out.println(!!!正在删除数据 deletesql);int count st.executeUpdate(deletesql);if (count -1) {successCount.incrementAndGet();}else{failureCount.incrementAndGet();}}cdl.countDown(); // 执行完一个线程递减1} catch (Exception e) {} finally {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}}).start();}try {cdl.await(); //前面线程没执行完其他线程等待不往下执行long spendtimeSystem.currentTimeMillis()-starttime;System.out.println( theadCount个线程花费时间:spendtime);} catch (InterruptedException e) {e.printStackTrace();}long endtime System.currentTimeMillis();System.out.println(已完成删除);System.out.println(成功删除数据 successCount 条);System.out.println(删除失败 failureCount 条);System.out.println(消耗的时间为毫秒 (endtime - starttime));System.out.println(消耗的时间为秒 TimeUnit.MILLISECONDS.toSeconds(endtime - starttime));}
}完整的项目git地址Doris工具
