Spring Data JPA中利用Map优化列表元素批处理：告别N+1查询

private Item getItemManufacturerPriceCodes(Item item) {
    List<ItemPriceCode> itemPriceCodes = item.getItemPriceCodes();
    // 循环遍历ItemPriceCode列表
    for (ItemPriceCode ipc : itemPriceCodes) {
        // 每次循环都执行一次数据库查询
        Optional<ManufacturerPriceCodes> mpc = manufacturerPriceCodesRepository
            .findByManufacturerIDAndPriceCodeAndRecordDeleted(
                item.getManufacturerID(), 
                ipc.getPriceCode(), 
                NOT_DELETED
            );
        if (mpc.isPresent()) {
            ipc.setManufacturerPriceCode(mpc.get().getName());
        }
    }
    // 移除标记为已删除的ItemPriceCode
    item.getItemPriceCodes()
        .removeIf(ipc -> DELETED.equals(ipc.getRecordDeleted()));
    return item;
}

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;

public interface ManufacturerPriceCodesRepository extends JpaRepository<ManufacturerPriceCodes, Long> {

    /**
     * 根据制造商ID、记录状态和ItemPriceCode列表批量查询ItemPriceCode的ID及其对应的ManufacturerPriceCodes名称。
     *
     * @param manufacturerId 制造商ID
     * @param notDeleted 记录未删除状态
     * @param itemPriceCodes 要查询的ItemPriceCode实体列表
     * @return 包含[ItemPriceCode ID, ManufacturerPriceCodes Name]对的列表
     */
    @Query("SELECT ipc.id, mpc.name FROM ManufacturerPriceCodes mpc JOIN mpc.priceCode ipc WHERE mpc.manufacturerID = :manufacturerId AND ipc IN :itemPriceCodes AND mpc.recordDeleted = :notDeleted")
    List<Object[]> findMFPNameByIdAndRecordDeletedAndPriceCodes(
        @Param("manufacturerId") String manufacturerId, 
        @Param("notDeleted") String notDeleted, 
        @Param("itemPriceCodes") List<ItemPriceCode> itemPriceCodes
    );
}

import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class ItemService { // 假设这是一个服务类

    private final ManufacturerPriceCodesRepository manufacturerPriceCodesRepository;

    // 构造器注入或其他方式获取repository实例
    public ItemService(ManufacturerPriceCodesRepository manufacturerPriceCodesRepository) {
        this.manufacturerPriceCodesRepository = manufacturerPriceCodesRepository;
    }

    private Item getItemManufacturerPriceCodes(Item item) {
        List<ItemPriceCode> itemPriceCodes = item.getItemPriceCodes();

        // 1. 执行批量查询，一次性获取所有相关的ManufacturerPriceCodes名称
        List<Object[]> keyPairs = manufacturerPriceCodesRepository
            .findMFPNameByIdAndRecordDeletedAndPriceCodes(
                item.getManufacturerID(), 
                NOT_DELETED, 
                itemPriceCodes
            );

        // 2. 将查询结果转换为Map，方便快速查找
        // Map的键为ItemPriceCode的ID，值为ManufacturerPriceCodes的名称
        Map<String, String> ipcToMFPNameMap = keyPairs.stream()
            .collect(Collectors.toMap(
                x -> String.valueOf(x[0]), // 假设ipc.id是String类型，或者需要转换为String
                x -> String.valueOf(x[1])  // mpc.name是String类型
            ));

        // 3. 遍历ItemPriceCode列表，从Map中获取并设置ManufacturerPriceCode名称
        itemPriceCodes.forEach(ipc -> {
            String mfpName = ipcToMFPNameMap.get(ipc.getId()); // 使用ItemPriceCode的ID作为键进行查找
            if (mfpName != null) {
                ipc.setManufacturerPriceCode(mfpName);
            }
        });

        // 4. 移除标记为已删除的ItemPriceCode (保持原有逻辑)
        item.getItemPriceCodes()
            .removeIf(ipc -> DELETED.equals(ipc.getRecordDeleted()));

        return item;
    }
}