Spring Data JPA：批量读取大表

简要：

  • 如果数据库表很小，则查询数据库方面不需要太多工程。
  • 但我们经常使用尺寸太大的表，并且对表的简单查询不起作用并且会破坏一些东西。
  • 这是我们需要谨慎编写查询的地方，这样我们就不会影响数据库或应用程序层。
  • 在本文中，我们将学习如何查询大型数据库表，而简单的 SELECT 查询将返回大型结果并可能耗尽应用程序内存。
  • 使用 Spring Data JPA PageRequest 是迄今为止最好的方法，因为它易于使用且机械性较低。

假设实体是一个简单的帐户表。

@Entity
@Table(name="ACCOUNTS")
public class Account {
    @Id @GeneratedValue(strategy= GenerationType.SEQUENCE, generator = "accounts_seq")
    @SequenceGenerator(name = "accounts_seq", sequenceName = "accounts_seq", allocationSize = 1)
    @Column(name = "user_id")
    private int userId;
    private String username;
    private String password;
    private String email;
    private Timestamp createdOn;
    private Timestamp lastLogin;
 
    @OneToOne
    @JoinColumn(name = "permissions_id")
    private Permission permission;
 
   // getter & setters
}

账户信息库
对于本练习，我们不需要自己编写任何派生查询，而是可以使用 JPARepository 提供的默认派生查询，因此 AccountRepository 为空。

public interface AccountRepository extends JpaRepository<Account, Integer> {}

选项 1：使用 PageRequest、JPARepository
这是在 Spring Data JPA 中实现分页的简单且最佳的方法之一。
首先，我们需要创建PageRequest对象

PageRequest pageRequest = PageRequest.of(0,10);

一旦我们有了 PageRequest，我们就可以将它传递给 JPARepository，如下所示，返回类型是 Page。

Page<Account> pageableAccount = accountRepository.findAll(pageRequest);

现在，一旦我们有了页面响应，我们就可以处理它并完成它。为了简洁起见，我们只是将其打印出来。


选项 2：将 TypedQuery 与 StartPosition 和 MaxResults 结合使用
在本例中，我们将使用 TypedQuery 来查询数据库。TypedQuery 对象允许我们设置响应的 startPosition 和 maxResult。

TypedQuery<Account> query = em.createQuery("SELECT a FROM Account a", Account.class);
query.setFirstResult(startPosition);
query.setMaxResults(batch_size);

通过使用上述设置，可以轻松地以分页方式读取记录。


选项 3：使用PreparedStatement、SQL OFFSET 和 LIMIT 子句
我们可以绕过 JPA 层来获取 JPA Session 对象和查询数据库。
使用 RAW SQL，我们可以设置 OFFSET 和 LIMIT 以返回记录子集，而不是读取整个表。

Session session = em.unwrap(Session.class);
 
session.doReturningWork(new ReturningWork<>() {
    @Override
    public Integer execute(Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection
                .prepareStatement("SELECT * FROM ACCOUNTS LIMIT ? OFFSET ?");
    }
}

一旦我们有了包含给定页面结果的 ResultSet，我们就可以像下面这样读取它们：

ResultSet resultSet = preparedStatement.executeQuery();
if(!resultSet.next()) break; // break when finish reading
do{
     System.out.println(resultSet.getString("user_id"));
 }while (resultSet.next());

​​​​​​​