Truncate Table ? executeUpdate OR createSQLQuery

14 / Apr / 2011 by Manoj Mohan 0 comments

In one of the modules that I was working on , I needed to delete the previous data from a table then load up the data into those tables again and then manipulate code through the ‘id’s’ on those tables.
So this is what I was doing …

[groovy]
Event.executeUpdate(‘delete from Event’)
EventInstance.executeUpdate(‘delete from EventInstance’)
[/groovy]


But the problem was that when I deleted the events, the ‘id’s’ on those tables would remain intact and the new Events that would load up would continue upon the earlier present ‘ id’s ‘ .i.e if the last present id was 24 before deleting , the new load-ups would continue from 25 onwards..

This made realise that truncate was a better option … But unfortunately the GORM layer does not support the ‘ truncate ‘ queries that I was looking for.
So in effect this query would give an error


[groovy]
Event.executeUpdate(‘truncate table Event’)
EventInstance.executeUpdate(‘truncate table EventInstance’)
[/groovy]

So with some help from ‘ Mr. Google ‘ and my fellow colleagues , i realised that there was another way to do this ..
Session Factory …
Session Factory allowed me to truncate those tables through  createSQLQuery() method
So my new approach was

[groovy]
def sessionFactory

def session = sessionFactory.getCurrentSession()
Query query = session.createSQLQuery(‘truncate table event’)
[/groovy]

The getCurrentSession() method looks at the current context to see if a session is stored in there. If there is one, it uses it, and if there isn’t a session in the current context, it creates a new one and saves it in there.
Then all I did was use the query to truncate the tables… and presto !! .. my work was done ..

Hope this helps .. 😀

Cheers

Manoj Mohan
Manoj (at) Intelligrape (dot) com

FOUND THIS USEFUL? SHARE IT

Leave a Reply

Your email address will not be published. Required fields are marked *