Setup:
Hibernate: hibernate-core-3.3.1.GA.jar
hibernate.cfg.xml:
Code:
...
<property name="hibernate.connection.password">mypassword</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost/myschema</property>
<property name="hibernate.current_session_context_class">thread</property>
<property name="hibernate.connection.isolation">2</property>
…
MySQL version: 5.1.34
MySQL JDBC driver: mysql-connector-java-5.1.7-bin.jar
Datamodel including entity B which extends from entity A (multi-table mapping).
We need to be able to delete a lot of A/B's (List<A> toBeDeleted) as fast as possible in one operation. We need to be able to roll back the transaction including the deletion.
We can do it like this:
Code:
public void delete(List<A> toBeDeleted) {
Iterator<A> it = toBeDeleted.iterator();
while (it.hasNext())
{
session.delete(it.next());
}
}
But it is very slow when there is a lot A's in toBeDeleted, so we would like to bulk delete using HQL:
Code:
public void delete(List<A> toBeDeleted) {
List<Long> pids = new ArrayList<Long>(toBeDeleted.size());
Iterator<A> it = toBeDeleted.iterator();
while (it.hasNext())
{
pids.add(it.next().getPID());
}
Query query = session.createQuery("delete from A where pid in (:pids)");
query.setParameterList("pids", pids);
query.executeUpdate();
}
Problem with this approach is that it uses a temporary table HT_A where it puts the pids of the A's to be deleted and then "delete from A where (pid) IN (select pid from HT_A)". This is fine but creating a temporary table in MySQL disables the possibility of rolling back the transaction (
http://dev.mysql.com/doc/refman/5.0/en/ ... ommit.html), and that is a big problem for us.
We have tried to create our own hibernate dialect inheriting from org.hibernate.dialect.MySQL5InnoDBDialect. But we cant make that help us:
- We have tried to override performTemporaryTableDDLInIsolation to make it return Boolean.TRUE. But that results in "org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk delete"-exceptions caused by "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'myschema.ht_a' doesn't exist"-exceptions.
- We have also tried to override supportsTemporaryTables to make it return false. But that results in "org.hibernate.HibernateException: cannot perform multi-table deletes using dialect not supporting temp tables"-exceptions.
So right now we need to do our deletion using SQL like this:
Code:
public void delete(List<A> toBeDeleted) {
List<Long> pids = new ArrayList<Long>(toBeDeleted.size());
Iterator<A> it = toBeDeleted.iterator();
while (it.hasNext())
{
pids.add(it.next().getPID());
}
SQLQuery query = session.createSQLQuery("delete from B where pid in (:pids)");
query.setParameterList("pids", pids);
query.executeUpdate();
query = session.createSQLQuery("delete from A where pid in (:pids)");
query.setParameterList("pids", pids);
query.executeUpdate();
}
But that is a little stupid because we would like to use as little SQL as possible, and because we need to maintain the code every time the subclass-hierarchy below A/B changes.
I think Hibernate-staff should make a solution to the problem so that you can bulk delete using HQL on MySQL and still be able to roll back the transaction including the deletion. It this the right place to make such a suggestion?
Login
FAQ
Search
