HQL - 在一个查询中更新字段并返回结果值
我有一个由整数列组成的表，每个整数本质上都是一个序列。每行代表一个客户帐户以及与其相关的序列。
我正在尝试找到一种有效的方法，以可变数量更新特定序列的值，然后检索更新后的值，所有这些都在一个 HQL 查询中进行。我正在使用 Hibernate 3.6.1 和 Spring 3.0.5。数据库是Postgres。
我可以使用本机 SQL 查询执行我想要的操作，如下所示：
update account.sequences set seq1 = seq1 + :seqIncrement where account_id = :accountId returning seq1;
但是，我非常喜欢使用现有的实体名称/字段而不是表名称。更新更名的地方越少越好。
我没有使用序列，因为我最终会处理数千个序列，并且它永远不会真正成为直接递增一的操作。我计划更新该值以涵盖我需要唯一 ID 的项目数量。通过在单个语句中执行此操作，我不必担心任何表锁定或并发问题。
那么...有人有更干净的方法吗？或者我只是过度思考了一些我已经可以做的事情？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
HQL 中实际上没有任何东西可以替代 SQL 中的返回子句。通常避免在 Hibernate 的查询中执行 UPDATE 语句。它会破坏缓存，在不滚动版本号的情况下更改实体字段，控制会话的内存状态，并且通常是坏juju。通常，在休眠应用程序中，您只会对大批量操作使用更新查询，其中通常使用会话的性能后果太大。
如果您执行此操作的频率足够高，以至于在更新之前锁定实体会出现问题，则本机 sql 可能是您的最佳选择。在它被证明是一个问题之前，我个人不会担心它。您实际上并没有在数据库游标中做那么多额外的工作来选择更新您要更新的内容，但我也不是 postgres 专家。
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.