Inserting multiple rows in Hibernate - batch insert
batch-file, batch-processing, hibernate
Solution
Your expectations are wrong. Hibernate won't generate a single insert statement. But it will add three insert statements to a batch and execute this batch, thus making a single roundtrip to the database.
Read http://docs.oracle.com/javase/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html for more information about JDBC batch updates.
Problem
I'm inserting multiple rows in Hibernate using the following transactional unit of code in my DAO class. ``` @Override @SuppressWarnings("unchecked") @Transactional(readOnly = false, propagation=Propagation.REQUIRES_NEW, rollbackFor={Throwable.class}) public String insertZoneCharge(Long zoneId, List<Object[]> items) { Session session=sessionFactory.openSession(); session.beginTransaction(); //Configuration configuration=new Configuration(); //configuration.setProperty("hibernate.jdbc.batch_size", "50"); int i=1; for(Object[] o:items) { if(o[2]!=null&&StringUtils.isNotBlank(o[2].toString())) { ZoneChargePK zoneChargeId=new ZoneChargePK(); zoneChargeId.setWeightId(Long.parseLong(o[0].toString())); zoneChargeId.setZoneId(zoneId); model.ZoneCharge zoneCharge=new model.ZoneCharge(); zoneCharge.setZoneChargePK(zoneChargeId); zoneCharge.setCharge(new BigDecimal(o[2].toString()).setScale(2, RoundingMode.HALF_UP)); session.save(zoneCharge); if(++i%50==0) { session.flush(); session.clear(); } } } session.getTransaction().commit(); session.close(); return "Data saved successfully."; } ``` The parameters of this method are supplied via a JSON request. I'm not going into depth about Oracle table details as it is unrelated. When I supply let's say 3 values, this loop is iterated thrice and three rows are created into the corresponding database table. When the transaction is successfully completed, the following native Oracle `INSERT` statement which I can see on the console appears to be executed thrice. ``` insert into WAGAFASHIONDB.ZONE_CHARGE (CHARGE, WEIGHT_ID, ZONE_ID) values (?, ?, ?) ``` Debug and trace info is as follows. ``` DEBUG [http-apr-8080-exec-84] (SqlStatementLogger.java:104) - insert into WAGAFASHIONDB.ZONE_CHARGE (CHARGE, WEIGHT_ID, ZONE_ID) values (?, ?, ?) TRACE [http-apr-8080-exec-84] (BasicBinder.java:83) - binding parameter [1] as [NUMERIC] - 1.00 TRACE [http-apr-8080-exec-84] (BasicBinder.java:83) - binding parameter [2] as [BIGINT] - 164 TRACE [http-apr-8080-exec-84] (BasicBinder.java:83) - binding parameter [3] as [BIGINT] - 22 DEBUG [http-apr-8080-exec-84] (SqlStatementLogger.java:104) - insert into WAGAFASHIONDB.ZONE_CHARGE (CHARGE, WEIGHT_ID, ZONE_ID) values (?, ?, ?) TRACE [http-apr-8080-exec-84] (BasicBinder.java:83) - binding parameter [1] as [NUMERIC] - 2.00 TRACE [http-apr-8080-exec-84] (BasicBinder.java:83) - binding parameter [2] as [BIGINT] - 221 TRACE [http-apr-8080-exec-84] (BasicBinder.java:83) - binding parameter [3] as [BIGINT] - 22 DEBUG [http-apr-8080-exec-84] (SqlStatementLogger.java:104) - insert into WAGAFASHIONDB.ZONE_CHARGE (CHARGE, WEIGHT_ID, ZONE_ID) values (?, ?, ?) TRACE [http-apr-8080-exec-84] (BasicBinder.java:83) - binding parameter [1] as [NUMERIC] - 3.00 TRACE [http-apr-8080-exec-84] (BasicBinder.java:83) - binding parameter [2] as [BIGINT] - 163 TRACE [http-apr-8080-exec-84] (BasicBinder.java:83) - binding parameter [3] as [BIGINT] - 22 ``` So, it appears that three separate `INSERT` statements have been executed and three separate trips to the database have been made. I expect it to execute only one multi-row `INSERT` statement in a single trip. Does Hibernate really execute three separate statements in this situations? If yes, then what is the way to execute statements in a specific batch? Is there anything wrong in my code? My Hibernate configuration in `application-context.xml` is as follows. ``` <property name="hibernateProperties"> <value> hibernate.dialect=org.hibernate.dialect.Oracle10gDialect hibernate.jdbc.batch_size=50 hibernate.cache.provider_class=org.hibernate.cache.EhCacheProvider hibernate.format_sql=false hibernate.show_sql=true hibernate.order_updates=true hibernate.connection.autocommit=false hibernate.order_inserts=true hibernate.order_updates=true hibernate.cache.use_query_cache=false hibernate.cache.use_second_level_cache=false javax.persistence.validation.mode=callback </value> </property> ```