Deleting bulk records from some history tables using hibernate/JDBC?

[Chinmay Subudhiray]

I have a requirement of deleting bulk records from some history tables. The table information from which the records has to be deleted will be kept in a temp table. The records in the history tables are in millions for which I want to delete them in batches so that the delete operation don�t fill up the log space in the Database. I am not using stored proc for this task as the table name has to be given as a parameter while executing the proc. So had finally thought of doing it in hibernate or plain JDBC. I tried using setMaxResults (int batchSIze) for doing it in batches but this is not for delete operation I think since the delete operation was not done in batches after setting setMaxResults . I am using native SQL for achieving this since I wanted to do away with mappings for these history tables.

Any pointers on how this can be achieved in hibernate or plain JDBC? It would be better if someone can share the source for achieving this task.

Thanks much in advance.
[ January 21, 2008: Message edited by: Chinmay Subudhiray ]

[Chinmay Subudhiray]

This is what I have coded but its not working as per my need


public void deleteTable() {
final HibernateTemplate ht = new HibernateTemplate(getSessionFactory());
ht.execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException {
Transaction tx = session.beginTransaction();
String hqlDelete = "Delete From TableName " ;
SQLQueryImpl queryString = (SQLQueryImpl) session.createSQLQuery(hqlDelete);
queryString.setMaxResults(100);
int noOfRecords = queryString.executeUpdate();
log.debug("No of Records deleted :" + noOfRecords);
tx.commit();
session.close();
return null;
}
});
}

[Jan Cumps]

I don't know if this is relevant to you, but if you use Oracle,
you can clear a table without hitting the rollback space (and without being able to roll back), by using trunc <tablename>

Please check with your dba if this practice is acceptable in your case.

Regards, Jan

[Chinmay Subudhiray]

I am not using Oracle Database in turn its Sybase. And i dont have to delete all the records. its actually deleting the records which are older than a specific date.