Oracle高效批量插入数据（150万行4.7分钟）

一、准备工作

 建表

CREATE TABLE TEMP_20200210
(
	DMC VARCHAR2(100);
)

用程序拼接如下的批量插入的SQL

INSERT INTO TEMP_20200210 (DMC)
	SELECT CD.* FROM 
	(
		SELECT 1234567 FROM DUAL UNION
		SELECT 1234568 FROM DUAL UNION
		SELECT 1234568 FROM DUAL 
	) CD

在C盘文件夹下放一个1.txt文件目录为C:\1.TXT 里面的内容如下。里面有大约150万行数据，36M左右

二、实践

　直接上代码，需要引入oracle的包。

package connect;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class oracle {
		//数据库连接对象
	    private static Connection conn = null;
	     
	    private static String driver = "oracle.jdbc.driver.OracleDriver"; //驱动
	     
	    private static String url = "jdbc:oracle:thin:@//ip:1521/orcl"; //连接字符串 ip替换成实际ip地址
	     
	    private static String username = "globalb"; //用户名
	     
	    private static String password = "globalb"; //密码
	     
	     
	    // 获得连接对象
	    private static synchronized Connection getConn(){
	        if(conn == null){
	            try {
	                Class.forName(driver);
	                conn = DriverManager.getConnection(url, username, password);
	            } catch (ClassNotFoundException e) {
	                e.printStackTrace();
	            }catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	        return conn;
	    }
	     
	    //执行查询语句
	    public void query(String sql, boolean isSelect) throws SQLException{
	        PreparedStatement pstmt;
	         
	        try {
	            pstmt = getConn().prepareStatement(sql);
	            //建立一个结果集，用来保存查询出来的结果
	            ResultSet rs = pstmt.executeQuery();
	            while (rs.next()) {
	                String name = rs.getString("DMC");
	                System.out.println(name);
	            }
	            rs.close();
	            pstmt.close();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	     
	    public void query(String sql) throws SQLException{
	        PreparedStatement pstmt;
	        pstmt = getConn().prepareStatement(sql);
	        pstmt.execute();
	        pstmt.close();
	    }
	     
	     
	    //关闭连接
	    public void close(){
	        try {
	            getConn().close();
	             
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	  //执行查询语句
	    public static void  insert(List list) throws SQLException{
	        Statement pstmt;
	        String sql ="INSERT INTO IPAM.TEMP_20200210 (DMC) SELECT CD.* FROM ("; //SELECT 1234567 FROM DUAL UNION 
	        for(int i= 0;i<list.size();i++){
	        	sql = sql+"SELECT "+list.get(i)+" FROM DUAL UNION ";
	        }
	        sql = sql.substring(0, sql.length()-6);
	        sql=sql+")CD";
	        //System.out.println(sql);
	        try {
	            pstmt = getConn().createStatement();
	            //建立一个结果集，用来保存查询出来的结果
	            pstmt.executeQuery(sql);
	            pstmt.close();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	    public static void main(String[] args) throws SQLException, IOException{
	    	Long a  = System.currentTimeMillis();
	    	 FileReader  fileReader =new FileReader("C:\\1.txt");
	         BufferedReader bufferedReader =new BufferedReader(fileReader);
	         StringBuilder  stringBuilder =new StringBuilder();
	           List<String>  strings =new ArrayList<>();
	            String  str=null;
	            while ((str=bufferedReader.readLine())!=null) {
	                //System.out.println(str);
	                  if (str.trim().length()>2) {
	                     strings.add(str);
	                 }  
	         }
	            System.out.println("total:"+strings.size());
	    	List l = strings;
	    	if(l.size()>0){
	    		if(l.size()<=2000){//每次只插入2000条数据，ORACLE插入字符串不能大于32k
	    			insert(l);
	    	    }else{
	    	            int times = (int)Math.ceil(l.size()/2000.0 );//这里的数字必须是浮点数，否则无法向上取整
	    	            for(int i=0; i<times; i++ ){
	    	          	insert(l.subList(i*2000, Math.min((i+1)*2000, l.size())));
	    	          	System.out.println("执行第"+(i+1)+"次");
	    	            }
	    	    }
	    	}
	    	Long b = System.currentTimeMillis();
	    	System.out.println("耗时："+(b-a)+"ms");
	    }
	}

　　

三、运行结果

282秒大约4.7分钟左右。

四、总结

以上工作的数据库环境为oracle11g rac ，java版本为java8。

操作系统为windows 2012R2 。

中心思想为分段批量插入，核心代码是参考其他博文的，原文是使用mybatis批量向oracle中插入数据。

if(l.size()>0){
	if(l.size()<=2000){//每次只插入2000条数据，ORACLE插入字符串不能大于32k
		insert(l);
          }else{
            int times = (int)Math.ceil(l.size()/2000.0 );//这里的数字必须是浮点数，否则无法向上取整
            for(int i=0; i<times; i++ ){
          	insert(l.subList(i*2000, Math.min((i+1)*2000, l.size())));
            }
          }
}

　　

当然也可以扩展为插入多列数据，只需要修改读数据的地方和插入的SQL即可。