国产片侵犯亲女视频播放_亚洲精品二区_在线免费国产视频_欧美精品一区二区三区在线_少妇久久久_在线观看av不卡

服務(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é)果，這里介紹第二種方式，小編覺(jué)得挺不錯(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","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());
  }
 }
 
}

查詢相關(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=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]

二、多對(duì)多映射

實(shí)體 Author ：作者。

實(shí)體 Book ：書(shū)籍

這里通過(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è)人覺(jué)得復(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

延伸 · 閱讀

精彩推薦
Weibo Article 1 Weibo Article 2 Weibo Article 3 Weibo Article 4 Weibo Article 5 Weibo Article 6 Weibo Article 7 Weibo Article 8 Weibo Article 9 Weibo Article 10 Weibo Article 11 Weibo Article 12 Weibo Article 13 Weibo Article 14 Weibo Article 15 Weibo Article 16 Weibo Article 17 Weibo Article 18 Weibo Article 19 Weibo Article 20 Weibo Article 21 Weibo Article 22 Weibo Article 23 Weibo Article 24 Weibo Article 25 Weibo Article 26 Weibo Article 27 Weibo Article 28 Weibo Article 29 Weibo Article 30 Weibo Article 31 Weibo Article 32 Weibo Article 33 Weibo Article 34 Weibo Article 35 Weibo Article 36 Weibo Article 37 Weibo Article 38 Weibo Article 39 Weibo Article 40
主站蜘蛛池模板： 精品一区二区三区中文字幕 | 亚洲国产精品久久久 | 成人免费观看高清视频 | 欧美日韩一级视频 | 亚洲视频免费在线观看 | 欧美日韩国产一区二区 | 99视频在线免费观看 | 日韩在线区 | 免费成人看片 | 丁香五月网久久综合 | 亚洲视频在线观看免费 | 亚洲综合一二区 | 中国一极毛片 | 色视频在线看 | 搞黄在线观看 | 一级片国产 | 韩日av在线 | 亚洲毛片 | 欧美亚洲在线 | 色女人av| 综合av在线| 亚洲激情在线 | 欧美精品一区二区三区在线 | 精久久 | 国产精品久久久久久中文字 | 亚洲不卡 | 五月天黄色片 | 日本高清无卡码一区二区久久 | 中文字幕免费看 | 69久久久久久 | 亚洲免费看片 | 欧美伦理一区二区三区 | yiren22成人网 | 亚洲欧美在线观看视频 | 高清精品一区二区 | 亚洲激情综合在线 | 亚洲 欧美 日韩 在线 | 日韩欧美国产一区二区三区 | 一区在线观看 | www.久久精品 | 黄色免费在线观看 |