时间戳

首页 > 解决方案 > JPA：即使在 FetchType.LAZY 之后也有 N+1 个查询

java - JPA：即使在 FetchType.LAZY 之后也有 N+1 个查询

问题描述

我已经阅读了几篇文章，展示了如何在 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;
    }
}

我的汽车课程如下：

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

业主服务：

@Service
public class OwnerServiceImpl implements OwnerService {

    @Autowired
    OwnerRepository ownerRepository;

    @Override
    public List<Owner> findAll() {
        return ownerRepository.findAll();
    }
}

所有者控制器：

@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 -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 只进行两次查询当我设置 @BatchSize(size = 5) 而不进行任何其他更改时，它只对数据库进行两次查询。

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

标签： javaspringjpaspring-data-jpa

解决方案

实际上问题是你使用了默认findAll的OwnerRepository并且你放了FetchType.LAZY.

因为你在 中获取了汽车Owner::convertToDto，Hibernate 必须获取由于延迟获取而没有获取的汽车。

为了避免额外的查询，请在 OwnerRepository getAllBy 中创建一个新的 JPA 方法，并使用 EntityGraph 来急切地获取查询中的汽车：

public class OwnerRepository extend JpaRepository<Owner, Long> {

    @EntityGraph(attributePaths = {
            "cars",
    })
    List<Owners> getAllBy();

}

然后在您的服务中使用它而不是 findAll。

推荐阅读