Using hibernate/hql to truncate a table?

Question

What is the recommended way to truncate a table using hibernate/hql?

I've tried this:

 Query query = session.createQuery("truncate table MyTable");
 query.executeUpdate();

But it didn't work (truncate doesn't seem do be documented anywhere in hql...)

Answer 1

I used the delete syntax in an HQL to maintain portability. Works great:

public abstract class GenericDAOImpl<T, ID extends Serializable> implements GenericDAO<T, ID> {

private Class<T> persistentClass;

// Balance of dao methods snipped... :)

/**
 * Clears all records from the targetted file.
 * @throws DAOException
 */
public int truncate() throws DAOException {
    Session s = getSession();
    int rowsAffected = 0;
    try {
        Class c = getPersistentClass();
        String hql = "delete from " + c.getSimpleName();
        Query q = s.createQuery( hql );
        rowsAffected = q.executeUpdate();
    } catch ( HibernateException e ) {
        throw new DAOException( "Unable to truncate the targetted file.", e );
    }
    return rowsAffected;
}
/**
 * Returns a Class object that matches target Entity.
 *
 * @return Class object from constructor
 */
public Class<T> getPersistentClass() {
    return persistentClass;
}

Works great and totally truncates the targeted table. Use with caution as your db server will perform this statement with great efficiency... :)

Answer 2

I guess an horrible way of doing it would be deleting all.

public int hqlTruncate(String myTable){
    String hql = String.format("delete from %s",myTable);
    Query query = session.createQuery(hql);
    return query.executeUpdate();
}

Answer 3

Be careful, truncate and delete are totally separate sql statements :

• delete is DML and truncate is DDL, which means that delete can be rollbacked and truncate cannot be rollbacked
• delete has to find each row one by one. truncate is instantaneous
• delete uses undo logs and truncate does not

If you put it all together :

1. if you want it to be rollbackable, you don't want to use truncate
2. if you use delete, given the size of the table you want to empty :
   • if the table is small you will see no difference
   • if the table is of medium size you will experience bad performance
   • if the table is large you will run out of space in the undo tablespace, and you won't be able to empty anything

so be careful of what statement you really want to use.

As to how truncating a table with hql, it should be forbidden to run DDL (truncate, create table, drop table, etc...) from and application. You should use delete. But if the table is large, it won't work, either. That's why emptying a table in an application is in general a bad idea. If you want to do some cleaning, it is often better to run truncate inside an sql script once each night.

Notice that I don't know the specifics of your application and that it is only talking in general.

Answer 4

You can use session.createSQLQuery() instead:

session.createSQLQuery("truncate table MyTable").executeUpdate();

Needless to say, this is not ideal in terms of portability. It's probably a good idea to define this query in mapping and retrieve it in code as named query.

Answer 5

Preventing SQL Injection you can use:

String escapedSQL = StringEscapeUtils.escapeSql(unescapedSQL);

from Apache Commons-Lang

method StringEscapeUtils.escapeSql