Hibernate Community • View topic - How to delete with small rollback segment

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

How to delete with small rollback segment

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

magaxe

Post subject: How to delete with small rollback segment

Posted: Tue Jan 24, 2006 7:26 am

Newbie

Joined: Tue Jan 24, 2006 7:03 am
Posts: 2

I am deleting old objects from an Oracle database using Hibernate. The following code is working fine but for many rows the rollback segment is too small and it is not possible to extend it any further.

Code:

            final String query = "delete MyClass "
                + "where date < :date ";
            mSes.createQuery(query)
               .setParameter("date", start)
                .executeUpdate();

So I tried to setMaxResults() but it doesn't seem to affect a delete query. Hibernate tries to delete all rows.

Code:

            final String query = "delete MyClass "
                + "where date < :date ";
            mSes.createQuery(query)
               .setParameter("date", start)
               .setMaxResults(max)
                .executeUpdate();

So I tried using an SQL query but got an

Quote:

java.lang.UnsupportedOperationException: Update queries only supported through HQL

Code:

            final String query = "delete from T_MyClass "
                + "where date < :date and rownum < 3000 ";
            mSes.createSQLQuery(query)
               .setParameter("date", start)
                .executeUpdate();

Is there a way to batch delete at most a given number of objects?

Hibernate version: 3.0.5

Name and version of the database: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

Top

steve

Post subject:

Posted: Tue Jan 24, 2006 3:15 pm

Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX

Always helpful to ask yourself: "How would I do this in SQL?"...

Top

magaxe

Post subject:

Posted: Wed Jan 25, 2006 5:56 am

Newbie

Joined: Tue Jan 24, 2006 7:03 am
Posts: 2

Found a working solution using SQL is:

Code:

Connection connection = mSes.connection();
String querySQL = "DELETE FROM T_MYCLASS WHERE date_column < ? AND rownum <= ?"; 
PreparedStatement ps = connection.prepareStatement(querySQL);
ps.setDate(1, start);
ps.setInt(2, 100);
ps.executeUpdate();
ps.close();

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum