Spring Data JPA多表查询

我就是我 2022-12-02 10:50 560阅读 0赞

多表查询在Spring Data JPA中有两种实现方式  
第一种创建一个结果集接口来接收多表连查的结果  
第二种利用JPA的关联映射来实现

先来熟悉一下几个注解

<table> 
 <thead> 
  <tr> 
   <th>注解</th> 
   <th>意思</th> 
   <th>属性</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>@ManyToOne</td> 
   <td>定义了连接表之间的多对一的关系。</td> 
   <td>targetEntity属性表示关联的实体类型，可省略，fetch属性表示加载策略,FetchType的取值范围LAZY（延迟加载）默认上EAGER，cascade属性表示级联操作，CascadeType取值范围PERSIST（级联持久化）,REMOVE（级联删除）,ALL（级联所以操作）…等，一般不推荐使用ALL，风险较大，cascade属性一般不在多对一的地方使用，通常在一对多地方使用</td> 
  </tr> 
  <tr> 
   <td>@JoinColumn</td> 
   <td>指定一个实体组织或实体的集合。这是用在多对一和一对多关联。</td> 
   <td>name属性表示外键的字段名称</td> 
  </tr> 
  <tr> 
   <td>@OneToMany</td> 
   <td>定义了连接表之间存在一个一对多的关系。</td> 
   <td>具有@ManyToOne注解的属性，还有mappedby属性用来设置对象之间的关系维护方</td> 
  </tr> 
 </tbody> 
</table>

这里只是列出了JPA进行多表查询的注解，如果对普通的JAP注解不了解的可以去看一下这篇相关的博客  
[什么是JPA、JPA的基础实战][JPA_JPA]

话不多说，直接上代码，让老夫来瞎操作一波  
先上两个实体类,用户类，角色类，`这里不建议使用@Data的注解，不然会出现堆栈溢出的异常`  
`大家在看实体类的时候一定要认真看哦，看清楚使用了哪些注解以及属性`  
用户类

import javax.persistence.*;
    import java.io.Serializable;
    
    @Entity
    @Table(name = "sys_user")
    public class SysUser implements Serializable { 
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private long userId;
        private String userName;
        private String userPassword;
    
        private long userFlag;
    
        @ManyToOne(targetEntity = SysRole.class)
        @JoinColumn(name = "user_role_id")
        private SysRole sysRole;
    
        public long getUserId() { 
            return userId;
        }
    
        public void setUserId(long userId) { 
            this.userId = userId;
        }
    
        public SysRole getSysRole() { 
            return sysRole;
        }
    
        public void setSysRole(SysRole sysRole) { 
            this.sysRole = sysRole;
        }
    
        public String getUserName() { 
            return userName;
        }
    
        public void setUserName(String userName) { 
            this.userName = userName;
        }
    
        public String getUserPassword() { 
            return userPassword;
        }
    
        public void setUserPassword(String userPassword) { 
            this.userPassword = userPassword;
        }
    
        public long getUserFlag() { 
            return userFlag;
        }
    
        public void setUserFlag(long userFlag) { 
            this.userFlag = userFlag;
        }
    
    
        public SysUser(String userName, String userPassword, long userFlag, SysRole sysRole) { 
            this.sysRole = sysRole;
            this.userName = userName;
            this.userPassword = userPassword;
            this.userFlag = userFlag;
        }
    
        public SysUser() { 
        }
    }

角色类

import javax.persistence.*;
    import java.io.Serializable;
    import java.util.HashSet;
    import java.util.Set;
    
    @Entity
    @Table(name = "sys_role")
    public class SysRole { 
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private long roleId;
        private String roleName;
        private String roleDesc;
        private long roleFlag;
    
        @OneToMany(targetEntity = SysUser.class, fetch = FetchType.LAZY, cascade = CascadeType.ALL, mappedBy = "sysRole")
        private Set<SysUser> sysUsers = new HashSet<SysUser>();
    
        public Set<SysUser> getSysUsers() { 
            return sysUsers;
        }
    
        public void setSysUsers(Set<SysUser> sysUsers) { 
            this.sysUsers = sysUsers;
        }
    
        public long getRoleId() { 
            return roleId;
        }
    
        public void setRoleId(long roleId) { 
            this.roleId = roleId;
        }
    
        public String getRoleName() { 
            return roleName;
        }
    
        public void setRoleName(String roleName) { 
            this.roleName = roleName;
        }
    
    
        public String getRoleDesc() { 
            return roleDesc;
        }
    
        public void setRoleDesc(String roleDesc) { 
            this.roleDesc = roleDesc;
        }
    
        public long getRoleFlag() { 
            return roleFlag;
        }
    
        public void setRoleFlag(long roleFlag) { 
            this.roleFlag = roleFlag;
        }
    
    
        public SysRole(String roleName, String roleDesc, long roleFlag) { 
            this.roleName = roleName;
            this.roleDesc = roleDesc;
            this.roleFlag = roleFlag;
        }
    
        public SysRole() { 
        }
    }

创建SysRoleRepository接口和SysUserRepository接口  
SysUserRepository接口

public interface SysUserRepository extends JpaRepository<SysUser,Long> { }

SysRoleRepository接口

public interface SysRoleRepository  extends JpaRepository<SysRole,Long> { }

测试基本的CRUD  
测试多对一查询

@Autowired
        SysUserRepository sysUserRepository;
    
        @Test
        public void testFindById() { 
            SysUser sysUser = sysUserRepository.findById(18L).get();
            System.out.println("userName:" + sysUser.getUserName());
            System.out.println("roleName:" + sysUser.getSysRole().getRoleName());
        }

结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdUR1a2Ux_size_16_color_FFFFFF_t_70_pic_center]  
测试多对一的添加

@Autowired
      SysRoleRepository sysRoleRepository;
    
      @Test
        public void testAdd() { 
            SysRole sysRole = new SysRole("test","001",1);
            SysUser sysUser = new SysUser("test","1234567",1,sysRole);
            sysRole.getSysUsers().add(sysUser);
            sysRoleRepository.save(sysRole);
        }

结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdUR1a2Ux_size_16_color_FFFFFF_t_70_pic_center 1]  
![在这里插入图片描述][20200830165402556.png_pic_center]  
删除的话需要把@OneToMany注解里面的cascade属性值改成CascadeType.ALL或者是CascadeType.REMOVE即可

到这来就结束咯，快点去使用一下吧

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdUR1a2Ux_size_16_color_FFFFFF_t_70_pic_center 2]

[JPA_JPA]: https://blog.csdn.net/liuduke1/article/details/108239347
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdUR1a2Ux_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/f05e5d2ae17442d68edd8accfbe26463.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdUR1a2Ux_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/5b56d7af01df4f25b3ae39b604bee53a.png
[20200830165402556.png_pic_center]: /images/20221123/4df13dfc13274ffd83dd040bae52d48b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpdUR1a2Ux_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/4cbf38258e3e499fb352efd7ae8c45f3.png