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.
ReplyDelete