Spring Data JPA 实现多表关联查询的示例代码
多表查询在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") publicList findViewInfo(); }
注:这里的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") List
注:
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); List
执行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,
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持毛票票。