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<Object[]> 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