CODEWITHSUNDEEP
hibernatejpasql-update
geneqew
geneqew

Reputation: 2571

JPA updating multiple rows and tables

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.

Upvotes: 3

Views: 12492

Answers (2)

DataNucleus
DataNucleus

Reputation: 15577

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

Upvotes: 1

Ralph
Ralph

Reputation: 120771

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)*

Upvotes: 2

Related Questions