Spring Data JPA 实现多表关联查询

最新推荐文章于 2024-09-06 15:54:47 发布
最新推荐文章于 2024-09-06 15:54:47 发布
阅读量1.2w 收藏 34
点赞数 6
分类专栏： Java 文章标签： JPA Spring 联合查询
原文链接：https://blog.csdn.net/johnf_nash/article/details/80587204
版权

原文链接：https://blog.csdn.net/johnf_nash/article/details/80587204

多表查询在spring data jpa中有两种实现方式，第一种是利用hibernate的级联查询来实现，第二种是创建一个结果集的接口来接收连表查询后的结果，这里介绍第二种方式。

一、一对一映射

实体 UserInfo ：用户。

实体 Address：家庭住址。

这里通过外键的方式(一个实体通过外键关联到另一个实体的主键)来实现一对一关联。

实体类

1、实体类 UserInfo.java
package com.johnfnash.learn.domain;

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="tb_user")
public class UserInfo implements Serializable {
  private static final long serialVersionUID = 8283950216116626180L;

  @Id
  @GeneratedValue(strategy=GenerationType.IDENTITY)
  private Long userId;
  private String name;
  private int age;
  private String sex;
  private String email;

  // 与 Address 的关联  
  private Long addressId;

  public UserInfo() {
    super();
  }

  public UserInfo(String name, int age, String sex, String email, Long addressId) {
    super();
    this.name = name;
    this.age = age;
    this.sex = sex;
    this.email = email;
    this.addressId = addressId;
  }

  // getter, setter

  @Override
  public String toString() {
    return String.format("UserInfo [userId=%d, name=%s, age=%s, sex=%s, email=%s]", userId, name, age, sex, email);
  }

}
2. 实体类 Address.java
package com.johnfnash.learn.domain;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "tb_address")
public class Address {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long addressId;
  private String areaCode;
  private String country;
  private String province;
  private String city;
  private String area;
  private String detailAddress;

  public Address() {
    super();
  }

  public Address(String areaCode, String country, String province, String city, String area,
      String detailAddress) {
    super();
    this.areaCode = areaCode;
    this.country = country;
    this.province = province;
    this.city = city;
    this.area = area;
    this.detailAddress = detailAddress;
  }

  // getter, setter

  @Override
  public String toString() {
    return "Address [addressId=" + addressId + ", areaCode=" + areaCode + ", country=" + country + ", province="
        + province + ", city=" + city + ", area=" + area + ", detailAddress=" + detailAddress + "]";
  }

}

Dao 层

1、UserInfoRepository.java
package com.johnfnash.learn.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.johnfnash.learn.domain.UserInfo;
import com.johnfnash.learn.domain.ViewInfo;

public interface UserInfoRepository extends JpaRepository<UserInfo, Long> {

  @Query(value = "SELECT new com.johnfnash.learn.domain.ViewInfo(u, a) FROM UserInfo u, Address a WHERE u.addressId = a.addressId")
  public List<ViewInfo> findViewInfo();

}

注：这里的 ViewInfo 类用来一个用来接收多表查询结果集的类（使用 new + 完整类名构造函数）
代码如下：

package com.johnfnash.learn.domain;

import java.io.Serializable;

public class ViewInfo implements Serializable {

  private static final long serialVersionUID = -6347911007178390219L;

  private UserInfo userInfo;
  private Address address;

  public ViewInfo() {

  }

  public ViewInfo(UserInfo userInfo) {
    Address address = new Address();
    this.userInfo = userInfo;
    this.address = address;
  }

  public ViewInfo(Address address) {
    UserInfo userInfo = new UserInfo();
    this.userInfo = userInfo;
    this.address = address;
  }

  public ViewInfo(UserInfo userInfo, Address address) {
    this.userInfo = userInfo;
    this.address = address;
  }

  // getter, setter

}
2. AddressRepository.java
package com.johnfnash.learn.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.johnfnash.learn.domain.Address;

public interface AddressRepository extends JpaRepository<Address, Long> {

}

测试代码

package com.johnfnash.learn;

import java.util.List;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.johnfnash.learn.domain.Address;
import com.johnfnash.learn.domain.UserInfo;
import com.johnfnash.learn.domain.ViewInfo;
import com.johnfnash.learn.repository.AddressRepository;
import com.johnfnash.learn.repository.UserInfoRepository;

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserInfoRepositoryTests {

  @Autowired
    private UserInfoRepository userInfoRepository;

  @Autowired
  private AddressRepository addressRepository;

  @Before
    public void init() {
        Address addr1 = new Address("027","CN","HuBei", "WuHan","WuChang", "123 street");
        Address addr2 = new Address("023","CN","ChongQing", "ChongQing","YuBei", "123 road");
        addressRepository.save(addr1);
        addressRepository.save(addr2);

        UserInfo user1 = new UserInfo("ZS", 21,"Male","123@xx.com", addr1.getAddressId());
        UserInfo user2 = new UserInfo("Ww", 25,"Male","234@xx.com", addr2.getAddressId());
        userInfoRepository.save(user1);
        userInfoRepository.save(user2);
    }

  @After
  public void deleteAll() {
    userInfoRepository.deleteAll();

    addressRepository.deleteAll();
  }

  @Test
  public void testQuery() {
    List<ViewInfo> viewInfos = userInfoRepository.findViewInfo();
    for (ViewInfo viewInfo : viewInfos) {
      System.out.println(viewInfo.getUserInfo());
      System.out.println(viewInfo.getAddress());
    }
  }

}

查询相关的 sql 如下：

Hibernate: select userinfo0_.user_id as col_0_0_, address1_.address_id as col_1_0_ from tb_user userinfo0_ cross join tb_address address1_ where userinfo0_.address_id=address1_.address_id
Hibernate: select userinfo0_.user_id as user_id1_4_0_, userinfo0_.address_id as address_2_4_0_, userinfo0_.age as age3_4_0_, userinfo0_.email as email4_4_0_, userinfo0_.name as name5_4_0_, userinfo0_.sex as sex6_4_0_ from tb_user userinfo0_ where userinfo0_.user_id=?
Hibernate: select address0_.address_id as address_1_3_0_, address0_.area as area2_3_0_, address0_.area_code as area_cod3_3_0_, address0_.city as city4_3_0_, address0_.country as country5_3_0_, address0_.detail_address as detail_a6_3_0_, address0_.province as province7_3_0_ from tb_address address0_ where address0_.address_id=?
Hibernate: select userinfo0_.user_id as user_id1_4_0_, userinfo0_.address_id as address_2_4_0_, userinfo0_.age as age3_4_0_, userinfo0_.email as email4_4_0_, userinfo0_.name as name5_4_0_, userinfo0_.sex as sex6_4_0_ from tb_user userinfo0_ where userinfo0_.user_id=?
Hibernate: select address0_.address_id as address_1_3_0_, address0_.area as area2_3_0_, address0_.area_code as area_cod3_3_0_, address0_.city as city4_3_0_, address0_.country as country5_3_0_, address0_.detail_address as detail_a6_3_0_, address0_.province as province7_3_0_ from tb_address address0_ where address0_.address_id=?
Hibernate: select userinfo0_.user_id as user_id1_4_, userinfo0_.address_id as address_2_4_, userinfo0_.age as age3_4_, userinfo0_.email as email4_4_, userinfo0_.name as name5_4_, userinfo0_.sex as sex6_4_ from tb_user userinfo0_
Hibernate: select address0_.address_id as address_1_3_, address0_.area as area2_3_, address0_.area_code as area_cod3_3_, address0_.city as city4_3_, address0_.country as country5_3_, address0_.detail_address as detail_a6_3_, address0_.province as province7_3_ from tb_address address0_

查询结果如下：

UserInfo [userId=1, name=ZS, age=21, sex=Male, email=123@xx.com]
Address [addressId=1, areaCode=027, country=CN, province=HuBei, city=WuHan, area=WuChang, detailAddress=123 street]
UserInfo [userId=2, name=Ww, age=25, sex=Male, email=234@xx.com]
Address [addressId=2, areaCode=023, country=CN, province=ChongQing, city=ChongQing, area=YuBei, detailAddress=123 road]

二、多对多映射

实体 Author ：作者。

实体 Book ：书籍

这里通过关联表的方式来实现多对多关联。

实体类

实体类：Author.java
package com.johnfnash.learn.domain;

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class Author implements Serializable {

  private static final long serialVersionUID = 1227555837798655046L;

  @Id
    @GeneratedValue
    private Integer id;

    private String name;

  public Author() {
    super();
  }

  public Author(String name) {
    super();
    this.name = name;
  }

  // getter, setter

  @Override
    public String toString() {
        return String.format("Author [id=%s, name=%s]", id, name);
    }

}
Book.java 实体类
package com.johnfnash.learn.domain;

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class Book implements Serializable {

  private static final long serialVersionUID = -2470510857424220408L;

  @Id
    @GeneratedValue
    private Integer id;

    private String name;

    public Book() {
        super();
    }

    public Book(String name) {
        super();
        this.name = name;
    }

  //getter, setter

  @Override
  public String toString() {
    return String.format("Book [id=%s, name=%s]", id, name);
  }

}
实体类BookAuthor.java
package com.johnfnash.learn.domain;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.Table;

@Entity
@IdClass(BookAuthorPK.class)
@Table(name = "book_author")
public class BookAuthor {

  @Id
  private Integer bookId;

  @Id
  private Integer authorId;

  public BookAuthor() {
    super();
  }

  public BookAuthor(Integer bookId, Integer authorId) {
    super();
    this.bookId = bookId;
    this.authorId = authorId;
  }

  // getter, setter

}

注：这里使用 @IdClass 注解指定一个联合主键类来映射实体类的多个属性。这个联合主键类的代码如下：

package com.johnfnash.learn.domain;

import java.io.Serializable;

public class BookAuthorPK implements Serializable {

  private static final long serialVersionUID = -1158141803682305656L;

  private Integer bookId;

  private Integer authorId;

  public Integer getBookId() {
    return bookId;
  }

  public void setBookId(Integer bookId) {
    this.bookId = bookId;
  }

  public Integer getAuthorId() {
    return authorId;
  }

  public void setAuthorId(Integer authorId) {
    this.authorId = authorId;
  }

}

Dao 层

BookRepository.java
package com.johnfnash.learn.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.johnfnash.learn.domain.Book;

public interface BookRepository extends JpaRepository<Book, Integer> {

  @Query(nativeQuery = true, value = "SELECT b.id, b.name, GROUP_CONCAT(a.name) as authorName from book b, author a, book_author ba"
      + " where b.id = ba.book_id and a.id = ba.author_id and b.name like ?1 group by b.id, b.name")
    List<Object[]> findByNameContaining(String name);

}

注：
1）这里使用 nativeQuery = true 指定使用原生 SQL 进行查询（个人觉得复杂的查询使用原生SQL更好
2）这里使用了 mysql 的内置函数 GROUP_CONCAT 进行行转列, HQL 无法直接识别。可能会出现 Caused by: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 的错误

JpaRepository.java
package com.johnfnash.learn.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.johnfnash.learn.domain.Author;

public interface AuthorRepository extends JpaRepository<Author, Integer> {

}
BookAuthorRepository.java
package com.johnfnash.learn.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.johnfnash.learn.domain.BookAuthor;

public interface BookAuthorRepository extends JpaRepository<BookAuthor, Integer> {

}

测试代码

package com.johnfnash.learn;

import static org.junit.Assert.assertEquals;

import java.util.List;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.johnfnash.learn.domain.Author;
import com.johnfnash.learn.domain.Book;
import com.johnfnash.learn.domain.BookAuthor;
import com.johnfnash.learn.repository.AuthorRepository;
import com.johnfnash.learn.repository.BookAuthorRepository;
import com.johnfnash.learn.repository.BookRepository;

@RunWith(SpringRunner.class)
@SpringBootTest
public class BookRepositoryTests {

  @Autowired
  private BookRepository bookRepository;

  @Autowired
  private AuthorRepository authorRepository;

  @Autowired
  private BookAuthorRepository bookAuthorRepository;

  @Before
  public void init() {
      Author lewis = new Author("Lewis");
      Author mark = new Author("Mark");
      Author peter = new Author("Peter");
      authorRepository.save(lewis);
      authorRepository.save(mark);
      authorRepository.save(peter);

      Book spring = new Book("Spring in Action");
      Book springboot = new Book("Spring Boot in Action");
      bookRepository.save(spring);
      bookRepository.save(springboot);

      bookAuthorRepository.save(new BookAuthor(spring.getId(), lewis.getId()));
      bookAuthorRepository.save(new BookAuthor(spring.getId(), mark.getId()));
      bookAuthorRepository.save(new BookAuthor(springboot.getId(), mark.getId()));
      bookAuthorRepository.save(new BookAuthor(springboot.getId(), peter.getId()));
  }

  @After
  public void deleteAll() {
    bookAuthorRepository.deleteAll();
    bookRepository.deleteAll();
    authorRepository.deleteAll();
  }

  @Test
  public void findAll() {
    assertEquals(bookRepository.findAll().size(), 2);
    assertEquals(authorRepository.findAll().size(), 3);

    List<Object[]> books = bookRepository.findByNameContaining("Spring%");
    for (Object[] book : books) {
      for (Object object : book) {
        System.out.print(object + ", ");
      }
      System.out.println();
    }
  }

}

执行 findAll 方法后，查询的相关 SQL 如下：

Hibernate: SELECT b.id, b.name, GROUP_CONCAT(a.name) as authorName from book b, author a, book_author ba where b.id = ba.book_id and a.id = ba.author_id and b.name like ? group by b.id, b.name

输出的结果如下：

3652, Spring in Action, Lewis,Mark, 
3653, Spring Boot in Action, Mark,Peter,

参考

(1) Spring Data JPA 实现多表关联查询

(2) springboot(五): spring data jpa的使用

(3) Spring Data JPA 实现多表关联查询

wml00000
关注
专栏目录
Spring Data JPA 实现多表关联查询的示例代码
08-27
在Spring Data JPA中，多表关联查询是一个常见的需求，特别是在复杂的业务场景下。本文将主要探讨通过创建结果集接口的方式来实现多表关联查询的方法，而不是依赖Hibernate的级联查询。 首先，我们要了解Spring ...
【jpa实现多表查询】
weixin_44103060的博客
10-28 2730
jpa多表关联查询
基于JPA实现多表连接查询
weixin_44157608的博客
08-21 7386
基于 JPA 显示城市信息一、JPAJPA 定义二、任务实战1.项目结构2.项目配置信息3.实体4.映射5.控制器6.UI三、效果图 一、JPA JPA 定义 Java 持久化 API Java 官方定义的 ORM 规范，只是一套规范，并没有提供底层的实现（基于抽象工厂设计模式，将定义与实现解耦），Hibernate 、TopLink 都是 JPA 的提供商，它们实现了 JPA 规范 JPA 基于注解的方式实现了 实体类 到 关系表 之间的映射 @Entity @Table @Id @Column @O
JPA多表查询
最新发布
m0_50272029的博客
09-06 465
结果集接口方法更灵活，可以通过自定义的查询结果接口来选择需要返回的字段。关联映射方法更方便管理关系，可以直接在实体类中通过@ManyToOne或@OneToMany等注解来实现关联查询。这两种方式在不同的场景下各有优势，具体选择哪种方式取决于项目的需求。
Spring Data JPA多表操作(5)
weixin_39248420的博客
12-01 854
Spring Data JPA多表操作(5) 数据库中多表之间的关系 多对多 一对多 一对一 数据库设计示例 实体示例 客户：指的是一家公司，我们记为A。 联系人：指的是A公司中的员工。 在不考虑兼职的情况下，公司和员工的关系即为一对多。 表设计示例 实体类关系设计 客户（公司）类（一） @Entity//表示当前类是一个实体类 @Table(name="cst_custom...
jpa 多表关联查询
srj904711648的博客
03-02 2509
jpa 多表关联查询
SpringDataJPA（三）：多表操作，复杂查询
you4580的专栏
07-29 2238
在实际开发中，我们数据库的表难免会有相互的关联关系，在操作表的时候就有可能会涉及到多张表的操作。有时我们在查询某个实体的时候，给定的条件是不固定的，这时就需要动态构建相应的查询语句，在Spring Data JPA中可以通过JpaSpecificationExecutor接口查询。多对多关系中：用中间表（第三章表）来描述这种关系，中间表中至少应该由两个字段组成，这两个字段作为外键指向两张表的主键，且这两个字段又组成了联合主键。在一对多关系中，我们习惯把一的一方称之为主表，把多的一方称之为从表。
Spring Data JPA 建立表的联合主键
08-26
在 Spring Data JPA 中，建立表的联合主键是一种常见的需求，本文将详细介绍如何使用 Spring Data JPA 建立表的联合主键，并提供了两种实现方式。 首先，我们需要了解什么是联合主键。在关系数据库中，主键是用于...
Spring Data JPA的优点和难点.pdf
12-05
- Spring Data JPA通过提供自动化的 Repository 实现，减少了大量手动编写SQL和DAO层代码的工作。开发者只需定义Repository接口，通过简单的查询方法名即可实现复杂的数据库查询。例如，`...
spring data jpa 的Specifications动态查询
02-07
Spring Data JPA是Spring框架的一个模块，它简化了Java应用程序对数据库的操作，通过提供自动的repository接口实现。在处理复杂的查询需求时，Spring Data JPA的`Specifications`接口提供了动态构建查询的能力，允许...
SpringBoot Data JPA 关联表查询的方法
08-27
今天，我将为大家介绍如何使用 SpringBoot Data JPA 实现一对多、多对一关联表查询。 首先，让我们来了解一下所需的开发环境： * IDEA 2017.1 * Java 1.8 * SpringBoot 2.0 * MySQL 5.X 我们的功能需求是通过关联...
Spring Data JPA实现多表的关联查询
热门推荐
pan_junbiao的博客
04-05 2万+
1、Spring Data JPA关系映射 对象关系映射（Object relational mapping）是指通过将对象状态映射到数据库列，来开发和维护对象和关系数据库之间的关系。它能够轻松处理（执行）各种数据库操作，如插入、更新、删除等。 关系映射的注解： 注解 说明 @JoinColumn 指定一个实体组织或实体集合。用在“多对一”和“一对多”的关联中。 @One...
Jpa多表查询
weixin_44420068的博客
03-17 1484
作业题目~~~~ API ###商品列表 GET /sell/buyer/product/list 参数 无 返回 { “code”: 0, “msg”: “成功”, “data”: [ { “name”: “热榜”, “type”: 1, “foods”: [ { “id”: “123456”, “name”: “皮蛋粥”, “price”: 1.2, “description”: “好...
Spring Data JPA多表查询的几种方法
q990609179的博客
11-29 1万+
Spring Data JPA多表查询的几种方法 前言 公司目前在ORM框架上选型只有两个选择 MyBatis-Plus Spring Data JPA 相信很多人会选择MyBatis-Plus（MyBatis-Plus（简称 MP）是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生）,主要因为JPA对多表复杂查询很不友好，特别是现在建表...
Spring Data JPA多表查询
liuduke1的博客
08-30 4613
多表查询在Spring Data JPA中有两种实现方式 第一种创建一个结果集接口来接收多表连查的结果 第二种利用JPA的关联映射来实现 先来熟悉一下几个注解 注解 意思 属性 @ManyToOne 定义了连接表之间的多对一的关系。 targetEntity属性表示关联的实体类型，可省略，fetch属性表示加载策略,FetchType的取值范围LAZY（延迟加载）默认上EAGER，cascade属性表示级联操作，CascadeType取值范围PERSIST（级联持久化）,REMOVE（级联删
jpa多表查询
qq_44049992的博客
04-13 639
因为被这个bug困扰了挺久，所以记录一下解决过程 参考文章： 参考文章1链接 参考文章2链接 想要实现faultquestion表和question表的联合查询 采用方法：用接口接收查询结果集，接口中需要有一个方法返回结果 default String getFaultResultModel() { return "studentId:" + getStudentId() + ";\n" + "questionId:" + getQuestionId(
JPA的多表复杂查询：详细篇
xuxuchuan的博客
10-21 661
最近工作中由于要求只能用hibernate+jpa 与数据库进行交互，在简单查询中，jpa继承CrudRepository接口 ，然后利用jpa的方法命名规范进行jpql查询，然而在进行复杂查询时，需要继承JpaSpecificationExecutor接口利用Specification进行复杂查询，由于我自己就遇到了这一问题，查了好多资料，虽然有方法，但是都没有一个详细的讲解，以至于知道方法而不...
Spring Data JPA 多表关联查询的实现
结缘缘的博客
07-20 2927
Spring Data JPA 多表关联查询的实现
Spring Data JPA 连表查询：接口方式实现示例
"本文将介绍如何使用Spring Data JPA实现多表关联查询，重点在于第二种方式，即通过创建结果集接口来处理连表查询的结果。这种方法对于理解和掌握Spring Data JPA的高级用法非常有帮助。我们将通过一个一对一映射的...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值