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的包。

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

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