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; }