记一次使用JPA分页查询SQL优化

原创已于 2022-02-17 17:07:49 修改 · 1.4k 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#java

于 2022-02-17 17:06:07 首次发布

后端专栏收录该内容

5 篇文章

订阅专栏

本文介绍了在Spring Data JPA中，通过优化SQL查询，利用COUNT(*) OVER()函数避免全表扫描，提高分页查询性能。作者对比了优化前后的代码，并展示了如何在达梦数据库中使用此方法获取总记录数。

没优化前代码如下
问题出在List countList = query.getResultList();
分页查询查总数的时候把所有的数据都查出来了数据量多的话会导致查询方法非常慢

		StringBuilder sql = new StringBuilder("SQL语句");

        Query query = em.createNativeQuery(sql.toString());

        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<JSONObject> countList = query.getResultList();

        Integer curPage = Integer.valueOf(params.get(PlatformPageUtils.CUR_PAGE).toString());
        Integer pageSize = Integer.valueOf(params.get(PlatformPageUtils.PAGE_SIZE).toString());
        Pageable pageable = PageRequest.of(curPage - 1, pageSize);
        query.setFirstResult(pageable.getPageNumber()*pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());
        List<JSONObject> list = query.getResultList();
        Page<JSONObject> page = new PageImpl<>(list,pageable,countList.size());
        return page;

优化后的代码如下
用了一个函数COUNT(*) OVER() 分页查询的时候可以同时把总数查出来
楼主用的是达梦数据库不同数据库这个函数不一样

		StringBuilder sql = new StringBuilder("SELECT COUNT(*) OVER() TOTAL 。。。");

        Query query = em.createNativeQuery(sql.toString());

        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

        Integer curPage = Integer.valueOf(params.get(PlatformPageUtils.CUR_PAGE).toString());
        Integer pageSize = Integer.valueOf(params.get(PlatformPageUtils.PAGE_SIZE).toString());
        Pageable pageable = PageRequest.of(curPage - 1, pageSize);
        query.setFirstResult(pageable.getPageNumber()*pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());
        List<JSONObject> list = query.getResultList();
        int total = 0;
        if(list.size() != 0)
            total = new JSONObject(list.get(0)).getIntValue("TOTAL");
        Page<JSONObject> page = new PageImpl<>(list,pageable,total);
        return page;