List of list of named parameters in EJBQL/HQL

Question

I'm trying to execute a long 'INSERT ON DUPLICATE KEY UPDATE' with up to a few thousand rows in a MySQL+JBoss+Hibernate application. It looks something like:

INSERT INTO Table (field1, field2, field3) VALUES
(value1_1, value2_1, value3_1),
(value1_2, value2_2, value3_2),
(value1_3, value2_3, value3_3),
...
ON DUPLICATE KEY UPDATE ...

This works fine as a native query where I build up the query string in a loop, but the long query strings themselves are retained in the Hibernate query plan cache and eat up a lot of heap space. So I'm wondering if it's possible to parameterize this query so that it's stored only once in the query plan cache.

Is there a way to parameterize a list of tuples like this? Ideally I could pass in a collection of { value1, value2, value3 } collections in setParameter(). It's easy enough to run thousands of one-line updates with (:value1, :value2, :value3), but that comes at a significant (2-3x) performance cost.

Thanks in advance.

Answer 1

Take a look at Hibernate batch inserts section of the docs.