【mybatis】mybatis分页拦截器搭配bootstrap-table使用
提前说明:
这一种方式已被我自己pass掉了,已经被新的方式迭代了。但是记录下自己曾经的成果还是有必要的,而且里面的思想还是不变的,另外技术不就是在不断地迭代中升级吗。千万不要想着一步完美,那样会让你止步不前。
业务说明:
前台bootstrap-table插件进行数据展示;后端SSM架子接收参数;
业务分析:
前台传递limit、offset、order、等参数,后台使用Map进行接收。在拦截器中修改原始sql语句,变为分页语句。同时将total总数查询出来。放入从前台接收的map中。在Service层中将mapper查询的list也放入map中然后返回即可。
解决代码:
前台js部分代码:
queryParams: function(params){ var temp = { limit: params.limit, //页面大小 offset: params.offset, //页码 order:params.order, sort:params.sort }; return temp; },
controller接收代码:
@RequestMapping("") @ResponseBody public Map<String ,Object> list(@RequestParam Map<String,Object> params){ // 存在排序字段时,将实体类字段转换为数据库字段 if(StringUtils.checkValNotNull(params.get("sort"))){ params.put("sort",StringUtils.camelToUnderline(params.get("sort").toString())); } return userService.list(params); }
service层代码:
public Map<String,Object> list(Map<String,Object> params) { params.put(Constants.ROWS,userMapper.selectPage(params)); return params; }
mapper接口代码:
List<UserEntity> selectPage(@Param("pageMap") Map<String,Object> params);
mapper对应的xml部分代码(这里多说一点:#{}和${}用法的区别,在下面排序中,使用预编译的方式有bug所以使用非预编译模式):
<select id="selectPage" resultType="xxx.UserEntity" flushCache="false"> SELECT id FROM user_entity <choose> <when test="pageMap!=null"> <trim prefix="WHERE" prefixOverrides="AND|OR"> <if test="pageMap.id != null">id=#{pageMap.id}</if> AND is_delete='0' </trim> </when> <otherwise> WHERE is_delete ='0' </otherwise> </choose> <if test="pageMap.sort != null"> ORDER BY ${pageMap.sort} ${pageMap.order} </if> </select>
分页拦截器:
/** * @author wzd * @date 2018/12/03 */ @Intercepts({ @Signature(method = "prepare", type = StatementHandler.class, args = { Connection.class,Integer.class }) }) public class PageInterceptor implements Interceptor { @Override public Object intercept(Invocation invocation) throws Throwable { if (invocation.getTarget() instanceof StatementHandler) { return handleStatementHandler(invocation); } return invocation.proceed(); } private Object handleStatementHandler(Invocation invocation) throws InvocationTargetException, IllegalAccessException { StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); MetaObject metaObject = SystemMetaObject.forObject(statementHandler); // 仅仅针对查询 MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement"); if (!SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())) { return invocation.proceed(); } String id = mappedStatement.getId(); // 仅仅针对需要分页的查询 if(id.matches(".+Page$")){ // 获取查询的参数 BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql"); Map<String,Object> params = (Map<String, Object>) boundSql.getParameterObject(); Map<String,Object> pageMap = (Map<String, Object>) params.get("pageMap"); String sql = boundSql.getSql(); String countSql = "SELECT COUNT(*) FROM ("+ sql+") total"; this.queryTotal(countSql,metaObject,pageMap, (Connection) invocation.getArgs()[0]); String pageSql = sql + " LIMIT "+pageMap.get("offset")+","+pageMap.get("limit"); metaObject.setValue("delegate.boundSql.sql", pageSql); // 禁用mybatis的内存(逻辑)分页,重置下面的两个参数 metaObject.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET); metaObject.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT); } // 将执行权交给下一个拦截器 return invocation.proceed(); } @Override public Object plugin(Object target) { if (target instanceof StatementHandler) { return Plugin.wrap(target, this); } return target; } @Override public void setProperties(Properties properties) { } /** * 查询数据总数(带有查询条件) * @param countSql * @param metaObject * @param pageMap * @param connection */ public void queryTotal(String countSql ,MetaObject metaObject, Map<String ,Object> pageMap , Connection connection){ try{ //利用原始sql语句的方法执行 PreparedStatement countStatement = connection.prepareStatement(countSql); ParameterHandler parameterHandler = (ParameterHandler) metaObject.getValue("delegate.parameterHandler"); // sql语句的参数设置 parameterHandler.setParameters(countStatement); ResultSet rs = countStatement.executeQuery(); //当结果集中有值时,表示页面数量大于等于1 if(rs.next()) { pageMap.put("total",rs.getInt(1)); } }catch(Exception e){ } } }