--工具类 package com.bainuo.vin.plugin; public class Page { private int showCount; // 每页显示记录数 private int totalPage; // 总页数 private int totalResult; // 总记录数 private int currentPage; // 当前页 private int currentResult; // 当前记录起始索引 private boolean entityOrField; // true:需要分页的地方,传入的参数就是Page实体;false:需要分页的地方,传入的参数所代表的实体拥有Page属性 private ParameterMap pm = new ParameterMap(); public ParameterMap getPm() { return pm; } public Page() { this.showCount = 10; } public int getTotalPage() { if (totalResult % showCount == 0) totalPage = totalResult / showCount; else totalPage = totalResult / showCount + 1; return totalPage; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } public int getTotalResult() { return totalResult; } public void setTotalResult(int totalResult) { this.totalResult = totalResult; } public int getCurrentPage() { if (currentPage <= 0) currentPage = 1; return currentPage; } public void setCurrentPage(int currentPage) { this.currentPage = currentPage; } public int getShowCount() { return showCount; } public void setShowCount(int showCount) { this.showCount = showCount; } public int getCurrentResult() { currentResult = (getCurrentPage() - 1) * getShowCount(); if (currentResult < 0) currentResult = 0; return currentResult; } public void setCurrentResult(int currentResult) { this.currentResult = currentResult; } public boolean isEntityOrField() { return entityOrField; } public void setEntityOrField(boolean entityOrField) { this.entityOrField = entityOrField; } @Override public String toString() { return "Page [showCount=" + showCount + ", totalPage=" + totalPage + ", totalResult=" + totalResult + ", currentPage=" + currentPage + ", currentResult=" + currentResult + ", entityOrField=" + entityOrField + ", pm=" + pm + "]"; } }
package com.bainuo.vin.plugin; /** * Created by sq on 2017/11/17. * 分页使用参数 */ public interface PageConstants { // public static final String SHOW_COUNT_KEY = "showCount" ; // 每页显示记录数的key // public static final String CURRENT_PAGE_KEY = "currentPage";//当前页 public static final String SHOW_COUNT_KEY = "limit" ; // 每页显示记录数的key public static final String CURRENT_PAGE_KEY = "page";//当前页 public static final String TOTAL_PAGE_KEY = "totalPage";//总页数 public static final String TOTAL_RESULT_KEY = "count";//总记录数 public static final String CURRENT_RESULT_KEY = "currentResult"; // 当前记录起始索引 //初始化当前页 public static final Integer INIT_CURRENT_PAGE = 1; //初始化每页显示的个数 public static final Integer INIT_SHOW_COUNT=20; /** * 通过总数量和显示个数计算总页数 * @param totalResult * @param showCount * @return */ public static int getTotalPage(Integer totalResult, Integer showCount) { int totalPage = 0; if (totalResult % showCount == 0) totalPage = totalResult / showCount; else totalPage = totalResult / showCount + 1; return totalPage; } /** * 获取当前结果 * @param currentPage * @param showCount * @return */ public static int getCurrentResult(Integer currentPage, Integer showCount) { int currentResult = (currentPage - 1) * showCount; if (currentResult < 0) currentResult = 0; return currentResult; } }
package com.bainuo.vin.plugin; import com.bainuo.vin.util.Tools; import org.apache.commons.lang.StringUtils; import org.apache.ibatis.executor.ErrorContext; import org.apache.ibatis.executor.ExecutorException; import org.apache.ibatis.executor.statement.BaseStatementHandler; import org.apache.ibatis.executor.statement.RoutingStatementHandler; import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.ParameterMapping; import org.apache.ibatis.mapping.ParameterMode; import org.apache.ibatis.plugin.*; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.reflection.property.PropertyTokenizer; import org.apache.ibatis.scripting.xmltags.ForEachSqlNode; import org.apache.ibatis.session.Configuration; import org.apache.ibatis.type.TypeHandler; import org.apache.ibatis.type.TypeHandlerRegistry; import javax.xml.bind.PropertyException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import java.util.Properties; /** * * 类名称:PagePlugin.java 类描述: * * @version 1.0 * @Intercepts 是mybaits的拦截器注解 * @Signature 表明要拦截的接口、方法以及对应的参数类型。 */ @Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class ,Integer.class}) }) public class PagePlugin implements Interceptor { private static String dialect = ""; // 数据库方言 private static String pageSqlId = ""; // mapper.xml中需要拦截的ID(正则匹配) public Object intercept(Invocation ivk) throws Throwable { if (ivk.getTarget() instanceof RoutingStatementHandler) { RoutingStatementHandler statementHandler = (RoutingStatementHandler) ivk.getTarget(); BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper.getValueByFieldName(statementHandler, "delegate"); MappedStatement mappedStatement = (MappedStatement) ReflectHelper.getValueByFieldName(delegate, "mappedStatement"); if (mappedStatement.getId().matches(pageSqlId)) { // 拦截需要分页的SQL BoundSql boundSql = delegate.getBoundSql(); // 分页SQL<select>中parameterType属性对应的实体参数,即Mapper接口中执行分页方法的参数,该参数不得为空 Object parameterObject = boundSql.getParameterObject(); if (parameterObject == null) { throw new NullPointerException("parameterObject尚未实例化!"); } else { Connection connection = (Connection) ivk.getArgs()[0]; String sql = boundSql.getSql(); // String countSql = "select count(0) from (" + sql+ ") as // tmp_count"; //记录统计 String countSql = "select count(0) from (" + sql + ") tmp_count"; // 记录统计 // == // oracle // 加 // as // 报错(SQL // command // not // properly // ended) PreparedStatement countStmt = connection.prepareStatement(countSql); BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject); setParameters(countStmt, mappedStatement, countBS, parameterObject); ResultSet rs = countStmt.executeQuery(); int count = 0; if (rs.next()) { count = rs.getInt(1); } rs.close(); countStmt.close(); // System.out.println(count); ParameterMap parameMap = null; if(parameterObject instanceof ParameterMap){ parameMap = (ParameterMap) parameterObject; doPageParm(parameMap,count); } String pageSql = generatePageSql(sql, parameMap); ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql); // 将分页sql语句反射回BoundSql. } } } return ivk.proceed(); } /** * 将总记录数封装到参数里面 * @param parameterMap * @param count 总数 */ public void doPageParm(ParameterMap parameterMap ,int count){ //如果总页数 Object shwoCount = parameterMap.get(PageConstants.SHOW_COUNT_KEY); Integer show = PageConstants.INIT_SHOW_COUNT; if(shwoCount!=null&&StringUtils.isNumeric(String.valueOf(show))){ show = Integer.parseInt(String.valueOf(shwoCount)); } parameterMap.put(PageConstants.SHOW_COUNT_KEY,show); Integer currentPage = PageConstants.INIT_CURRENT_PAGE; Object curreantString = parameterMap.get(PageConstants.CURRENT_PAGE_KEY); if(curreantString!=null&&StringUtils.isNumeric(String.valueOf(curreantString))){ currentPage = Integer.parseInt(String.valueOf(curreantString)); } parameterMap.put(PageConstants.CURRENT_PAGE_KEY,currentPage); //总页数 Integer totalPage = PageConstants.getTotalPage(count, show); parameterMap.put(PageConstants.TOTAL_PAGE_KEY,totalPage); //当前索引号 Integer currentResult = PageConstants.getCurrentResult(currentPage, show); parameterMap.put(PageConstants.CURRENT_RESULT_KEY,currentResult); //总记录数 parameterMap.put(PageConstants.TOTAL_RESULT_KEY,count); } /** * 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler * * @param ps * @param mappedStatement * @param boundSql * @param parameterObject * @throws SQLException */ @SuppressWarnings({ "rawtypes", "unchecked" }) private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException { ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId()); List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); if (parameterMappings != null) { Configuration configuration = mappedStatement.getConfiguration(); TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject); for (int i = 0; i < parameterMappings.size(); i++) { ParameterMapping parameterMapping = parameterMappings.get(i); if (parameterMapping.getMode() != ParameterMode.OUT) { Object value; String propertyName = parameterMapping.getProperty(); PropertyTokenizer prop = new PropertyTokenizer(propertyName); if (parameterObject == null) { value = null; } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { value = parameterObject; } else if (boundSql.hasAdditionalParameter(propertyName)) { value = boundSql.getAdditionalParameter(propertyName); } else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName())) { value = boundSql.getAdditionalParameter(prop.getName()); if (value != null) { value = configuration.newMetaObject(value) .getValue(propertyName.substring(prop.getName().length())); } } else { value = metaObject == null ? null : metaObject.getValue(propertyName); } TypeHandler typeHandler = parameterMapping.getTypeHandler(); if (typeHandler == null) { throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId()); } typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType()); } } } } /** * 根据数据库方言,生成特定的分页sql * * @param sql * @param parameMap * @return */ private String generatePageSql(String sql, ParameterMap parameMap) { if (parameMap != null && Tools.notEmpty(dialect)) { StringBuffer pageSql = new StringBuffer(); if ("mysql".equals(dialect)) { pageSql.append(sql); pageSql.append(" limit " +parameMap.get(PageConstants.CURRENT_RESULT_KEY) + "," + parameMap.get(PageConstants.SHOW_COUNT_KEY)); } else if ("oracle".equals(dialect)) { pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from ("); pageSql.append(sql); // pageSql.append(") as tmp_tb where ROWNUM<="); pageSql.append(") tmp_tb where ROWNUM<="); pageSql.append(parameMap.get(PageConstants.CURRENT_RESULT_KEY)).append("+").append( parameMap.get(PageConstants.SHOW_COUNT_KEY)); pageSql.append(") where row_id>"); pageSql.append(parameMap.get(PageConstants.CURRENT_RESULT_KEY)); } return pageSql.toString(); } else { return sql; } } public Object plugin(Object arg0) { return Plugin.wrap(arg0, this); } public void setProperties(Properties p) { dialect = p.getProperty("dialect"); if (Tools.isEmpty(dialect)) { try { throw new PropertyException("dialect property is not found!"); } catch (PropertyException e) { e.printStackTrace(); } } pageSqlId = p.getProperty("pageSqlId"); if (Tools.isEmpty(pageSqlId)) { try { throw new PropertyException("pageSqlId property is not found!"); } catch (PropertyException e) { e.printStackTrace(); } } } }
package com.bainuo.vin.plugin; import javax.servlet.http.HttpServletRequest; import java.io.UnsupportedEncodingException; import java.util.*; /** * 拦截所有参数 * * @author Administrator * */ @SuppressWarnings("rawtypes") public class ParameterMap extends HashMap implements Map { /** * */ private static final long serialVersionUID = 1L; Map map = null; HttpServletRequest request; public ParameterMap() { map = new HashMap(); } public ParameterMap(Page page) { Map<String, Object> pageMap = new HashMap<String, Object>(); pageMap.put("currentPage", page.getCurrentPage()); pageMap.put("totalPage", page.getTotalPage()); pageMap.put("totalResult", page.getTotalResult()); pageMap.put("showCount", page.getShowCount()); map = pageMap; } public Map getMap() { return map; } public void setMap(Map map) { this.map = map; } @SuppressWarnings("unchecked") public ParameterMap(HttpServletRequest request) { this.request = request; try { request.setCharacterEncoding("utf-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } Map Proerties = request.getParameterMap(); Map returnMap = new HashMap(); Iterator entries = Proerties.entrySet().iterator(); Entry entry; while (entries.hasNext()) { String name = ""; String value = ""; entry = (Entry) entries.next(); name = (String) entry.getKey(); Object valueObject = entry.getValue(); if (valueObject == null) { value = ""; } else if (valueObject instanceof String[]) { String[] values = (String[]) valueObject; for (int i = 0; i < values.length; i++) { value = values[i] + "," + value; } value = value.substring(0, value.length() - 1); } else { value = valueObject.toString(); } returnMap.put(name, value); } // returnMap.put("SessionID", request.getSession().getId()); map = returnMap; } @Override public Object get(Object key) { Object obj = null; if (map.get(key) instanceof Object[]) { Object[] arr = (Object[]) map.get(key); obj = request == null ? arr : (request.getParameter((String) key) == null ? arr : arr[0]); } else { obj = map.get(key); } return obj; } public String getString(Object key) { String str = ""; if (get(key) != null) { str = String.valueOf(get(key)); } return str; } @SuppressWarnings("unchecked") @Override public Object put(Object key, Object value) { if (value == null) { return map.put(key, ""); } return map.put(key, value); } @SuppressWarnings("unchecked") public Object putObject(Object key, Object value) { return map.put(key, value); } @Override public Object remove(Object key) { return map.remove(key); } @Override public void clear() { map.clear(); } @Override public boolean containsKey(Object key) { return map.containsKey(key); } @Override public boolean containsValue(Object value) { return map.containsValue(value); } @Override public Set entrySet() { return map.entrySet(); } @Override public boolean isEmpty() { return map.isEmpty(); } @Override public Set keySet() { return map.keySet(); } @SuppressWarnings("unchecked") @Override public void putAll(Map m) { map.putAll(m); } @Override public int size() { return map.size(); } @Override public Collection values() { return map.values(); } }
package com.bainuo.vin.plugin; import java.lang.reflect.Field; /** * @author Administrator 反射工具 分页获取到 */ public class ReflectHelper { /** * 获取obj对象fieldName的Field * * @param obj * @param fieldName * @return */ public static Field getFieldByFieldName(Object obj, String fieldName) { for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass .getSuperclass()) { try { return superClass.getDeclaredField(fieldName); } catch (NoSuchFieldException e) { } } return null; } /** * 获取obj对象fieldName的属性值 * * @param obj * @param fieldName * @return * @throws SecurityException * @throws NoSuchFieldException * @throws IllegalArgumentException * @throws IllegalAccessException */ public static Object getValueByFieldName(Object obj, String fieldName) throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException { Field field = getFieldByFieldName(obj, fieldName); Object value = null; if (field != null) { if (field.isAccessible()) { value = field.get(obj); } else { field.setAccessible(true); value = field.get(obj); field.setAccessible(false); } } return value; } /** * 设置obj对象fieldName的属性值 * * @param obj * @param fieldName * @param value * @throws SecurityException * @throws NoSuchFieldException * @throws IllegalArgumentException * @throws IllegalAccessException */ public static void setValueByFieldName(Object obj, String fieldName, Object value) throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException { Field field = obj.getClass().getDeclaredField(fieldName); if (field.isAccessible()) { field.set(obj, value); } else { field.setAccessible(true); field.set(obj, value); field.setAccessible(false); } } }
--配置文件 mybatis.config-locations = classpath:mybatis/mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <setting name="cacheEnabled" value="true" /><!-- 全局映射器启用缓存 --> <setting name="useGeneratedKeys" value="true" /> <setting name="defaultExecutorType" value="REUSE" /> <setting name="callSettersOnNulls" value="true"/> <!-- 打印查询语句 --> <setting name="logImpl" value="STDOUT_LOGGING" /> </settings> <plugins> <plugin interceptor="com.bainuo.vin.plugin.PagePlugin"> <property name="dialect" value="mysql"/> <property name="pageSqlId" value=".*Page.*"/> </plugin> </plugins> </configuration>