[Jpa框架 ] 简单易懂的Jpa 实现连表查询结果集返回

不要被我使用的框架迷惑 实体类啥的 不一样 都不要去关注 ,你主要就看 SQL 和 实体类 如何对应的关系 就OK

这儿的结构是:

实体类 you 用户表,项目表,用户项目中间表,返回结果集表

这里的SQL 是其中的一种;

1. SQL 里面用到的属性 都是实体类的属性
2. 表名称是包的名称
3. 这里要 new new cn.bist.cdapp.vo.ProjectUserVo(返回的结果集)

“SELECT new cn.bist.cdapp.vo.ProjectUserVo(pm.projectName , us.name) from ProjectForm pm ,EruptUser us,ProjectUserForm pr WHERE pm.id=pr.thisId AND pr.peopleId=us.id AND pr.peopleId= :id”)

这里是第二种的SQL写法:
就是数据库的原SQL

注意的点:

1. SQL 对应关系, SQL返回数据要对应实体类的字段顺序,
2. 构造函数 有参和无参构造函数
3. @AllArgsConstructor 这是注解 实体类 用于用户表
   @NoArgsConstructor

这是用户表里面定义的返回接收结果的VO,我这是需求是 必须在用户登录的时候 也要返回对应用户所属的项目

@Transient
private List<ProjectUserVo> projectFormList;

用户项目中间表

package cn.bist.cdapp.backstage.model;


import lombok.Data;
import org.hibernate.annotations.GenericGenerator;
import xyz.erupt.annotation.Erupt;
import xyz.erupt.annotation.EruptField;
import xyz.erupt.annotation.sub_field.Edit;
import xyz.erupt.annotation.sub_field.EditType;
import xyz.erupt.annotation.sub_field.View;
import xyz.erupt.annotation.sub_field.sub_edit.ChoiceType;
import xyz.erupt.annotation.sub_field.sub_edit.Search;
import xyz.erupt.upms.handler.SqlChoiceFetchHandler;

import javax.persistence.*;

@Erupt(name = "用户项目(关联)管理表")
@Table(name = "project_user_form")
@Entity
@Data
public class ProjectUserForm {

    /**
     * 项目编号
     */
    @Id
    @GeneratedValue(generator = "generator")
    @GenericGenerator(name = "generator", strategy = "native")
    @Column(name = "id")
    @EruptField
    private Long id;

    @Column(name = "this_id")
    private Long thisId;
    @Column(name = "people_id")
    private Long peopleId;



    @EruptField(
            views = @View(title = "项目负责人"),
            edit = @Edit(
                    search = @Search,
                    title = "项目负责人下拉选择", type = EditType.CHOICE,
                    choiceType = @ChoiceType(fetchHandler = SqlChoiceFetchHandler.class,
                            fetchHandlerParams = "SELECT id,name FROM `e_upms_user`"
                    )
            )

    )
     private Long userId;

    @EruptField(
            views = @View(title = "项目绑定"),
            edit = @Edit(
                    search = @Search,
                    title = "项目下拉选择", type = EditType.CHOICE,
                    choiceType = @ChoiceType(fetchHandler = SqlChoiceFetchHandler.class,
                            fetchHandlerParams = "select id,project_name from project_form"
                    )
            )

    )
     private Long projectId;





}

项目表:

package cn.bist.cdapp.backstage.model;


import lombok.Data;
import org.hibernate.annotations.GenericGenerator;
import xyz.erupt.annotation.Erupt;
import xyz.erupt.annotation.EruptField;
import xyz.erupt.annotation.sub_field.Edit;
import xyz.erupt.annotation.sub_field.EditType;
import xyz.erupt.annotation.sub_field.View;
import xyz.erupt.upms.model.EruptUser;

import javax.persistence.*;
import java.util.Set;

@Erupt(name = "项目管理")
@Table(name = "project_form")
@Entity
@Data
public class ProjectForm {


    /**
     * 项目编号
     */
    @Id
    @GeneratedValue(generator = "generator")
    @GenericGenerator(name = "generator", strategy = "native")
    @Column(name = "id")
    @EruptField
    private Long id;

    /**
     * 项目名称
     */
    @EruptField(
            views = @View(title = "项目名称"),
            edit = @Edit(title = "项目名称")
    )
    @Column(name = "project_name",  columnDefinition = "varchar(20) comment '项目名称'")
    private String projectName;


    @ManyToMany //多对多
    @JoinTable(name = "project_user_form", //定义多对多中间表
            joinColumns = @JoinColumn(name = "this_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "people_id", referencedColumnName = "id"))
    @EruptField(
            views = @View(
                    title = "下属人员"
            ),
            edit = @Edit(
                    title = "下属人员",
                    type = EditType.CHECKBOX
            )
    )
    private Set<EruptUser> users; //Table对象定义如下👇






}

关联表返回结果查询集:

package cn.bist.cdapp.vo;






public class ProjectUserVo {

  private String projectName;
  private String userName;

  public String getProjectName() {
    return projectName;
  }

  public void setProjectName(String projectName) {
    this.projectName = projectName;
  }

  public String getUserName() {
    return userName;
  }

  public void setUserName(String userName) {
    this.userName = userName;
  }
public ProjectUserVo(){}

  public ProjectUserVo(String projectName, String userName) {
    this.projectName = projectName;
    this.userName = userName;
  }
}

DAO层的SQL 编写

package cn.bist.cdapp.dao;


import cn.bist.cdapp.backstage.model.ProjectForm;
import cn.bist.cdapp.model.EquipmentFlowForm;
import cn.bist.cdapp.model.User;
import cn.bist.cdapp.vo.ProjectUserVo;
import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.math.BigInteger;
import java.util.List;
public interface UserDao extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {

//
//    @Query(value = "SELECT new cn.bist.cdapp.vo.ProjectUserVo(pr.project_name as projectName , us.name as userName)    from project_user_form pm ,e_upms_user us,project_form pr WHERE   pm.this_id=pr.id AND pm.people_id=us.id AND pm.people_id= :id" )
//    List<ProjectUserVo> projectFormList(@Param("id") Long id);



    @Query(value = "SELECT new cn.bist.cdapp.vo.ProjectUserVo(pm.projectName , us.name) from ProjectForm pm ,EruptUser us,ProjectUserForm pr WHERE   pm.id=pr.thisId AND pr.peopleId=us.id AND pr.peopleId= :id")
    List<ProjectUserVo> projectFormList(@Param("id") Long id);



}