我读过一些有关如何在JPA中解决n+1查询问题的文章,但是它们都没能解决我的问题。
当我尝试获取数据时,JPA会做出n+1个查询。
select owner0_.id as id1_1_, owner0_.created_at as created_2_1_, owner0_.updated_at as updated_3_1_, owner0_.name as name4_1_, owner0_.version as version5_1_ from owner owner0_
select cars0_.owner_id as owner_id6_0_0_, cars0_.id as id1_0_0_, cars0_.id as id1_0_1_, cars0_.created_at as created_2_0_1_, cars0_.updated_at as updated_3_0_1_, cars0_.license_no as license_4_0_1_, cars0_.owner_id as owner_id6_0_1_, cars0_.version as version5_0_1_ from car cars0_ where cars0_.owner_id=? [1]
select cars0_.owner_id as owner_id6_0_0_, cars0_.id as id1_0_0_, cars0_.id as id1_0_1_, cars0_.created_at as created_2_0_1_, cars0_.updated_at as updated_3_0_1_, cars0_.license_no as license_4_0_1_, cars0_.owner_id as owner_id6_0_1_, cars0_.version as version5_0_1_ from car cars0_ where cars0_.owner_id=? [2]
以下是代码片段:
@Entity
public class Owner extends BaseEntity implements EntityTransformer<OwnerDto> {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@Version
private Long version;
@OneToMany(mappedBy = "owner", fetch = FetchType.LAZY)
private Set<Car> cars;
@Override
public OwnerDto convertToDto() {
OwnerDto ownerDto = new OwnerDto();
ownerDto.setId(this.getId());
ownerDto.setName(this.getName());
ownerDto.setVersion(this.getVersion());
if (this.getCars() != null) ownerDto.setCars(this.getCars().stream().map(Car::convertToDto).collect(Collectors.toSet()));
return ownerDto;
}
}
我的Car类如下所示:
@Entity
public class Car {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String licenseNo;
@Version
private Integer version;
@JoinColumn( name = "owner_id" )
@ManyToOne(fetch = FetchType.LAZY, optional = false)
private Owner owner;
@Override
public CarDto convertToDto() {
CarDto carDto = new CarDto();
carDto.setId(this.getId());
carDto.setLicenseNo(this.getLicenseNo());
carDto.setVersion(this.getVersion());
return carDto;
}
}
OwnerService:
@Service
public class OwnerServiceImpl implements OwnerService {
@Autowired
OwnerRepository ownerRepository;
@Override
public List<Owner> findAll() {
return ownerRepository.findAll();
}
}
OwnerController:
@RestController
public class OwnerController {
@Autowired
private OwnerService ownerService;
@GetMapping(value = "/owners", produces = "application/vnd.demo.api.v1+json")
public ResponseEntity<List<OwnerDto>> findAll() {
return ResponseEntity.ok(ownerService.findAll().stream().map(Owner::convertToDto).collect(Collectors.toList()));
}
}
cURL:
curl -X POST \
http://localhost:8080/owner \
-H 'Accept: application/vnd.demo.api.v1+json' \
-H 'Content-Type: application/json' \
-H 'Host: localhost:8080' \
-d '{
"name": "pranay5"
}'
curl -X POST \
http://localhost:8080/owner/5/car \
-H 'Accept: application/vnd.demo.api.v1+json' \
-H 'Content-Type: application/json' \
-H 'Host: localhost:8080' \
-d '{
"licenseNo": "MSH-5555"
}'
代码有问题吗?
另外注意:当我设置@BatchSize(size = 5)而没有进行其他更改时,JPA只会向数据库发出两个查询。
select o_.id , o_.created_at, o_.updated_at, o_.name from owner o_
select c_.owner_id, c_.id, c_.created_at, c_.updated_at, c_.license_no, c_.owner_id, from car c_ where c_.owner_id in (?, ?, ?, ?, ?) [1,2,3,4,5]
但我有疑问,为什么FetchType.LAZY会导致N+1个查询?
代码:https://github.com/pranayhere/exception-demo-mvn
FetchType.LAZY
的作用。它不是解决N+1问题
的方案,而是导致该问题的原因。最简单(但不是最优)的选择是切换到FetchType.EAGER
。如果您不想切换,则应使用实体图(示例在此处https://www.baeldung.com/spring-data-jpa-named-entity-graphs)或使用带有`FETCH JOIN`的查询,如上所述。 - Nikolai ShevchenkoFetchType.EAGER
,要么编写自定义查询以进行FETCH JOIN
集合(使其在此实例中变为eager)。调整@BatchSize
不会解决这个问题。 - M. Deinum