如何在Spring Boot库中查询多对多关系

Question

我试图让api返回一个注释列表，通过与标签的多对多关系关联，给定一个标签ID。Spring boot自动创建了一个名为notes_tables的桥接表，其中包含一个notes_id字段和一个labels_id字段。Spring Boot还创建了一个notes表和一个labels表。我尝试了以下步骤：

@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);

我只需要让它工作，但我特别好奇我是否能让它与jpa方法查询一起工作。任何查询都可以，只要它可以工作。

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

}

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

        }

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

Answer 1

所以这是我能想出的一种春战利品方法。CrudRepository有findById（整数id)，它返回一个可选对象。您所要做的就是optional.get（）返回封装的对象，然后可以用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();    
    }