Spring Data JPA 多表關(guān)聯(lián)查詢的實(shí)現(xiàn)

多表查詢?cè)趕pring data jpa中有兩種實(shí)現(xiàn)方式，第一種是利用hibernate的級(jí)聯(lián)查詢來(lái)實(shí)現(xiàn)，第二種是創(chuàng)建一個(gè)結(jié)果集的接口來(lái)接收連表查詢后的結(jié)果。第一種方式需要繼承JpaSpecificationExecutor接口，利用Specification 進(jìn)行復(fù)雜查詢，還需要定義好表之間的映射關(guān)系，比較復(fù)雜。所以這里還是介紹第二種比較簡(jiǎn)單的方式。

一對(duì)一映射

1. 創(chuàng)建實(shí)體類：

• 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 的關(guān)聯(lián)
    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類(關(guān)聯(lián)查詢的結(jié)果存儲(chǔ)類)

@Data
public class PartViewInfo {
    private String name;
    private String areaCode;
	
	//滿參構(gòu)造函數(shù)
	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. 測(cè)試代碼：

@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. 測(cè)試結(jié)果：

• 使用到的相關(guān)sql語(yǔ)句

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=?

• 得到的結(jié)果

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

5. 易出問(wèn)題的地方：

• 持久層的sql語(yǔ)句中，定義別名后就要全用別名，否則報(bào)錯(cuò)。
• 結(jié)果類中，要自定義一個(gè)滿參數(shù)的構(gòu)造函數(shù)，不能靠Lombok。

參考

Spring Data JPA 實(shí)現(xiàn)多表關(guān)聯(lián)查詢