SpringBoot(JPA关联)

JPA多表查询

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

示例代码

多表联接查询

实体类Role

java">
@Entity
@Table(name = "sys_role")
@Data
public class Role {@Id@GeneratedValue(strategy = GenerationType.IDENTITY)@Column(name = "role_id")private Long roleId;@Column(name = "role_name")private String roleName;@Column(name = "role_desc")private String roleDesc;@Column(name = "role_flag")private Integer roleFlag;}

实体类User

java">@Entity
@Table(name = "sys_user")
@Data
//@NamedQueries(@NamedQuery(name = "User.findUsersByName",query = "select u from User u where u.usrName = ?1"))
public class User implements Serializable {@Id@GeneratedValue(strategy = GenerationType.IDENTITY)@Column(name = "usr_id")private long usrId;@Column(name = "usr_name")private String usrName;@Column(name = "usr_password")private String usrPassword;@Column(name = "usr_role_id")private long usrRoleId;@Column(name = "usr_flag")private long usrFlag;}

接口UserInfo(里面提供所需数据的getter方法)

java">public interface UserInfo {public Long getUsrId();public String getUsrName();public String getUsrPassword();public Long getUsrRoleId();public Integer getUsrFlag();// 角色名称public String getRoleName();
}

在运行中spring会给接口（UserInfo）自动生产一个代理类来接收返回的结果，代码中使用getXX的形式来获取

接口UserRepository(编写查询方法，返回类型设置为UserInfo)

java">public interface UserRepository extends JpaRepository<User,Long>, JpaSpecificationExecutor<User> {@Query("select u.usrId as usrId, u.usrName as usrName, u.usrPassword as usrPassword, u.usrRoleId as usrRoldId, u.usrFlag as usrFlag, r.roleName as roleName from User u, Role r where u.usrRoleId = r.roleId and u.usrId = ?1")public UserInfo getUserInfo(Long usrId);}

测试验证

java">    @Testpublic void testGetUserInfo(){UserInfo userInfo = userRepository.getUserInfo(2l);System.out.println("usrName：" + userInfo.getUsrName());System.out.println("roleName：" + userInfo.getRoleName());}

测试结果

Hibernate: selectuser0_.usr_id as col_0_0_,user0_.usr_name as col_1_0_,user0_.usr_password as col_2_0_,user0_.usr_role_id as col_3_0_,user0_.usr_flag as col_4_0_,role1_.role_name as col_5_0_ fromsys_user user0_ cross joinsys_role role1_ whereuser0_.usr_role_id=role1_.role_id and user0_.usr_id=?
usrName：test
roleName：客户经理

关联映射

在软件开发中，类与类之间最普遍的关系就是关联关系，而且关联是有方向的。以角色（Role）和用户为例，应该角色下有多个用户，而一个用户只能属于一个角色。

从User到Role的关联就是多对一关联，这就意味着每个User对象只会引用一个Role对象，因此在User类中应该定义一个Role类型的属性，来引用所关联的Role对象。

从Role到User是一对多关联，这意味着每个Role对象会引用一组User对象，因此在Role类中应该定义一个集合类型的属性，来引用所以关联的User对象。

单向多对一关联

修改User实体类

java">@Entity
@Table(name = "sys_user")
@Data
public class User implements Serializable {@Id@GeneratedValue(strategy = GenerationType.IDENTITY)@Column(name = "usr_id")private long usrId;@Column(name = "usr_name")private String usrName;@Column(name = "usr_password")private String usrPassword;@Column(name = "usr_flag")private long usrFlag;@ManyToOne(targetEntity = Role.class)@JoinColumn(name = "usr_role_id")private Role role;public User() {}public User(String usrName, String usrPassword, long usrFlag, Role role) {this.usrName = usrName;this.usrPassword = usrPassword;this.usrFlag = usrFlag;this.role = role;}}

测试

java">    @Testpublic void testGet(){User user = userRepository.findById(2L).get();System.out.println("usrName：" + user.getUsrName());System.out.println("roleName：" + user.getRole().getRoleName());}

测试结果

Hibernate: selectu1_0.usr_id,r1_0.role_id,r1_0.role_desc,r1_0.role_flag,r1_0.role_name,u1_0.usr_flag,u1_0.usr_name,u1_0.usr_password fromsys_user u1_0 left joinsys_role r1_0 on r1_0.role_id=u1_0.usr_role_id whereu1_0.usr_id=?
Hibernate: selectu1_0.usr_role_id,u1_0.usr_id,u1_0.usr_flag,u1_0.usr_name,u1_0.usr_password fromsys_user u1_0 whereu1_0.usr_role_id=?
usrName：test
roleName：客户经理

双向一对多关联

实体类User

java">@Entity
@Table(name = "sys_role")
@Data
public class Role {@Id@GeneratedValue(strategy = GenerationType.IDENTITY)@Column(name = "role_id")private Long roleId;@Column(name = "role_name")private String roleName;@Column(name = "role_desc")private String roleDesc;@Column(name = "role_flag")private Integer roleFlag;@OneToMany(targetEntity = User.class,fetch = FetchType.EAGER,cascade = CascadeType.ALL,mappedBy = "role")private Set<User> users = new HashSet<>();public Role() {}public Role(String roleName, String roleDesc, Integer roleFlag) {this.roleName = roleName;this.roleDesc = roleDesc;this.roleFlag = roleFlag;}}

测试

java">    @Testpublic void testGet(){Role role = roleRepository.findById(1L).get();System.out.println("roleName：" + role.getRoleName());System.out.println("users.size：" + role.getUsers().size());}

测试结果

Hibernate: selectr1_0.role_id,r1_0.role_desc,r1_0.role_flag,r1_0.role_name,u1_0.usr_role_id,u1_0.usr_id,u1_0.usr_flag,u1_0.usr_name,u1_0.usr_password fromsys_role r1_0 left joinsys_user u1_0 on r1_0.role_id=u1_0.usr_role_id wherer1_0.role_id=?
roleName：管理员
users.size：3