SpringData JPA多对一查询

绝地灬酷狼 2022-06-12 07:51 334阅读 0赞

实体类：

①PaperStore类

package com.sssp.entity;
    
    import Java.util.Date;
    
    import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.Id;
    import javax.persistence.JoinColumn;
    import javax.persistence.ManyToOne;
    import javax.persistence.Table;
    
    @Table(name="PaperStore")
    @Entity
    public class PaperStore {
    
        private Integer id;
        private String paperName;
        private Vocation vocation;
        private String paperType;
        private String paperDegree;
        private Integer paperPublish;
        private Integer assembleMode;
        private Date paperDate;
        private String examTime;
    
        @GeneratedValue
        @Id
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getPaperName() {
            return paperName;
        }
    
        public void setPaperName(String paperName) {
            this.paperName = paperName;
        }
    
        @JoinColumn(name="Vocation_Id")
        @ManyToOne
        public Vocation getVocation() {
            return vocation;
        }
    
        public void setVocation(Vocation vocation) {
            this.vocation = vocation;
        }
    
        public String getPaperType() {
            return paperType;
        }
    
        public void setPaperType(String paperType) {
            this.paperType = paperType;
        }
    
        public String getPaperDegree() {
            return paperDegree;
        }
    
        public void setPaperDegree(String paperDegree) {
            this.paperDegree = paperDegree;
        }
    
        public Integer getPaperPublish() {
            return paperPublish;
        }
    
        public void setPaperPublish(Integer paperPublish) {
            this.paperPublish = paperPublish;
        }
    
        public Integer getAssembleMode() {
            return assembleMode;
        }
    
        public void setAssembleMode(Integer assembleMode) {
            this.assembleMode = assembleMode;
        }
    
        public Date getPaperDate() {
            return paperDate;
        }
    
        public void setPaperDate(Date paperDate) {
            this.paperDate = paperDate;
        }
    
        public String getExamTime() {
            return examTime;
        }
    
        public void setExamTime(String examTime) {
            this.examTime = examTime;
        }
    
        @Override
        public String toString() {
            return "PaperStore [id=" + id + ", paperName=" + paperName + ", vocation=" + vocation + ", paperType="
                    + paperType + ", paperDegree=" + paperDegree + ", paperPublish=" + paperPublish + ", assembleMode="
                    + assembleMode + ", paperDate=" + paperDate + ", examTime=" + examTime + "]";
        }
    
    }

②QuestionPaper类

package com.sssp.entity;
    
    import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.Id;
    import javax.persistence.JoinColumn;
    import javax.persistence.ManyToOne;
    import javax.persistence.Table;
    
    @Table(name="Question_Paper")
    @Entity
    public class QuestionPaper {
    
        private Integer id;
        private PaperStore paperStore;
        private QuestionInfo questionInfo;
    
        @GeneratedValue
        @Id
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        @JoinColumn(name="Paper_Id")
        @ManyToOne
        public PaperStore getPaperStore() {
            return paperStore;
        }
    
        public void setPaperStore(PaperStore paperStore) {
            this.paperStore = paperStore;
        }
    
        @JoinColumn(name="Question_Id")
        @ManyToOne
        public QuestionInfo getQuestionInfo() {
            return questionInfo;
        }
    
        public void setQuestionInfo(QuestionInfo questionInfo) {
            this.questionInfo = questionInfo;
        }
    
    }

③repository

import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.data.jpa.repository.Query;
    
    import com.sssp.entity.PaperStore;
    import com.sssp.entity.QuestionPaper;
    
    public interface QuestionPaperRepository extends JpaRepository<QuestionPaper, Integer>,JpaSpecificationExecutor<QuestionPaper>{
        
        public List<QuestionPaper> getByPaperStore(PaperStore paperStore);
        
        @Query("SELECT qp FROM QuestionPaper qp INNER JOIN qp.paperStore ps WHERE ps.id = ?1")
        List<QuestionPaper> getQuestionPaper(Integer id);
    }

需求：通过paperId得到QuestionPaper，一个paperId对应多个QuestionPaper

方法一：先查询PaperStore，再通过PaperStore查询QuestionPaper

service层：

PaperStore paperStore = paperStoreRepository.getById(id);

List<QuestionPaper> questionPapers= questionPaperRepository.getByPaperStore(paperStore);

方法二：通过@Query注解

service层：

List<QuestionPaper> questionPapers= questionPaperRepository.getQuestionPaper(id);

方法三：Repository继承JpaSpecificationExecutor

service层：

Specification<QuestionPaper> specification = new Specification<QuestionPaper>() {
    
                @Override
                public Predicate toPredicate(Root<QuestionPaper> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                    Join<QuestionPaper, PaperStore> join = root.join("paperStore",JoinType.INNER);
                    Path<Integer> exp = join.get("id");
                    return cb.ge(exp, id);
                }
            };
            List<QuestionPaper> questionPapers= questionPaperRepository.findAll(specification);

方法四：属性的连缀

@Query("SELECT qp.questionInfo.questionId FROM QuestionPaper qp WHERE qp.paperStore.id = ?1")  
    List<Integer> getQuestionId(Integer id);