When I was testing my application, I have encountered an error using such a query:
@NamedQuery(name = "entity.myquery",
query = "UPDATE MyEntity d SET d.isEnabled=false where d.myTest.id = :testId")
I get this error :
org.hibernate.exception.SQLGrammarException,
ERROR: syntax error at or near "cross"
Using this query, Hibernate translate it to a join query.
update mySchema.my_entity cross join set is_enabled = false where id = ?
Or, no join is accepted in a Bulk HQL query.
So the Solution is to use a sub-select.
@NamedQuery(name = "entity.myquery",
query = "UPDATE MyEntityd SET d.isEnabled=false where d.myTest.id IN "
+ "(SELECT e.id FROM myTest e where id = :testId)")
@NamedQuery(name = "entity.myquery",
query = "UPDATE MyEntity d SET d.isEnabled=false where d.myTest.id = :testId")
I get this error :
org.hibernate.exception.SQLGrammarException,
ERROR: syntax error at or near "cross"
Using this query, Hibernate translate it to a join query.
update mySchema.my_entity cross join set is_enabled = false where id = ?
Or, no join is accepted in a Bulk HQL query.
So the Solution is to use a sub-select.
@NamedQuery(name = "entity.myquery",
query = "UPDATE MyEntityd SET d.isEnabled=false where d.myTest.id IN "
+ "(SELECT e.id FROM myTest e where id = :testId)")
Thank you Mariem, you post really helped me. Before I was using EclipseLink, and this JQL worked fine:
ReplyDeleteUPDATE Transaction t
SET t.category = :category
WHERE t.account.user.id = :userId
AND t.tag = :tag
Now I switched to Hibernate, so I've had to adapt my JQL:
UPDATE Transaction t
SET t.category = :category
WHERE t.account.id IN (SELECT a.id FROM Account a WHERE a.user.id = :userId)
AND t.tag = :tag
Hi Adam,
DeleteI am really glad to read you comment and to know that my solution was of help for you and maybe for others also :)
From http://docs.jboss.org/hibernate/core/4.2/manual/en-US/html_single/#batch-direct
ReplyDeleteNo join, either implicit or explicit, can be specified in a bulk HQL query. Sub-queries can be used in the where-clause, where the subqueries themselves may contain joins.
This comment has been removed by a blog administrator.
ReplyDeleteThis solution is very bad for PostgreSQL. In concurrent transaction, the WHERE clause of the second update should be reevaluated, but the inner select is cached. You will end up with an unwanted update
ReplyDelete