Spring Data JPA关联查询结果重复，如何获取指定Snippet列表？

使用Spring Data JPA关联Snippet和SnippetCollection实体查询时，返回结果包含冗余的关联嵌套数据，希望只获取与指定SnippetCollection ID关联的Snippet表字段数据。

一、MariaDB数据库结构及测试数据 #

CREATE TABLE `user` (
    `id` INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(`id`) 
);

CREATE TABLE `snippet_collection` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`title` VARCHAR(34) NOT NULL,
`description` VARCHAR(125) NOT NULL,
`programming_language` VARCHAR(64) NOT NULL,
`date_created` DATE NOT NULL,
`link` VARCHAR(512),

CONSTRAINT `fk_sc_user`
   FOREIGN KEY (user_id) REFERENCES user(id),

PRIMARY KEY (`id`)
);

CREATE TABLE `snippet` (
  `id` int NOT NULL AUTO_INCREMENT,
  `snippet_collection_id` int NOT NULL,
  `title` VARCHAR(34) NOT NULL,
  `is_public` BOOLEAN DEFAULT false,
  `programming_language` VARCHAR(64) NOT NULL,
  `date_created` DATE NOT NULL,
  `code` TEXT,
  
  CONSTRAINT `fk_s_snippet_collection`
    FOREIGN KEY (snippet_collection_id) REFERENCES `snippet_collection`(id),
    
  PRIMARY KEY (`id`)
);

INSERT INTO `user` (`id`) VALUES (1);
INSERT INTO `user` (`id`) VALUES (2);

INSERT INTO `snippet_collection`(`user_id`, `title`, `description`, `programming_language`, `date_created`) 
VALUES(1, 'http servlet code snippets', 'Lorem ipsom dolor amet', 'java', CURDATE());

INSERT INTO `snippet_collection`(`user_id`, `title`, `description`, `programming_language`, `date_created`) 
VALUES(2, 'http php code ', 'Lorem ipsom asda dolor amet', 'php', CURDATE());

INSERT INTO `snippet`(`snippet_collection_id`, `title`, `is_public`, `programming_language`, `date_created`, `code`)  
VALUES(1, 'luv2code angular http', FALSE, 'java', CURDATE(), 'const x =>{ console.log(y); }');

INSERT INTO `snippet`(`snippet_collection_id`, `title`, `is_public`, `programming_language`, `date_created`, `code`)  
VALUES(1, 'luv2code java http', FALSE, 'java', CURDATE(), 'let x =>{ console.log(y); }');

INSERT INTO `snippet`(`snippet_collection_id`, `title`, `is_public`, `programming_language`, `date_created`, `code`)  
VALUES(1, 'luv2code spring boot http', FALSE, 'java', CURDATE(), 'var x =>{ console.log(y); }');

INSERT INTO `snippet`(`snippet_collection_id`, `title`, `is_public`, `programming_language`, `date_created`, `code`)  
VALUES(2, 'asddasd', FALSE, 'javax', CURDATE(), 'var x =>{ consssole.log(y); }');

二、Spring Boot JPA实体类 #

SnippetCollection实体 #

@Entity
@Table(name = "snippet_collection")
public class SnippetCollection {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String title;
    private String description;

    @Column(name = "programming_language")
    private String programmingLanguage;

    @Temporal(TemporalType.DATE)
    @Column(name = "date_created")
    private Date dateCreated;

    private String link;

    @JsonIgnore
    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;

    @OneToMany(mappedBy = "snippetCollection", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Snippet> snippets;

    public SnippetCollection() {}

    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
    public String getTitle() { return title; }
    public void setTitle(String title) { this.title = title; }
    public String getDescription() { return description; }
    public void setDescription(String description) { this.description = description; }
    public String getProgrammingLanguage() { return programmingLanguage; }
    public void setProgrammingLanguage(String programmingLanguage) { this.programmingLanguage = programmingLanguage; }
    public Date getDateCreated() { return dateCreated; }
    public void setDateCreated(Date dateCreated) { this.dateCreated = dateCreated; }
    public String getLink() { return link; }
    public void setLink(String link) { this.link = link; }
    public User getUser() { return user; }
    public void setUser(User user) { this.user = user; }
    public List<Snippet> getSnippets() { return snippets; }
    public void setSnippets(List<Snippet> snippets) { this.snippets = snippets; }
}

Snippet实体 #

@Entity
@Table(name = "snippet")
public class Snippet {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String title;

    @Column(name = "is_public")
    private boolean isPublic;

    @Column(name = "programming_language")
    private String programmingLanguage;

    @Temporal(TemporalType.DATE)
    @Column(name = "date_created")
    private Date dateCreated;

    @Lob
    private String code;
    
    @ManyToOne
    @JoinColumn(name = "snippet_collection_id")
    private SnippetCollection snippetCollection;

    public Snippet() {}

    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
    public String getTitle() { return title; }
    public void setTitle(String title) { this.title = title; }
    public boolean isPublic() { return isPublic; }
    public void setPublic(boolean isPublic) { this.isPublic = isPublic; }
    public String getProgrammingLanguage() { return programmingLanguage; }
    public void setProgrammingLanguage(String programmingLanguage) { this.programmingLanguage = programmingLanguage; }
    public Date getDateCreated() { return dateCreated; }
    public void setDateCreated(Date dateCreated) { this.dateCreated = dateCreated; }
    public String getCode() { return code; }
    public void setCode(String code) { this.code = code; }
    public SnippetCollection getSnippetCollection() { return snippetCollection; }
    public void setSnippetCollection(SnippetCollection snippetCollection) { this.snippetCollection = snippetCollection; }
}

三、当前Repository查询代码 #

@Repository
public interface SnippetCollectionRepository extends CrudRepository<SnippetCollection, Integer> {

    @Query("SELECT s FROM Snippet s JOIN s.snippetCollection r WHERE r.id = 1 ")
    List<Snippet> findSnippetCollectionBySnippetId(int id);
}

四、当前Postman返回结果 #

[
    {
        "id": 1,
        "title": "luv2code angular http",
        "programmingLanguage": "java",
        "dateCreated": "2022-10-18",
        "code": "const x =>{ console.log(y); }",
        "snippetCollection": {
            "id": 1,
            "title": "http servlet code snippets",
            "description": "Lorem ipsom dolor amet",
            "programmingLanguage": "java",
            "dateCreated": "2022-10-18",
            "link": null,
            "snippets": [
                {
                    "id": 1,
                    "title": "luv2code angular http",
                    "programmingLanguage": "java",
                    "dateCreated": "2022-10-18",
                    "code": "const x =>{ console.log(y); }",
                    "public": false
                },
                {
                    "id": 2,
                    "title": "luv2code java http",
                    "programmingLanguage": "java",
                    "dateCreated": "2022-10-18",
                    "code": "let x =>{ console.log(y); }",
                    "public": false
                },
                {
                    "id": 3,
                    "title": "luv2code spring boot http",
                    "programmingLanguage": "java",
                    "dateCreated": "2022-10-18",
                    "code": "var x =>{ console.log(y); }",
                    "public": false
                }
            ]
        },
        "public": false
    },
    {
        "id": 2,
        "title": "luv2code java http",
        "programmingLanguage": "java",
        "dateCreated": "2022-10-18",
        "code": "let x =>{ console.log(y); }",
        "snippetCollection": {
            "id": 1,
            "title": "http servlet code snippets",
            "description": "Lorem ipsom dolor amet",
            "programmingLanguage": "java",
            "dateCreated": "2022-10-18",
            "link": null,
            "snippets": [
                {
                    "id": 1,
                    "title": "luv2code angular http",
                    "programmingLanguage": "java",
                    "dateCreated": "2022-10-18",
                    "code": "const x =>{ console.log(y); }",
                    "public": false
                },
                {
                    "id": 2,
                    "title": "luv2code java http",
                    "programmingLanguage": "java",
                    "dateCreated": "2022-10-18",
                    "code": "let x =>{ console.log(y); }",
                    "public": false
                },
                {
                    "id": 3,
                    "title": "luv2code spring boot http",
                    "programmingLanguage": "java",
                    "dateCreated": "2022-10-18",
                    "code": "var x =>{ console.log(y); }",
                    "public": false
                }
            ]
        },
        "public": false
    },
    {
        "id": 3,
        "title": "luv2code spring boot http",
        "programmingLanguage": "java",
        "dateCreated": "2022-10-18",
        "code": "var x =>{ console.log(y); }",
        "snippetCollection": {
            "id": 1,
            "title": "http servlet code snippets",
            "description": "Lorem ipsom dolor amet",
            "programmingLanguage": "java",
            "dateCreated": "2022-10-18",
            "link": null,
            "snippets": [
                {
                    "id": 1,
                    "title": "luv2code angular http",
                    "programmingLanguage": "java",
                    "dateCreated": "2022-10-18",
                    "code": "const x =>{ console.log(y); }",
                    "public": false
                },
                {
                    "id": 2,
                    "title": "luv2code java http",
                    "programmingLanguage": "java",
                    "dateCreated": "2022-10-18",
                    "code": "let x =>{ console.log(y); }",
                    "public": false
                },
                {
                    "id": 3,
                    "title": "luv2code spring boot http",
                    "programmingLanguage": "java",
                    "dateCreated": "2022-10-18",
                    "code": "var x =>{ console.log(y); }",
                    "public": false
                }
            ]
        },
        "public": false
    }
]

五、期望返回结果 #

[
    {
        "id": 1,
        "title": "luv2code angular http",
        "programmingLanguage": "java",
        "dateCreated": "2022-10-18",
        "code": "const x =>{ console.log(y); }",
        "public": false
    },
    {
        "id": 2,
        "title": "luv2code java http",
        "programmingLanguage": "java",
        "dateCreated": "2022-10-18",
        "code": "let x =>{ console.log(y); }",
        "public": false
    },
    {
        "id": 3,
        "title": "luv2code spring boot http",
        "programmingLanguage": "java",
        "dateCreated": "2022-10-18",
        "code": "var x =>{ console.log(y); }",
        "public": false
    }
]

解决方案 #

方法1：调整Repository设计，直接从Snippet实体查询 #

创建SnippetRepository，通过关联ID直接过滤，更符合业务语义：

@Repository
public interface SnippetRepository extends CrudRepository<Snippet, Integer> {
    List<Snippet> findBySnippetCollectionId(int collectionId);
}

调用时传入目标SnippetCollection的ID即可，Spring Data JPA会自动生成对应的SQL查询语句。

方法2：忽略关联字段的JSON序列化 #

当前冗余数据是因为Snippet中的snippetCollection字段被序列化，而该字段又嵌套了snippets列表。只需在Snippet类的关联字段上添加@JsonIgnore注解，阻止其被序列化输出：

@JsonIgnore // 添加此注解
@ManyToOne
@JoinColumn(name = "snippet_collection_id")
private SnippetCollection snippetCollection;

修改后返回的JSON将只保留Snippet自身的字段，不再包含关联的集合数据。

方法3：使用投影精准控制返回字段 #

如果只需要Snippet的部分字段，可以定义投影接口来指定返回内容：

public interface SnippetProjection {
    int getId();
    String getTitle();
    String getProgrammingLanguage();
    Date getDateCreated();
    String getCode();
    boolean isPublic();
}

然后在Repository中返回投影类型：

@Repository
public interface SnippetRepository extends CrudRepository<Snippet, Integer> {
    List<SnippetProjection> findBySnippetCollectionId(int collectionId);
}

这种方式可以严格控制返回的字段，彻底避免冗余数据。

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