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

}

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 + "]";
 }

}

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

}

注：这里的 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

}

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

}

测试代码

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","[e-mail]", addr1.getaddressid());
    userinfo user2 = new userinfo("ww", 25,"male","[e-mail]", 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());
  }
 }

}

查询相关的 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_

查询结果如下：

userinfo [userid=1, name=zs, age=21, sex=male, ]
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, ]
address [addressid=2, areacode=023, country=cn, province=chongqing, city=chongqing, area=yubei, detailaddress=123 road]

二、多对多映射

实体 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;

  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 实体类

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

}

实体类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

}

注：这里使用 @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;
 }

}

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）这里使用 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> {

}

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

}

测试代码

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

}

执行 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

输出的结果如下：

3652, spring in action, lewis,mark,
3653, spring boot in action, mark,peter,

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