java从oracle中读取大量数据写出到文件

以你之姓@ 2022-12-07 04:28 492阅读 0赞

## java从oracle中读取大量数据写出到文件 ##

**背景**：项目中需要从oracle中读取表字段并以指定间隔符拼接字段，输出到txt。每张表的数据量大概在2000W左右，因为是只需要读一张表指定字段的全部数据，也不需要考虑查询优化，只针对表读取规模进行优化即可。

版本oracle 11G

**思路一：**

使用oracle语句对数据表进行分页读取，这里应注意到的是rowid效率和rownum效率问题,还有使不使用order by

SELECT t.*
      FROM a t, (SELECT ROWNUM rn, c.*
                      FROM (SELECT   ROWID k
                                FROM a
                            ORDER BY ID) c) b
    WHERE t.ROWID = b.k AND b.rn BETWEEN 10001 AND 20000;

有一篇对比文章，大家可以看一下 [http://www.itpub.net/thread-1603830-1-1.html][http_www.itpub.net_thread-1603830-1-1.html]

这个思路对我的需求来说也不太适合，分页读取确实缩短了查询时间，但是对于上千万的大表来说，越往后分页花的时间越长，整体来说效率提升不多大。

**思路二:**

在写数据时，使用多线程，提升写的效率，测试证明，我的效率瓶颈是在读取数据方面，不是写数据方面。

**思路三：**

使用ResultSet对结果集进行分批读取，开始的时候没想到ResultSet直接支持了分批读取，在前面两个思路上花费了不少时间。

先讲解下再贴代码  
ResultSet的常见用法：[点击跳转查看][Link 1]

大多数文章知识讲解了常见方法及参数的设定，ResultSet的**setFetchSize()** 和 **setMaxRows()** 缺很少有人提及

简单来说
    setFetchSize()  ：是设置ResultSet每次向数据库取的行数 
    
    例如：rs.setFetchSize(100),ResultSet每次向数据库读取100条数据，
    之后下一百条数据的读取是在ResultSet内部完成的，不需要手动去进行调用或定位数据从哪行开始。
    
    setMaxRows() :是设置Resultset最多返回的行数，不需要读取全部数据，只要特定行数的数据，可以选择此方法。

有篇文章里分析的还算可以：  
[JDBC读取数据优化setFetchSize][JDBC_setFetchSize]  
[JDBC读取数据优化-fetch size][JDBC_-fetch size]

另外如果设置的

stmt = destCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

可能会报这个错  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXlpbjY1NTM_size_16_color_FFFFFF_t_70_pic_center]

这时需要把 
    ResultSet.TYPE_SCROLL_SENSITIVE
    改为
    ResultSet.TYPE_SCROLL_INSENSITIVE,

下面贴上我的代码

public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException { 
    
            String selsql;
            Connection destCon = null;
            Statement stmt = null;
            BufferedWriter output = null;
            long rowCount = 0L;
            int colCounts = 0;
            ResultSet res = null;
    
            long flen = 0L;
            selsql = "select RANDOM_STRING from myTestTable";
            destCon = getConnection();
            int fileCount = 1;
            EtlRuler etlRuler = new EtlRuler();
            etlRuler.setLocal_path("E:\\web_project\\");
            etlRuler.setFile_name("test010.txt");
            String filePath = etlRuler.getLocal_path() + etlRuler.getFile_name().replace("${NUM}", "00" + fileCount);
            etlRuler.getDataPath().add(filePath);
    
    
            File file = new File(etlRuler.getDataPath().get(fileCount - 1));
            if (!file.exists()) { 
                file.createNewFile();
            }
            StringBuilder line = new StringBuilder();
            output = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file, false), StandardCharsets.UTF_8));
    
            stmt = destCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            res = stmt.executeQuery(selsql);
            res.setFetchSize(2000);
    
    
    
            if (res == null || !res.next()) { 
                System.out.println("数据文件sql无数据！");
                // throw new Exception("数据文件sql无数据！");
            }
            assert res != null;
            res.previous();
    
            // 获取字段元信息
            ResultSetMetaData rsmd1 = res.getMetaData();
            colCounts = rsmd1.getColumnCount();
            int j = 0;
            String str = "";
            while (res.next()) { 
                //System.out.println("开始读取数据" + rowCount++);
                // 打印进度
                rowCount++;
                if (rowCount % 2000 == 0) { 
                    Date date = new Date();
                    //20w条数据的时候写入一下，之后清空StringBuilder，重新添加数据
                    System.out.println("执行时间：" + date);
                    System.out.println(rowCount + " ----rows proceed");
    // output.write(line.toString());
    // output.flush();
    // line.delete(0, line.length());
                }
    
                for (int i = 1; i <= colCounts; i++) { 
                    //line.append(res.getString(i)).append("\n");
                    str = res.getString(i)+"\n";
                }
                output.write(str);
                output.flush();
                str = "";
    
                //System.out.println("开始写入数据");
    
    
                if (file.length() > (1024 * 500)) { 
                    if (etlRuler.getFile_name().contains("${NUM}")) { 
                        //output.write(line.toString());
                        //output.flush();
                        //line.delete(0,line.length());
                        fileCount++;
                        System.out.println("创建新文件");
                        String newfilePath = etlRuler.getLocal_path() + etlRuler.getFile_name().replace("${NUM}", "00" + fileCount);
                        etlRuler.getDataPath().add(newfilePath);
                        file = new File(etlRuler.getDataPath().get(fileCount - 1));
                        if (!file.exists()) { 
                            file.createNewFile();
                        }
                        output = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file, false),
                                StandardCharsets.UTF_8));
                    }
                }
            }
    
            //System.out.println("开始写入数据");
            output.write(line.toString());
            output.flush();
            output.close();
            flen = file.length();
            System.out.println("文件大小：" + flen);
        }
    
    public static Connection getConnection() throws ClassNotFoundException, SQLException { 
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = null;
            con = DriverManager.getConnection(
                    "jdbc:oracle:thin:@" + ip + ":" + port + ":" + sid, user,
                    password);  //设置数据库连接字符串
            return con;
        }

[http_www.itpub.net_thread-1603830-1-1.html]: http://www.itpub.net/thread-1603830-1-1.html
[Link 1]: https://www.cnblogs.com/lyuweigh/p/eb16d79e30a2e4a9f606770ff591fc7b.html
[JDBC_setFetchSize]: https://blog.csdn.net/bisal/article/details/82735614
[JDBC_-fetch size]: https://blog.csdn.net/bincavin/article/details/8689186
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3podXlpbjY1NTM_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/5d0fcec58a12490eba85f37819f8d879.png