spring - 管理 JPA 中查询的更新顺序

Question

我正在创建一个简单的看板应用程序，如下所示，每个看板由一系列阶段组成，每个阶段都有一个级别字段来定义其位置。我希望能够随意添加、移动和删除关卡，所以我必须保持每个关卡的级别一致，足够简单。

@Entity
@Table(name = "kanbans")
data class Kanban (
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "id", nullable = false)
        var id: Int? = null,

        @get:NotNull
        @get:NotBlank
        @Column(name = "name", nullable = false)
        var name: String? = null,

        @get:NotNull
        @get:NotBlank
        @Column(name = "description", nullable = false)
        var description: String? = null,

        @get:NotNull
        @Column(name = "closed", nullable = false)
        var closed: Boolean? = null,

        @get:NotNull
        @Column(name = "created_at", nullable = false)
        var createdAt: LocalDateTime? = null,

        @get:NotNull
        @Column(name = "updated_at", nullable = false)
        var updatedAt: LocalDateTime? = null,
)

@Entity
@Table(name = "stages")
data class Stage (
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "id", nullable = false)
        var id: Int? = null,

        @get:NotNull
        @get:NotBlank
        @Column(name = "name", nullable = false)
        var name: String? = null,

        @get:NotNull
        @get:NotBlank
        @Column(name = "description", nullable = false)
        var description: String? = null,

        @get:NotNull
        @Column(name = "closed", nullable = false)
        var closed: Boolean? = null,

        @get:NotNull
        @Column(name = "level", nullable = false)
        var level: Int? = null,

        @OneToMany(fetch = FetchType.LAZY, mappedBy = "stage")
        var tasks: List<Task> = ArrayList(),

        @get:NotNull
        @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "kanban_id", nullable = false)
        var kanban: Kanban? = null,

        @get:NotNull
        @Column(name = "created_at", nullable = false)
        var createdAt: LocalDateTime? = null,

        @get:NotNull
        @Column(name = "updated_at", nullable = false)
        var updatedAt: LocalDateTime? = null,
)

创建第一个阶段时，它始终将其级别分配为 0，然后在添加新级别时，级别将定义阶段列表中的阶段位置。问题是，当我尝试更新以前的现有阶段以让位给新阶段时，我发现完成这项工作的唯一方法是saveAndFlush循环调用，但我发现这不是一个好主意。

@Repository
interface StageRepository : JpaRepository<Stage, Int> {
    fun findAllByKanbanAndLevelGreaterThanEqualOrderByLevelDesc(kanban: Kanban, level: Int): List<Stage>

    @Modifying
    @Transactional
    @Query("UPDATE Stage s SET s.level = s.level + 1 WHERE s.kanban = :kanban AND s.level >= :level")
    fun incrementLevelForKanbanStagesWhereLevelIsGreaterThan(kanban: Kanban, level: Int)
}

该incrementLevelForKanbanStagesWhereLevelIsGreaterThan方法失败，因为数据库具有唯一约束level并kanban_id出现以下错误：

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "stages_kanban_id_level_key"
  Detalhe: Key (kanban_id, level)=(337, 1) already exists.

这显然正在发生，因为它试图在将级别 1 更新为级别 2 之前将级别 0 更新为级别 1，因此我尝试过：

@Transactional
    @Query("UPDATE Stage s SET s.level = s.level + 1 WHERE s.kanban = :kanban AND s.level >= :level ORDER BY s.level DESC")
    fun incrementLevelForKanbanStagesWhereLevelIsGreaterThan(kanban: Kanban, level: Int)

不编译，

@Service
@Transactional
class StageCrudService: CrudService<Stage, Int, StageRepository, StageValidationService>() {
    @Throws(ValidationException::class)
    override fun create(model: Stage): Stage {
        prepareToCreate(model)
        validationService.canSave(model)
        incrementKanbanStageLevels(model)
        return repository.save(model)
    }

    private fun prepareToCreate(model: Stage) {
        val now = LocalDateTime.now()
        val closed = model.closed ?: false
        model.closed = closed
        model.createdAt = now
        model.updatedAt = now
        model.level = model.level ?: 0
    }

    private fun incrementKanbanStageLevels(model: Stage) {
        val level = model.level ?: 0
        val stages = repository.findAllByKanbanAndLevelGreaterThanEqualOrderByLevelDesc(model.kanban!!, level)
        stages.forEach { stage ->
            stage.level = stage.level?.plus(1)
        }
        repository.saveAll(stages)
        repository.flush()
    }
}

和

private fun incrementKanbanStageLevels(model: Stage) {
        val level = model.level ?: 0
        val stages = repository.findAllByKanbanAndLevelGreaterThanEqualOrderByLevelDesc(model.kanban!!, level)
        stages.forEach { stage ->
            stage.level = stage.level?.plus(1)
            repository.save(stage)
        }
        repository.flush()
    }

但两者都以与查询相同的方式失败。现在的问题是：

有没有更好的方法来管理这种情况的更新顺序，而不是这样做：

private fun incrementKanbanStageLevels(model: Stage) {
        val level = model.level ?: 0
        val stages = repository.findAllByKanbanAndLevelGreaterThanEqualOrderByLevelDesc(model.kanban!!, level)
        stages.forEach { stage ->
            stage.level = stage.level?.plus(1)
            repository.saveAndFlush(stage)
        }
    }

标签： springkotlinjpaspring-data-jpa

Answer 1

在我看来，您可能正在尝试实现可以​​通过 JPA@OrderColumn注释为您管理的东西：

https://docs.oracle.com/javaee/7/api/javax/persistence/OrderColumn.html

指定用于维护列表的持久顺序的列。持久性提供者负责在检索时和在数据库中维护订单。持久性提供者负责在刷新到数据库时更新排序以反映影响列表的任何插入、删除或重新排序。

要使用它，您需要建立双向关系，并且当项目被添加到列表中或从列表中删除时，级别应该由您的 JPA 提供程序维护

@Entity
@Table(name = "kanbans")
data class Kanban (
        .....

        @get:NotNull
        @get:NotBlank
        @OrderColumn(name = "level")
        @OneToMany(fetch = FetchType.LAZY, mappedBy = "kanban")
        var stage: List<Stage> = ArrayList()

       .....
}

因此，您可以删除和添加项目（在任何位置），并且将为您维护序列。