Spring Boot JdbcTemplate SQL 查询分页

港控/mmm° 2023-09-26 10:33 67阅读 0赞

**目录**

*  [它是如何运作的][Link 1]
 *  [相关文件][Link 2]
    
     *  [分页运行器.java][.java]
     *  [架构.sql][.sql]
     *  [数据.sql][.sql 1]
 *  [示例输出][Link 3]

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

### 它是如何运作的[\#][Link 1] ###

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

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

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

### 相关文件[\#][Link 2] ###

#### 分页运行器.java[\#][.java] ####

文件： [src/main/java/com/示例/演示/分页运行器.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[\#][.sql] ####

File: [src/main/resources/schema.sql][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 1] ####

文件：[主/资源/数据.sql][.sql 2]

-- 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');

### 示例输出[\#][Link 3] ###

您可以通过运行应用程序来查看此内容：`./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

[Link 1]: https://zwbetz.com/sql-query-pagination-with-spring-boot-and-jdbctemplate/#how-it-works
[Link 2]: https://zwbetz.com/sql-query-pagination-with-spring-boot-and-jdbctemplate/#relevant-files
[.java]: https://zwbetz.com/sql-query-pagination-with-spring-boot-and-jdbctemplate/#paginationrunnerjava
[.sql]: https://zwbetz.com/sql-query-pagination-with-spring-boot-and-jdbctemplate/#schemasql
[.sql 1]: https://zwbetz.com/sql-query-pagination-with-spring-boot-and-jdbctemplate/#datasql
[Link 3]: https://zwbetz.com/sql-query-pagination-with-spring-boot-and-jdbctemplate/#sample-output
[GitHub]: https://github.com/zwbetz-gh/sql-query-pagination-with-spring-boot-and-jdbctemplate
[src_main_java_com_.java]: https://github.com/zwbetz-gh/sql-query-pagination-with-spring-boot-and-jdbctemplate/blob/main/src/main/java/com/example/demo/PaginationRunner.java
[src_main_resources_schema.sql]: https://github.com/zwbetz-gh/sql-query-pagination-with-spring-boot-and-jdbctemplate/blob/main/src/main/resources/schema.sql
[.sql 2]: https://github.com/zwbetz-gh/sql-query-pagination-with-spring-boot-and-jdbctemplate/blob/main/src/main/resources/data.sql