分页查询

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 areaNmFROM ${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;}