java - hibernate/JPA : Update then delete in one transaction

Question

是否可以在同一事务中执行更新查询，然后在更新查询之后立即执行删除查询？我正在尝试根据 token 的哈希值激活一个帐户，然后在同一笔交易中删除该 token 。

transaction.begin();
entityManager
.createNativeQuery(
                      "UPDATE accounts AS ac "
                    + "INNER JOIN account_tokens AS ak ON ac.id = ak.account_id "
                    + "SET ac.account_state = "
                    + "CASE "
                    + "WHEN ac.account_state = 'AWAITING_ACTIVATION' THEN 'ACTIVATED' "
                    + "END "
                    + "WHERE ak.token_hash = :tokenHash")
                    .setParameter()
                    .executeUpdate();
em.createNativeQuery(
                      "DELETE FROM account_tokens AS ak "
                    + "WHERE ak.token_hash = :tokenHash")
                    .setParameter()
                    .executeUpdate(); // delete
transaction.commit();

Answer 1

是的，使用 PL/SQL 过程。

您不能减少查询的数量 - 它们都做不同的事情 - 但您可以通过将其全部包装为 PLSQL 函数来减少往返数据库的次数和解析的次数。

   CREATE PROCEDURE s_u_d(a)
    BEGIN

    UPDATE tab_x SET tab_x.avalue=1 WHERE tab_x.another=a;

    DELETE FROM tab_y WHERE tab_y.avalue=a;

    SELECT * 
    FROM tab_x
    WHERE tab_x.another=a;

    END;