Spring Data JPA 实现多表关联查询的示例代码

2023-09-10 18:18:05 257

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

一、一对一映射

实体UserInfo：用户。

实体Address：家庭住址。

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

实体类

1、实体类UserInfo.java

packagecom.johnfnash.learn.domain;

importjava.io.Serializable;

importjavax.persistence.Entity;
importjavax.persistence.GeneratedValue;
importjavax.persistence.GenerationType;
importjavax.persistence.Id;
importjavax.persistence.Table;

@Entity
@Table(name="tb_user")
publicclassUserInfoimplementsSerializable{
privatestaticfinallongserialVersionUID=8283950216116626180L;

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
privateLonguserId;
privateStringname;
privateintage;
privateStringsex;
privateStringemail;

//与Address的关联
privateLongaddressId;

publicUserInfo(){
super();
}

publicUserInfo(Stringname,intage,Stringsex,Stringemail,LongaddressId){
super();
this.name=name;
this.age=age;
this.sex=sex;
this.email=email;
this.addressId=addressId;
}

//getter,setter

@Override
publicStringtoString(){
returnString.format("UserInfo[userId=%d,name=%s,age=%s,sex=%s,email=%s]",userId,name,age,sex,email);
}

}

2.实体类Address.java

packagecom.johnfnash.learn.domain;

importjavax.persistence.Entity;
importjavax.persistence.GeneratedValue;
importjavax.persistence.GenerationType;
importjavax.persistence.Id;
importjavax.persistence.Table;

@Entity
@Table(name="tb_address")
publicclassAddress{

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
privateLongaddressId;
privateStringareaCode;
privateStringcountry;
privateStringprovince;
privateStringcity;
privateStringarea;
privateStringdetailAddress;

publicAddress(){
super();
}

publicAddress(StringareaCode,Stringcountry,Stringprovince,Stringcity,Stringarea,
StringdetailAddress){
super();
this.areaCode=areaCode;
this.country=country;
this.province=province;
this.city=city;
this.area=area;
this.detailAddress=detailAddress;
}

//getter,setter

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

}

Dao层

1、UserInfoRepository.java

packagecom.johnfnash.learn.repository;

importjava.util.List;

importorg.springframework.data.jpa.repository.JpaRepository;
importorg.springframework.data.jpa.repository.Query;

importcom.johnfnash.learn.domain.UserInfo;
importcom.johnfnash.learn.domain.ViewInfo;

publicinterfaceUserInfoRepositoryextendsJpaRepository{

@Query(value="SELECTnewcom.johnfnash.learn.domain.ViewInfo(u,a)FROMUserInfou,AddressaWHEREu.addressId=a.addressId")
publicListfindViewInfo();

}

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

代码如下：

packagecom.johnfnash.learn.domain;

importjava.io.Serializable;

publicclassViewInfoimplementsSerializable{

privatestaticfinallongserialVersionUID=-6347911007178390219L;

privateUserInfouserInfo;
privateAddressaddress;

publicViewInfo(){

}

publicViewInfo(UserInfouserInfo){
Addressaddress=newAddress();
this.userInfo=userInfo;
this.address=address;
}

publicViewInfo(Addressaddress){
UserInfouserInfo=newUserInfo();
this.userInfo=userInfo;
this.address=address;
}

publicViewInfo(UserInfouserInfo,Addressaddress){
this.userInfo=userInfo;
this.address=address;
}

//getter,setter

}

2.AddressRepository.java

packagecom.johnfnash.learn.repository;

importorg.springframework.data.jpa.repository.JpaRepository;

importcom.johnfnash.learn.domain.Address;

publicinterfaceAddressRepositoryextendsJpaRepository{

}

测试代码

packagecom.johnfnash.learn;

importjava.util.List;

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

importcom.johnfnash.learn.domain.Address;
importcom.johnfnash.learn.domain.UserInfo;
importcom.johnfnash.learn.domain.ViewInfo;
importcom.johnfnash.learn.repository.AddressRepository;
importcom.johnfnash.learn.repository.UserInfoRepository;

@RunWith(SpringRunner.class)
@SpringBootTest
publicclassUserInfoRepositoryTests{

@Autowired
privateUserInfoRepositoryuserInfoRepository;

@Autowired
privateAddressRepositoryaddressRepository;

@Before
publicvoidinit(){
Addressaddr1=newAddress("027","CN","HuBei","WuHan","WuChang","123street");
Addressaddr2=newAddress("023","CN","ChongQing","ChongQing","YuBei","123road");
addressRepository.save(addr1);
addressRepository.save(addr2);

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

@After
publicvoiddeleteAll(){
userInfoRepository.deleteAll();

addressRepository.deleteAll();
}

@Test
publicvoidtestQuery(){
ListviewInfos=userInfoRepository.findViewInfo();
for(ViewInfoviewInfo:viewInfos){
System.out.println(viewInfo.getUserInfo());
System.out.println(viewInfo.getAddress());
}
}

}

查询相关的sql如下：

Hibernate:selectuserinfo0_.user_idascol_0_0_,address1_.address_idascol_1_0_fromtb_useruserinfo0_crossjointb_addressaddress1_whereuserinfo0_.address_id=address1_.address_id
Hibernate:selectuserinfo0_.user_idasuser_id1_4_0_,userinfo0_.address_idasaddress_2_4_0_,userinfo0_.ageasage3_4_0_,userinfo0_.emailasemail4_4_0_,userinfo0_.nameasname5_4_0_,userinfo0_.sexassex6_4_0_fromtb_useruserinfo0_whereuserinfo0_.user_id=?
Hibernate:selectaddress0_.address_idasaddress_1_3_0_,address0_.areaasarea2_3_0_,address0_.area_codeasarea_cod3_3_0_,address0_.cityascity4_3_0_,address0_.countryascountry5_3_0_,address0_.detail_addressasdetail_a6_3_0_,address0_.provinceasprovince7_3_0_fromtb_addressaddress0_whereaddress0_.address_id=?
Hibernate:selectuserinfo0_.user_idasuser_id1_4_0_,userinfo0_.address_idasaddress_2_4_0_,userinfo0_.ageasage3_4_0_,userinfo0_.emailasemail4_4_0_,userinfo0_.nameasname5_4_0_,userinfo0_.sexassex6_4_0_fromtb_useruserinfo0_whereuserinfo0_.user_id=?
Hibernate:selectaddress0_.address_idasaddress_1_3_0_,address0_.areaasarea2_3_0_,address0_.area_codeasarea_cod3_3_0_,address0_.cityascity4_3_0_,address0_.countryascountry5_3_0_,address0_.detail_addressasdetail_a6_3_0_,address0_.provinceasprovince7_3_0_fromtb_addressaddress0_whereaddress0_.address_id=?
Hibernate:selectuserinfo0_.user_idasuser_id1_4_,userinfo0_.address_idasaddress_2_4_,userinfo0_.ageasage3_4_,userinfo0_.emailasemail4_4_,userinfo0_.nameasname5_4_,userinfo0_.sexassex6_4_fromtb_useruserinfo0_
Hibernate:selectaddress0_.address_idasaddress_1_3_,address0_.areaasarea2_3_,address0_.area_codeasarea_cod3_3_,address0_.cityascity4_3_,address0_.countryascountry5_3_,address0_.detail_addressasdetail_a6_3_,address0_.provinceasprovince7_3_fromtb_addressaddress0_

查询结果如下：

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=123street]
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=123road]

二、多对多映射

实体Author：作者。

实体Book：书籍

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

实体类

实体类：Author.java

packagecom.johnfnash.learn.domain;

importjava.io.Serializable;

importjavax.persistence.Entity;
importjavax.persistence.GeneratedValue;
importjavax.persistence.Id;

@Entity
publicclassAuthorimplementsSerializable{

privatestaticfinallongserialVersionUID=1227555837798655046L;

@Id
@GeneratedValue
privateIntegerid;

privateStringname;

publicAuthor(){
super();
}

publicAuthor(Stringname){
super();
this.name=name;
}

//getter,setter

@Override
publicStringtoString(){
returnString.format("Author[id=%s,name=%s]",id,name);
}

}

Book.java实体类

packagecom.johnfnash.learn.domain;

importjava.io.Serializable;

importjavax.persistence.Entity;
importjavax.persistence.GeneratedValue;
importjavax.persistence.Id;

@Entity
publicclassBookimplementsSerializable{

privatestaticfinallongserialVersionUID=-2470510857424220408L;

@Id
@GeneratedValue
privateIntegerid;

privateStringname;

publicBook(){
super();
}

publicBook(Stringname){
super();
this.name=name;
}

//getter,setter

@Override
publicStringtoString(){
returnString.format("Book[id=%s,name=%s]",id,name);
}

}

实体类BookAuthor.java

packagecom.johnfnash.learn.domain;

importjavax.persistence.Entity;
importjavax.persistence.Id;
importjavax.persistence.IdClass;
importjavax.persistence.Table;

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

@Id
privateIntegerbookId;

@Id
privateIntegerauthorId;

publicBookAuthor(){
super();
}

publicBookAuthor(IntegerbookId,IntegerauthorId){
super();
this.bookId=bookId;
this.authorId=authorId;
}

//getter,setter

}

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

packagecom.johnfnash.learn.domain;

importjava.io.Serializable;

publicclassBookAuthorPKimplementsSerializable{

privatestaticfinallongserialVersionUID=-1158141803682305656L;

privateIntegerbookId;

privateIntegerauthorId;

publicIntegergetBookId(){
returnbookId;
}

publicvoidsetBookId(IntegerbookId){
this.bookId=bookId;
}

publicIntegergetAuthorId(){
returnauthorId;
}

publicvoidsetAuthorId(IntegerauthorId){
this.authorId=authorId;
}

}

Dao层

BookRepository.java

packagecom.johnfnash.learn.repository;

importjava.util.List;

importorg.springframework.data.jpa.repository.JpaRepository;
importorg.springframework.data.jpa.repository.Query;

importcom.johnfnash.learn.domain.Book;

publicinterfaceBookRepositoryextendsJpaRepository{

@Query(nativeQuery=true,value="SELECTb.id,b.name,GROUP_CONCAT(a.name)asauthorNamefrombookb,authora,book_authorba"
+"whereb.id=ba.book_idanda.id=ba.author_idandb.namelike?1groupbyb.id,b.name")
ListfindByNameContaining(Stringname);

}

注：

1）这里使用nativeQuery=true指定使用原生SQL进行查询（个人觉得复杂的查询使用原生SQL更好

2）这里使用了mysql的内置函数GROUP_CONCAT进行行转列,HQL无法直接识别。可能会出现Causedby:org.hibernate.QueryException:Nodatatypefornode:org.hibernate.hql.internal.ast.tree.MethodNode的错误

JpaRepository.java

packagecom.johnfnash.learn.repository;

importorg.springframework.data.jpa.repository.JpaRepository;

importcom.johnfnash.learn.domain.Author;

publicinterfaceAuthorRepositoryextendsJpaRepository{

}

BookAuthorRepository.java

packagecom.johnfnash.learn.repository;

importorg.springframework.data.jpa.repository.JpaRepository;

importcom.johnfnash.learn.domain.BookAuthor;

publicinterfaceBookAuthorRepositoryextendsJpaRepository{

}

测试代码

packagecom.johnfnash.learn;

importstaticorg.junit.Assert.assertEquals;

importjava.util.List;

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

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

@RunWith(SpringRunner.class)
@SpringBootTest
publicclassBookRepositoryTests{

@Autowired
privateBookRepositorybookRepository;

@Autowired
privateAuthorRepositoryauthorRepository;

@Autowired
privateBookAuthorRepositorybookAuthorRepository;

@Before
publicvoidinit(){
Authorlewis=newAuthor("Lewis");
Authormark=newAuthor("Mark");
Authorpeter=newAuthor("Peter");
authorRepository.save(lewis);
authorRepository.save(mark);
authorRepository.save(peter);

Bookspring=newBook("SpringinAction");
Bookspringboot=newBook("SpringBootinAction");
bookRepository.save(spring);
bookRepository.save(springboot);

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

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

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

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

}

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

Hibernate:SELECTb.id,b.name,GROUP_CONCAT(a.name)asauthorNamefrombookb,authora,book_authorbawhereb.id=ba.book_idanda.id=ba.author_idandb.namelike?groupbyb.id,b.name

输出的结果如下：

3652,SpringinAction,Lewis,Mark,
3653,SpringBootinAction,Mark,Peter,

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。

Spring Data JPA 实现多表关联查询的示例代码

热门推荐

随机推荐