Java 批量插入 Oracle 数据库

在 Java 中批量插入 Oracle 数据库有几种高效的方法，以下是几种常用的实现方式：

使用 JDBC 批处理

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class OracleBatchInsert {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
        String username = "your_username";
        String password = "your_password";
        String sql = "INSERT INTO your_table (id, name, description) VALUES (?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // 设置自动提交为false，以便手动控制事务
            conn.setAutoCommit(false);
            // 添加批处理数据
            for (int i = 1; i <= 1000; i++) {
                pstmt.setInt(1, i);
                pstmt.setString(2, "Name " + i);
                pstmt.setString(3, "Description " + i);
                pstmt.addBatch();
                // 每100条执行一次批处理
                if (i % 100 == 0) {
                    pstmt.executeBatch();
                }
            }
            // 执行剩余的批处理
            pstmt.executeBatch();
            // 提交事务
            conn.commit();
            System.out.println("批量插入成功");
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                // 发生异常时回滚
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

使用 Oracle 的批量绑定优化

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class OracleOptimizedBatchInsert {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
        String username = "your_username";
        String password = "your_password";
        String sql = "INSERT INTO your_table (id, name, description) VALUES (?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            conn.setAutoCommit(false);
            // 设置批量大小
            int batchSize = 1000;
            pstmt.setExecuteBatch(batchSize);
            // 添加批处理数据
            for (int i = 1; i <= 10000; i++) {
                pstmt.setInt(1, i);
                pstmt.setString(2, "Name " + i);
                pstmt.setString(3, "Description " + i);
                pstmt.addBatch();
            }
            // 执行批处理
            int[] results = pstmt.executeBatch();
            conn.commit();
            System.out.println("成功插入 " + results.length + " 条记录");
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

使用 Oracle 的批量操作API (Oracle-specific)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
public class OracleSpecificBatchInsert {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
        String username = "your_username";
        String password = "your_password";
        String sql = "INSERT INTO your_table (id, name, description) VALUES (?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(url, username, password);
             OraclePreparedStatement pstmt = (OraclePreparedStatement) conn.prepareStatement(sql)) {
            conn.setAutoCommit(false);
            // 设置批量大小
            int batchSize = 1000;
            pstmt.setExecuteBatch(batchSize);
            // 添加批处理数据
            for (int i = 1; i <= 10000; i++) {
                pstmt.setInt(1, i);
                pstmt.setString(2, "Name " + i);
                pstmt.setString(3, "Description " + i);
                pstmt.addBatch();
            }
            // 执行批处理
            int[] results = pstmt.executeBatch();
            conn.commit();
            System.out.println("成功插入 " + results.length + " 条记录");
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

使用 Spring JdbcTemplate 批量插入

如果你使用 Spring 框架，可以使用 JdbcTemplate 进行批量插入：

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;
import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
public class SpringBatchInsertExample {
    private JdbcTemplate jdbcTemplate;
    private TransactionTemplate transactionTemplate;
    public void batchInsert(List<YourEntity> entities) {
        String sql = "INSERT INTO your_table (id, name, description) VALUES (?, ?, ?)";
        transactionTemplate.execute(status -> {
            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    YourEntity entity = entities.get(i);
                    ps.setInt(1, entity.getId());
                    ps.setString(2, entity.getName());
                    ps.setString(3, entity.getDescription());
                }
                @Override
                public int getBatchSize() {
                    return entities.size();
                }
            });
            return null;
        });
    }
}

性能优化建议

1. 调整批量大小：100-1000 之间效果较好，需要根据实际情况测试
2. 禁用自动提交：手动控制事务
3. 使用连接池：如 HikariCP、Druid 等
4. 调整 Oracle 参数：
   • default_batch_get_size
   • prefetch_rows
   • insert_batch_threshold
5. 考虑使用 Oracle 的批量加载工具：如 SQL*Loader

注意事项

1. 确保Oracle数据库表有合适的索引,但过多索引会影响批量插入性能
2. 大批量插入时考虑分批处理,避免内存问题
3. 处理好异常和事务回滚
4. 对于超大批量数据(百万级)，考虑使用Oracle的SQL*Loader或外部表等工具

方法可以根据你的具体需求和环境选择合适的实现方式。