文章目錄

• • • • • • 1. 表結構
          • 2. 批量刪除
          • 3. 批量插入
          • 4. 批量刪除和插入
          • 5. 實體類
          • 6. main

1. 表結構

-- Create table create table HERO (SNO VARCHAR2(20) not null,USER_NAME VARCHAR2(20),AGE NUMBER(30) );comment on table HERO is '英雄信息表';comment on column HERO.SNO is '英雄編碼'; comment on column HERO.USER_NAME is '英雄名稱'; comment on column HERO.AGE is '英雄年齡';alter table HERO add primary key (SNO);

2. 批量刪除

/*** 批量刪除** @param heroList 數據即可* @param url 數據庫url* @param user 數據庫用戶名* @param password 數據庫密碼* @throws SQLException*/public static void batchDel(List<HERO> heroList, String url, String user, String password, String cleansql) throws SQLException {//開始時間long start = System.currentTimeMillis();PreparedStatement clean = null;Connection conn = null;try {//數據連接conn = DriverManager.getConnection(url, user, password);//設置手動提交conn.setAutoCommit(false);//計數器int count = 1;clean = null;// 批量插入時ps對象必須放到for循環外面clean = conn.prepareStatement(cleansql);//把數據集合依次遍歷for (HERO hero : heroList) {clean.setString(1, hero.getSNO());clean.addBatch();// 每1000條一批次記錄插入一次if (count % 1000 == 0) {clean.executeBatch();conn.commit();clean.clearBatch();}}// 最后一批次剩余數量不足1000clean.executeBatch();conn.commit();} catch (SQLException e) {conn.rollback();e.printStackTrace();} finally {clean.clearBatch();}long end = System.currentTimeMillis();System.out.println(end - start);}

3. 批量插入

/*** 批量插入** @param heroList* @param url* @param user* @param password* @param sql*/public static void batchInsert(List<HERO> heroList, String url, String user, String password, String sql) throws SQLException {Connection conn = null;long begin = 0;long end = 0;try {conn = DriverManager.getConnection(url, user, password);conn.setAutoCommit(false);PreparedStatement pstmt = conn.prepareStatement(sql);for (HERO u : heroList) {pstmt.setString(1, u.getSNO());pstmt.setString(2, u.getUSER_NAME());pstmt.setInt(3, u.getAGE());pstmt.addBatch();}begin = System.currentTimeMillis();pstmt.executeBatch();conn.commit();} catch (SQLException e) {conn.rollback();e.printStackTrace();} finally {end = System.currentTimeMillis();conn.close();}logger.info("使用原生jdbc插入1000萬條數據總耗時:{}", end - begin);}

4. 批量刪除和插入

/*** 批量刪除和插入** @param heroList 數據即可* @param url 數據庫url* @param user 數據庫用戶名* @param password 數據庫密碼* @throws SQLException*/public static void batchDelAndInsert(List<HERO> heroList, String url, String user, String password, String cleansql, String sql) throws SQLException {//開始時間long start = System.currentTimeMillis();//數據連接Connection conn = DriverManager.getConnection(url, user, password);//設置手動提交conn.setAutoCommit(false);//計數器int count = 1;PreparedStatement ps = null;PreparedStatement clean = null;// 批量插入時ps對象必須放到for循環外面clean = conn.prepareStatement(cleansql);ps = conn.prepareStatement(sql);//把數據集合依次遍歷for (HERO hero : heroList) {clean.setString(1, hero.getSNO());ps.setString(1, hero.getSNO());ps.setString(2, hero.getUSER_NAME());ps.setInt(3, hero.getAGE());clean.addBatch();ps.addBatch();// 每1000條一批次記錄插入一次if (count % 1000 == 0) {clean.executeBatch();ps.executeBatch();conn.commit();clean.clearBatch();ps.clearBatch();}}// 最后一批次剩余數量不足1000clean.executeBatch();ps.executeBatch();conn.commit();clean.clearBatch();ps.clearBatch();long end = System.currentTimeMillis();System.out.println(end - start);}

5. 實體類

package com.sinosoft.cmiip.modular.insurance.test;import com.baomidou.mybatisplus.annotation.TableField; import com.baomidou.mybatisplus.annotation.TableId; import lombok.Data;@Data public class HERO {@TableId(value = "SNO")private String SNO;@TableField("USER_NAME")private String USER_NAME;@TableField("AGE")private int AGE; }

6. main

public static void main(String[] args) throws SQLException {String url = "jdbc:oracle:thin:@ip地址:1521:lisdb";String user = "dca";String password = "dca";// 模擬1000萬個用戶List<HERO> heroList = generateHero(1000000);logger.info("生成用戶記錄總數:{}", heroList.size());// 測試1 批量刪除和插入//刪除sqlString cleansql = "delete HERO family where SNO = ?";//插入sqlString sql = "insert into HERO(SNO,USER_NAME,AGE) values (?,?,?)";batchDelAndInsert(heroList, url, user, password, cleansql, sql);// 測試2 批量刪除batchDel(heroList, url, user, password, cleansql);// 測試3 批量插入batchInsert(heroList, url, user, password, sql);}

總結

以上是生活随笔為你收集整理的ORACLE JDBC 对千万数据 批量删除和批量插入的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。