Generate a lot of data in a list and generate the file

Question

I have a table in the database that has about 2.3 million records.

I need to export this data to a csv file through a java / web application.

I'm using JPA / Hibernate. But I'm having trouble.

HibernateEntityManager hem = this.getEntityManager().unwrap(HibernateEntityManager.class);
    Session session = hem.getSession();
    org.hibernate.Query qu = 
        session.createSQLQuery("select li.* from ligacoes li").
            setFetchSize(Integer.MIN_VALUE).setReadOnly(true);
    ScrollableResults r = qu.scroll(ScrollMode.FORWARD_ONLY);

    int count = 0;
    while (r.next()) {

But when the program reads about 200,000 records it throws an exception:

Exception in thread "ContainerBackgroundProcessor[StandardEngine[Catalina]]"  
java.lang.OutOfMemoryError: Java heap space

I've tried other means, but I'm still having difficulties. I can't export the data directly from the database. The data must be exported through this application.

Can anyone help?

Have a look at http://stackoverflow.com/questions/7003026/problem-exporting-a-lot-of-data-from-database-to-csv-with-java — user3487063, Sep 15 '14 at 19:42
Why don't you use `SELECT ... INTO OUTFILE` query instead? Check [this link](http://dev.mysql.com/doc/refman/5.1/en/select-into.html) for more information. It's much more efficient and it creates and writes the content to the file. — oardic, Sep 15 '14 at 19:44
Maybe your default max heap size is just too small. Did you check how big would be your csv file? — Gas, Sep 15 '14 at 20:33
if you can't increase the memory size then you need to implement the pagination-like solution. So get for example 500K records, insert it into csv and then get second 500k. To make sure all the data in continuous and nothing inserted when you are fetching the data, you can keep track of ID (Primary Key). so get the last record's id and save it and next time get another bunch after that ID — pms, Sep 15 '14 at 22:11

score 0 · Answer 1 · answered Sep 16 '14 at 18:33

I solved the question.

I split the query. Instead of bringing a very large list, I bring a minor list several times. Pagination.

        HibernateEntityManager hem = this.getEntityManager().unwrap(HibernateEntityManager.class);
        Session session = hem.getSession();

        int pageIn = 1;
        int pageFim = 20000;
        boolean fim = false;
        int count = 0;

        while (!fim) {
            ++count;
            org.hibernate.Query qu = session.createSQLQuery("select li.* from ligacoes li where li.operadora='81' "
                    + "and length(li.numero_destino) < 8 and li.valor_contrato <> '0'");
            qu.setFirstResult(pageIn - 1);
            qu.setMaxResults(pageFim);
            List<Ligacoes> chamadasVivo = new ArrayList<>(objectToLigacoes(qu.list()));

            count += pageFim;

            if (chamadasVivo.size() <= 0) {
                break;
            }

            for (Ligacoes li : chamadasVivo) {
                //append the file...

            }

            if (count % 100000 == 0) {
                session.flush();
                session.clear();
            }

            pageIn += pageFim;
        }

Generate a lot of data in a list and generate the file

1 Answers1