Spring Data JPA自定义多表更新查询失效，如何实现物料归还业务数据更新？

• 物料归还时从MaterialWithdraw表对应用户最新领用记录的领用数量中，扣减本次归还数量
• 剩余归还数量存入MaterialReturn表的pending字段
• 调用更新接口时，将MaterialReturn同记录的pending字段值同步到piece字段

• JPQL语法错误：不能在同一个@Query中同时编写查询和更新语句，两类操作需要拆分实现
• 注解误用：@RequestParam是Controller层接收HTTP参数的注解，Repository层参数无需添加该注解
• 缺少必要注解：数据修改类操作必须搭配@Modifying和@Transactional注解，才会实际执行数据库写入操作
• 拼写错误：方法名、表名存在拼写错误（udpdate应为update、WithdrwMaterial应为实体类对应的MaterialWithdraw）

1. MaterialReturnRepository 代码 #

package com.nilmani.workmanagement.repository

import com.nilmani.workmanagement.entity.MaterialReturn
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Modifying
import org.springframework.data.jpa.repository.Query
import javax.transaction.Transactional

interface MaterialReturnRepository : JpaRepository<MaterialReturn?, Long?> {

    /**
     * 将指定记录的pending值同步到piece字段
     * @param depositId 关联的单据id
     * @return 受影响的行数
     */
    @Modifying
    @Transactional
    @Query("update MaterialReturn m set m.piece = m.pending where m.depositId = :depositId")
    fun syncPendingToPiece(depositId: Long): Int

    /**
     * 查询指定单据对应的待同步pending值
     * @param depositId 关联的单据id
     * @return pending字段值
     */
    @Query("select m.pending from MaterialReturn m where m.depositId = :depositId")
    fun getPendingByDepositId(depositId: Long): Long
}

2. MaterialWithdrawRepository 代码 #

需要新增领用表的Repository实现扣减逻辑：

package com.nilmani.workmanagement.repository

import com.nilmani.workmanagement.entity.MaterialWithdraw
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Modifying
import org.springframework.data.jpa.repository.Query
import javax.transaction.Transactional

interface MaterialWithdrawRepository : JpaRepository<MaterialWithdraw?, Long?> {

    /**
     * 扣减用户最新领用记录的物料数量
     * @param userId 领用用户id
     * @param materialId 物料id
     * @param returnNum 本次归还数量
     * @return 受影响的行数
     */
    @Modifying
    @Transactional
    @Query(value = "update material_withdraw w set w.withdraw_num = w.withdraw_num - :returnNum where w.user_id = :userId and w.material_id = :materialId order by w.create_time desc limit 1", nativeQuery = true)
    fun deductReturnQuantity(userId: Long, materialId: Long, returnNum: Long): Int
}

3. 业务层组装逻辑 #

在Service层把多步操作组装为完整业务流程：

@Service
class MaterialReturnService(
    private val materialReturnRepository: MaterialReturnRepository,
    private val materialWithdrawRepository: MaterialWithdrawRepository
) {
    fun handleMaterialReturn(userId: Long, materialId: Long, returnNum: Long, depositId: Long) {
        // 第一步：扣减领用表中用户最新领用记录的数量
        materialWithdrawRepository.deductReturnQuantity(userId, materialId, returnNum)
        // 第二步：将return表的pending值同步到piece字段
        materialReturnRepository.syncPendingToPiece(depositId)
    }
}

内容的提问来源于stack exchange，提问作者Mama