JAVA代码处理百万级数据量插入ORACLE数据库

这种数据量建议采用原生JDBC进行处理，通过PreparedStatement+批量提交的方式，具体代码如下：

/**
 * <b>文件名：</b>Demo4.java<br>
 * <b>描述：</b><br>
 * <b>作者：</b>FISHROAD<br>
 * <b>时间：</b>2018年11月14日 下午10:18:33<br>
 * Copyright(c) 2018 Joyin Tech,LTD. All Rights Reserved.
 */
package ZRQ;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

/**
 * <b>类名：</b>Demo4<br>
 * <b>描述：</b><br>
 * <b>作者：</b>FISHROAD<br>
 * <b>时间：</b>2018年11月14日 下午10:18:33<br>
 */
public class Demo4 {
	public static void main(String[] arg) throws Exception {

		Long beginTime = System.currentTimeMillis();
		// 文件路径(每天生成的文件都会 放在以当天日期命名的文件夹下)
		String fileDir = "E:\\XXX\\XXX\\999_20191217.TXT";

		InputStream is = new FileInputStream(fileDir);
		
		InputStreamReader inr = new InputStreamReader(is, "GBK");
		@SuppressWarnings("resource")
		BufferedReader bf = new BufferedReader(inr);
		String dataLine = "";
		List<Acplan> lst = new ArrayList<Acplan>();
		while ((dataLine = bf.readLine()) != null) {
			String[] str = dataLine.split("\\|\\+\\|");
			Acplan vo = new Acplan();
			vo.setCoOrgNo(str[0]);
			vo.setContNo(str[1]);
			vo.setTermNo(new BigDecimal(str[2]));
			vo.setDueDate(str[3]);
			vo.setAmountOfTerm(str[4]);
			vo.setAmountOfPrincipal(str[5]);
			vo.setInterestPayments(str[6]);
			vo.setPrincipalStatus(str[7]);
			vo.setPenalty(str[8]);
			vo.setRepayOfPrincipal(str[9]);
			vo.setInterestPaidBack(str[10]);
			vo.setPaidInterestPaid(str[11]);
			vo.setReductOfPrincipal(str[12]);
			vo.setInterestRelief(str[13]);
			vo.setPenalyRelief(str[14]);
			vo.setCurrentTermStatus(str[15]);
			vo.setTrustProjNo(str[16]);
			lst.add(vo);
		}
		System.out.println("总数据条数为："+lst.size());
		Long endTime = System.currentTimeMillis();
		System.out.println("读取文件耗时："+(endTime - beginTime) / 1000 + "秒");
		
		BasicDataSource aa = new BasicDataSource();
		aa.setDriverClassName("oracle.jdbc.driver.OracleDriver");
		aa.setUrl("jdbc:oracle:thin:@localhost:1521:orcl");
		aa.setUsername("XXX");
		aa.setPassword("XXX");
		Connection conn = aa.getConnection();
		conn.setAutoCommit(false);
		
		String sql = "insert into XXX_FILE_FORM"
				+ "(CO_ORG_NO, CONT_NO, TERM_NO,DUE_DATE,AMOUNT_OF_TERM, AMOUNT_OF_PRINCIPAL,"
				+ "INTEREST_PAYMENTS,PRINCIPAL_STATUS, PENALTY,REPAY_OF_PRINCIPAL, INTEREST_PAID_BACK,"
				+ "PAID_INTEREST_PAID,REDUCT_OF_PRINCIPAL, INTEREST_RELIEF,PENALY_RELIEF,CURRENT_TERM_STATUS,"
				+ " TRUST_PROJ_NO, RECENT_REPAY_TIME)"
				+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement pst = conn.prepareStatement(sql);
		for (int i = 0; i < lst.size(); i++) {
			Acplan acp = (Acplan) lst.get(i);
			pst.setString(1, acp.getCoOrgNo());
			pst.setString(2, acp.getContNo());
			pst.setBigDecimal(3, acp.getTermNo());
			pst.setString(4, acp.getDueDate());
			pst.setString(5, acp.getAmountOfTerm());
			pst.setString(6, acp.getAmountOfPrincipal());
			pst.setString(7, acp.getInterestPayments());
			pst.setString(8, acp.getPrincipalStatus());
			pst.setString(9, acp.getPenalty());
			pst.setString(10, acp.getRepayOfPrincipal());
			pst.setString(11, acp.getInterestPaidBack());
			pst.setString(12, acp.getPaidInterestPaid());
			pst.setString(13, acp.getReductOfPrincipal());
			pst.setString(14, acp.getInterestRelief());
			pst.setString(15, acp.getPenalyRelief());
			pst.setString(16, acp.getCurrentTermStatus());
			pst.setString(17, acp.getTrustProjNo());
			pst.setString(18, acp.getRecentRepayTime());
			pst.addBatch();
			// 每1000次提交一次
			if (i > 0 && i % 1000 == 0) {// 可以设置不同的大小；如50，100，500，1000等等
				pst.executeBatch();
				conn.commit();
				pst.clearBatch();
			}
		}
		// 剩余的部分
		pst.executeBatch();
		conn.commit();
		pst.clearBatch();
		pst.close();
		conn.close();
		Long endTime1 = System.currentTimeMillis();
		System.out.println("插入数据耗时：" + (endTime1 - beginTime) / 1000 + "秒");
	}
}

表结构如下：

create table XXX_FILE_FORM
(
  co_org_no           VARCHAR2(10),
  cont_no             VARCHAR2(40),
  term_no             NUMBER,
  due_date            VARCHAR2(8),
  amount_of_term      VARCHAR2(20),
  amount_of_principal VARCHAR2(20),
  interest_payments   VARCHAR2(20),
  principal_status    VARCHAR2(2),
  penalty             VARCHAR2(20),
  repay_of_principal  VARCHAR2(20),
  interest_paid_back  VARCHAR2(20),
  paid_interest_paid  VARCHAR2(20),
  reduct_of_principal VARCHAR2(20),
  interest_relief     VARCHAR2(20),
  penaly_relief       VARCHAR2(20),
  current_term_status VARCHAR2(1),
  trust_proj_no       VARCHAR2(20),
  recent_repay_time   VARCHAR2(10)
);

导入文件如下：

999_20191217.TXT

https://download.csdn.net/download/Fishroad/12041494

 

测试结果如下：

总数据条数为：700000
读取文件耗时：6秒
插入数据耗时：6秒

总数据条数为：1400000
读取文件耗时：12秒
插入数据耗时：32秒