|
Spring Data JPA 多表关联查询的实现
多表查询在spring data jpa中有两种实现方式，第一种是利用hibernate的级联查询来实现，第二种是创建一个结果集的接口来接收连表查询后的结果。第一种方式需要继承JpaSpecificationExecutor接口，利用Specification 进行复杂查询，还需要定义好表之间的映射关系，比较复杂。所以这里还是介绍第二种比较简单的方式。
一对一映射
创建实体类：
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;
}
}
持久层：
AddressRepository接口
public interface AddressRepository extends JpaRepository {
}
UserInfoRepository
public interface UserInfoRepository extends JpaRepository {
@Query("select new com.example.jpatestformanytable.entity.PartViewInfo(u.name, a.areaCode) from UserInfo u, Address a where u.addressId = a.addressId")
public List findViewInfo2();
}
测试代码：
@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","[email protected]", addr1.getAddressId());
UserInfo user2 = new UserInfo("Ww", 25,"Male","[email protected]", addr2.getAddressId());
userInfoRepository.save(user1);
userInfoRepository.save(user2);
}
public void deleteAll() {
userInfoRepository.deleteAll();
addressRepository.deleteAll();
}
@Test
public void testQuery() {
init();
List partViewInfoList = userInfoRepository.findViewInfo2();
for (PartViewInfo partViewInfo : partViewInfoList) {
System.out.println(partViewInfo);
}
deleteAll();
}
}
测试结果：
使用到的相关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'}
易出问题的地方：
持久层的sql语句中，定义别名后就要全用别名，否则报错。结果类中，要自定义一个满参数的构造函数，不能靠Lombok。
参考
Spring Data JPA 实现多表关联查询
|