时间戳

首页 > 解决方案 > 如何在spring boot存储库中查询多对多关系

mysql - 如何在spring boot存储库中查询多对多关系

问题描述

我试图让 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 方法查询一起工作。只要有效，任何查询都可以。

编辑：实体 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);
    }

}

标签.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);
        }

        }

服务：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);
    }

}

标签服务.java

标签： mysqlsqlspringspring-bootjpa

解决方案

试试这个！

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

编辑：

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

构建一个适当的构造函数并尝试这个。

推荐阅读