CODEWITHSUNDEEP
hibernate
hellzone
hellzone

Reputation: 5246

How to insert all rows in a single statement

How can I insert all rows in a single statement. Here is my code but It takes to long to insert rows. 

for(Myobject object : objectList)
        getCurrentSession().save(object);

It creates a statement for every record;

insert into myobject (id, type) values (?, ?)
insert into myobject (id, type) values (?, ?)
....

What I want to do is;

insert into myobject (id, type) values (?, ?), (?, ?), (?, ?) ......(?, ?);

Is there any way to create this statement?

Upvotes: 4

Views: 4485

Answers (4)

Alain Cruz
Alain Cruz

Reputation: 5097

I would like to add a way I manage to solved this issue using Native Queries. I took some inspiration from @Bohemian answer. First, on the Service layer, we split the list of objects in chunks and then on the DAO layer we insert each chunk.

SERVICE

@Override
public void massInsert(List<Object> objects) throws Exception {

    // First, let's split the list in chunks.
    final int chunkSize = 50;
    final AtomicInteger counter = new AtomicInteger();
    final Collection<List<Object>> result =
            objects.stream().collect(Collectors.groupingBy(it -> counter.getAndIncrement() / chunkSize)).values();

    // Now, for each iteration, we will insert the corresponding details.
    for (List<Objects> oList : result) {
        this.dao.massInsert(oList);
    }
}

DAO

@Override
public void massInsert(List<Object> objects) throws Exception {
    Session session = this.sessionFactory.getCurrentSession();

    // Create the query. It is important to consider that we will be using a
    // native query, which we will build from scratch. This is done in order to improve the insert speed.
    String hql = "insert into TABLE (column1, column2) VALUES ";

    // For each object, add a new object to insert.
    // In the end we will need to remove the last comma.
    for (int i = 0; i < objects.size(); i++) {
        hql = hql.concat("(?, ?),");
    }
    hql = hql.substring(0, hql.length()-1);

    // Create the query.
    Query query = session.createSQLQuery(hql);

    // Now, for each object, set the needed parameters.
    int index = 1;
    for (Object o : objects) {
        query.setParameter(index++, o.getAttribute1());
        query.setParameter(index++, o.getAttribute2());
    }

    // Execute the query.
    query.executeUpdate();

}

It works much faster than inserting each row one by one. Hope it helps.

Upvotes: 3

Satya
Satya

Reputation: 56

Just enable "DEBUG"level logging for hibernate and see if the batch inserts are happening.

  log4j.rootLogger=TRACE, file, stdout

  log4j.logger.org.hibernate=TRACE

  log4j.logger.org.hibernate.type=ALL

You should see logs like below.

  DEBUG AbstractBatcher:66 - Executing batch size: 20

Insert statements your are seeing are generated well before the call to ps.executeBatch(). Line number 70 of org.hibernate.jdbc.BatchingBatcher.

Upvotes: 0

bogdan.herti
bogdan.herti

Reputation: 1107

You may be interested in batch inserts on Hibernate tutorial.

The problem is not about the save() operation because all is doing this operation is to put the object saved in the first-level cache (in the session, making it persistent), but the flush() operation which triggers the insert. And they recommend the approach below in order to achieve good performance.

It is said also in the tutorial - I haven't tried - that you could get OutOfMemoryException for very high number of rows made persistent and they seem to recommend 20 as the batch size.

When making new objects persistent flush() and then clear() the session regularly in order to control the size of the first-level cache.

for ( int i=0; i<objectList.size(); i++ ) {
   getCurrentSession().save(objectList.get(i));
   if ( i % 20 == 0 ) { //20, same as the JDBC batch size
    //flush a batch of inserts and release memory:
   session.flush();
   session.clear();
   }
}

EDIT Set also hibernate.jdbc.batch_size in the configuration file to 20. or 50 if you want. Hibernate will have to group them and instead of 20 inserts you should have only one grouped by 20:

into myobject (id, type) values (id1, val1), (id2, val2), ......(id20, val20)

Upvotes: 4

Bohemian
Bohemian

Reputation: 425073

Use a native query (raw SQL):

entityManager
  .createNativeQuery("insert into myobject (id, type) values (?, ?), (?, ?), (?, ?) ......(?, ?)")
  .setParameter(1, foo)
  // etc
  .execute();

Upvotes: 2

Related Questions