aspose file tools*
The moose likes Object Relational Mapping and the fly likes Deleting bulk records from some history tables using hibernate/JDBC? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Deleting bulk records from some history tables using hibernate/JDBC?" Watch "Deleting bulk records from some history tables using hibernate/JDBC?" New topic
Author

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

Chinmay Subudhiray
Greenhorn

Joined: Jun 25, 2007
Posts: 3
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
Greenhorn

Joined: Jun 25, 2007
Posts: 3
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
Bartender

Joined: Dec 20, 2006
Posts: 2497
    
    8

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


OCUP UML fundamental and ITIL foundation
youtube channel
Chinmay Subudhiray
Greenhorn

Joined: Jun 25, 2007
Posts: 3
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.
 
 
subject: Deleting bulk records from some history tables using hibernate/JDBC?