How to bulk insert with Spring-Batch?

Question

I'm trying to do bulk/batch inserts using spring-batch.

public ItemWriter<MyEntity> jpaItemWriter() { LocalSessionFactoryBuilder builder = new LocalSessionFactoryBuilder(ds); builder.addAnnotatedClasses(MyEntity.class); builder.setProperty("hibernate.show_sql", "true"); builder.setProperty("hibernate.batch_size", "20"); builder.setProperty("hibernate.order_updates", "true"); builder.setProperty("hibernate.order_inserts", "true"); HibernateItemWriter<MyEntity> writer = new HibernateItemWriter<>(); writer.setSessionFactory(builder.buildSessionFactory()); return writer; }

Result: I'm getting only single insert statements, not bulk inserts! I can see it from the logs both of hibernate + on postgresql level. Why is the bulk insert not working?

Update:

@Entity public class MyEntity { @Id String shortname; String fullname; }

Can we see MyEntity? Hibernate disables insert batching at the JDBC level transparently if the primary key of the inserting table isGenerationType.Identity... — Michael Minella
– Michael Minella, Commented Jan 9, 2015 at 15:39
I don't have any insertion strategy on the ID as I control the ID's myself. — membersound
– membersound, Commented Jan 9, 2015 at 16:00
Please see my update. Also: maybe I'm misinterpreting things, but: how would I actually identify "bulk inserts" in the sql log? All I see are plain insert statements one after the other, just as I would expect them to be seen without batch insert. — membersound
– membersound, Commented Jan 9, 2015 at 17:33
This really is a Hibernate question more than a Spring Batch question since the orchestration of a batch insert is handled by Hibernate, not Spring Batch. Do you have autocommit = true on your datasource? — Michael Minella
– Michael Minella, Commented Jan 9, 2015 at 19:35

outdev · Accepted Answer · 2015-01-13 19:48:17Z

Spring has nothing to do with the SQL statements batching, its all managed by Hibernate.
I see you have batching enabled and configured properly, but that's not enough to make it work...you also need to use the right session type. in hibernate there are two session types: stateful session and stateless session.
The stateful session, which is obtained with

sessionFactory.openSession();

and also is used by default if using @Transactional, never uses batching (even if configured) and sends all SQL statements, at once, at transaction commit. However, you can simulate batching by calling flush() from time to time, and SQL statements will be sent to the db on every flush().

The stateless session, which is obtained with

sessionFactory.openStatelessSession();

respects the batching configuration, so just switch to stateless session, and batching will works as expected. Hibernate will log every session.insert(), but will not sent the SQL insert statement to the database, instead the SQL insert statements are sent as batches of configured size. So its best to "tail -f" the database log. The main idea of having two session types is that, the stateful session uses cache, and every saved entity ends up in the 1st level cache, and therefore if you save 100k entities you will get OOM. The solution is to use stateless session which doesn't interact with any level cache. You can read more about the stateless session.

Collectives™ on Stack Overflow

How to bulk insert with Spring-Batch?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related