UPDATE and JOIN with JPQL

Question

Tutorials and samples about JPQL always deal with SELECT statement and sometimes, simple UPDATE statements. I need to update a table with a join.

I have simplified my env :

KEY
= id
- counter

APPLET
= id
! key_id (1-1)

DEVICE
= id
! applet_id (1-1)
! user_id (1-n)

USER
= id
- login

A device has a unique applet, which has a unique keyset. But an user can own several devices.

I need to reset the counter of every KEY attached to the USER login "x".

I tried some syntax with UPDATE and JOIN, without success. Any clue ?

Thank you.

Solution 1

What did you try and what error did you get? What is your object model?

Perhaps something like,

Update Key k set k.counter = 0 where exists (Select u from User u join u.devices d where u.login = "x" and d.applet.key = k)

See, http://en.wikibooks.org/wiki/Java_Persistence/JPQL_BNF#Update

You could also select the objects and reset the counter in memory and commit the changes.

Solution 2

JPQL does not support join operations in bulk update operations. When you edit query, nativeQuery = true, you can join.

Query should be written according to the fields in the database.

      @Transactional
      @Modifying
      @Query(nativeQuery = true,
          value = "UPDATE Team t" +
          " SET current = :current " +
          " FROM " +
          "   members," +
          "   account" +
          " WHERE " +
          "   members.members_id = t.members_id " +
          "   AND members.account_id = :account " +
          "   AND t.current = :current_true ")
      int updateTeam(
        @Param("current") String current,
        @Param("account") Long account,
        @Param("current_true") Integer current_true);