Spring Data JDBC中@Query自定义查询触发额外SQL问题解决

问题核心是Spring Data JDBC对带有@MappedCollection注解的实体，默认会在加载主实体后自动执行额外查询来加载关联数据，哪怕你用@Query写了JOIN查询也会触发N+1查询。要让它只执行你的自定义查询，按以下步骤操作：

1. 编写完整的JOIN查询语句
   确保自定义SQL通过LEFT JOIN（或INNER JOIN，根据业务需求）一次性获取customer、customer_order、customer_profile的所有需要字段，用别名区分不同表的字段避免冲突，示例：

   SELECT 
       c.id AS customer_id, c.username AS customer_username,
       co.id AS order_id, co.order_number AS order_number, co.amount AS order_amount,
       cp.id AS profile_id, cp.email AS profile_email, cp.phone AS profile_phone
   FROM customer c
   LEFT JOIN customer_order co ON c.id = co.customer_id
   LEFT JOIN customer_profile cp ON c.id = cp.customer_id
   WHERE c.username = :username
   

2. 使用自定义投影代替原实体
   不要让Repository方法返回带有@MappedCollection的原Customer Record，而是定义一个包含所有关联数据的投影Record，结构匹配查询结果：

   // 主投影，包含客户及其关联数据
   public record CustomerWithAllData(
       Long customer_id,
       String customer_username,
       List<CustomerOrderProjection> orders,
       CustomerProfileProjection profile
   ) {}
   
   // 订单子投影
   public record CustomerOrderProjection(
       Long order_id,
       String order_number,
       BigDecimal order_amount
   ) {}
   
   // 客户资料子投影
   public record CustomerProfileProjection(
       Long profile_id,
       String profile_email,
       String profile_phone
   ) {}
   

3. Repository方法返回投影类型
   在Repository接口中，用@Query指定上述SQL，返回自定义投影类型：

   @Query("""
       SELECT 
           c.id AS customer_id, c.username AS customer_username,
           co.id AS order_id, co.order_number AS order_number, co.amount AS order_amount,
           cp.id AS profile_id, cp.email AS profile_email, cp.phone AS profile_phone
       FROM customer c
       LEFT JOIN customer_order co ON c.id = co.customer_id
       LEFT JOIN customer_profile cp ON c.id = cp.customer_id
       WHERE c.username = :username
       """)
   CustomerWithAllData findByUsername(String username);
   

这样Spring Data JDBC会直接将JOIN后的结果集映射到投影对象，不会触发额外的关联查询——因为投影中没有@MappedCollection注解，框架不会自动执行关联数据的加载逻辑，只会执行你定义的这一条SQL。

另外需要注意：对于一对多关联（比如customer和customer_order），JOIN后的结果集会有重复的客户数据，Spring Data JDBC会自动帮你合并成单个主投影对象，将重复的订单数据整理成列表，无需手动处理。

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