How to query a many to many relationship in spring boot repository

Question

I am trying to have the api return a list of notes, associated by a many to many relationship with labels, given a label id. Spring boot automatically created a bridge table called notes_tables with a notes_id field and a labels_id field. Spring Boot also created a notes table and a labels table. I attempted the following:

@Query(value="select * from notes join notes_labels on note.id=notes_id join labels on labels_id=labels.id where labels_id=:lid", nativeQuery=true)
        public List<Note> findNotesForLabel(@Param("lid") int labelId);

I just need to get this to work but I am specifically curious if I can get it to work with jpa method query. Any query will do as long as it works though.

EDIT: Entities Note.java

package com.example.maapi.models;

import com.fasterxml.jackson.annotation.JsonIgnore;
import javax.persistence.*;
import java.util.List;
import java.util.Objects;

@Entity
@Table(name = "notes")
public class Note {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String note;
    private String title;
    private String status = "private";

    @ManyToOne
    @JsonIgnore
    private User user;

    @ManyToOne
    @JsonIgnore
    private Folder folder;

   @ManyToMany
   @JsonIgnore

   private List<Label> labels;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public Folder getFolder() {
        return folder;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public void setFolder(Folder folder) {
        this.folder = folder;
    }

   public List<Label> getLabels() {
       return labels;
   }

   public void setLabels(List<Label> labels) {
       this.labels = labels;
   }


    @Override
    public boolean equals(Object o) {
        if (o == this)
            return true;
        if (!(o instanceof Note)) {
            return false;
        }
        Note note = (Note) o;
        return id == note.id && Objects.equals(note, note.note) && 
 Objects.equals(title, note.title) && Objects.equals(status, 
 note.status) && Objects.equals(user, note.user) && 
 Objects.equals(folder, note.folder) && Objects.equals(labels, 
 note.labels);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, note, title, status, user, folder, 
 labels);
    }

}

Label.java

        package com.example.maapi.models;

        import com.fasterxml.jackson.annotation.JsonIgnore;

        import javax.persistence.*;
        import java.util.List;
        import java.util.Objects;

        @Entity
        @Table(name = "labels")
        public class Label {
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private int id;
        private String title;
        private String status = "private";

        @ManyToOne
        @JsonIgnore
        private User user;

        @ManyToOne
        @JsonIgnore
        private Folder folder;

        @ManyToMany(mappedBy = "labels")
        @JsonIgnore
        private List<Note> notes;

        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 Folder getFolder() {
            return folder;
        }

        public void setFolder(Folder folder) {
            this.folder = folder;
        }

        public List<Note> getNotes() {
           return notes;
        }

       public void setNotes(List<Note> notes) {
           this.notes = notes;
       }

        public String getStatus() {
            return status;
        }

        public void setStatus(String status) {
            this.status = status;
        }

        public User getUser() {
            return user;
        }

        public void setUser(User user) {
            this.user = user;
        }


        @Override
        public boolean equals(Object o) {
            if (o == this)
                return true;
            if (!(o instanceof Label)) {
                return false;
            }
            Label label = (Label) o;
            return id == label.id && Objects.equals(title, label.title) && 
        Objects.equals(status, label.status) && Objects.equals(user, 
         label.user) && Objects.equals(folder, label.folder) && 
         Objects.equals(notes, label.notes);
        }

        @Override
        public int hashCode() {
            return Objects.hash(id, title, status, user, folder, notes);
        }

        }

Services: NoteService.java

package com.example.maapi.services;

import com.example.maapi.models.Folder;
import com.example.maapi.models.Note;
import com.example.maapi.models.User;
import com.example.maapi.repositories.FolderRepo;
import com.example.maapi.repositories.NoteRepo;
import com.example.maapi.repositories.UserRepo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class NoteService {
    @Autowired
    NoteRepo noteRepo;
    @Autowired
    UserRepo userRepo;
    @Autowired
    FolderRepo folderRepo;

    public List<Note> findAllNotes(){
        return noteRepo.findAllNotes();
    }

    public Note findNoteById(int noteId){
        return noteRepo.findNoteById(noteId);
    }

    public List<Note> findNotesByUser(int userId){
        return noteRepo.findNotesByUser(userId);
    }

    public Note createNoteForUser(int userId, Note note){
        User user = userRepo.findUserById(userId);
        note.setUser(user);
        return noteRepo.save(note);
    }

    public List<Note> findNotesByFolder(int folderId){
        return noteRepo.findNotesByFolder(folderId);
    }

    public Note createNoteForFolder(int folderId, Note note){
        Folder folder = folderRepo.findFolderById(folderId);
        note.setFolder(folder);
        note.setUser(folder.getUser());
        return noteRepo.save(note);
    }

    public int updateNote(int noteId, Note updatedNote){
        Note note = noteRepo.findNoteById(noteId);
        updatedNote.setUser(note.getUser());
        updatedNote.setFolder(note.getFolder());
        noteRepo.save(updatedNote);
        if(updatedNote.equals(note)){
            return 1;
        } else {
            return 0;
        }
    }

    public int deleteNote(int noteId){
        noteRepo.deleteById(noteId);
        if(noteRepo.findNoteById(noteId) == null) {
            return 1;
        } else {
            return 0;
        }
    }

    // SEARCH IMPLEMENTATION

    public List<Note> searchForNote(String note){
        return noteRepo.searchForNote(note);
    }

}

LabelService.java

Look here my friend https://stackoverflow.com/questions/33438483/spring-data-jpa-query-manytomany — Konstantin Ziubin, Apr 20 '20 at 11:35

Harisudha · Answer 1 · 2020-04-21T02:32:18.710

Try this one!

SELECT * FROM notes n INNER JOIN notes_labels nl ON nl.notes_id = n.note_id WHERE nl.labels_id = ?1

Edit:

@Entity
@Table(name = "notes")
@NamedNativeQuery(name = "Note.getNoteByLabel", resultSetMapping = "getNote",
query = "SELECT n.id,n.note,n.title,n.status FROM notes n INNER JOIN notes_labels nl ON nl.notes_id = n.note_id WHERE nl.labels_id = ?1")
@SqlResultSetMapping(name = "getNote", classes = @ConstructorResult(targetClass = Note.class, 
columns = {@ColumnResult(name = "id", type = Integer.class),@ColumnResult(name = "note", type = String.class)
@ColumnResult(name = "title", type = String.class),@ColumnResult(name = "status", type = String.class)}))
public class Note {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String note;
    private String title;
    private String status = "private";

NoteRepo.java

@Query(nativeQuery = true)
List<Note> getNoteByLabel(int labelId);

Build a proper constructor and try this one.

The SQL doesn't work in `@Query` in its pure form. You have to use JQL. — Nikolas Charalambidis, Apr 20 '20 at 10:44
@Query(value="SELECT * FROM notes n INNER JOIN notes_labels nl ON nl.notes_id = n.id WHERE nl.labels_id = :lid", nativeQuery=true) - this allows you to use the native query. Unfortunately it is throwing the following error - No converter found capable of converting from type [java.lang.Integer] to type [com.example.maapi.models.Note] — okapetanios, Apr 20 '20 at 21:40

score 0 · Answer 2 · answered Apr 20 '20 at 22:20

So this is the spring-booty way to do this that I was able to figure out. CrudRepository has findById(Integer id) which returns an Optional object. All you have to do is optional.get() to return the encapsulated object and then you can return the desired field (in my case List notes) with a getter.

// CrudRepo interface provides the findById method which returns an Optional<Label>
    // object that may or may not exist. Optional.get() returns the encapsulated object.
    public List<Note> findNotesByLabelId(int labelId) {
      Optional<Label> label = labelRepo.findById(labelId);
        return label.get().getNotes();    
    }

score 0 · Answer 3 · answered Oct 10 '21 at 09:32

You have to think on it as if it was simple POO. For example, you can use:

@Query("FROM Note n WHERE (SELECT l FROM Label l WHERE l.id = :lid) MEMBER OF labels")
public List<Note> findNotesByLabel(@Param("lid") int id);

which basically means,

get all notes where given id's label is part of the labels attribute

I don't fully know each implementation yet, surely the documentation would give a better approach, but I just came up with that problem and it did the trick

How to query a many to many relationship in spring boot repository

3 Answers3