本方法是通过拦截mybatis底层的数据库操作达到分页目的
- private int showCount = 10; //每页显示记录数
- private int totalPage; //总页数
- private int totalResult; //总记录数
- private int currentPage; //当前页
- private int currentResult; //当前记录起始索引
- private boolean entityOrField; //true:需要分页的地方,传入的参数就是Page实体;false:需要分页的地方,传入的参数所代表的实体拥有Page属性
- private String pageStr; //最终页面显示的底部翻页导航,详细见:getPageStr();
- 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;
- if(currentPage>getTotalPage())
- currentPage = getTotalPage();
- return currentPage;
- }
- public void setCurrentPage(int currentPage) {
- this.currentPage = currentPage;
- }
- public String getPageStr() {
- StringBuffer sb = new StringBuffer();
- if(totalResult>0){
- sb.append(" <ul>\n");
- if(currentPage==1){
- sb.append(" <li class=\"pageinfo\">首页</li>\n");
- sb.append(" <li class=\"pageinfo\">上页</li>\n");
- }else{
- sb.append(" <li><a href=\"#@\" οnclick=\"nextPage(1)\">首页</a></li>\n");
- sb.append(" <li><a href=\"#@\" οnclick=\"nextPage("+(currentPage-1)+")\">上页</a></li>\n");
- }
- int showTag = 3; //分页标签显示数量
- int startTag = 1;
- if(currentPage>showTag){
- startTag = currentPage-1;
- }
- int endTag = startTag+showTag-1;
- for(int i=startTag; i<=totalPage && i<=endTag; i++){
- if(currentPage==i)
- sb.append("<li class=\"current\">"+i+"</li>\n");
- else
- sb.append(" <li><a href=\"#@\" οnclick=\"nextPage("+i+")\">"+i+"</a></li>\n");
- }
- if(currentPage==totalPage){
- sb.append(" <li class=\"pageinfo\">下页</li>\n");
- sb.append(" <li class=\"pageinfo\">尾页</li>\n");
- }else{
- sb.append(" <li><a href=\"#@\" οnclick=\"nextPage("+(currentPage+1)+")\">下页</a></li>\n");
- sb.append(" <li><a href=\"#@\" οnclick=\"nextPage("+totalPage+")\">尾页</a></li>\n");
- }
- sb.append(" <li class=\"pageinfo\">第"+currentPage+"页</li>\n");
- sb.append(" <li class=\"pageinfo\">共"+totalPage+"页</li>\n");
- sb.append("</ul>\n");
- sb.append("<script type=\"text/javascript\">\n");
- sb.append("function nextPage(page){");
- sb.append(" if(true && document.forms[0]){\n");
- sb.append(" var url = document.forms[0].getAttribute(\"action\");\n");
- sb.append(" if(url.indexOf('?')>-1){url += \"&"+(entityOrField?"currentPage":"page.currentPage")+"=\";}\n");
- sb.append(" else{url += \"?"+(entityOrField?"currentPage":"page.currentPage")+"=\";}\n");
- sb.append(" document.forms[0].action = url+page;\n");
- sb.append(" document.forms[0].submit();\n");
- sb.append(" }else{\n");
- sb.append(" var url = document.location+';\n");
- sb.append(" if(url.indexOf('?')>-1){\n");
- sb.append(" if(url.indexOf('currentPage')>-1){\n");
- sb.append(" var reg = /currentPage=\\d*/g;\n");
- sb.append(" url = url.replace(reg,'currentPage=');\n");
- sb.append(" }else{\n");
- sb.append(" url += \"&"+(entityOrField?"currentPage":"page.currentPage")+"=\";\n");
- sb.append(" }\n");
- sb.append(" }else{url += \"?"+(entityOrField?"currentPage":"page.currentPage")+"=\";}\n");
- sb.append(" document.location = url + page;\n");
- sb.append(" }\n");
- sb.append("}\n");
- sb.append("</script>\n");
- }
- pageStr = sb.toString();
- return pageStr;
- }
- public void setPageStr(String pageStr) {
- this.pageStr = pageStr;
- }
- 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;
- }
通过拦截StatementHandler的prepare方法的分页插件类
- @Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})
- public class PagePlugin implements Interceptor {
- private static String dialect = ""; //数据库方言
- private static String pageSqlId = ""; //mapper.xml中需要拦截的ID(正则匹配)
- public Object intercept(Invocation ivk) throws Throwable {
- // TODO Auto-generated method stub
- 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();
- Object parameterObject = boundSql.getParameterObject();//分页SQL<select>中parameterType属性对应的实体参数,即Mapper接口中执行分页方法的参数,该参数不得为空
- 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"; //记录统计
- 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);
- Page page = null;
- if(parameterObject instanceof Page){ //参数就是Page实体
- page = (Page) parameterObject;
- page.setEntityOrField(true); //见com.flf.entity.Page.entityOrField 注释
- page.setTotalResult(count);
- }else{ //参数为某个实体,该实体拥有Page属性
- Field pageField = ReflectHelper.getFieldByFieldName(parameterObject,"page");
- if(pageField!=null){
- page = (Page) ReflectHelper.getValueByFieldName(parameterObject,"page");
- if(page==null)
- page = new Page();
- page.setEntityOrField(false); //见com.flf.entity.Page.entityOrField 注释
- page.setTotalResult(count);
- ReflectHelper.setValueByFieldName(parameterObject,"page", page); //通过反射,对实体对象设置分页对象
- }else{
- throw new NoSuchFieldException(parameterObject.getClass().getName()+"不存在 page 属性!");
- }
- }
- String pageSql = generatePageSql(sql,page);
- ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql); //将分页sql语句反射回BoundSql.
- }
- }
- }
- return ivk.proceed();
- }
- /**
- * 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler
- * @param ps
- * @param mappedStatement
- * @param boundSql
- * @param parameterObject
- * @throws SQLException
- */
- 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 page
- * @return
- */
- private String generatePageSql(String sql,Page page){
- if(page!=null && Tools.notEmpty(dialect)){
- StringBuffer pageSql = new StringBuffer();
- if("mysql".equals(dialect)){
- pageSql.append(sql);
- pageSql.append(" limit "+page.getCurrentResult()+","+page.getShowCount());
- }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(page.getCurrentResult()+page.getShowCount());
- pageSql.append(") where row_id>");
- pageSql.append(page.getCurrentResult());
- }
- return pageSql.toString();
- }else{
- return sql;
- }
- }
- public Object plugin(Object arg0) {
- // TODO Auto-generated method stub
- 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) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- pageSqlId = p.getProperty("pageSqlId");
- if (Tools.isEmpty(pageSqlId)) {
- try {
- throw new PropertyException("pageSqlId property is not found!");
- } catch (PropertyException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- }
- }
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})
public class PagePlugin implements Interceptor {
private static String dialect = ""; //数据库方言
private static String pageSqlId = ""; //mapper.xml中需要拦截的ID(正则匹配)
public Object intercept(Invocation ivk) throws Throwable {
// TODO Auto-generated method stub
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();
Object parameterObject = boundSql.getParameterObject();//分页SQL<select>中parameterType属性对应的实体参数,即Mapper接口中执行分页方法的参数,该参数不得为空
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"; //记录统计
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);
Page page = null;
if(parameterObject instanceof Page){ //参数就是Page实体
page = (Page) parameterObject;
page.setEntityOrField(true); //见com.flf.entity.Page.entityOrField 注释
page.setTotalResult(count);
}else{ //参数为某个实体,该实体拥有Page属性
Field pageField = ReflectHelper.getFieldByFieldName(parameterObject,"page");
if(pageField!=null){
page = (Page) ReflectHelper.getValueByFieldName(parameterObject,"page");
if(page==null)
page = new Page();
page.setEntityOrField(false); //见com.flf.entity.Page.entityOrField 注释
page.setTotalResult(count);
ReflectHelper.setValueByFieldName(parameterObject,"page", page); //通过反射,对实体对象设置分页对象
}else{
throw new NoSuchFieldException(parameterObject.getClass().getName()+"不存在 page 属性!");
}
}
String pageSql = generatePageSql(sql,page);
ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql); //将分页sql语句反射回BoundSql.
}
}
}
return ivk.proceed();
}
/**
* 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler
* @param ps
* @param mappedStatement
* @param boundSql
* @param parameterObject
* @throws SQLException
*/
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 page
* @return
*/
private String generatePageSql(String sql,Page page){
if(page!=null && Tools.notEmpty(dialect)){
StringBuffer pageSql = new StringBuffer();
if("mysql".equals(dialect)){
pageSql.append(sql);
pageSql.append(" limit "+page.getCurrentResult()+","+page.getShowCount());
}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(page.getCurrentResult()+page.getShowCount());
pageSql.append(") where row_id>");
pageSql.append(page.getCurrentResult());
}
return pageSql.toString();
}else{
return sql;
}
}
public Object plugin(Object arg0) {
// TODO Auto-generated method stub
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) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
pageSqlId = p.getProperty("pageSqlId");
if (Tools.isEmpty(pageSqlId)) {
try {
throw new PropertyException("pageSqlId property is not found!");
} catch (PropertyException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
该类中定义了两个私有静态变量:dialect、pageSqlId,这两个变量是在该类初始化时,即调用setProperties方法时初始化的,相关的值是在MyBatis配置文件中配置的,如下:
- <plugins>
- <plugin interceptor="com.flf.plugin.PagePlugin">
- <property name="dialect" value="mysql"/>
- <property name="pageSqlId" value=".*listPage.*"/>
- </plugin>
- </plugins>
<plugins>
<plugin interceptor="com.flf.plugin.PagePlugin">
<property name="dialect" value="mysql"/>
<property name="pageSqlId" value=".*listPage.*"/>
</plugin>
</plugins>
上面的示例表明数据库方言为mysql,拦截所有mapper.xml映射文件中id包含有listPage的SQL。
反射帮助类:
- /**
- * @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);
- }
- }
- }