MyBatis PageHelper后台使用

pageHelper分页插件使用总结

1、添加jar包货添加pom文件

<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.0.0</version>
</dependency>

项目实用：

 <!--Mybatis分页插件PageHelper-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.3</version>
        </dependency>

2、添加插件配置
在mybatis-config配置文件中添加插件
<configuration>
<!-- 配置分页插件 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 指定数据库方言 -->
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
</configuration>

项目实用：

import com.github.pagehelper.PageHelper;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.Properties;


@Configuration
public class MybatisPageHelperConfig {
    //配置mybatis的分页插件pageHelper
     @Bean
    public PageHelper pageHelper(){
               PageHelper pageHelper = new PageHelper();
               Properties properties = new Properties();
               properties.setProperty("offsetAsPageNum","true");
               properties.setProperty("rowBoundsWithCount","true");
               properties.setProperty("reasonable","true");
               properties.setProperty("dialect","mysql");    //配置mysql数据库的方言
               pageHelper.setProperties(properties);
               return pageHelper;
    }
}

3、在controller中方法参数
@RequestParam(value = "PageNum", defaultValue = "1") Integer PageNum, @RequestParam(value = "PageSize", defaultValue = "5") Integer PageSize

项目实用：

@RequestMapping("/getRhareList.json")
    public Map<String, Object> getRohareList(HttpServletRequest request,
                                                 HttpServletResponse response,
                                                 String route,// 通道
                                                 String symbol, // 股票代码
                                                 Integer status,// 状态 全部-不传 未派送-0 已派送-1
                                                 String timeMin,// 日期（yyyy-MM-dd）
                                                 String timeMax,// 日期（yyyy-MM-dd）
                                                 Integer timeType,// 日期类型 0-登记日 1-除权日
                                                 @RequestParam(defaultValue = "updateTime", required = false) String orderBy, //排序字段
                                                 @RequestParam(defaultValue = "0", required = false) Integer ascending, //排序规则 0-倒序 1-正序
                                                 @RequestParam(defaultValue = "1", required = false) Integer page,//页码
                                                 @RequestParam(defaultValue = "50", required = false) Integer limit //页数
    ) {
        //返回对象
        Map<String, Object> m = new HashMap();
        PageInfo data = shareService.getRouteShareList(route, symbol, status, timeMin, timeMax, timeType, orderBy, ascending, page, limit);
        if (data != null) {
            List<Map<String, Object>> list = data.getList();
            m.put("code", 200);
            m.put("msg", "获取成功");
            m.put("totalPage", data.getPages());
            m.put("count", data.getTotal());
            m.put("pageNo", data.getPageNum());
            m.put("data", list);
        } else {
            m.put("code", ((ShareServiceImpl) shareService).getErrorCode());
            m.put("msg", ((ShareServiceImpl) shareService).getErrorMsg());
        }
        return m;
    }

4、写在service的组装PageInfo
//设置分页
PageHelper.startPage(1, 30);
//执行查询
list=service.方法；
//取得分页结果
PageInfo pageInfo=new PageInfo(list);

项目实用：

public PageInfo<List<Map>> getReList(String route, String symbol, Integer status, String timeMin, String timeMax, Integer timeType, String orderBy, Integer ascending, Integer page, Integer limit) {
        List<Map> data = new ArrayList<>();
        Map findMap = new HashMap();
        findMap.put("route", route);
        findMap.put("symbol", symbol);
        findMap.put("status", status);
        findMap.put("timeMin", timeMin);
        findMap.put("timeMax", timeMax);
        findMap.put("timeType", timeType);
        findMap.put("ascending", ascending == 0 ? "DESC" : "ASC");
        findMap.put("orderBy", StringUtils.getDBCol(orderBy));

        if (page != null) {//分页
            PageHelper.startPage(page, limit);
        }
        List<Map> list = null;
        try {
            list = taRouteShareExtendsMapper.selectList(findMap);
        } catch (Exception e) {
            logger.error("查询失败[DB]", e);
            setError(201, "查询失败[DB]");
            return null;
        }
        PageInfo pageInfo = new PageInfo(list);
        if (list == null) {
            return new PageInfo(data);
        }
        //将派息转换成税后 即减除红利税
        Map<String, Object> params = new HashMap<>();
        for (Map m : list) {
            //获取通道下所有交易账号的红利税
            params.put("route", m.get("route"));
            params.put("symbol", m.get("symbol"));
            params.put("shareStrategyId", m.get("shareStrategyId"));
            List<TaAccountShare> taAccountShareList = taAccountShareExtendsMapper.selectAccountShareListByParams(params);
            AtomicReference<Double> dividendInterest = new AtomicReference<>(0d);
            if (taAccountShareList != null) {
                taAccountShareList.stream().forEach(taAccountShare -> {
                    AccountSetting as = ACCOUNT_SETTING_MAP_CHCHE.get(taAccountShare.getAccountId());
                    dividendInterest.set(MathUtil.freePlus(dividendInterest.get(), MathUtil.multi(SymbolUtils.getDividendTax(as, taAccountShare.getSymbol()), taAccountShare.getSendedInterest())));
                });
            }
            Double sendInterest = m.get("sendInterest") == null ? 0 : (Double) m.get("sendInterest");
            m.put("sendInterest", MathUtil.minus(sendInterest, dividendInterest.get()));//预计派息（税后）
          /*  Double dividendInterest = m.get("dividendInterest") == null ? null : (Double) m.get("dividendInterest");
            if (dividendInterest != null && dividendInterest != 0) {
                Double sendInterest = m.get("sendInterest") == null ? 0 : (Double) m.get("sendInterest");
                m.put("sendInterest", MathUtil.minus(sendInterest, dividendInterest));//预计派息（税后）
            }*/
            //格式化时间
            m.put("loginDate", DateUtils.formatByPattern(m.get("loginDate"), "yyyy-MM-dd"));
            m.put("exDate", DateUtils.formatByPattern(m.get("exDate"), "yyyy-MM-dd"));
            // 判断是否派送
           /* Long isExist = m.get("isExist") == null ? null : (Long) m.get("isExist");
            if (isExist != null && isExist > 0) {
                m.put("status", 1);
            }*/
        }
        PageInfo pageInfo_data = new PageInfo(list);
        pageInfo_data.setPages(pageInfo.getPages());
        pageInfo_data.setTotal(pageInfo.getTotal());
        pageInfo_data.setPageNum(pageInfo.getPageNum());
        return pageInfo_data;
    }

注意：依然会出现拉取数据量很大时候查询性能的问题，最好还是

对于数据量很大的查询最好不要一次性按条件全部查出，而是通过sql的分页limit查出当前页数据，再通过另外一条count出总条数组装出分页数据。

Map<String, Object> resultMap = new HashMap<>();
        String asendingStr = ascending == 0 ? "desc" : "asc";
        int startIndex = (page - 1) * limit;
        List<Map<String, Object>> resultList = accountCashService.getCashFlowList(accountId, type, currency, startDate, endDate, orderBy, asendingStr, startIndex, limit);
        Integer totalCount = accountCashService.getCashFlowTotalCount(accountId, type, currency, startDate, endDate);
        List<Map<String, Object>> showlist = resultList;
        int totalPage = totalCount % limit == 0 ? totalCount / limit : totalCount / limit + 1;
        int index = 0;
        for (Map<String, Object> map : showlist) {
            map.put("index", index++);
        }
        resultMap.put("totalPage", totalPage);
        resultMap.put("list", showlist);
        resultMap.put("count", totalCount);
        resultMap.put("code", 200);
        return resultMap;

另外：

页面多个字段排序中有的字段是从lts取到的数据组装成的才使用排序工具类，能使用sql尽量用sql排序。

SortListUtils.sortMap2(resultList, orderBy, ascending == 0 ? "desc" : "asc");