因為業(yè)務(wù)需要一個分頁查詢的列表，但是對數(shù)據(jù)內(nèi)容有去重的需求，簡單了解了JPA的@Query注解使用方式后就直接用了自定義sql來做，測試通過后上線測了兩筆也沒問題就沒太在意。但是隔了一天運營就反應(yīng)說功能報錯了，而且我去看日志發(fā)現(xiàn)報錯很奇怪，是sql語法錯誤，如果是sql語法錯誤那么是怎么測試通過的呢？報錯如下：

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from test a where a.id in (select max(b.id) from test' at line 1

但是我寫的sql在結(jié)果集那里是沒有括號的

select a.* from test a where a.id in (select max(b.id) from test b group by name) order by a.id desc;

這個括號是如何生成的呢，經(jīng)過反復(fù)的測試樣本測試之后發(fā)現(xiàn)，這個是jpa在做分頁需要查詢總數(shù)量的時候自動加了一個sql

select count(a.*) from test a where a.id in (select max(b.id) from test b group by name) order by a.id desc;

這個語法在高版本的mysql中是不支持的，只能寫成

select count(*) from test a where a.id in (select max(b.id) from test b group by name) order by a.id desc;

那么一開始又是怎么測試通過的呢，為什么有的查詢（pageSize>count）jpa不會加上count(pageSize<count)而有的又會自動count？
debug到j(luò)pa源碼之后，才知道jpa在執(zhí)行分頁查詢的時候有對記錄總數(shù)的獲取方式做優(yōu)化，具體實現(xiàn)在org.springframework.data.repository.support.PageableExecutionUtils的getPage方法，jpa就是通過這個方法將結(jié)果集封裝成page，而其中在計算total的時候會判斷當(dāng)前頁偏移量，當(dāng)前pageSize是否大于查詢條件的pageSize，如果能直接計算出來total就不會調(diào)用count去數(shù)據(jù)庫查詢記錄總數(shù)

/**
     * Constructs a {@link Page} based on the given {@code content}, {@link Pageable} and {@link Supplier} applying
     * optimizations. The construction of {@link Page} omits a count query if the total can be determined based on the
     * result size and {@link Pageable}.
     *
     * @param content must not be {@literal null}.
     * @param pageable must not be {@literal null}.
     * @param totalSupplier must not be {@literal null}.
     * @return the {@link Page}.
     */
    public static <T> Page<T> getPage(List<T> content, Pageable pageable, LongSupplier totalSupplier) {

        Assert.notNull(content, "Content must not be null!");
        Assert.notNull(pageable, "Pageable must not be null!");
        Assert.notNull(totalSupplier, "TotalSupplier must not be null!");

        if (pageable.isUnpaged() || pageable.getOffset() == 0) {

            if (pageable.isUnpaged() || pageable.getPageSize() > content.size()) {
                return new PageImpl<>(content, pageable, content.size());
            }

            return new PageImpl<>(content, pageable, totalSupplier.getAsLong());
        }

        if (content.size() != 0 && pageable.getPageSize() > content.size()) {
            return new PageImpl<>(content, pageable, pageable.getOffset() + content.size());
        }

        return new PageImpl<>(content, pageable, totalSupplier.getAsLong());
    }

jpa生成count語句的邏輯也非常簡單，就是做了一個正則來替換查詢語句，對于我這個查詢，count語句就變成了上面那個樣子，jpa也考慮到這樣生成的sql可能是有問題的，所以@Query注解提供了countQuery屬性，查詢語句比較復(fù)雜的時候可以自己寫一個count語句給jpa使用。