Java Fetching Huge Result Set

Question

I am having a table with huge number of records say nearly 25gb with more than 1000 million records. I want to fetch them using java and write to a csv. I thought of using java8 stream to stop bring whole result set to memory but still i am getting a java heap space error.

    try {

                close=UncheckedCloseable.wrap(con);
                pSt = con.prepareStatement("select * from "+this.tblName);
                close=close.nest(pSt);
                con.setAutoCommit(false);
                pSt.setFetchSize(5000);

                ResultSet resultSet = pSt.executeQuery();
                close=close.nest(resultSet);
                Stream<String> buffer = StreamSupport.stream(new Spliterators.AbstractSpliterator<String>(
                        Long.MAX_VALUE,Spliterator.ORDERED) {

                    @Override
                    public boolean tryAdvance(Consumer<? super String> action) {
                        try {
                            if(!resultSet.next()) return false;
                            action.accept(createRecord(resultSet));
                            return true;
                        } catch(SQLException | IllegalAccessException | IllegalArgumentException | InvocationTargetException ex) {
                            throw new RuntimeException(ex);
                        }
                    }

                }, false).onClose(close);

                System.out.println(buffer.count());
                Files.write(Paths.get(home.getAbsolutePath()+"/"+tblName+".csv"), (Iterable<String>)buffer::iterator);

            }


private String createRecord(ResultSet resultSet) throws SQLException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        StringBuffer buffer = new StringBuffer();
        int colCount = resultSet.getMetaData().getColumnCount();
        while(resultSet.next()) {
            for(int i=1; i<=colCount; i++) {
                String columnName = resultSet.getMetaData().getColumnTypeName(i);
                String getType = StringUtils.capitalize(columnName.toLowerCase());
                Method methodName = getMethodName(resultSet,getType);
                Object result = methodName.invoke(resultSet, i);
                if(result == null || result.toString().equalsIgnoreCase("null")){
                    result =  "";
                }
                if (result instanceof String) {
                    result = ((String) result).trim().replaceAll(",", "\\\\,").replace("\n", "").replace("\r", "");
                }
                buffer.append(result+",");
            }
            buffer.setLength(buffer.length()-1);
            buffer.append("\n");
        }
        return buffer.toString();
    }

`createRecord(..)` iterates the entire `ResultSet` (not a single record). — Andrew S, Sep 17 '18 at 17:48
Your `createRecord` method iterates over the whole ResultSet, trying to create one big string of all records. IMHO you should completely remove the `while (resultSet.next())` loop from `createRecord` — Thomas Kläger, Sep 17 '18 at 17:48
Then how to iterate over a batch of record to convert them into string? — Siddharth Chaurasia, Sep 17 '18 at 17:55
Write to the file on each iteration of the `ResultSet` (instead of writing to the file all at once). — Andrew S, Sep 17 '18 at 18:12
So basicaclly you are asking make the createRecord method return void and than inside the createRecord method directly write to file on eatch call to next? — Siddharth Chaurasia, Sep 17 '18 at 18:22
If you look at the answer from https://stackoverflow.com/a/32232173/5646962, you will see that the `createRecord` should "[...] produce[s] an item out of a single line from the `ResultSet`" — Thomas Kläger, Sep 17 '18 at 18:38
Yeah I saw that but had few doubts.. let me try and will get back to you in case of doubt. :) — Siddharth Chaurasia, Sep 17 '18 at 20:32

Java Fetching Huge Result Set

0 Answers0