Spring Data JPA 实现多表关联查询

作者: yichen_china | 来源:发表于2020-12-25 08:45 被阅读0次

【Spring Boot + Kotlin 实战教程】Sprin
Spring Data JPA进阶
Spring Data JPA 实现多表关联查询
Spring Data JPA实现动态多表关联查询
关于JPA中动态查询的做法
7 Spring-data-jpa查询方法
Example 查询
JPA查询
Spring Data JPA 基本使用
JPA的关联对象查询

最近抽出时间来做博客，数据库操作使用的是 JPA，相对比 Mybatis 而言，JPA 单表操作非常方便，增删改查都已经写好了。但是多表操作就不如 Mybatis 那种直接写 sql 语句来得方便，JPA 的多表操作比较麻烦。

先用两个好东西可以用来转换Do，To，Vo等对象互转

UserActionForm uForm = (UserActionForm) form;  
// 构造一个User对象  
User user = new User();  
// 逐一赋值  
user.setUsername(uForm.getUsername);  
user.setPassword(uForm.getPassword);  
user.setAge(uForm.getAge);  

#转换后删除多余参数
BeanUtils.copyProperties("要转换的类", "转换后的类");  
#转换后不删除多余参数
PropertyUtils.copyProperties("要转换的类", "转换后的类");

需求描述

获得文章列表，文章列表里需要显示每篇文章的分类目录列表，因为一个文章可能有多个分类，一个分类当然也可以对应多篇文章的。

下面是我用截图软件画的一个图，描述的是三张表和其主要字段。

image.png

文章表 article 和分类表 category 通过中间表 article_category 关联

我们的需求也很简单，从数据库里查询所有文章，并给每篇文章加一个分类列表的属性。

如图像这样

代码实现

Spring Data JPA 需要的依赖和配置文件这里就不给出了，因为不是本文的重点。

实体类

package com.liuyanzhao.blog.entity;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
import java.util.Set;
/**
 * @author 言曌
 * @date 2017/12/11 下午7:46
 */
@Entity
@Table(name = "article")
public class Article implements Serializable {
    private static final long serialVersionUID = 7419229779731522702L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Lob//text
    @Column(columnDefinition="text")
    private String title;
    @Lob //longtext
    @Column(columnDefinition="longtext")
    private String content;
    private Integer userId;
    private Integer likeCount;
    private Date createTime;
    private Date updateTime;
    private Integer status;
    @OneToMany(mappedBy = "article",cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<ArticleCategory> articleCategoryList;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getContent() {
        return content;
    }
    public void setContent(String content) {
        this.content = content;
    }
    public Integer getUserId() {
        return userId;
    }
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
    public Integer getLikeCount() {
        return likeCount;
    }
    public void setLikeCount(Integer likeCount) {
        this.likeCount = likeCount;
    }
    public Date getCreateTime() {
        return createTime;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
    public Date getUpdateTime() {
        return updateTime;
    }
    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }
    public Integer getStatus() {
        return status;
    }
    public void setStatus(Integer status) {
        this.status = status;
    }
    public static long getSerialVersionUID() {
        return serialVersionUID;
    }
    public Set<ArticleCategory> getArticleCategoryList() {
        return articleCategoryList;
    }
    public void setArticleCategoryList(Set<ArticleCategory> articleCategoryList) {
        this.articleCategoryList = articleCategoryList;
    }
}

注意 43-44 行@OneToMany(mappedBy = "article",cascade = CascadeType.ALL, orphanRemoval = true)

2、实体类 Category.java

package com.liuyanzhao.blog.entity;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Set;
/**
 * @author 言曌
 * @date 2017/12/11 下午8:16
 */
@Entity
@Table(name = "category")
public class Category implements Serializable {
    private static final long serialVersionUID = 7419229779731522702L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String name;
    private String key;
    private Integer status;
    @OneToMany(mappedBy = "category")
    private Set<ArticleCategory> articleCategoryList;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getStatus() {
        return status;
    }
    public void setStatus(Integer status) {
        this.status = status;
    }
    public String getKey() {
        return key;
    }
    public void setKey(String key) {
        this.key = key;
    }
    public static long getSerialVersionUID() {
        return serialVersionUID;
    }
    public Set<ArticleCategory> getArticleCategoryList() {
        return articleCategoryList;
    }
    public void setArticleCategoryList(Set<ArticleCategory> articleCategoryList) {
        this.articleCategoryList = articleCategoryList;
    }
}

注意 28-29 行 @OneToMany(mappedBy = "category")
3、实体类 ArticleCategory.java

package com.liuyanzhao.blog.entity;
import javax.persistence.*;
import java.io.Serializable;
/**
 * @author 言曌
 * @date 2017/12/12 下午4:08
 */
@Entity
@Table(name = "article_category")
public class ArticleCategory implements Serializable {
    private static final long serialVersionUID = 7419229779731522702L;
    @Id
    @ManyToOne
    @JoinColumn(name = "article_id")
    private Article article;
    @Id
    @ManyToOne
    @JoinColumn(name = "category_id")
    private Category category;
    public static long getSerialVersionUID() {
        return serialVersionUID;
    }
    public Article getArticle() {
        return article;
    }
    public void setArticle(Article article) {
        this.article = article;
    }
    public Category getCategory() {
        return category;
    }
    public void setCategory(Category category) {
        this.category = category;
    }
}

注意 17-25 行
@Id
@ManyToOne
@JoinColumn(name = "article_id")
private Article article;
@Id
@ManyToOne
@JoinColumn(name = "category_id")
private Category category;

Dao 层

1、ArticleDao.java

package com.liuyanzhao.blog.dao;
import com.liuyanzhao.blog.entity.Article;
import com.liuyanzhao.blog.vo.ArticleVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
/**
 * @author 言曌
 * @date 2017/11/28 下午3:31
 */
public interface ArticleDao extends JpaRepository<Article, Integer> {
    //获取文章列表，按status和id降序
    Page<ArticleVO> findAllByOrderByStatusDescIdDesc(Pageable pageable);
}

2、CategoryDao.java

package com.liuyanzhao.blog.dao;
import com.liuyanzhao.blog.entity.Category;
import org.springframework.data.jpa.repository.JpaRepository;
/**
 * @author 言曌
 * @date 2017/12/12 上午11:16
 */
public interface CategoryDao  extends JpaRepository<Category, Integer> {
}

Service 层
1、ArticleService.java

package com.liuyanzhao.blog.service;
import com.liuyanzhao.blog.vo.ArticleVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
/**
 * @author 言曌
 * @date 2017/12/9 下午4:10
 */
public interface ArticleService {
    //获得文章列表
    Page<ArticleVO> findAll(Pageable pageable);
}

2、ArticleServiceImpl.java

package com.liuyanzhao.blog.service.Impl;
import com.liuyanzhao.blog.dao.ArticleDao;
import com.liuyanzhao.blog.service.ArticleService;
import com.liuyanzhao.blog.vo.ArticleVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import javax.transaction.Transactional;
/**
 * @author 言曌
 * @date 2017/12/9 下午4:10
 */
@Service("articleService")
@Transactional
public class ArticleServiceImpl implements ArticleService {
    @Autowired
    private ArticleDao articleDao;
    @Override
    public Page<ArticleVO> findAll(Pageable pageable) {
        Page<ArticleVO> articleVOPage = articleDao.findAllByOrderByStatusDescIdDesc(pageable);
        return articleVOPage;
    }
}

Controller 层
ArticleController.java

package com.liuyanzhao.blog.controller;
import com.liuyanzhao.blog.service.ArticleService;
import com.liuyanzhao.blog.vo.ArticleVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.ModelAndView;
/**
 * @author 言曌
 * @date 2017/11/28 下午3:33
 */
@Controller
public class ArticleController {
    @Autowired
    private ArticleService articleService;
    @RequestMapping(value = "/admin/article")
    public ModelAndView listUser(@RequestParam(value = "page",defaultValue = "1") Integer page,
                                 @RequestParam(value = "size",defaultValue = "10") Integer size) {
        ModelAndView modelAndView = new ModelAndView();
        PageRequest request = new PageRequest(page-1,size);
        Page<ArticleVO> articleVOPage = articleService.findAll(request);
        modelAndView.addObject("articleVOPage",articleVOPage);
        modelAndView.setViewName("/admin/article/list");
        return modelAndView;
    }
}

视图层
视图层主要看表格的打印吧，分页部分和其他内容就不贴出来了

<table class="table table-bordered">
          <tr>
              <th><input type="checkbox" id="allSelect" onclick="DoCheck()"></th>
              <th>ID</th>
              <th>作者</th>
              <th>标题</th>
              <th>分类</th>
              <th>更新时间</th>
              <th>操作</th>
          </tr>
          <c:forEach var="article" items="${articleVOPage.content}">
              <tr>
                  <td><input type="checkbox" name="ids" value="${article.id}"></td>
                  <td>${article.id}</td>
                  <td>${article.userId}</td>
                  <td><a href="">${article.title}</a></td>
                  <td>
                      <c:forEach var="c" items="${article.articleCategoryList}">
                          <a href="">${c.category.name}</a> &nbsp;
                      </c:forEach>
                  </td>
                  <td>${article.updateTime}</td>
                  <td>
                      <a href="${pageContext.request.contextPath}/admin/user/profile/${article.id}">
                          <button type="button" class="btn btn-success btn-xs">查看</button>
                      </a>
                      <button type="button" class="btn btn-danger btn-xs"
                              onclick="deleteUser(${article.id})">删除
                      </button>
                      <a href="${pageContext.request.contextPath}/admin/user/edit/${article.id}">
                          <button type="button" class="btn btn-primary btn-xs">编辑</button>
                      </a>
                  </td>
              </tr>
          </c:forEach>
      </table>

最终效果图就是上面的

image

本文转载来源地址：https://liuyanzhao.com/6978.html

【Spring Boot + Kotlin 实战教程】Sprin
【Spring Boot + Kotlin 实战教程】Spring Data JPA 多表关联查询映射到 Dt...
Spring Data JPA进阶
Spring Data JPA进阶目录 ddl属性配置通过解析方法名查询关联查询 spring.jpa.pr...
Spring Data JPA 实现多表关联查询
最近抽出时间来做博客，数据库操作使用的是 JPA[https://liuyanzhao.com/tag/jpa/]...
Spring Data JPA实现动态多表关联查询
在面向关系型数据库（例如：MySQL）的查询中，动态的、多表关联的查询属于比较复杂的情况。所以，我们只要掌握了这种...
关于JPA中动态查询的做法
原文出处: 【一目了然】Spring Data JPA使用Specification动态构建多表查询、复杂查询及排...
7 Spring-data-jpa查询方法
springdata-jpa 八种查询方法 Spring Data JPA 简单查询--接口方法 jpa动态查询-...
Example 查询
Query By Example Spring Data JPA 实例查询
JPA查询
jpa常用的查询方式 jpa官方文档 https://docs.spring.io/spring-data/jpa...
Spring Data JPA 基本使用
JPA查询传递参数和对象参数对象通过spring-data-jpa进行复杂对象查询通过spring-dat...
JPA的关联对象查询
记录一种有趣的JPA的关联对象查询方式。代码如下：参考: SpringBoot第八讲Spring Data J...

Spring Data JPA 实现多表关联查询

先用两个好东西可以用来转换Do，To，Vo等对象互转

需求描述

代码实现

实体类

Dao 层

相关文章

【Spring Boot + Kotlin 实战教程】Sprin

Spring Data JPA进阶

Spring Data JPA 实现多表关联查询

Spring Data JPA实现动态多表关联查询

关于JPA中动态查询的做法

7 Spring-data-jpa查询方法

Example 查询

JPA查询

Spring Data JPA 基本使用

JPA的关联对象查询

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读