分页查询

1：三层架构 controller---service---dao

2：前端传来的数据参数为 页数 每页数 或者查询条件

Controller :

public String queryBaseUserList(@ApiParam(required = false, name = "searchKeyWord", value = "查询关键字", defaultValue = "工号or姓名or组织架构") @RequestParam(required = false) String searchKeyWord,
                                @ApiParam(required = false, name = "positionName", value = "职位", defaultValue = "经理") @RequestParam(required = false) String positionName,
                                @ApiParam(required = true, name = "start", value = "分页起始") @RequestParam Integer start,
                                @ApiParam(required = true, name = "length", value = "分页返回记录数") @RequestParam Integer length){

    long begin = System.currentTimeMillis();
    //默认返回成功
    Map<String, Object> result = PassengerFlowCountingUIUtil.getSuccessResult();

    PageBean<UserVO> pageData = userService.search(searchKeyWord,positionName,start,length);

    PassengerFlowCountingUIUtil.addElement(result, "start", String.valueOf(start));
    PassengerFlowCountingUIUtil.addElement(result, "length", String.valueOf(length));
    long costTime = System.currentTimeMillis() - begin;
    PassengerFlowCountingUIUtil.addElement(result, "cost", String.valueOf(costTime) + "ms");
    PassengerFlowCountingUIUtil.addElement(result, "recordsTotal", String.valueOf(pageData.getTotalRows()));
    PassengerFlowCountingUIUtil.addReturnData(result, pageData.getList());

    logger.info("queryBaseUserList costs:" + (System.currentTimeMillis() - begin) + " ms");
    return PassengerFlowCountingUIUtil.toJSONString(result);
}

service为接口，具体为实现类

Service:

public PageBean<UserVO> search(String searchKeyWord, String positionName, Integer start, Integer length) {

    Integer totalRows =  userDao.searchTotalCount(searchKeyWord,positionName);
    //定义分页对像
    PageBean<UserVO> pageBean = new PageBean<UserVO>(start, length, totalRows.intValue());
    List<UserDto> userDtoList = userDao.search(searchKeyWord,positionName,start,length);
    List<UserVO> userVOList = convert(userDtoList);
    pageBean.setList(userVOList);
    return pageBean;
}

两个方法：一个是查询总记录数

                一个是查询出来以后为分页对象：

/**
 * 分页类封装
 * <p>

 */
public class PageBean<T> {
    private int totalRows;// how many rows
    private int currentPage;// the index of page now
    private int totalPages;// how many pages
    private int pageSize;// how many rows in each page
    private List<T> list;// the data need to show at current page

    /**
     * @param currentPage the index of page now
     * @param pageSize    how many rows in each page
     * @param totalRows   how many pages
     */
    public PageBean(int currentPage, int pageSize, int totalRows) {
        if (currentPage < 1) {
            currentPage = 0;
        }
        this.currentPage = currentPage;
        this.pageSize = pageSize;
        this.totalRows = totalRows;
        this.totalPages = (int) Math.ceil(totalRows / (double) pageSize);
    }

    /**
     * Whether there is next page
     *
     * @return
     */
    public synchronized boolean hasNextPage() {
        boolean result = false;
        if (this.currentPage < this.totalPages) {
            result = true;
        }
        return result;
    }

    /**
     * Whether there is previous page
     *
     * @return
     */
    public synchronized boolean hasPreviousPage() {
        boolean boo = false;
        if (this.currentPage > 1) {
            boo = true;
        }
        return boo;
    }

    /**
     * Get the data list you need to show in current page(after invoke the
     * method setList)
     *
     * @return
     */
    public synchronized List<T> getList() {
        return list;
    }

    /**
     * set the data list you need to show in current page
     *
     * @param list
     */
    public synchronized void setList(List<T> list) {
        this.list = list;
    }

    /**
     * get the index of current page
     *
     * @return
     */
    public synchronized int getCurrentPage() {
        return currentPage;
    }

    /**
     * how many pages
     *
     * @return
     */
    public synchronized int getTotalPages() {

        return totalPages;
    }

    /**
     * how many rows in a page
     *
     * @return
     */
    public synchronized int getPageSize() {
        return pageSize;
    }

    /**
     * all rows
     *
     * @return
     */
    public synchronized int getTotalRows() {
        return totalRows;
    }

    /**
     * the index of start row
     *
     * @return
     */
    public synchronized int getStartRow() {
        return ((this.currentPage - 1) * this.pageSize);
    }
}

用户转换方法：

/**
 * 用户数据类型转换 dto-> vo
 * @param userDtoList 用户list
 * @return 用户voList
 */
private List<UserVO> convert(List<UserDto> userDtoList) {
    List<UserVO> userVOList = new ArrayList<UserVO>();
    if(CollectionUtils.isNotEmpty(userDtoList)){
        for (UserDto userDto : userDtoList) {
            UserVO userVO = new UserVO();
            BeanUtils.copyProperties(userDto, userVO);
            userVOList.add(userVO);
        }
    }
    return userVOList;
}

dao:

查询总数：

public Integer searchTotalCount(String searchKeyWord, String positionName) {
    long begin = System.currentTimeMillis();
    logger.info("start search user totalCount...");
    Map<String, Object> parameter = new HashMap<String, Object>();
    parameter.put(PassengerFlowCountingSqlMapConstant.SQL_PARAMETER_TABLE_NAME,findUserManagerTableName());
    parameter.put("searchKeyWord", searchKeyWord);
    parameter.put("positionName", positionName);
    Gson gson = new Gson();
    logger.info("searchTotalCount whith paramter:" + gson.toJson(parameter));
    Integer count = dalClient.queryForObject(PassengerFlowCountingSqlMapConstant.NAME_SPACE_USER_MANAGER + PassengerFlowCountingSqlMapConstant.SQL_MAP_USER_PAGE_SEARCH_COUNT, parameter, Integer.class);
    logger.info("searchTotalCount costs：" + (System.currentTimeMillis() - begin) + "ms");
    return count;
}

sql:

SELECT count(1)
FROM ${tableName}
 WHERE 1=1
 <#if searchKeyWord?? && searchKeyWord !='' >
    AND (EMPLOYEE_ID=:searchKeyWord OR EMPLOYEE_NAME = :searchKeyWord OR LONG_POSITION_NAME LIKE '%${searchKeyWord}%')
 </#if>
  <#if positionName?? && positionName !='' >
    AND POSITION_NAME =:positionName
 </#if>

查询对象：

public List<UserDto> search(String searchKeyWord, String positionName, Integer start, Integer length) {
    long begin = System.currentTimeMillis();
    logger.info("start search user list...");
    Map<String, Object> parameter = new HashMap<String, Object>();
    parameter.put(PassengerFlowCountingSqlMapConstant.SQL_PARAMETER_TABLE_NAME,findUserManagerTableName());
    parameter.put("searchKeyWord", searchKeyWord);
    parameter.put("positionName", positionName);
    parameter.put("start", start);
    parameter.put("length", length);
    Gson gson = new Gson();
    logger.info("search whith paramter:" + gson.toJson(parameter));
    List<UserDto> userList = dalClient.queryForList(PassengerFlowCountingSqlMapConstant.NAME_SPACE_USER_MANAGER + PassengerFlowCountingSqlMapConstant.SQL_MAP_USER_PAGE_SEARCH, parameter, UserDto.class);
    logger.info("query search user list costs：" + (System.currentTimeMillis() - begin) + "ms");
    return userList;
}

sql:

SELECT
    ID as id,EMPLOYEE_ID as employeeId,EMPLOYEE_NAME as employeeName,POSITION_ID as positionId,POSITION_NAME as positionName,DEPT_CODE as deptCode,
 ORGID as orgId, LONG_POSITION_NAME as longPositionName,ORG_TEMPLATE_CODE as orgTemplateCode,MANAGER_ORG_LEVEL as managerOrgLevel,STORE_CODE as storeCode,
 STR_NM as strNm,FINANCIAL_CODE as financialCode,SALE_ORG_NM as saleOrgNm,AREA_CD as areaCd,AREA_NM as areaNm
FROM ${tableName}
 WHERE 1=1
 <#if searchKeyWord?? && searchKeyWord !='' >
    AND (EMPLOYEE_ID=:searchKeyWord OR EMPLOYEE_NAME = :searchKeyWord OR LONG_POSITION_NAME LIKE '%${searchKeyWord}%')
 </#if>
  <#if positionName?? && positionName !='' >
    AND POSITION_NAME =:positionName
 </#if>
 ORDER BY EMPLOYEE_ID ASC limit :start,:length

自己封装的jdbc框架:

类：

public class DefaultDalClient implements DalClient, InitializingBean {
    protected transient Logger logger = LoggerFactory.getLogger(this.getClass());
    protected Resource[] sqlMapConfigLocation;
    protected String entityPackage;
    protected Configuration configuration = new Configuration();
    protected DataSource dataSource;
    protected SqlAuditor sqlAuditor;
    protected MappedSqlExecutor mappedSqlExecutor;
    protected boolean profileLongTimeRunningSql;
    protected long longTimeRunningSqlIntervalThreshold;

    public DefaultDalClient() {
    }

    public Resource[] getSqlMapConfigLocation() {
        return this.sqlMapConfigLocation;
    }

    public void setSqlMapConfigLocation(Resource[] sqlMapConfigLocation) {
        this.sqlMapConfigLocation = sqlMapConfigLocation;
    }

    public String getEntityPackage() {
        return this.entityPackage;
    }

    public void setEntityPackage(String entityPackage) {
        this.entityPackage = entityPackage;
    }

    public Configuration getConfiguration() {
        return this.configuration;
    }

    public boolean isProfileLongTimeRunningSql() {
        return this.profileLongTimeRunningSql;
    }

    public void setProfileLongTimeRunningSql(boolean profileLongTimeRunningSql) {
        this.profileLongTimeRunningSql = profileLongTimeRunningSql;
    }

    public long getLongTimeRunningSqlIntervalThreshold() {
        return this.longTimeRunningSqlIntervalThreshold;
    }

    public void setLongTimeRunningSqlIntervalThreshold(long longTimeRunningSqlIntervalThreshold) {
        this.longTimeRunningSqlIntervalThreshold = longTimeRunningSqlIntervalThreshold;
    }

    public DataSource getDataSource() {
        return this.dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public SqlAuditor getSqlAuditor() {
        return this.sqlAuditor;
    }

    public void setSqlAuditor(SqlAuditor sqlAuditor) {
        this.sqlAuditor = sqlAuditor;
    }

    public Number persist(Object entity) {
        return (Number)this.persist(entity, Number.class);
    }

    public <T> T persist(Object entity, Class<T> requiredType) {
        this.assertMapped(entity);
        return this.mappedSqlExecutor.persist(entity, requiredType);
    }

    public int merge(Object entity) {
        this.assertMapped(entity);
        return this.mappedSqlExecutor.merge(entity);
    }

    public int dynamicMerge(Object entity) {
        this.assertMapped(entity);
        return this.mappedSqlExecutor.dynamicMerge(entity);
    }

    public int remove(Object entity) {
        this.assertMapped(entity);
        return this.mappedSqlExecutor.remove(entity);
    }

    public <T> T find(Class<T> entityClass, Object entity) {
        this.assertMapped(entityClass);
        return this.mappedSqlExecutor.find(entityClass, entity);
    }

方法：

public <T> List<T> queryForList(String sqlId, Map<String, Object> paramMap, Class<T> requiredType) {
    return this.mappedSqlExecutor.queryForList(sqlId, paramMap, requiredType);
}

MappedSqlExecutor类:

public class MappedSqlExecutor extends JdbcTemplate {
    public static final String SQL_AUDIT_LOGMESSAGE = "SQL Statement [{}] with parameter object [{}] ran out of the normal time range, it consumed [{}] milliseconds.";
    private static Logger logger = LoggerFactory.getLogger(MappedSqlExecutor.class);
    protected Configuration configuration;
    protected boolean profileLongTimeRunningSql;
    protected long longTimeRunningSqlIntervalThreshold;
    protected ExecutorService sqlAuditorExecutor;
    protected NamedParameterJdbcTemplate execution = new NamedParameterJdbcTemplate(this);
    private String databaseUrl;
    private String databaseUserName;
    private DBType dbType;
    private SqlAuditor sqlAuditor;
    private String logPrefix;

    public MappedSqlExecutor() {
    }

    public boolean isProfileLongTimeRunningSql() {
        return this.profileLongTimeRunningSql;
    }

    public void setProfileLongTimeRunningSql(boolean profileLongTimeRunningSql) {
        this.profileLongTimeRunningSql = profileLongTimeRunningSql;
    }

    public long getLongTimeRunningSqlIntervalThreshold() {
        return this.longTimeRunningSqlIntervalThreshold;
    }

    public void setLongTimeRunningSqlIntervalThreshold(long longTimeRunningSqlIntervalThreshold) {
        this.longTimeRunningSqlIntervalThreshold = longTimeRunningSqlIntervalThreshold;
    }

    public void setDataSource(DataSource dataSource) {
        try {
            Connection connection = dataSource.getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            this.databaseUrl = metaData.getURL();
            this.databaseUserName = metaData.getUserName();
            String productName = metaData.getDatabaseProductName();
            if(productName.toLowerCase().contains("db2")) {
                this.dbType = DBType.DB2;
            } else if(productName.toLowerCase().contains("mysql")) {
                this.dbType = DBType.MYSQL;
            } else if(productName.toLowerCase().contains("oracle")) {
                this.dbType = DBType.ORACLE;
            }
        } catch (SQLException var5) {
            throw this.getExceptionTranslator().translate((String)null, (String)null, var5);
        }

        super.setDataSource(dataSource);
    }

    public DataSource getDataSource() {
        return super.getDataSource();
    }

    public Configuration getConfiguration() {
        return this.configuration;
    }

    public void setConfiguration(Configuration configuration) {
        this.configuration = configuration;
    }

    public String getLogPrefix() {
        return this.logPrefix;
    }

    public void setLogPrefix(String logPrefix) {
        this.logPrefix = logPrefix;
    }

    public SqlAuditor getSqlAuditor() {
        return this.sqlAuditor;
    }

    public DBType getDbType() {
        return this.dbType;
    }

    public String getDatabaseUrl() {
        return this.databaseUrl;
    }

    public String getDatabaseUserName() {
        return this.databaseUserName;
    }

    public void setSqlAuditor(SqlAuditor sqlAuditor) {
        this.sqlAuditor = sqlAuditor;
    }

    public Number persist(Object entity) {
        return (Number)this.persist(entity, Number.class);
    }

    public <T> T persist(Object entity, Class<T> requiredType) {
        long startTimestamp = System.currentTimeMillis();
        String insertSQL = null;
        Map<String, Object> paramMap = null;
        String tracerService = this.getTrackerServiceName();
        TraceContext traceContext = this.getTraceContext(tracerService);
        boolean var22 = false;

        Object var14;
        label128: {
            String sqlId;
            try {
                var22 = true;
                Class<? extends Object> entityClass = entity.getClass();
                sqlId = entityClass.getName() + ".insert";
                MappedStatement mappedStatement = this.configuration.getMappedStatement(sqlId, true);
                this.applyStatementSettings(mappedStatement);
                paramMap = ValueParser.parser(entity);
                insertSQL = mappedStatement.getBoundSql(paramMap);
                KeyHolder keyHolder = new GeneratedKeyHolder();
                this.logMessage("persist", insertSQL, paramMap);
                Object key;
                if(mappedStatement.getKeyGenerator() != null) {
                    key = this.queryBySequence(mappedStatement.getKeyGenerator(), false);
                    paramMap.put(mappedStatement.getIdProperty(), key);
                }

                if(mappedStatement.getIsGenerator().booleanValue()) {
                    this.execution.update(insertSQL, new MapSqlParameterSource(paramMap), keyHolder);
                } else {
                    this.execution.update(insertSQL, new MapSqlParameterSource(paramMap));
                }

                key = paramMap.get(mappedStatement.getIdProperty());
                if(key == null || key instanceof Number && ((Number)key).doubleValue() == 0.0D) {
                    DalUtils.setProperty(entity, mappedStatement.getIdProperty(), keyHolder.getKey());
                    key = keyHolder.getKey();
                }

                this.logMessage("persist", insertSQL, paramMap);
                Tracer.clientAccept(traceContext);
                var14 = key;
                var22 = false;
                break label128;
            } catch (Exception var23) {
                Tracer.clientAcceptWithError(traceContext, var23.getMessage());
                this.throwException(var23);
                sqlId = null;
                var22 = false;
            } finally {
                if(var22) {
                    if(this.isProfileLongTimeRunningSql()) {
                        long interval = System.currentTimeMillis() - startTimestamp;
                        if(interval > this.getLongTimeRunningSqlIntervalThreshold()) {
                            logger.warn("SQL Statement [{}] with parameter object [{}] ran out of the normal time range, it consumed [{}] milliseconds.", new Object[]{insertSQL, paramMap, Long.valueOf(interval)});
                            this.executeSqlAuditorIfNecessary(insertSQL, paramMap, interval);
                        }
                    }

                }
            }

            if(this.isProfileLongTimeRunningSql()) {
                long interval = System.currentTimeMillis() - startTimestamp;
                if(interval > this.getLongTimeRunningSqlIntervalThreshold()) {
                    logger.warn("SQL Statement [{}] with parameter object [{}] ran out of the normal time range, it consumed [{}] milliseconds.", new Object[]{insertSQL, paramMap, Long.valueOf(interval)});
                    this.executeSqlAuditorIfNecessary(insertSQL, paramMap, interval);
                }
            }

            return sqlId;
        }

        if(this.isProfileLongTimeRunningSql()) {
            long interval = System.currentTimeMillis() - startTimestamp;
            if(interval > this.getLongTimeRunningSqlIntervalThreshold()) {
                logger.warn("SQL Statement [{}] with parameter object [{}] ran out of the normal time range, it consumed [{}] milliseconds.", new Object[]{insertSQL, paramMap, Long.valueOf(interval)});
                this.executeSqlAuditorIfNecessary(insertSQL, paramMap, interval);
            }
        }

        return var14;
    }

    public Object queryBySequence(String sequence, boolean needUpdate) {
        Map result;
        if(needUpdate) {
            this.execution.update(sequence, new HashMap());
            result = this.execution.queryForMap("select last_insert_id() as seq", new HashMap());
            return result.get("seq");
        } else {
            result = this.execution.queryForMap(sequence, new HashMap());
            return result.get("1");
        }
    }

public <T> List<T> queryForList(String sqlId, Map<String, Object> paramMap, RowMapper<T> rowMapper) {
    long startTimestamp = System.currentTimeMillis();
    String sql = null;
    String tracerService = this.getTrackerServiceName();
    TraceContext traceContext = this.getTraceContext(tracerService);
    boolean var19 = false;

    List var11;
    label81: {
        List list;
        try {
            var19 = true;
            MappedStatement stmt = this.configuration.getMappedStatement(sqlId, true);
            this.applyStatementSettings(stmt);
            sql = stmt.getBoundSql(paramMap);
            this.logMessage("queryForList(3 paramter)", sql, paramMap);
            list = this.execution.query(sql, DalUtils.mapIfNull(paramMap), rowMapper);
            this.logMessage("queryForList(3 paramter)", sql, paramMap);
            Tracer.clientAccept(traceContext);
            var11 = list;
            var19 = false;
            break label81;
        } catch (Exception var20) {
            Tracer.clientAcceptWithError(traceContext, var20.getMessage());
            this.throwException(var20);
            list = null;
            var19 = false;
        } finally {
            if(var19) {
                if(this.isProfileLongTimeRunningSql()) {
                    long interval = System.currentTimeMillis() - startTimestamp;
                    if(interval > this.getLongTimeRunningSqlIntervalThreshold()) {
                        logger.warn("SQL Statement [{}] with parameter object [{}] ran out of the normal time range, it consumed [{}] milliseconds.", new Object[]{sqlId, paramMap, Long.valueOf(interval)});
                        this.executeSqlAuditorIfNecessary(sql, paramMap, interval);
                    }
                }

            }
        }

        if(this.isProfileLongTimeRunningSql()) {
            long interval = System.currentTimeMillis() - startTimestamp;
            if(interval > this.getLongTimeRunningSqlIntervalThreshold()) {
                logger.warn("SQL Statement [{}] with parameter object [{}] ran out of the normal time range, it consumed [{}] milliseconds.", new Object[]{sqlId, paramMap, Long.valueOf(interval)});
                this.executeSqlAuditorIfNecessary(sql, paramMap, interval);
            }
        }

        return list;
    }

    if(this.isProfileLongTimeRunningSql()) {
        long interval = System.currentTimeMillis() - startTimestamp;
        if(interval > this.getLongTimeRunningSqlIntervalThreshold()) {
            logger.warn("SQL Statement [{}] with parameter object [{}] ran out of the normal time range, it consumed [{}] milliseconds.", new Object[]{sqlId, paramMap, Long.valueOf(interval)});
            this.executeSqlAuditorIfNecessary(sql, paramMap, interval);
        }
    }

    return var11;
}