Spring Boot多行SQL结果按slpcode分组返回JSON及批量操作问题

你使用的businesspartnerparamsalesperson表是销售人员和客户ID的关联表，同一个slpCode对应多条关联记录，现有代码中findVendedorBySlpCode方法要求返回单个Vendedor实体，ORM框架检测到查询返回94条结果不符合单个返回的预期，因此抛出NonUniqueResultException异常。

1. 修正实体类错误 #

现有Vendedor类的setSlpCode方法存在赋值逻辑错误，修正后代码如下：

public void setSlpCode(Long slpCode) {
    this.slpCode = slpCode;
}

2. 新增返回结果DTO #

新建DTO类匹配你需要的JSON结构：

import lombok.AllArgsConstructor;
import lombok.Data;
import java.util.List;

@Data
@AllArgsConstructor
public class VendedorRespDTO {
    private Long slpcode;
    private List<Long> businesspartnerparamid;
}

3. 修改Repository层代码 #

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface VendedorRepository extends JpaRepository<Vendedor, Long> {
    // 返回指定slpCode对应的所有关联记录
    List<Vendedor> findBySlpCode(Long slpCode);
    
    // 新增批量更新方法，用于客户ID转移
    @Modifying
    @Query("update Vendedor v set v.slpCode = :targetSlpCode where v.slpCode = :sourceSlpCode")
    int batchUpdateSlpCode(@Param("sourceSlpCode") Long sourceSlpCode, @Param("targetSlpCode") Long targetSlpCode);

    // 新增删除方法，用于删除指定销售人员的所有关联记录
    @Modifying
    @Query("delete from Vendedor v where v.slpCode = :slpCode")
    int deleteBySlpCode(@Param("slpCode") Long slpCode);
}

4. 修改Service层代码 #

import org.springframework.transaction.annotation.Transactional;

@Service
@AllArgsConstructor
public class ApiService {

    private final VendedorRepository vendedorRepository;
    private final ClienteRepository clienteRepository;

    public Optional<VendedorRespDTO> findVendedorByCode (Long slpCode) {
        List<Vendedor> vendedorList = vendedorRepository.findBySlpCode(slpCode);
        if (vendedorList.isEmpty()) {
            return Optional.empty();
        }
        // 提取所有关联的客户ID
        List<Long> customerIds = vendedorList.stream()
                .map(Vendedor::getBusinessPartnerParamID)
                .toList();
        return Optional.of(new VendedorRespDTO(slpCode, customerIds));
    }

    public Optional<Cliente> findClienteByCode (String code){
        return clienteRepository.findClienteByCardCode(code);
    }

    // 客户ID转移方法
    @Transactional
    public int transferCustomer(Long sourceSlpCode, Long targetSlpCode) {
        return vendedorRepository.batchUpdateSlpCode(sourceSlpCode, targetSlpCode);
    }

    // 删除指定销售人员所有关联记录方法
    @Transactional
    public int deleteSalespersonRelation(Long slpCode) {
        return vendedorRepository.deleteBySlpCode(slpCode);
    }
}

5. 修改Controller层代码 #

import org.springframework.http.ResponseEntity;

@CrossOrigin
@RestController
@EnableAutoConfiguration
@RequestMapping("/vendedor")
@AllArgsConstructor
public class ApiController {

    private ApiService apiService;

    @GetMapping(value="/{slpCode}", produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity<VendedorRespDTO> findVendedorBySlpCode (@PathVariable Long slpCode){
        return apiService.findVendedorByCode(slpCode)
                .map(ResponseEntity::ok)
                .orElse(ResponseEntity.notFound().build());
    }

    // 客户ID转移接口
    @PutMapping("/transfer")
    public ResponseEntity<Integer> transferCustomer(@RequestParam Long sourceSlpCode, @RequestParam Long targetSlpCode) {
        int updatedCount = apiService.transferCustomer(sourceSlpCode, targetSlpCode);
        return ResponseEntity.ok(updatedCount);
    }

    // 删除销售人员关联记录接口
    @DeleteMapping("/{slpCode}")
    public ResponseEntity<Integer> deleteSalespersonRelation(@PathVariable Long slpCode) {
        int deletedCount = apiService.deleteSalespersonRelation(slpCode);
        return ResponseEntity.ok(deletedCount);
    }
}

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