Spring-data-jpa 渴望获取加入和使用分页不起作用

Question

我对 hibernate 和 Spring-data-jpa 很陌生。我想做的就是使用分页获取一个实体及其所有相关实体(大约有 4000 万条记录)。即一个查询急切地获取根实体及其所有映射实体/集合 = 1 个查询

但是我遇到了 (n+1) 个问题:根实体的一个查询 + 每个根实体的相关映射实体/集合的一个查询 = (n+1) 个查询这确实会影响性能。

我也想知道我在实体中的映射是否正确。

有人可以指导我吗？谢谢

应用程序栈:

<java-version>1.7</java-version>
<spring.framework.version>4.0.2.RELEASE</spring.framework.version>
<hibernate-core>4.3.4.Final</hibernate-core>
<hibernate-validator>5.1.0.Final</hibernate-validator>
<hibernate-entitymanager>4.3.4.Final</hibernate-entitymanager>
<spring-data-jpa>1.5.1.RELEASE</spring-data-jpa>

我有2个实体类(客户和订单)如下:

客户实体

@Entity
@Table(name = "view_customerOrders_to_process") 
public class Customer implements Serializable {

private Long id;
private List<Order> Orders;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "customer_id", updatable = false)
public Long getId() {
   return id;
}

........

@NotFound(action = NotFoundAction.IGNORE)
@OneToMany(fetch = FetchType.EAGER, mappedBy = "customer") 
//Do not cascade any changes to child entities.
@Where(clause = "order_status = 'PENDING'")
public List<Order> getOrders() {
    return order;
}
}

尝试使用 batchSize，但由于某种原因它不起作用(同样的 n+1 问题)。
//@Fetch(FetchMode.SELECT)
//@BatchSize(size=25)

订单实体:

@Table(name = "tbl_orders") 
public class Order implements Serializable {

    private Long id;
    private int customerId;    
    private Customer customer;
    private OrderType orderType;

    ..........

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "order_id", updatable = false)
    public Long getId() {
        return this.id;
    }

    @OneToOne
    @NotFound(action = NotFoundAction.IGNORE)
    @JoinColumn(name = "order_type", nullable = true, unique = false, updatable = false)
    public OrderType getOrderType() {
        return this.orderType;
    }

    @NotFound(action = NotFoundAction.IGNORE)
    @ManyToOne(fetch = FetchType.LAZY, optional = true, targetEntity = Customer.class)
    @Fetch(FetchMode.JOIN)
    @JoinColumns({
        @JoinColumn(updatable = false, insertable = false,
            name="customer_id", referencedColumnName = "customer_id", nullable=false),
      . . . . .
    })
    public Customer getCustomer() {
        return customer;
    }
}

服务类:

@Service
public class MetsGeneratorService {

    . . .

    @Autowired
    private CustomerRepository customerRepository;

    public List<Customer> run() {
        PageRequest pageRequest = new PageRequest(0, batchSize);        
        List<Customer> customers = customerRepository.findOrdersUnprocessed(pageRequest);
        return customers;
    }

    . . .
}

存储库:

public interface CustomerRepository extends JpaRepository<Customer, Long> {

    @Query("SELECT e FROM Customer e")
    List<Customer> findOrdersUnprocessed(Pageable pageable);

    //Tried using the below one which seems to eliminate paging
    //and understandably getting out of memory.
    //@Query(value = "SELECT e FROM Customer e left join fetch e.orders")       

}

试过改成

@Query(value = "SELECT e FROM Customer e left join fetch e.orders",
        countQuery = "select count(e) from Customer e")
List<Customer> findOrdersUnprocessed(Pageable pageable);

如果您看到它正在处理的日志，则执行“select *”而不是分页。

日志消息是WARN org.hibernate.hql.internal.ast.QueryTranslatorImpl - HHH000104: firstResult/maxResults 用集合获取指定；在内存中申请!

Answer 1

你可以使用

@Fetch(FetchMode.SUBSELECT)

去除(n+1)个查询问题。