Running Native SQL deletes or updates via Hibernate

Keywords:
hibernate manual native SQL update delete bulkUpdate child table spring

Problem:
Probably not the best idea but for performance, limitations in hibernate mappings or errors in the objects you find it'd be much easier to just run some manual SQL. Can you do this via hibernate?

Solution:
Yes you can, hibernate call this "Native SQL". This is fairly well document, but I noticed its missing an example of doing a manual update/delete - where the result is not going to give you a list() of anything.

For Query you make via createSQLQuery(...), use executeUpdate() which returns the number of rows effected.

Here an example (using the CATS/DOGS table names from the hibernate document):

Query deleteQuery = session.createSQLQuery(
    "delete from CATS "
    + "where DOG_ID in ( "
        + "select d.ID from DOGS d "
        + "where d.STATUS = ?)");
deleteQuery.setString(0, "VICIOUS");
int updated = deleteQuery.executeUpdate();

If you're integrating with hibernate via spring, the same can be done via the template-callback mechanism (where your DAO extends org.springframework.orm.hibernate3.support.HibernateDaoSupport):

Integer deletedData = (Integer)getHibernateTemplate().execute(new HibernateCallback () {
    public Object doInHibernate(Session session) throws HibernateException, SQLException {
        // delete the data
        Query deleteQuery = session.createSQLQuery(
            "delete from CATS "
            + "where DOG_ID in ( "
                + "select d.ID from DOGS d "
                + "where d.STATUS = ?)");
        deleteQuery.setString(0, "VICIOUS");
        int updated = deleteQuery.executeUpdate();
    }
});
if (log.isDebugEnabled()) {
    log.debug("rows deleted from CATS: " + deletedData);
}

Notes:
If you're wondering if you can avoid hard coding the table references by asking hibernate for the table-name mapping, this does seem possible - Get the table name from the model in Hibernate - but this approach doesn't seem to be "public" so may disappear.