Java中高效处理列表元素映射：优化N+1查询问题

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

    // N+1查询问题：对于itemPriceCodes中的每个元素，都会执行一次数据库查询
    for (ItemPriceCode ipc : itemPriceCodes) {
        Optional<ManufacturerPriceCodes> mpc = manufacturerPriceCodesRepository
            .findByManufacturerIDAndPriceCodeAndRecordDeleted(
                item.getManufacturerID(),
                ipc.getPriceCode(),
                NOT_DELETED
            );
        if (mpc.isPresent()) {
            ipc.setManufacturerPriceCode(mpc.get().getName());
        }
    }

    // 后续处理，与N+1问题无关
    item.getItemPriceCodes()
        .removeIf(ipc -> DELETED.equals(ipc.getRecordDeleted()));
    return item;
}

import org.springframework.data.jpa.repository.JpaRepository;
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、价格码列表和删除状态批量查询制造商价格码信息。
     *
     * @param manufacturerId 制造商ID
     * @param recordDeleted 记录删除状态
     * @param priceCodes 价格码列表
     * @return 包含价格码和名称的Object数组列表
     */
    @Query("SELECT mpc.priceCode, mpc.name FROM ManufacturerPriceCodes mpc WHERE mpc.manufacturerID = :manufacturerId AND mpc.priceCode IN :priceCodes AND mpc.recordDeleted = :recordDeleted")
    List<Object[]> findPriceCodeAndNameByManufacturerIdAndRecordDeletedAndPriceCodes(
        @Param("manufacturerId") String manufacturerId,
        @Param("recordDeleted") Integer recordDeleted, // 假设NOT_DELETED是Integer类型
        @Param("priceCodes") List<String> priceCodes
    );
}

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

public class ItemService { // 假设这是一个服务类，包含getItemManufacturerPriceCodes方法

    private final ManufacturerPriceCodesRepository manufacturerPriceCodesRepository;
    // ... 其他依赖注入

    public ItemService(ManufacturerPriceCodesRepository manufacturerPriceCodesRepository /* ... */) {
        this.manufacturerPriceCodesRepository = manufacturerPriceCodesRepository;
        // ...
    }

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

        // 1. 提取所有需要查询的价格码
        List<String> priceCodesToQuery = itemPriceCodes.stream()
            .map(ItemPriceCode::getPriceCode)
            .collect(Collectors.toList());

        // 2. 执行批量查询
        // 注意：如果priceCodesToQuery为空，此查询可能返回空列表，或者根据JPA提供商的行为而定。
        // 最好在调用前检查priceCodesToQuery是否为空，以避免不必要的数据库调用。
        if (priceCodesToQuery.isEmpty()) {
            // 如果没有价格码需要查询，直接跳过
            item.getItemPriceCodes()
                .removeIf(ipc -> DELETED.equals(ipc.getRecordDeleted()));
            return item;
        }

        List<Object[]> keyPairs = manufacturerPriceCodesRepository
            .findPriceCodeAndNameByManufacturerIdAndRecordDeletedAndPriceCodes(
                item.getManufacturerID(),
                NOT_DELETED, // 假设NOT_DELETED是一个常量，例如 0
                priceCodesToQuery
            );

        // 3. 将查询结果转换为Map，实现价格码到名称的快速查找
        // keyPairs中的每个Object[]包含 [priceCode, name]
        Map<String, String> priceCodeToMFPNameMap = keyPairs.stream()
            .collect(Collectors.toMap(
                arr -> (String) arr[0], // 价格码作为Map的键
                arr -> (String) arr[1]  // 名称作为Map的值
            ));

        // 4. 遍历原始列表，利用Map进行高效更新
        itemPriceCodes.forEach(ipc -> {
            String manufacturerPriceCodeName = priceCodeToMFPNameMap.get(ipc.getPriceCode());
            if (manufacturerPriceCodeName != null) {
                ipc.setManufacturerPriceCode(manufacturerPriceCodeName);
            }
        });

        // 5. 后续处理（保持不变）
        item.getItemPriceCodes()
            .removeIf(ipc -> DELETED.equals(ipc.getRecordDeleted()));
        return item;
    }
}