3

i need to update multiple rows in Table a and it seems ok to use the object oriented approach by using a for loop and doing a merge. 

...
// get list and assign columns to be updated
...
for (MyEntity e : entityList) {
   em.merge(e);
}
..

this approach also enables me to update other tables (as other property pf MyEntity). on one of the questions posted here updating multiple rows using JPA , the second option which uses a namedQuery to update seems to be faster. However how will the update be implemented if i need to batch update multiple tables as well?

i have tried:

@NamedQuery(name="Agent.updateAccountStatusByTree",
        query="UPDATE com.sample.core.data.Agent a "
            + "INNER JOIN com.sample.core.data.Player p "
            + "ON a.player.id = p.id "
            + "SET a.accountStatus=:accountStatus, p.status=:accountStatus "
            + "WHERE a.site.id=:siteId and a.agentTree like :agentTree")})

however this throws a:

org.hibernate.hql.ast.QuerySyntaxException: expecting "set", found 'INNER'

another option which i tried is:

 @NamedQuery(name="Agent.updateAccountStatusByTree",
        query="update com.sample.core.data.Agent a "
            + "set a.accountStatus=:accountStatus, a.player.status=:playerStatus "
            + "where a.site.id=:siteId and a.agentTree like :agentTree")})

since i already have a refer to the other table, however this generates an invalid query throwing a sql grammar exception... is there any other way to achieve this?

Thanks.

Community
  • 1
  • 1
geneqew
  • 2,401
  • 5
  • 33
  • 48

2 Answers2

2

I have he feeling that you try the compete wrong thing. The Named Query should be an JPQL (HQL) query but it looks like you are trying a SQL query.

So want to use native queries, then you should use @NamedNativeQuery instead. If not you need to translate the query to JPQL (HQL)

*For difference beteween JPQL and HQL have a look at the comments.

Btw JPQL is based on the Hibernate Query Language (HQL), an earlier non-standard query language included in the Hibernate object-relational mapping library.

Hibernate and the HQL were created before the JPA specification. As of Hibernate 3 JPQL is a subset of HQL. (Wikipedia)*

Ralph
  • 118,862
  • 56
  • 287
  • 383
  • @DataNucleus: This question is about hibernate unknwon version. Hibernate supports Hibernate supports `@NamedQuery` and `@NamedNativeQuery` at least since Hibernate core 3.2 + Hibernate Annotations 3.3. At this time it was defently HQL but not JPQL. But this was long time ago, so I updated my answer a bit. – Ralph Nov 08 '11 at 08:01
  • @DataNucleus: why do you deleted you comment, at least it was a good comment. – Ralph Nov 08 '11 at 08:05
  • Sorry Ralph, you're too fast at replying ;-) and I was going to rephrase mine, but by that time you'd already replied and also made your post much clearer too. – DataNucleus Nov 08 '11 at 08:08
  • thanks for the inputs.. i ended up using the native query. also, it seems there is still a bug in hibernate if the annotation based namednativequery is to be used. so my final solution was to build query string manually and call em.createNativeQuery... agein thanks! – geneqew Nov 17 '11 at 04:42
1

JPA spec states 

update_clause ::= UPDATE entity_name [[AS] identification_variable]
SET update_item {, update_item}*
update_item ::= [identification_variable]{state_field | single_valued_object_field

so you cannot JOIN in an UPDATE statement. Only way you can update using JOINs is in SQL, and then you lose datastore independence since not all RDBMS support JOINs there in the same way

DataNucleus
  • 15,497
  • 3
  • 32
  • 37