问题：
使用 Spring Data JPA 定义 Repo find方法时，发现会执行了N+2条SQL 。
Entity 定义如：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
@Data @Entity @Table(name = "event") public class Event implements Serializable { .... @OneToOne(mappedBy = "event") private Action action; } @Data @Entity @Table(name = "action") public class Action implements Serializable { .... @JsonIgnore @OneToOne(fetch = FetchType.LAZY) @JoinColumn(name = "event_id", insertable = false, updatable = false) private Event event; } |
解决：
使用 @Query 及 left fetch join 来减少 SQL，最后只会执行两条 sql
如：
|
|
@RepositoryRestResource(path = "event") public interface EventRestRepo extends PagingAndSortingRepository<Event, Integer>, CrudRepository<Event, Integer> { @Query("select e" + " from Event e " + " left join fetch e.action " + " where e.companyCode=?1 ") Page<Event> find2(String companyCode, Pageable p); } |
参考：