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?

Thanks much in advance.

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;
}
});
}