sunny123456

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
Spring Data JPA 实现多表关联查询

Spring Data JPA 实现多表关联查询

多表查询在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);

}


}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47

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 + "]";

}


}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47

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();


}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

注：这里的 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


}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35

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> {


}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

测试代码

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(<span class="hljs-string">"ZS"</span>, <span class="hljs-number">21</span>,<span class="hljs-string">"Male"</span>,<span class="hljs-string">"123@xx.com"</span>, addr1<span class="hljs-preprocessor">.getAddressId</span>())<span class="hljs-comment">;</span>
    UserInfo user2 = new UserInfo(<span class="hljs-string">"Ww"</span>, <span class="hljs-number">25</span>,<span class="hljs-string">"Male"</span>,<span class="hljs-string">"234@xx.com"</span>, addr2<span class="hljs-preprocessor">.getAddressId</span>())<span class="hljs-comment">;</span>
    userInfoRepository<span class="hljs-preprocessor">.save</span>(user1)<span class="hljs-comment">;</span>
    userInfoRepository<span class="hljs-preprocessor">.save</span>(user2)<span class="hljs-comment">;</span>
}



@After

public void deleteAll() {

userInfoRepository.deleteAll();


addressRepository<span class="hljs-preprocessor">.deleteAll</span>()<span class="hljs-comment">;</span>



}


@Test

public void testQuery() {

List<ViewInfo> viewInfos = userInfoRepository.findViewInfo();

for (ViewInfo viewInfo : viewInfos) {

System.out.println(viewInfo.getUserInfo());

System.out.println(viewInfo.getAddress());

}

}


}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58

查询相关的 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_
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

查询结果如下：

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]
  • 1
  • 2
  • 3
  • 4

二、多对多映射

实体 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;


<span class="hljs-keyword">private</span> 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);

}


}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

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;


<span class="hljs-keyword">private</span> String name;

<span class="hljs-keyword">public</span> <span class="hljs-title">Book</span>() {
    <span class="hljs-keyword">super</span>();
}

<span class="hljs-keyword">public</span> <span class="hljs-title">Book</span>(String name) {
    <span class="hljs-keyword">super</span>();
    <span class="hljs-keyword">this</span>.name = name;
}



//getter, setter


@Override

public String toString() {

return String.format("Book [id=%s, name=%s]", id, name);

}


}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

实体类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


}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

注：这里使用 @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;

}


}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

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
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

注：
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> {


}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

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> {


}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

测试代码

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(<span class="hljs-string">"Spring in Action"</span>)<span class="hljs-comment">;</span>
  Book springboot = new Book(<span class="hljs-string">"Spring Boot in Action"</span>)<span class="hljs-comment">;</span>
  bookRepository<span class="hljs-preprocessor">.save</span>(spring)<span class="hljs-comment">;</span>
  bookRepository<span class="hljs-preprocessor">.save</span>(springboot)<span class="hljs-comment">;</span>

  bookAuthorRepository<span class="hljs-preprocessor">.save</span>(new BookAuthor(spring<span class="hljs-preprocessor">.getId</span>(), lewis<span class="hljs-preprocessor">.getId</span>()))<span class="hljs-comment">;</span>
  bookAuthorRepository<span class="hljs-preprocessor">.save</span>(new BookAuthor(spring<span class="hljs-preprocessor">.getId</span>(), mark<span class="hljs-preprocessor">.getId</span>()))<span class="hljs-comment">;</span>
  bookAuthorRepository<span class="hljs-preprocessor">.save</span>(new BookAuthor(springboot<span class="hljs-preprocessor">.getId</span>(), mark<span class="hljs-preprocessor">.getId</span>()))<span class="hljs-comment">;</span>
  bookAuthorRepository<span class="hljs-preprocessor">.save</span>(new BookAuthor(springboot<span class="hljs-preprocessor">.getId</span>(), peter<span class="hljs-preprocessor">.getId</span>()))<span class="hljs-comment">;</span>



}


@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&lt;Object[]&gt; books = bookRepository<span class="hljs-preprocessor">.findByNameContaining</span>(<span class="hljs-string">"Spring%"</span>)<span class="hljs-comment">;</span>
for (Object[] book : books) {
  for (Object object : book) {
    System<span class="hljs-preprocessor">.out</span><span class="hljs-preprocessor">.print</span>(object + <span class="hljs-string">", "</span>)<span class="hljs-comment">;</span>
  }
  System<span class="hljs-preprocessor">.out</span><span class="hljs-preprocessor">.println</span>()<span class="hljs-comment">;</span>
}



}


}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76

执行 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
  • 1

输出的结果如下：

3652, Spring in Action, Lewis,Mark, 
3653, Spring Boot in Action, Mark,Peter, 
  • 1
  • 2

参考

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

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

https://blog.csdn.net/johnf_nash/article/details/80587204
posted on 2022-03-24 19:48  sunny123456  阅读(1306)  评论(0)  编辑  收藏  举报