Spring Data JPA 实现多表关联查询

本文地址：https://liuyanzhao.com/6978.html

 

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

 

需求描述

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

 

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

 

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

 

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

如图像这样

 

代码实现

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

 

实体类

1、实体类 Article.java

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 行

 

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 行

 

 

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 行

 

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>

 

最终效果图就是上面的