解決Spring Data JPA Hibernate的N+1問題的最佳方法

xkzhangsanx發表於2024-09-14
Spring

　　最佳方法：定製@NamedEntityGraph、定製查詢和定製VO，可以做到按照需要最佳查詢，需要注意的地方：定製VO的欄位一定要等於或小於實際查詢的欄位，才不會複製的時候觸發N+1查詢。

1 問題復現

1.1 專案結構

1.2 entity

package com.xkzhangsan.jpa.entity;

import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;

@Entity
@Getter
@Setter
@Table(name = "user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    @OneToOne(cascade = CascadeType.DETACH, fetch = FetchType.LAZY)
    @JoinColumn(name = "user_detail_id")
    private UserDetail userDetail;
}
package com.xkzhangsan.jpa.entity;

import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;

@Entity
@Getter
@Setter
@Table(name = "user_detail")
public class UserDetail {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String address;
}

1.3 repository

package com.xkzhangsan.jpa.repository;

import com.xkzhangsan.jpa.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Integer> {
}

1.4 service

package com.xkzhangsan.jpa.service;

import com.xkzhangsan.jpa.entity.User;
import com.xkzhangsan.jpa.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;

    public List<User> findAll(){
        return userRepository.findAll();
    }

}

1.5 controller

package com.xkzhangsan.jpa.controller;

import com.xkzhangsan.jpa.entity.User;
import com.xkzhangsan.jpa.service.UserService;
import com.xkzhangsan.jpa.vo.UserDetailVO;
import com.xkzhangsan.jpa.vo.UserVO;
import org.modelmapper.ModelMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.stream.Collectors;

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @RequestMapping(value = "/")
    public List<UserVO> getPersons() {
        ModelMapper modelMapper = new ModelMapper();
        List<User> userList = userService.findAll();
        if (!CollectionUtils.isEmpty(userList)) {
            return userList.stream().map(user -> {
                UserVO userVO = modelMapper.map(user, UserVO.class);
                UserDetailVO userDetailVO = modelMapper.map(user.getUserDetail(), UserDetailVO.class);
                userVO.setUserDetailVO(userDetailVO);
                return userVO;
            }).collect(Collectors.toList());
        }
        return null;
    }
}

1.6 測試

查詢1次，實際查詢了4次，這裡的N指的是集合的數量3，所以 N+1，就是3+1=4

1.7 問題原因

User關聯物件懶載入導致

2 最佳解決方法

　　定製@NamedEntityGraph、定製查詢和定製VO，可以做到按照需要最佳查詢。

2.1 定製@NamedEntityGraph

package com.xkzhangsan.jpa.entity;

import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;

@Entity
@Getter
@Setter
@Table(name = "user")
@NamedEntityGraph(name = "user.userDetail", attributeNodes = {
        @NamedAttributeNode(value = "userDetail")
})
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    @OneToOne(cascade = CascadeType.DETACH, fetch = FetchType.LAZY)
    @JoinColumn(name = "user_detail_id")
    private UserDetail userDetail;
}

從程式碼種可以看出增加註解，使用user.userDetail會聯表查詢UserDetail

@NamedEntityGraph(name = "user.userDetail", attributeNodes = {
        @NamedAttributeNode(value = "userDetail")
})

2.1.2 定製查詢

package com.xkzhangsan.jpa.repository;

import com.xkzhangsan.jpa.entity.User;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface UserRepository extends JpaRepository<User, Integer> {

    @Override
    @EntityGraph(value = "user.userDetail", type = EntityGraph.EntityGraphType.FETCH)
    List<User> findAll();
}

從程式碼種可以看出，重寫了findAll，使用了user.userDetail

2.3 定製VO（可選）

根據需要返回的欄位定義一個VO，如果沒有需要則不需要定製，比如當前例項種就不需要。

測試結果如下，只查詢了一次，但聯表查詢了，left outer join user_detail

select user0_.id as id1_0_0_, userdetail1_.id as id1_1_1_, user0_.name as name2_0_0_, user0_.user_detail_id as user_det3_0_0_, userdetail1_.address as address2_1_1_ from user user0_ left outer join user_detail userdetail1_ on user0_.user_detail_id=userdetail1_.id

2.3.1 需要定製VO的例項

比如，只想返回使用者id和name，可以定製一個UserSimpleVO，如下：

package com.xkzhangsan.jpa.vo;

import lombok.Getter;
import lombok.Setter;


@Getter
@Setter
public class UserSimpleVO {
    private Long id;

    private String name;
}

因為不包括UserDetailVO userDetailVO，所以複製屬性的時候不會觸發更多的查詢。

需要注意的地方：定製VO的欄位一定要等於或小於實際查詢的欄位，才不會複製的時候觸發N+1查詢。

還有一種方式不用定製VO，透過設定ModelMapper跳過不需要的欄位，但這樣有2個問題

（1）需要設定ModelMapper，比較麻煩

（2）既然不需要一些欄位，定製VO是最有效的方法，這樣符合迪米特法則，比如不能展示的敏感欄位，如果查詢或處理過程中沒有處理好，可能導致誤返回，最好定製VO，在VO刪除這個欄位。

3 使用EAGER載入方式

既然是因為LAZY導致的，改成EAGER是否可以解決問題？經過驗證是不可行的，仍然會查詢多次，比如：

package com.xkzhangsan.jpa.entity;

import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;

@Entity
@Getter
@Setter
@Table(name = "user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    @OneToOne(cascade = CascadeType.DETACH, fetch = FetchType.EAGER)
    @JoinColumn(name = "user_detail_id")
    private UserDetail userDetail;
}

發現仍然會執行多個sql

Hibernate: select user0_.id as id1_0_, user0_.name as name2_0_, user0_.user_detail_id as user_det3_0_ from user user0_
Hibernate: select userdetail0_.id as id1_1_0_, userdetail0_.address as address2_1_0_ from user_detail userdetail0_ where userdetail0_.id=?
Hibernate: select userdetail0_.id as id1_1_0_, userdetail0_.address as address2_1_0_ from user_detail userdetail0_ where userdetail0_.id=?
Hibernate: select userdetail0_.id as id1_1_0_, userdetail0_.address as address2_1_0_ from user_detail userdetail0_ where userdetail0_.id=?

相關文章