Spring Data JPA 多表关联查询的实现

多表查询在spring data jpa中有两种实现方式，第一种是利用hibernate的级联查询来实现，第二种是创建一个结果集的接口来接收连表查询后的结果。第一种方式需要继承JpaSpecificationExecutor接口，利用Specification 进行复杂查询，还需要定义好表之间的映射关系，比较复杂。所以这里还是介绍第二种比较简单的方式。

一对一映射

1. 创建实体类：

• UserInfo类

@Entity
@Data
@Table(name="tb_user")
public class UserInfo implements Serializable {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long userId;
    private String name;
    private int age;
    private String sex;
    private String email;

    // 与 Address 的关联
    private Long addressId;
 }

• Address类

@Entity
@Data
@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;
 }

• PartViewInfo类(关联查询的结果存储类)

@Data
public class PartViewInfo {
    private String name;
    private String areaCode;
	
	//满参构造函数
	public PartViewInfo(String name, String areaCode) {
        this.name = name;
        this.areaCode = areaCode;
    }
 }

2. 持久层：

• AddressRepository接口

public interface AddressRepository extends JpaRepository<Address, Long> {
}

• UserInfoRepository

public interface UserInfoRepository extends JpaRepository<UserInfo, Long> {
    @Query("select new com.example.jpatestformanytable.entity.PartViewInfo(u.name, a.areaCode) from UserInfo u, Address a where u.addressId = a.addressId")
    public List<PartViewInfo> findViewInfo2();
}

3. 测试代码：

@SpringBootTest
public class sdda {

    @Autowired
    private UserInfoRepository userInfoRepository;

    @Autowired
    private AddressRepository addressRepository;


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


    public void deleteAll() {
        userInfoRepository.deleteAll();

        addressRepository.deleteAll();
    }

    @Test
    public void testQuery() {
        init();
        List<PartViewInfo> partViewInfoList = userInfoRepository.findViewInfo2();
        for (PartViewInfo partViewInfo : partViewInfoList) {
            System.out.println(partViewInfo);
        }
        deleteAll();
    }

}

4. 测试结果：

• 使用到的相关sql语句

Hibernate: insert into tb_address (area, area_code, city, country, detail_address, province) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into tb_address (area, area_code, city, country, detail_address, province) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into tb_user (address_id, age, email, name, sex) values (?, ?, ?, ?, ?)
Hibernate: insert into tb_user (address_id, age, email, name, sex) values (?, ?, ?, ?, ?)
Hibernate: select userinfo0_.name as col_0_0_, address1_.area_code 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_1_, userinfo0_.address_id as address_2_1_, userinfo0_.age as age3_1_, userinfo0_.email as email4_1_, userinfo0_.name as name5_1_, userinfo0_.sex as sex6_1_ from tb_user userinfo0_
Hibernate: delete from tb_user where user_id=?
Hibernate: delete from tb_user where user_id=?
Hibernate: select address0_.address_id as address_1_0_, address0_.area as area2_0_, address0_.area_code as area_cod3_0_, address0_.city as city4_0_, address0_.country as country5_0_, address0_.detail_address as detail_a6_0_, address0_.province as province7_0_ from tb_address address0_
Hibernate: delete from tb_address where address_id=?
Hibernate: delete from tb_address where address_id=?

• 得到的结果

PartViewInfo{name='ZS', areaCode='027'}
PartViewInfo{name='Ww', areaCode='023'}

5. 易出问题的地方：

• 持久层的sql语句中，定义别名后就要全用别名，否则报错。
• 结果类中，要自定义一个满参数的构造函数，不能靠Lombok。

参考

Spring Data JPA 实现多表关联查询