Hibernate PostgreSQL选择用于外部联接问题的更新

Question

我已经面临问题，试图使用带有Hibernate作为JPA实现的Spring Data选择更新行和PostgreSQL。

假设我们有实体：a，b，c。

public class A{
   @Id
   private Long id;
   @OneToMany(fetch = FetchType.EAGER)
   private Set<B> bSet;
   @OneToMany(fetch = FetchType.EAGER)
   private Set<C> cSet;
}

假设我们要选择一个使用所有相关的B和C实体进行更新的A，即与表相关的锁定行。

@Query(SELECT a FROM A a 
       LEFT JOIN FETCH a.bSet
       LEFT JOIN FETCH a.cSet
       WHERE a.id=?)
@Lock(LockModeType.PESSIMISTIC_WRITE)
public A selectAndLockA(Long Aid);

查询看起来像

SELECT a.column1, ... from tableA a LEFT JOIN tableB b ... FOR UPDATE of a,c

用于更新A，C

查询将尝试锁定两个表，导致异常类似： org.postgresql.util.psqlexception：错误：对于更新，无法应用于外部JOIN的无效一侧

我试图存档的是仅锁定第一张表"用于更新"

可以以某种方式配置或告诉Hibernate仅锁定第一张表。

Answer 1

postresql不支持这。如果您做一个外部选择，则没有什么可以阻止某人将行插入左连接表中，从而修改您正在查看的结果集（例如，在重复读取时列将不再为null）。

。

有关详细说明，请参见此处

Answer 2

自从提出问题以来已经很长时间了，但是我有一个类似的问题，希望我的答案对某人有帮助。

假设我们有这个JPA实体：

@Entity
@Table(name = "card_transactions")
public class CardTransactionsEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "card_trans_seq")
    @SequenceGenerator(name = "card_trans_seq", sequenceName = "card_trans_seq")
    private Long id;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
            @JoinColumn(name = "ofd_id", referencedColumnName = "ofd_id"),
            @JoinColumn(name = "receipt_id", referencedColumnName = "receipt_id")})
    private ReceiptsEntity receipt;
    @Column
    @Enumerated(EnumType.STRING)
    private CardTransactionStatus requestStatus;
    ...
}

@Entity
@Table(name = "receipts")
public class ReceiptsEntity {
    @EmbeddedId
    private OfdReceiptId id; 
    ...
}
@Embeddable
public class OfdReceiptId implements Serializable {
    @Column(name = "ofd_id")
    @Enumerated(EnumType.STRING)
    private OfdId ofdId;
    @Column(name = "receipt_id")
    private String receiptId;
    ...
}

我们希望选择带有收入的card Transactionsentity，以进行悲观更新仅CardTransactionsentity。可以使用Hibernate和Spring Data JPA存储库作为

来完成此操作。

public interface CardTransactionRepository extends JpaRepository<CardTransactionsEntity, Long> {
    @Query("select ct from CardTransactionsEntity ct left join fetch ct.receipt r where ct.requestStatus = :requestStatus")
    @Lock(value = LockModeType.PESSIMISTIC_WRITE)
    @QueryHints(value = {
            @QueryHint(name = "javax.persistence.lock.timeout", value = "-2"), // LockOptions.SKIP_LOCKED
            @QueryHint(name = "org.hibernate.lockMode.r", value = "NONE") // "r" is alias for ct.receipt and will excluded from PESSIMISTIC_WRITE
    })
    List<CardTransactionsEntity> loadCardTransactions(@Param("requestStatus") CardTransactionStatus requestStatus, Pageable pageable);
}

此存储库方法将执行查询，例如

SELECT ct.*, r.* from card_transactions ct LEFT OUTER JOIN receipts r ON ct.ofd_id = r.ofd_id and ct.receipt_id = r.receipt_id WHERE ct.request_status=? LIMIT ? FOR UPDATE OF ct SKIP LOCKED

Answer 3

您可以通过使用fetchype.lazy将表绕过此错误。此提取类型是默认类型，不需要为@Onetomany加入指定。

public class A{
   @Id
   private Long id;
   @OneToMany
   private Set<B> bSet;
   @OneToMany
   private Set<C> cSet;
}