HQL - Update field and return resulting value in one query

I have a table that is going to consist of columns of integers, each essentially being a sequence. Each row represent a single customer account and the sequences tied to it.

I am trying to find an effective way to update the value of a particular sequence by a variable amount, and then to retrieve that updated value, all in one HQL query. I'm using Hibernate 3.6.1 and Spring 3.0.5. The database is Postgres.

I can do what I want with a native SQL query as follows:

update account.sequences set seq1 = seq1 + :seqIncrement where account_id = :accountId returning seq1;

However, I'd greatly prefer to use the existing Entity names/fields rather than the table names. The fewer places to update changed name the better.

I'开发者_开发技巧m not using sequences as I would end up dealing with thousands of them and it's never really going to be a straight increment-by-one operation. I'm planning on updating the value to cover the number of items for which I need unique ids. By doing this in a single statement I don't have to worry about any table locking or concurrency issues.

So...does anyone have a cleaner way of doing this? Or am I simply over-thinking something that I can already do?

There isn't really anything in HQL to replace the returning clause in SQL. Executing UPDATE statements in a query in Hibernate is usually avoided. It breaks the caches, alters entity fields without rolling the version number, hoses the in memory state of your session, and is generally bad juju. Normally in a hibernate application you'd only use an update query for large batch operations where the performance consequences of using the Session normally are just too much.

If you're doing this with high enough frequency that locking the entity prior to the update is an issue, the native sql probably is your best option. I wouldn't personally worry about it until it's proven to be a problem. You're not really doing that much extra work in the DB cursor to select for update the things you're about to update, but I am not a postgres expert either.