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 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 2

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 3

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 4

be carefull, 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 bey one. truncate is instantaneous - delete use 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 perfs - 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 carefull 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. So it should be 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