java中的接口是类吗
219
2023-06-17
MyBatis实现物理分页的实例
MyBatis使用RowBounds实现的分页是逻辑分页,也就是先把数据记录全部查询出来,然在再根据offset和limit截断记录返回
为了在数据库层面上实现物理分页,又不改变原来MyBatis的函数逻辑,可以编写plugiaoRdJaOQRGn截获MyBatis Executor的statementhandler,重写SQL来执行查询
下面的插件代码只针对mysql
plugin代码
package plugin;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.logging.Log;
import org.apache.ibatis.logging.LogFactory;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.factory.ObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.RowBounds;
/**
* 通过拦截StatementHandler
的prepare
方法,重写sql语句实现物理分页。
* 老规矩,签名里要拦截的类型只能是接口。
*
*/
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
public class PaginationInterceptor implements Interceptor {
private static final Log logger = LogFactory.getLog(PaginationInterceptor.class);
private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
private static String DEFAULT_PAGE_SQL_ID = ".*Page$"; // 需要拦截的ID(正则匹配)
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY,
DEFAULT_OBJECT_WRAPPER_FACTORY);
RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue("delegate.rowBounds");
// 分离代理对象链(由于目标类可能被多个拦截器拦截,从而形成多次代理,通过下面的两次循环可以分离出最原始的的目标类)
while (metaStatementHandler.hasGetter("h")) {
Object object = metaStatementHandler.getValue("h");
metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
}
// 分离最后一个代理对象的目标类
while (metaStatementHandler.hasGetter("target")) {
Object object = metaStatementHandler.getValue("target");
metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
}
// property在mybatis settings文件内配置
Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration");
// 设置pageSqlId
String pageSqlId = configuration.getVariables().getProperty("pageSqlId");
if (null == pageSqlId || "".equals(pageSqlId)) {
logger.warn("Property pageSqlId is not setted,use default '.*Page$' ");
pageSqlId = DEFAULT_PAGE_SQL_ID;
}
MappedStatement mappedStatement = (MappedStatement)
metaStatementHandler.getValue("delegate.mappedStatement");
// 只重写需要分页的sql语句。通过MappedStatement的ID匹配,默认重写以Page结尾的MappedStatement的sql
if (mappedStatement.getId().matches(pageSqlId)) {
BoundSql boundSql = (BoundSql) maoRdJaOQRGetaStatementHandler.getValue("delegate.boundSql");
Object parameterObject = boundSql.getParameterObject();
if (parameterObject == null) {
throw new NullPointerException("parameterObject is null!");
} else {
String sql = boundSql.getSql();
// 重写sql
String pageSql = sql + " LIMIT " + rowBounds.getOffset() + "," + rowBounds.getLimit();
metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
// 采用物理分页后,就不需要mybatis的内存分页了,所以重置下面的两个参数
metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);
metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
}
}
// 将执行权交给下一个拦截器
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
// 当目标类是StatementHandler类型时,才包装目标类,否者直接返回目标本身,减少目标被代理的次数
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
} else {
return target;
}
}
@Override
public void setProperties(Properties properties) {
//To change body of implemented methods use File | Settings | File Templates.
}
}
配置plugin
查询SQL
<![CDATA[
SELECT * FROM user
]]>
调用示例
@Override
public List
RowBounds rowBounds = new RowBounds(offset, limit);
return getSqlSession().selectList("dao.userdao.selectUserByPage", new Object(), rowBounds);
}
另外,结合Spring MVC,编写翻页和生成页码代码
页码类
package util;
/**
* Created with IntelliJ IDEA.
* User: zhenwei.liu
* Date: 13-8-7
* Time: 上午10:29
* To change this template use File | Settings | File Templates.
*/
public class Pagination {
private String url; // 页码url
private int pageSize = 10; // 每页显示记录数
private int currentPage = 1; // 当前页码
private int maxPage = Integer.MAX_VALUE; // 最大页数
// 获取offset
public int getOffset() {
return (currentPage - 1) * pageSize;
}
// 获取limit
public int getLimit() {
return getPageSize();
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
if (currentPage < 1)
currentPage = 1;
if (currentPage > maxPage)
currentPage = maxPage;
this.currentPage = currentPage;
}
public int getMaxPage() {
return maxPage;
}
public void setMaxPage(int maxPage) {
this.maxPage = maxPage;
}
}
为了计算最大页码,需要知道数据表的总记录数,查询SQL如下
<![CDATA[
SELECT COUNT(*) FROM user
]]>
@Override
public Integer countTable() {
return getSqlSession().selectOne("dao.userdao.countUser");
}
Controller中的使用
@RequestMapping("/getUserByPage")
public String getUserByPage(@RequestParam
int page, Model model) {
pagination.setCurrentPage(page);
pagination.setUrl(getCurrentUrl());
pagination.setMaxPage(userDao.countTable() / pagination.getPageSize() + 1);
List
pagination.getOffset(), pagination.getLimit());
model.addAttribute(pagination);
model.addAttribute(userList);
return "index";
}
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。
发表评论
暂时没有评论,来抢沙发吧~