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 實現多表關聯查詢