Spring Boot JdbcTemplate SQL 查询分页

目录

• 它是如何运作的
• 相关文件
  • 分页运行器.java
  • 架构.sql
  • 数据.sql
• 示例输出

有时，您需要获取的数据量对于单个行程来说太大了。不要害怕，分页是解决这个问题的一种方法。

它是如何运作的#

如果您以前使用过弹簧启动，您可能知道 JPA 包含开箱即用的分页支持。虽然这很好，但在某些情况下（比如我最近在工作中遇到的那个），你正在使用原始的、复杂的SQL，但你仍然需要一些方法来做分页。

幸运的是，通过一些尝试和错误，以及一堆谷歌搜索，我制定了一个解决方案。

如果遇到问题，请在 GitHub 存储库中签出整个项目。

相关文件#

分页运行器.java#

文件： src/main/java/com/示例/演示/分页运行器.java

// package and import lines removed for brevity 
@Component
public class PaginationRunner implements ApplicationRunner {

  Logger logger = LoggerFactory.getLogger(PaginationRunner.class);

  /**  * The pageSize is configurable. We default it to 5 here.  * You can override it in the src/main/resources/application.properties file by setting pagination_runner.page_size.  * Or, via env var by setting PAGINATION_RUNNER_PAGE_SIZE.  */
  @Value("${pagination_runner.page_size:5}")
  private int pageSize;

  /**  * The jdbcTemplate uses the default data source. Which, in this demo, is the in-memory H2 database.  */
  @Autowired
  private JdbcTemplate jdbcTemplate;

  /**  * This class implements ApplicationRunner.  * So, this component will run after the Spring Application Context is initialized.  */
  @Override
  public void run(ApplicationArguments args) throws Exception {
    logger.info("Starting PaginationRunner");
    loopThroughThePages();
    logger.info("Finished PaginationRunner");
  }

  /**  * Loop through the pages until you encounter an empty page.  */
  private void loopThroughThePages() {
    Pageable pageable = PageRequest.of(0, pageSize);
    Page<Map<String, Object>> page = findAll(pageable);

    while (!page.isEmpty()) {
      logProgress(pageable, page);
      page.stream().forEach(this::handleRow);
      pageable = pageable.next();
      page = findAll(pageable);
    }
  }

  /**  * Find all the rows.  * You _could_ create the query using LIMIT and OFFSET...  * But, I went with a plain WHERE clause that selects a range of IDs because it's faster.  */
  private Page<Map<String, Object>> findAll(Pageable pageable) {
    long startId = pageable.getOffset();
    long endId = startId + pageable.getPageSize();
    String sql = String.format(
        "SELECT * FROM word WHERE id > %s AND id <= %s",
        startId,
        endId
    );
    logger.info("findAll sql: {}", sql);
    List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
    long total = countAll();
    return new PageImpl<>(rows, pageable, total);
  }

  /**  * Count all the rows.  */
  private long countAll() {
    String sql = "SELECT COUNT(*) FROM word";
    logger.info("countAll sql: {}", sql);
    return jdbcTemplate.queryForObject(sql, Long.class);
  }

  /**  * Log the progress.  * You'll thank yourself for this, especially if the "job" is long-running.  */
  private void logProgress(Pageable pageable, Page<Map<String, Object>> page) {
    int currentPage = pageable.getPageNumber() + 1;
    int totalPages = page.getTotalPages();
    int currentRowCount = page.getNumberOfElements();
    long totalRowCount = page.getTotalElements();
    logger.info(
        "On page {} of {}. Rows in page: {}. Total rows: {}",
        currentPage, totalPages, currentRowCount, totalRowCount
    );
  }

  /**  * Actually do something with each row.  * In this demo, I'm just logging the row.  * In a real scenario, maybe you're building up a bulk request to send somewhere else, etc.  */
  private void handleRow(Map<String, Object> row) {
    logger.info(row.toString());
  }

}

架构.sql#

File: src/main/resources/schema.sql

-- When using the in-memory H2 database, this is how the schema is defined. 
CREATE TABLE word (
  id INT AUTO_INCREMENT PRIMARY KEY,
  word CHARACTER VARYING
);

数据.sql#

文件：主/资源/数据.sql

-- When using the in-memory H2 database, this is how the data is seeded. -- I got this data by grepping for words starting with "b" in the built-in Mac dictionary at /usr/share/dict/words. 
INSERT INTO word (word) VALUES ('babblesome');
INSERT INTO word (word) VALUES ('babbling');
INSERT INTO word (word) VALUES ('babblingly');
INSERT INTO word (word) VALUES ('babblish');
INSERT INTO word (word) VALUES ('babblishly');
INSERT INTO word (word) VALUES ('babbly');
INSERT INTO word (word) VALUES ('babby');
INSERT INTO word (word) VALUES ('babe');
INSERT INTO word (word) VALUES ('babehood');
INSERT INTO word (word) VALUES ('babelet');
INSERT INTO word (word) VALUES ('babelike');

示例输出#

您可以通过运行应用程序来查看此内容：./gradlew bootRun

Starting PaginationRunner
findAll sql: SELECT * FROM word WHERE id > 0 AND id <= 5
countAll sql: SELECT COUNT(*) FROM word
On page 1 of 3. Rows in page: 5. Total rows: 11
{ID=1, WORD=babblesome}
{ID=2, WORD=babbling}
{ID=3, WORD=babblingly}
{ID=4, WORD=babblish}
{ID=5, WORD=babblishly}
findAll sql: SELECT * FROM word WHERE id > 5 AND id <= 10
countAll sql: SELECT COUNT(*) FROM word
On page 2 of 3. Rows in page: 5. Total rows: 11
{ID=6, WORD=babbly}
{ID=7, WORD=babby}
{ID=8, WORD=babe}
{ID=9, WORD=babehood}
{ID=10, WORD=babelet}
findAll sql: SELECT * FROM word WHERE id > 10 AND id <= 15
countAll sql: SELECT COUNT(*) FROM word
On page 3 of 3. Rows in page: 1. Total rows: 11
{ID=11, WORD=babelike}
findAll sql: SELECT * FROM word WHERE id > 15 AND id <= 20
countAll sql: SELECT COUNT(*) FROM word
Finished PaginationRunner