ORACLE JDBC 对千万数据 批量删除和批量插入

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();
                }
            }

            // 最后一批次剩余数量不足1000
            clean.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            e.printStackTrace();
        } finally {
            clean.clearBatch();
        }

        long end = System.currentTimeMillis();
        System.out.println(end - start);
    }
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

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);
    }
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

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();
            }
        }
        // 最后一批次剩余数量不足1000
        clean.executeBatch();
        ps.executeBatch();
        conn.commit();

        clean.clearBatch();
        ps.clearBatch();

        long end = System.currentTimeMillis();
        System.out.println(end - start);
    }
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

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;
}
16
17

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 批量删除和插入
        //删除sql
        String cleansql = "delete HERO family where SNO = ?";
        //插入sql
        String 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);
    }
16
17
18
19
20
21
22
23