Bulk insert operation is a nice feature in that a single DML such as HQL or SQL will result in zero or multiple records inserted into a table. Unfortunately, Hibernate 3.0.5 does not yet support bulk insert operation, even though both bulk update and delete operations are supported. Since bulk insert is supposed to be supported in Hibernate 3.1, so I downloaded the latest 3.1beta2 release and tried. It didn't work.

So, how can we make bulk insert operations happen in Hibernate 3.0.5 ? Well here is a quick hack. Hibernate allows parameterized native SQL Query to be specified in the Hibernate mapping file via the <sql-query> XML element. So one obvious idea is to specify a native SQL Query with an INSERT statement. However, I couldn't get this to work without tweaking two classes:

    org.hibernate.loader.Loader.java
    org.hibernate.loader.custom.CustomLoader.java

The hibernate3.0.5-patched.jar can be used as a drop-in replacement of the hibernate3.jar as found in the 3.0.5 distribution.

Example:

    <sql-query name="bulkInsertQuery1">
        <return-scalar column="null" type="int"/>
        insert into A (a1, a2, a3)
        select b1, b2, :p1 from B
        where b3 = :p2
    </sql-query>

1. The <return-scalar> is necessary to keep Hibernate 3.0.5 happy, so it won't reject the insert statement.


2. The column="null" is a special notation recognized by the patched classes to simply execute the native SQL without bothering with the return value.

Usage Sample: (Assume we are using Spring.)

    String[] paramNames = {"p1", "p2"};
    Object[] paramValues = {"p1value", "p2value"};
    // Execute the native SQL Query purely for it's side effect
    getHibernateTemplate().findByNamedQueryAndNamedParam("bulkInsertQuery1", paramNames, paramValues);

That's all. It's a hack but it works perfectly for executing any arbitrary native SQL!

Is this the smell of wet dog ? Hopefully the Hibernate team will provide proper support for HQL bulk insert operation in the near future. As requested, this hack has been submitted as a patch.