Spring Boot 学习研究笔记(十六) -Spring Data JPA 实现多表关联查询-阿里云开发者社区

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;

Spring Boot 学习研究笔记(十六) -Spring Data JPA 实现多表关联查询

Spring Data JPA 实现多表关联查询

一、多对多的实现

需求

第二步，配置多表关联

二、无限递归调用问题解决

热门文章

最新文章

相关课程

相关电子书

相关实验场景