I'm joining 2 entities using Spring data JPA query, but the result set seems to be repeating,-CodePudding

I'm trying to join 2 entities using Spring data JPA query, but the result set seems to be repeating. I only want the fields/columns of Snippet table that is related to the id of SnippetCollection table.

MariaDB SQL code

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 Entities

    // 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;
    }

}

Here's my query in the 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);
}

Result I'm getting from 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
    }
]

Is there a way to only have this result set?

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

CodePudding user response：

Better to create DTO to convert from entity to your POJO object. That way you can control the response and send limited data to the client.

CodePudding user response：

It's all good now. Added @JsonIgnore annotation on

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