Spring Data JPA双向多对多按标签ID查文章用DTO投影报错如何优化？

需实现GET /tag/{tagId}接口，查询所有包含指定ID标签的文章，使用DTO筛选指定字段返回，id为1的标签标题为JAVA33333333，预期返回结构如下：

{
   "id":1,
   "title":"meu camarada a gente filips",
   "createdAt":"17-06-2021",
   "slug":"meu-camarada-a-gente-filips",
   "summary":"Criar conteúdo para blogs  ",
   "tags":[
      {
         "id":1,
         "title":"JAVA33333333"
      },
      {
         "id":2,
         "title":"python33333333"
      }
   ]
},
{
   "id":2,
   "title":"LULA LIVRE",
   "slug":"lula-ladrao",
   "summary":"Criar conteúdo para blogs ",
   "tags":[
      {
         "id":1,
         "title":"JAVA33333333"
      },
      {
         "id":2,
         "title":"python33333333"
      }
   ]
},
{
   "id":3,
   "title":"LULA LIVRE pcsharp",
   "slug":"lula-ladrao-pcsharp",
   "summary":"Criar conteúdo suficiente",
   "tags":[
      {
         "id":1,
         "title":"JAVA33333333"
      }
   ]
}

问题1：接口投影查询报SQL语法错误 #

报错内容：could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
相关代码如下：

投影接口定义 #

public interface PostResponseProjection {
    Long getId();
    String getTitle();
    LocalDateTime getCreatedAt();
    String getSlug();
    String getSummary();
    List<TagProjectionResponse> getTags();
}
public interface TagProjectionResponse {
    Long getId();
    String getTitle();
}

TagRepository代码 #

@Repository
public interface TagRepository extends JpaRepository<Tag, Long> {
 
  @Query("SELECT post.id as id "+ 
        ",post.title as title "+            
        ",post.createdAt as createdAt"+
        ",post.slug as slug"+
        ",post.summary as summary"+
        ",post.tags as tags "+
        "FROM Tag tag INNER JOIN tag.posts post WHERE tag.id=:tagId")
  Set<PostResponseProjection> findAllPostWthByTagId(@Param("tagId") Long id);
}

实体类定义 #

Post实体

@Entity
@Table(name = "post")
@Getter @Setter
@AllArgsConstructor @NoArgsConstructor
@ToString
public class Post {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;   
    private String title;
    @Column(columnDefinition = "TEXT")
    private String body;
    @JsonFormat(shape = Shape.STRING,pattern = "dd-MM-yyyy")
    private LocalDate createdAt = LocalDate.now();
    private String slug;
    private PublishedStatus status;
    @Size(max = 100)
    private String summary;
    
    @ManyToMany(cascade = {
        CascadeType.PERSIST,
        CascadeType.MERGE
    },fetch = FetchType.EAGER)
    @JoinTable(name = "post_tag",
        joinColumns = @JoinColumn(name="post_id"),
        inverseJoinColumns = @JoinColumn(name="tag_id")
    )
    @JsonProperty("tags")
     @JsonIgnoreProperties("posts")    
    private Set<Tag> tags = new HashSet<>();
}

Tag实体

@Entity
@Table(name = "tag")
@Getter @Setter
@AllArgsConstructor @NoArgsConstructor
@ToString
public class Tag {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;    
    @ManyToMany(mappedBy = "tags",fetch = FetchType.EAGER) 
    private Set<Post> posts = new HashSet<>();
}

问题2：JPQL调整后报类型转换错误 #

调整后的JPQL如下：

@Query("SELECT tag.posts FROM Tag tag INNER JOIN tag.posts post WHERE tag.id=:tagId")

报错内容：Requested tuple value [index=0, realType=com.microservices.post.model.Post] cannot be assigned to requested type [java.util.Set]; nested exception is java.lang.IllegalArgumentException: Requested tuple value [index=0, realType=com.microservices.post.model.Post] cannot be assigned to requested type [java.util.Set]

问题3：PostRepository查询正常但存在N+1性能问题 #

PostRepository查询代码如下：

@Query("SELECT post FROM Post post JOIN post.tags as tags where tags.id= :tagId")
 Set<PostResponseProjection> findAllPostByTagsId(@Param("tagId") Long id);

该写法可正常返回结果，但Hibernate会执行6次查询，性能开销过大。

问题根源说明 #

• 问题1报错原因：Spring Data原生接口投影不支持嵌套集合类型的映射，同时投影中getCreatedAt返回类型为LocalDateTime，和实体类的LocalDate类型不匹配，导致结果集映射失败。
• 问题2报错原因：SELECT tag.posts返回的是单个Post对象，无法直接匹配投影中Set类型的tags字段，触发类型转换异常。
• 问题3性能问题原因：JPQL中仅用JOIN做过滤条件，没有加载关联的tags集合，每个Post访问tags属性时都会触发额外查询，导致N+1问题。

调整步骤 #

步骤1：定义类DTO替代接口投影 #

// Tag返回DTO
@Data
@AllArgsConstructor
public class TagDTO {
    private Long id;
    private String title;
}

// 文章返回DTO
@Data
@AllArgsConstructor
public class PostDTO {
    private Long id;
    private String title;
    private LocalDate createdAt;
    private String slug;
    private String summary;
    private Set<TagDTO> tags;
}

步骤2：修改PostRepository查询，用JOIN FETCH避免N+1 #

@Repository
public interface PostRepository extends JpaRepository<Post, Long> {
    @Query("SELECT DISTINCT post FROM Post post JOIN FETCH post.tags tags WHERE tags.id = :tagId")
    Set<Post> findAllPostByTagId(@Param("tagId") Long tagId);
}

说明：JOIN FETCH会让Hibernate用关联查询一次性加载Post和对应的Tag数据，仅执行1次SQL；加DISTINCT是为了在内存中对多对多关联查询返回的重复Post记录去重，不会在SQL层面加DISTINCT影响性能。

步骤3：业务层手动完成实体到DTO的转换 #

// 业务层示例代码
public List<PostDTO> getPostsByTagId(Long tagId) {
    Set<Post> posts = postRepository.findAllPostByTagId(tagId);
    return posts.stream().map(post -> {
        Set<TagDTO> tagDTOS = post.getTags().stream()
                .map(tag -> new TagDTO(tag.getId(), tag.getTitle()))
                .collect(Collectors.toSet());
        return new PostDTO(
                post.getId(),
                post.getTitle(),
                post.getCreatedAt(),
                post.getSlug(),
                post.getSummary(),
                tagDTOS
        );
    }).collect(Collectors.toList());
}

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