一区二区三区在线-一区二区三区亚洲视频-一区二区三区亚洲-一区二区三区午夜-一区二区三区四区在线视频-一区二区三区四区在线免费观看

服務(wù)器之家：專注于服務(wù)器技術(shù)及軟件下載分享
分類導(dǎo)航

PHP教程|ASP.NET教程|Java教程|ASP教程|編程技術(shù)|正則表達(dá)式|C/C++|IOS|C#|Swift|Android|VB|R語(yǔ)言|JavaScript|易語(yǔ)言|vb.net|

服務(wù)器之家 - 編程語(yǔ)言 - Java教程 - Spring Data JPA 實(shí)現(xiàn)多表關(guān)聯(lián)查詢的示例代碼

Spring Data JPA 實(shí)現(xiàn)多表關(guān)聯(lián)查詢的示例代碼

2021-05-13 11:39我飛故我在的專欄 Java教程

多表查詢?cè)趕pring data jpa中有兩種實(shí)現(xiàn)方式，第一種是利用hibernate的級(jí)聯(lián)查詢來(lái)實(shí)現(xiàn)，第二種是創(chuàng)建一個(gè)結(jié)果集的接口來(lái)接收連表查詢后的結(jié)果，這里介紹第二種方式，小編覺得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起

多表查詢?cè)趕pring data jpa中有兩種實(shí)現(xiàn)方式，第一種是利用hibernate的級(jí)聯(lián)查詢來(lái)實(shí)現(xiàn)，第二種是創(chuàng)建一個(gè)結(jié)果集的接口來(lái)接收連表查詢后的結(jié)果，這里介紹第二種方式。

一、一對(duì)一映射

實(shí)體 UserInfo ：用戶。

實(shí)體 Address：家庭住址。

這里通過(guò)外鍵的方式(一個(gè)實(shí)體通過(guò)外鍵關(guān)聯(lián)到另一個(gè)實(shí)體的主鍵)來(lái)實(shí)現(xiàn)一對(duì)一關(guān)聯(lián)。

實(shí)體類

1、實(shí)體類 UserInfo.java

?
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
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 的關(guān)聯(lián)
 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. 實(shí)體類 Address.java

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 類用來(lái)一個(gè)用來(lái)接收多表查詢結(jié)果集的類（使用 new + 完整類名構(gòu)造函數(shù)）

代碼如下：

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

?
1
2
3
4
5
6
7
8
9
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> {
 
}

測(cè)試代碼

?
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
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","[email protected]", addr1.getAddressId());
    UserInfo user2 = new UserInfo("Ww", 25,"Male","[email protected]", 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());
  }
 }
 
}

查詢相關(guān)的 sql 如下：

?
1
2
3
4
5
6
7
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_

查詢結(jié)果如下：

UserInfo [userId=1, name=ZS, age=21, sex=Male, [email protected]]
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 protected]]
Address [addressId=2, areaCode=023, country=CN, province=ChongQing, city=ChongQing, area=YuBei, detailAddress=123 road]

二、多對(duì)多映射

實(shí)體 Author ：作者。

實(shí)體 Book ：書籍

這里通過(guò)關(guān)聯(lián)表的方式來(lái)實(shí)現(xiàn)多對(duì)多關(guān)聯(lián)。

實(shí)體類

實(shí)體類：Author.java

?
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
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 實(shí)體類

?
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
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);
 }
 
}

實(shí)體類BookAuthor.java

?
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
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 注解指定一個(gè)聯(lián)合主鍵類來(lái)映射實(shí)體類的多個(gè)屬性。這個(gè)聯(lián)合主鍵類的代碼如下：

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 進(jìn)行查詢（個(gè)人覺得復(fù)雜的查詢使用原生SQL更好

2）這里使用了 mysql 的內(nèi)置函數(shù) GROUP_CONCAT 進(jìn)行行轉(zhuǎn)列, HQL 無(wú)法直接識(shí)別。可能會(huì)出現(xiàn) Caused by: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 的錯(cuò)誤

JpaRepository.java

?
1
2
3
4
5
6
7
8
9
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

?
1
2
3
4
5
6
7
8
9
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> {
 
}

測(cè)試代碼

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

執(zhí)行 findAll 方法后，查詢的相關(guān) SQL 如下：

?
1
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

輸出的結(jié)果如下：

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

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持服務(wù)器之家。

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

延伸 · 閱讀

精彩推薦
主站蜘蛛池模板： 精品第一国产综合精品蜜芽 | 99久久一区二区精品 | 亚洲欧美日韩中文字幕久久 | 亚洲精美视频 | 国产亚洲精品网站 | 免费视频网 | 欧美日韩专区国产精品 | 熟睡中的麻麻大白屁股小说 | 国产在线观看91精品一区 | 国产精品成人在线播放 | hd性欧美俱乐部中文 | 国产精品成人扳一级aa毛片 | videojapan日本孕交孕 | 国产精品视频第一区二区三区 | 韩国理论片最新第一页 | 亚洲国产精品二区久久 | 亚洲国产成人资源在线桃色 | 天美麻豆 | 暖暖免费高清完整版观看日本 | 玩乳h文奶水和尚 | 日本不卡一区二区三区在线观看 | 精品一区在线 | 海绵宝宝第二季全集免费观看 | 国产精品国产香蕉在线观看网 | chinese帅男gay野外性 | 久久视热频国产这里只有精品23 | 日本无翼乌漫画 | 国产成人精选免费视频 | 国产三级精品91三级在专区 | 欧美成人禁片在线观看俄罗斯 | 美女的让男人桶爽网站 | 奇米小说| 99九九国产精品免费视频 | 亚洲欧美日韩综合在线 | 美女黑人做受xxxxxⅹ | free service性v极品 | 国产精品亚欧美一区二区三区 | 四虎精品永久在线网址 | 日韩精品中文字幕久久 | 日本色女 | 精品视频国产 |