Multivalue Mysql Inserts using HibernateTemplate

Question

I am using Spring HibernateTemplate and need to insert hundreds of records into a mysql database every second.

Not sure what is the most performant way of doing it, but I am trying to see how the multi value mysql inserts do using hibernate.

String query = "insert into user(age, name, birth_date) values(24, 'Joe', '2010-05-19 14:33:14'), (25, 'Joe1', '2010-05-19 14:33:14')"

getHibernateTemplate().execute(new HibernateCallback(){
 public Object doInHibernate(Session session) throws HibernateException, SQLException {
      return session.createSQLQuery(query).executeUpdate();
 }
});

But I get this error: 'could not execute native bulk manipulation query.' Please check your query .....

Any idea of I can use a multi value mysql insert using Hibernate? or is my query incorrect?

Any other ways that I can improve the performance? I did try the saveOrUpdateAll() method, and that wasn't good enough!

Answer 1

From section 14.1 from Hibernate's docs:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
for ( int i=0; i<100000; i++ ) {
Customer customer = new Customer(.....);
    session.save(customer);

    if ( i % 20 == 0 ) { //20, same as the JDBC batch size

        //flush a batch of inserts and release memory:

        session.flush();

        session.clear();
    }
}
tx.commit();
session.close();

So, you'd need to be able to pass in the collection of tuples you are trying to persist, construct them as persistant objects, and then save, flushing at some desired interval.

If this isn't adequate, I'd suggest Hibernate is a lousy solution for what you are trying to do.

Answer 2

I tend to only use Hibernate for single object operations.

For midsize amounts of data, like in reports, I like Spring JdbcTemplate.

For very large batch inserts, I'll generate an input file with tab separated values (after filtering tabs out of data) and use whatever shell program the database provides for batch input, such as MySql's mysqlimport. The two steps can performed by different machines to scale for large volume of imported data.

Answer 3

HibernateTemplate has a bulkUpdate method which should work for this

Answer 4

We use iBatis for getting high performance DML over mySQL. It's better than JDBC since another level of abstraction. iBatis has a batch operations, which also includes inserts.

Just make another DAO interface specific for this cases, and have it implement it using iBatis (FooSQLMap which imlements FooDAO). You can call the new FooDAO from your service, which makes it invisible to your service caller.