Inserting multiple rows in Hibernate - batch insert

Question

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>

Configuration configuration=new Configuration(); configuration.setProperty("hibernate.jdbc.batch_size", "50"); is meaningless. You have to do that once you create your SessionFactory — Ori Dar
– Ori Dar, Commented Apr 3, 2013 at 8:41
@orid - Commented out those statements. This answer says "It's just not made definitively persistent until the transaction commit". So, is it not possible to do this just in a single trip to a database? — Tiny
– Tiny, Commented Apr 4, 2013 at 11:46

JB Nizet · Accepted Answer · 2013-04-04 11:50:05Z

5

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.

answered Apr 4, 2013 at 11:50

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting multiple rows in Hibernate - batch insert

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related