Spring Data JPA 实现多表关联查询
一、多对多的实现
需求
[1] 通过用户ID 查询视频信息，通过该视频信息也获得对应的用户信息
如果要从用户表的信息获得视频表的信息。必须需要三个条件：
- 必须需要有一个中间表。
- 必须需要中间表对应本表的外键。
- 必须需要中间表对应关联表的外键。
第一步：配置单表
user 表
package com.call.show.model; import com.call.show.common.jpa.JsonType; import com.call.show.model.converter.ChanelConverter; import com.call.show.model.converter.MemberConverter; import com.call.show.model.converter.OnlineConverter; import com.call.show.model.converter.PlatformConverter; import org.hibernate.annotations.Type; import org.hibernate.annotations.TypeDef; import org.springframework.format.annotation.DateTimeFormat; import javax.persistence.*; import java.io.Serializable; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @DESC users表的实体类 */ @Entity @Table(name="users") @TypeDef(name = "JsonType", typeClass = JsonType.class) public class User implements Serializable { @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "menuSeq") @SequenceGenerator(name = "menuSeq", initialValue = 100000, allocationSize = 1, sequenceName = "MENU_SEQUENCE") @Column(name="id", columnDefinition = "BIGINT") /** * 用户唯一ID */ private long id; /** * 设备id */ @Column(name = "device", unique = true, nullable = true,length = 33) private String device ; /** * 在线状态 * 0-离线 1-在线 */ @Column(name = "online") @Convert(converter = OnlineConverter.class) private EnumOnline online = null; /** * 平台类型 * 0-iOS 1-Android */ @Column(name = "platform") @Convert(converter = PlatformConverter.class) private EnumPlatform platform = null ; /** * 渠道编号 */ @Column(name = "chanle") @Convert(converter = ChanelConverter.class) private EnumChanel chanle = null ; /** * App 版本号 */ @Column(name = "app_version", unique = false, nullable = false,length = 16) private String appVersion="0.0.1"; /** * 是否会员 */ @Column(name = "member") @Convert(converter = MemberConverter.class) private EnumMember member = null; /** * 会员到期时间 */ @Column(name = "member_expire_time",nullable = true) @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") private Date memberExpireTime; /** * 语言 * 0-简体中文 1-英文 */ @Column(name = "language",nullable = false,length = 2) private short language = 0; /** * 注册时间 */ @Column(name = "register_time",updatable = false) @Temporal(TemporalType.TIMESTAMP) @org.hibernate.annotations.CreationTimestamp private Date registerTime ; /** * 最后一次心跳时间 */ @Column(name = "heart_time") @org.hibernate.annotations.UpdateTimestamp @Temporal(TemporalType.TIMESTAMP) private Date heartTime ; /** * 上级用户绑定列表 * {"data": {"1": 661, "2": 639, "3": 637, "4": 633, * "ids": [661, 639, 637, 633], "bindTime": "2020-10-15 20:07:34"}} */ @Column(name = "agent_parent_ids",columnDefinition="jsonb") @Type(type = "JsonType") private Map<String,Object> agentParentIds = new HashMap<>(); /** * 账号信息 备用字段 * */ @Column(name = "account_info",columnDefinition="jsonb") @Type(type = "JsonType") private Map<String,Object> accountInfo = new HashMap<>(); /** * 扩展信息备用字段timestamp */ @Column(name = "extended_info",columnDefinition="jsonb") @Type(type = "JsonType") private Map<String,Object> extendedInfo = new HashMap<>(); /** * 最后一次登录ip */ @Column(name = "login_last_ip", unique = false, nullable = true, updatable = true,length = 20) private String loginLastIp ; /** * 最后一次登录时间 */ @Column(name = "login_last_time") @org.hibernate.annotations.UpdateTimestamp @Temporal(TemporalType.TIMESTAMP) private Date loginLastTime; public User() { } public EnumMember getMember() { return member; } public EnumOnline getOnline() { return online; } public EnumChanel getChanle() { return chanle; } public long getId() { return id; } public int getLanguage() { return language; } public EnumPlatform getPlatform() { return platform; } public String getAppVersion() { return appVersion; } public String getDevice() { return device; } public Date getHeartTime() { return heartTime; } public Date getMemberExpireTime() { return memberExpireTime; } public void setAppVersion(String appVersion) { this.appVersion = appVersion; } public String getLoginLastIp() { return loginLastIp; } public Date getLoginLastTime() { return loginLastTime; } public Date getRegisterTime() { return registerTime; } public void setAccountInfo(Map<String, Object> accountInfo) { this.accountInfo = accountInfo; } public void setExtendedInfo(Map<String, Object> extendedInfo) { this.extendedInfo = extendedInfo; } public void setAgentParentIds(Map<String, Object> agentParentIds) { this.agentParentIds = agentParentIds; } public Map<String, Object> getAccountInfo() { return accountInfo; } public Map<String, Object> getAgentParentIds() { return agentParentIds; } public Map<String, Object> getExtendedInfo() { return extendedInfo; } public void setChanle(EnumChanel chanle) { this.chanle = chanle; } public void setDevice(String device) { this.device = device; } public void setHeartTime(Date heartTime) { this.heartTime = heartTime; } public void setId(long id) { this.id = id; } public void setLanguage(short language) { this.language = language; } public void setLoginLastIp(String loginLastIp) { this.loginLastIp = loginLastIp; } public void setMember(EnumMember member) { this.member = member; } public void setMemberExpireTime(Date memberExpireTime) { this.memberExpireTime = memberExpireTime; } public void setOnline(EnumOnline online) { this.online = online; } public void setPlatform(EnumPlatform platform) { this.platform = platform; } public void setLoginLastTime(Date loginLastTime) { this.loginLastTime = loginLastTime; } public void setRegisterTime(Date registerTime) { this.registerTime = registerTime; } @Override public int hashCode() { return super.hashCode(); } @Override public boolean equals(Object obj) { return super.equals(obj); } @Override public String toString() { return super.toString(); } } } Video 表 package com.call.show.model; import com.call.show.model.converter.StatusConverter; import com.fasterxml.jackson.annotation.JsonIgnore; import javax.persistence.*; import java.util.Date; import java.util.List; /** * @DESC 视频列表的实体类 */ @Entity @Table(name="video") public class Video { @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name="id", columnDefinition = "BIGINT") /** * 视频对应的主键ID */ private long id; /** * 视频封面地址 */ @Column(name="cover_url",nullable = false,columnDefinition = "TEXT") private String coverUrl; /** * 视频地址 */ @Column(name="url", unique = true,nullable = false,columnDefinition = "TEXT") private String url; /** * 排序ID */ @Column(name="sort_id", columnDefinition = "BIGINT",nullable = true) private long sortId; /** * 状态 0 禁用 1- 启用 2 -删除 */ @Column(name="status") @Convert(converter = StatusConverter.class) private EnumStatus status = null; /** * 更新时间 */ @Column(name = "update_time",updatable = false) @Temporal(TemporalType.TIMESTAMP) @org.hibernate.annotations.UpdateTimestamp private Date updateTime ; public void setId(long id) { this.id = id; } public long getId() { return id; } public Date getUpdateTime() { return updateTime; } public void setCoverUrl(String coverUrl) { this.coverUrl = coverUrl; } public EnumStatus getStatus() { return status; } public long getSortId() { return sortId; } public String getCoverUrl() { return coverUrl; } public void setSortId(long sortId) { this.sortId = sortId; } public String getUrl() { return url; } public void setStatus(EnumStatus status) { this.status = status; } public void setUpdateTime(Date updateTime) { this.updateTime = updateTime; } public void setUrl(String url) { this.url = url; } }
第二步，配置多表关联
需求配置内容：
- 必须需要有一个中间表。
- 必须需要中间表对应本表的外键。
- 必须需要中间表对应关联表的外键。
在User中加入如下代码：
/** * 关联中间表-用户视频表 */ @ManyToMany @JoinTable(name = "user_video", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "video_id")) private List<Video> videoArray; public List<Video> getVideoArray() { return videoArray; } public void setVideoArray(List<Video> videoArray) { this.videoArray = videoArray; }
-在Video中加入如下代码：
@ManyToMany @JoinTable(name = "user_video", joinColumns = @JoinColumn(name = "video_id"), inverseJoinColumns = @JoinColumn(name = "user_id")) private List<User> userArray; public List<User> getUserArray() { return userArray; } public void setUserArray(List<User> userArray) { this.userArray = userArray; }
第三步，测试
@Override public ResultData queryUserVideo(Integer userId){ Optional<User> userOptional =null; try { userOptional = userRepository.findById(userId.longValue()); }catch (final Exception exception) { return ResultUtils.error(null); } User user = userOptional.get(); List<Video> videos = user.getVideoArray(); for (Video item:videos) { System.out.println("item:" + item.getId() + " url:" + item.getUrl()); } return ResultUtils.success(videos); }
二、无限递归调用问题解决
HttpMessageNotWritableException: Could not write JSON: Infinite recursion (StackOverflowE
问题描述：
在使用SpringBoot进行多对多的关联查询的时候，写数据回页面时出现异常， 出现了这个StackOverflowError的错误。 即： 在将对象转换为JSON格式的数据的时候出现了无限递归调用的情况：
Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: Infinite recursion (StackOverflowError); nested exception is com.fasterxml.jackson.databind.JsonMappingException: Infinite recursion (StackOverflowError) (through reference chain: org.hibernate.collection.internal.PersistentBag[0]->com.call.show.model.User["videoArray"]->org.hibernate.collection.internal.PersistentBag[0]->com.call.show.model.Video["userArray"]->org.hibernate.collection.internal.PersistentBag[0]->com.call.show.model.User["videoArray"]->org.hibernate.collection.internal.PersistentBag[0]->com.call.show.model.Video["userArray"]->org.hibernate.collection.internal.PersistentBag[0]->com.call.show.model.User["videoArray"]->org.hibernate.collection.internal.PersistentBag[0 .......
//循环打印出上面的内容
原因：
实体类之间互相关联 在序列化A实体类的时候 由于A里有B类， 然后去序列化B 在序列化B实体类的时候， 又由于B里有A类 然后去序列化A，如此反复递归 从而造成该问题。
我的实体类：
User 类中有一个字段List<Video> videoArray，在遍历集合中，输出一个Video实例的时候，List<User> userArray 字段也将输出，因为是双向多对多的关联查询，每一个Video实例也会输出List<User> userArray`字段值，因此一直递归下去直到栈溢出报错，反之，从Video中读User也是一样的道理。
解决方法：
在被维护的表里面，表示其他表的外键上添加@JsonIgnore注解。
例一：
/** * 多对多的关系，理论上是维护关系的一方 */ @ManyToMany @JoinTable( name = "user_video", joinColumns = @JoinColumn(name = "video_id"), inverseJoinColumns = @JoinColumn(name = "user_id") ) private List<User> userArray;
例二：
/** * 关联中间表-用户视频表 * JsonIgnore * * * 需要在被维护的@JsonIgnore 注解 * * 解决无限递归调用BUG * */ @JsonIgnore @ManyToMany @JoinTable( name = "user_video", joinColumns = @JoinColumn(name = "video_id"), inverseJoinColumns = @JoinColumn(name = "user_id") ) private List<User> userArray;
