biotext.org.uk

Execute SQL outside of a transaction in Hibernate

by Andrew on Mar.03, 2009, under Tips

I hit upon a snag today — while PostgreSQL requires that certain maintenance commands (e.g. vacuum analyze) are executed outside of a transactional context, it’s actually quite hard to get at Hibernate’s underlying database connection directly. Each Session object has a connection() method which returns a JDBC connection object, but this actually turns out to be a Hibernate-generated proxy for the real connection object, which refuses to work outside of a transaction.

You can probably get around this by using autocommit mode instead of explicit transactions, but this is a configuration property that affects your whole application, and is considered harmful.

Eventually I hit on a really dodgy workaround. You can manually rollback the transaction at the start of your SQL statement the old-fashioned way:


<sql-query name="nasty.hack">
<![CDATA[
rollback transaction; vacuum analyze;
]]>
</sql-query>

This also works when used with the prepareStatement() method as shown here.

Frightening but effective. If you know of a better way, let me know!

Andrew.

Share/save this page:
  • email
  • Print
  • Google Bookmarks
  • del.icio.us
  • Digg
  • Reddit
  • StumbleUpon
  • Technorati
  • DZone
  • Slashdot
  • Fark
  • Facebook
  • MySpace
  • LinkedIn
  • Live
  • connotea
:, ,
1 comment for this entry:
  1. Stefan
    August 7th, 2009 on 10:33 am

    Thank’s! Short but very useful article!

Leave a Reply

Search

Use the form below to search the site:

Leave a comment if you can't find what you need.

Links

Archives