我正在使用SpringdataJPA查询连接2个实体，但结果集似乎重复，

我正在尝试使用Spring Data JPA查询联接2个实体，但结果集似乎重复。我只需要与SnippetCollection表的ID相关的Snippet表的字段/列。
MariaDB SQL代码

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); }');

Sping Boot JPA实体

// snippet collections entity
    @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
@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;
    
    @JsonIgnore
    @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
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
    }
]

有没有办法只得到这个结果集？

"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
                }
            ]

我正在使用SpringdataJPA查询连接2个实体，但结果集似乎重复，

2条答案

相关问题

热门标签

最新问答