java - 从具有主组合键的关联表中删除

Question

当我想从具有复合键和额外列的关联表中删除时遇到问题。

关联表由一个实体表示，该实体具有两个对象:协作者和能力，并且具有关系@ManyToOne。另一方面，我与 CollaboratorCompetence Entity 存在关系 @OneToMany，该关系具有复合键(collaborator_id，competent_id)。

当我执行此查询来删除 collaborators_competences 表上的一行时，它起作用了。

Query 1

DELETE FROM collaborators_competences WHERE (collaborator_id, competence_id)
                                                IN (SELECT collaborator_id, competence_id FROM collaborators_competences
                                                    JOIN collaborators c on collaborators_competences.collaborator_id = c.id
                                                JOIN competences c2 on collaborators_competences.competence_id = c2.id
                                                WHERE c.id = (SELECT collaborators.id FROM collaborators WHERE login = :collabLogin) AND c2.id = :competenceId)

Query 2 (simplified)

DELETE FROM  collaborators_competences cc WHERE cc.collaborator_id = (SELECT collaborators.id FROM collaborators WHERE collaborators.login = :collabLogin)AND cc.competence_id = :competenceId

现在，当我使用存储库执行相同的查询时，我收到错误“无法提取结果集”。

Repository

public interface CollaboratorCompetenceRepository extends JpaRepository<CollaboratorCompetence, CollaboratorCompetenceId> {
    Optional<CollaboratorCompetence> findById(CollaboratorCompetenceId id);

//    @Query(value = "DELETE FROM CollaboratorCompetence " +
//        "WHERE CollaboratorCompetence.collaborator IN (SELECT Collaborator FROM Collaborator WHERE Collaborator.login = :collabLogin)" +
//        "AND CollaboratorCompetence.competence IN (SELECT COmpetence FROM Competence WHERE Competence.id = :competenceId)")
//    @Query(value = "DELETE FROM  collaborators_competences WHERE collaborator_id = 1128 AND competence_id = 2551", nativeQuery = true)
    @Query(value = "DELETE FROM collaborators_competences WHERE (collaborator_id, competence_id)\n" +
        "                                                IN (SELECT collaborator_id, competence_id FROM collaborators_competences\n" +
        "                                                    JOIN collaborators c on collaborators_competences.collaborator_id = c.id\n" +
        "                                                JOIN competences c2 on collaborators_competences.competence_id = c2.id\n" +
        "                                                WHERE c.id = (SELECT collaborators.id FROM collaborators WHERE login = :collabLogin) AND c2.id = :competenceId)", nativeQuery = true)
    void deleteByCollaboratorLoginAndCompetenceId(@Param("collabLogin") String login, @Param("competenceId") Long id);

    void deleteCollaboratorCompetenceById(CollaboratorCompetenceId id);
}

即使使用 Spring Data，Hibernate 也只执行选择查询，而不执行删除查询。

CollaboratorCompetence Entity

@Entity
@Table(name = "collaborators_competences")
public class CollaboratorCompetence {

    @EmbeddedId
    private CollaboratorCompetenceId id;

    @ManyToOne
    @MapsId("collaboratorId")
    private Collaborator collaborator;

    @ManyToOne
    @MapsId("competenceId")
    private Competence competence;

    @Column(name = "note")
    private Integer note;

    public CollaboratorCompetence() {
    }

    public CollaboratorCompetence(Collaborator collaborator, Competence competence, Integer note) {
        this.id = new CollaboratorCompetenceId(collaborator.getId(), competence.getId());
        this.collaborator = collaborator;
        this.competence = competence;
        this.note = note;
    }

CollaboratorCompetenceId (Embedable class)

@Embeddable
public class CollaboratorCompetenceId implements Serializable {

    @JoinColumn(name = "competence_id", table = "collaborators_competences")
    private Long competenceId;

    @JoinColumn(name = "collaborator_id", table = "collaborators_competences")
    private Long collaboratorId;

    public CollaboratorCompetenceId() {
    }

    public CollaboratorCompetenceId(Long competenceId, Long collaboratorId) {
        this.competenceId = competenceId;
        this.collaboratorId = collaboratorId;
    }

    public Long getCompetenceId() {
        return competenceId;
    }

    public void setCompetenceId(Long competenceId) {
        this.competenceId = competenceId;
    }

    public Long getCollaboratorId() {
        return collaboratorId;
    }

    public void setCollaboratorId(Long collaboratorId) {
        this.collaboratorId = collaboratorId;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        CollaboratorCompetenceId that = (CollaboratorCompetenceId) o;
        return Objects.equals(competenceId, that.competenceId) &&
            Objects.equals(collaboratorId, that.collaboratorId);
    }

    @Override
    public int hashCode() {
        return Objects.hash(competenceId, collaboratorId);
    }

    @Override
    public String toString() {
        return "CollaboratorCompetenceId{" +
            "competenceId=" + competenceId +
            ", collaboratorId=" + collaboratorId +
            '}';
    }
}

Error stack :

Caused by: org.hibernate.exception.GenericJDBCException: could not extract ResultSet
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2168)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1931)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893)
    at org.hibernate.loader.Loader.doQuery(Loader.java:938)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
    at org.hibernate.loader.Loader.doList(Loader.java:2692)
    at org.hibernate.loader.Loader.doList(Loader.java:2675)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507)
    at org.hibernate.loader.Loader.list(Loader.java:2502)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2200)
    at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1016)
    at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:152)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
    at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1463)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:214)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:602)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:590)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    ... 175 common frames omitted
Caused by: org.postgresql.util.PSQLException: Aucun résultat retourné par la requête.
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:107)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
    ... 203 common frames omitted

Answer 1

您需要向查询添加注释@Modifying。

@Modifying
@Transactional
@Query(value = "DELETE FROM collaborators_competences WHERE (collaborator_id, competence_id)\n" +
    "                                                IN (SELECT collaborator_id, competence_id FROM collaborators_competences\n" +
    "                                                    JOIN collaborators c on collaborators_competences.collaborator_id = c.id\n" +
    "                                                JOIN competences c2 on collaborators_competences.competence_id = c2.id\n" +
    "                                                WHERE c.id = (SELECT collaborators.id FROM collaborators WHERE login = :collabLogin) AND c2.id = :competenceId)", nativeQuery = true)
void deleteByCollaboratorLoginAndCompetenceId(@Param("collabLogin") String login, @Param("competenceId") Long id);




@Transactional
void deleteCollaboratorCompetenceById(CollaboratorCompetenceId id);

使用@Modifying

正如注释所说，它用于修改查询。现在，什么是修改查询。

• 修改查询是向实体写入一些数据的任何查询，即修改实体。这包括更新和删除查询。
• @Modifying 应用于使用 @Query 注释的 JPA 存储库中的自定义查询

注意: 有时您可能会遇到 RollBackException 异常，此时您需要使用 @Transactional 注解来处理逻辑。当您遇到此类异常时，只需使用 @Transactional 注释您的方法或存储库方法签名即可。