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.
{ 2 } Comments
Thank’s! Short but very useful article!
good hack, thanks.
Slightly less dodgy solution would be do something like this:
Connection connection = session.connection();
connection.setAutoCommit(true);
SingleConnectionDataSource singleConnectionDataSource = new SingleConnectionDataSource(connection, true);
final JdbcTemplate template = new JdbcTemplate(singleConnectionDataSource);
template.execute(sql);
connection.setAutoCommit(false);
This way you are only using autocommit for one statement.
Post a Comment