hibernate out of memory error when batching with postgresql

Question

Hello I'm new to Hibernate and here is what I'm trying to do: select some data from a database table,and insert them into another table:

EntityManager em = LocalEntityManagerFactory.createEntityManager();
List<Object> results = em.createNativeQuery("select id from bar")
for (Object o : results) {
    Foo foo = new Foo();
    foo.setBarId(o.toString());
    em.persist(foo);
}

I understood that code above will get all id from database, store them in memory and put them into a List.This might get OutOfMemoryError, if there are too many ids. So I've checked Hibernate official user guide for batching, but what I've got from the Example didn't work:

EntityManager em= LocalEntityManagerFactory.createEntityManager();
txn = em.getTransaction();
txn.begin();
int batchSize = 25;
Session session = em.unwrap( Session.class );
ScrollableResults scrollableResults = session
    .createNativeQuery("select id from bar")
    .setCacheMode( CacheMode.IGNORE )
    .scroll( ScrollMode.FORWARD_ONLY );

int count = 0;
while ( scrollableResults.next() ) {
    String id= (String) scrollableResults.get( 0 );
    Foo foo = new Foo();
    foo.setBarId(id);
    em.persist(foo);
    if ( ++count % batchSize == 0 ) {
        //flush a batch of updates and release memory:
        em.flush();
        em.clear();
    }
}

txn.commit();

I've tried hibernate stream() method ,it didn't work either:

try (Stream<Object> objs = session
            .createNativeQuery("select id from bar")
            .stream()){
        objs.forEach(m -> {
            Foo foo = new Foo();
            foo.setBarId(m.toString());
            em.persist(foo);
        });
    }

After that I found this question ,and disabled 2nd level cache :

<property name="hibernate.cache.use_query_cache" value="false"/>

It still return OutOfMemoryError. What did I do wrong?Here is ErrorTrace:

 org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [Jersey Web Application] in context with path [] threw exception [org.glassfish.jersey.server.ContainerException: java.lang.OutOfMemoryError: Java heap space] with root cause
 java.lang.OutOfMemoryError: Java heap space
    at java.util.Arrays.copyOf(Arrays.java:3181)
    at java.util.ArrayList.grow(ArrayList.java:261)
    at java.util.ArrayList.ensureExplicitCapacity(ArrayList.java:235)
    at java.util.ArrayList.ensureCapacityInternal(ArrayList.java:227)
    at java.util.ArrayList.add(ArrayList.java:458)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2168)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1931)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893)
    at org.hibernate.loader.Loader.scroll(Loader.java:2768)
    at org.hibernate.loader.custom.CustomLoader.scroll(CustomLoader.java:380)
    at org.hibernate.internal.SessionImpl.scrollCustomQuery(SessionImpl.java:2175)
    at org.hibernate.internal.AbstractSharedSessionContract.scroll(AbstractSharedSessionContract.java:1021)
    at org.hibernate.query.internal.NativeQueryImpl.doScroll(NativeQueryImpl.java:199)
    at org.hibernate.query.internal.AbstractProducedQuery.scroll(AbstractProducedQuery.java:1371)
    at org.hibernate.query.internal.AbstractProducedQuery.stream(AbstractProducedQuery.java:1395)

hibernate out of memory error when batching with postgresql

0 Answers0