3

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?

snieguu
  • 2,073
  • 2
  • 20
  • 39
hellzone
  • 5,393
  • 25
  • 82
  • 148

4 Answers4

4

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)
bogdan.herti
  • 1,110
  • 2
  • 9
  • 18
  • It doesn't change anything. Still tries to insert all rows one by one. – hellzone Oct 04 '13 at 07:31
  • It should group the insert by 20 of them. You can also see the accepted response on this [question](http://stackoverflow.com/questions/6687422/hibernate-batch-size-confusion) I just found on SO. – bogdan.herti Oct 04 '13 at 07:40
  • What I didn't understand is I want to add 10.000 records and It takes nearly 115 seconds and with your way(grouping by 20) it takes 114 seconds. – hellzone Oct 04 '13 at 07:47
  • How much does it take if you insert them directly from your DB client (in case you have the possibility to test this)? What if you change this to 50? And well...this is the way to do this as you could see from from the tutorial or the other question on SO. I would expected to save more time... – bogdan.herti Oct 04 '13 at 07:51
  • What I found is hibernate still tries to insert one by one. Here is Hibernate output; Hibernate: insert into myobject (id, type) values (?, ?) Hibernate: insert into myobject (id, type) values (?, ?).... – hellzone Oct 04 '13 at 07:56
  • And you have set `hibernate.jdbc.batch_size` in the configuration file? If yes, now you moved to a new problem - batch inserting is not enabled. I don't understand why. – bogdan.herti Oct 04 '13 at 08:05
  • Yes I have set batch size. Thanks for your helps by the way. – hellzone Oct 04 '13 at 08:10
3

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.

Alain Cruz
  • 4,757
  • 3
  • 25
  • 43
2

Use a native query (raw SQL):

entityManager
  .createNativeQuery("insert into myobject (id, type) values (?, ?), (?, ?), (?, ?) ......(?, ?)")
  .setParameter(1, foo)
  // etc
  .execute();
Bohemian
  • 412,405
  • 93
  • 575
  • 722
0

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.

Satya
  • 56
  • 4