com.mars.jdbc.helper.templete.JdbcPage Maven / Gradle / Ivy

Go to download
package com.mars.jdbc.helper.templete;

import com.mars.jdbc.helper.model.PageModel;
import com.mars.jdbc.helper.model.PageParamModel;
import com.mars.jdbc.helper.templete.base.BaseSelect;

import java.util.List;
import java.util.Map;

/**
 * 分页
 */
public class JdbcPage {


    /**
     * 有参查询列表
     * @param sql sql语句
     * @param param 参数
     * @param dataSourceName 连接名
     * @return 数据
     */
    public static PageModel selectList(String sql, PageParamModel param, String dataSourceName) throws Exception {
        return selectList(sql,param, Map.class,dataSourceName);
    }

    /**
     * 有参查询列表，指定返回类型
     * @param sql sql语句
     * @param param 参数
     * @param cls 返回类型
     * @param dataSourceName 连接名
     * @return 数据
     */
    public static  PageModel selectList(String sql, PageParamModel param, Class cls, String dataSourceName) throws Exception {

        /* 将查询sql转化成分页所需的两条语句 */
        String selectSql = getSelectSql(sql);
        String countSql = getCountSql(sql);

        /* 将查询参数提取出来并转化成需要的格式 */
        Map pageParam = getParam(param);

        /* 查询总条数 */
        List countList = BaseSelect.selectList(countSql, pageParam, Map.class, dataSourceName);

        /* 获取总条数 */
        Object countNum = getCountNum(countList);
        if(countNum == null){
            return null;
        }

        /* 如果能进到这一步说明有数据，此刻再查询需要的数据 */
        List dataList = BaseSelect.selectList(selectSql, pageParam, cls, dataSourceName);

        /* 组装返回对象 */
        PageModel pageModel = new PageModel<>();
        pageModel.setPageCount(Integer.parseInt(countNum.toString()));
        pageModel.setDataList(dataList);
        pageModel.setCurrentPage(param.getCurrentPage());
        pageModel.setPageSize(param.getPageSize());

        int pageTotal = pageModel.getPageCount() / pageModel.getPageSize();

        if (pageModel.getPageCount() % pageModel.getPageSize() == 0) {
            pageModel.setPageTotal(pageTotal);
        } else {
            pageModel.setPageTotal(pageTotal + 1);
        }
        return pageModel;
    }

    /**
     * 从返回数据中获取总条数
     * @param countList 返回的数据
     * @return 总条数
     */
    private static Object getCountNum(List countList) {
        if (countList == null || countList.size() < 1) {
            return null;
        }

        Map countItem = countList.get(0);
        if (countItem == null || countItem.size() < 1) {
            return null;
        }

        Object countNum = countItem.get("countNum");
        if (countNum == null || countNum.toString().equals("")) {
            return null;
        }

        return countNum;
    }

    /**
     * 获取总数sql
     * @param sql
     * @return
     */
    private static String getCountSql(String sql){
        sql = sql.toLowerCase();
        int index = sql.indexOf("from");
        sql = sql.substring(index);

        StringBuffer sqlBuilder = new StringBuffer("select count(0) countNum  ");
        sqlBuilder.append(sql);
        return sqlBuilder.toString();
    }

    /**
     * 获取分页sql
     * @param sql
     * @return
     */
    private static String getSelectSql(String sql){
        StringBuffer sqlBuilder = new StringBuffer();
        sqlBuilder.append(sql);
        sqlBuilder.append(" limit #{pageStart},#{pageSize}");
        return sqlBuilder.toString();
    }

    /**
     * 重组参数
     * @param param
     * @return
     */
    private static Map getParam(PageParamModel param){
        Map objectMap = param.getParam();
        objectMap.put("pageStart",(param.getCurrentPage()-1) * param.getPageSize());
        objectMap.put("pageSize",param.getPageSize());
        return objectMap;
    }
}