SpirngMvc+myBatis 物理分页
来源:互联网 发布:华为云和阿里云区别 编辑:程序博客网 时间:2024/05/16 19:27
1.实现mybatis 物理分页,一个最简单的方式是,是在你的mapper的SQL语句中直接写类似如下方式 :
程序代码
<select id="getUserArticles" parameterType="Your_params" resultMap="resultUserArticleList">
select user.id,user.userName,user.userAddress,article.id aid,article.title,article.content from user,article
where user.id=article.userid and user.id=#{id} limit #{offset},#{pagesize}
</select>
请注意这里的 parameterType 是你传入的参数类,或者map ,里面包含了offset,pagesize ,和其他你需要的参数,用这种方式,肯定可以实现分页。这是简单的一种方式。但更通用的一种方式是用 mybatis 插件的方式. 参考了网上的很多资料 ,mybatis plugin 方面的资料。写自己的插件.
程序代码
<select id="getUserArticles" parameterType="Your_params" resultMap="resultUserArticleList">
select user.id,user.userName,user.userAddress,article.id aid,article.title,article.content from user,article
where user.id=article.userid and user.id=#{id} limit #{offset},#{pagesize}
</select>
程序代码
package com.yihaomen.util;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import javax.xml.bind.PropertyException;
import org.apache.ibatis.builder.xml.dynamic.ForEachSqlNode;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.Executor;
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.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.property.PropertyTokenizer;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
public class PagePlugin implements Interceptor {
private static String dialect = "";
private static String pageSqlId = "";
@SuppressWarnings("unchecked")
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)) {
BoundSql boundSql = delegate.getBoundSql();
Object parameterObject = boundSql.getParameterObject();
if (parameterObject == null) {
throw new NullPointerException("parameterObject error");
} else {
Connection connection = (Connection) ivk.getArgs()[0];
String sql = boundSql.getSql();
String countSql = "select count(0) from (" + sql + ") myCount";
System.out.println("总数sql 语句:"+countSql);
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();
PageInfo page = null;
if (parameterObject instanceof PageInfo) {
page = (PageInfo) parameterObject;
page.setTotalResult(count);
} else if(parameterObject instanceof Map){
Map<String, Object> map = (Map<String, Object>)parameterObject;
page = (PageInfo)map.get("page");
if(page == null)
page = new PageInfo();
page.setTotalResult(count);
}else {
Field pageField = ReflectHelper.getFieldByFieldName(
parameterObject, "page");
if (pageField != null) {
page = (PageInfo) ReflectHelper.getValueByFieldName(
parameterObject, "page");
if (page == null)
page = new PageInfo();
page.setTotalResult(count);
ReflectHelper.setValueByFieldName(parameterObject,
"page", page);
} else {
throw new NoSuchFieldException(parameterObject
.getClass().getName());
}
}
String pageSql = generatePageSql(sql, page);
System.out.println("page sql:"+pageSql);
ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql);
}
}
}
return ivk.proceed();
}
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());
}
}
}
}
private String generatePageSql(String sql, PageInfo page) {
if (page != null && (dialect !=null || !dialect.equals(""))) {
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(") 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 (dialect ==null || dialect.equals("")) {
try {
throw new PropertyException("dialect property is not found!");
} catch (PropertyException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
pageSqlId = p.getProperty("pageSqlId");
if (dialect ==null || dialect.equals("")) {
try {
throw new PropertyException("pageSqlId property is not found!");
} catch (PropertyException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
此插件有两个辅助类:PageInfo,ReflectHelper,你可以下载源代码参考。
写了插件之后,当然需要在 mybatis 的配置文件Configuration.xml 里配置这个插件
<plugins> <plugin interceptor="org.sh.util.PagePlugin"> <property name="dialect" value="mysql" /> <property name="pageSqlId" value=".*ListPage.*" /> </plugin> </plugins>
请注意,这个插件定义了一个规则,也就是在mapper中sql语句的id 必须包含ListPage才能被拦截。否则将不会分页处理
接下来编写Controller UserController.java
@RequestMapping("/pagelist")public String pageList(HttpServletRequest request,HttpServletResponse response, Model model) {int currentPage = request.getParameter("page") == null ? 1 : Integer.parseInt(request.getParameter("page"));int pageSize = 3;if (currentPage <= 0) {currentPage = 1;}int currentResult = (currentPage - 1) * pageSize;System.out.println("*****requestUrl*******"+request.getRequestURL());System.out.println("*****QueryString*******"+request.getQueryString());PageInfo page = new PageInfo();page.setShowCount(pageSize);page.setCurrentResult(currentResult);List<Article> articles = userMapper.selectArticleListPage(page, 1);System.out.println("articles.size"+articles.size());System.out.println(page);int totalCount = page.getTotalResult();System.out.println("totalCount:"+totalCount);int lastPage = 0;if (totalCount % pageSize == 0) {System.out.println("wei 0");lastPage = totalCount / pageSize;} else {System.out.println("不为wei 0");lastPage = totalCount / pageSize + 1;}System.out.println("lastPage:"+lastPage);System.out.println("currentPage"+currentPage);if (currentPage == lastPage) {currentPage = lastPage-1;}String pageStr = "";pageStr = String.format("<a href=\"%s\">上一页</a> <a href=\"%s\">下一页</a>",request.getRequestURI() + "?page=" + (currentPage - 1),request.getRequestURI() + "?page=" + (currentPage + 1));model.addAttribute("articleList", articles);model.addAttribute("pageStr", pageStr);return "article/list";}@RequestMapping("/pagelist")public String pageList(HttpServletRequest request,HttpServletResponse response, Model model) {int currentPage = request.getParameter("page") == null ? 1 : Integer.parseInt(request.getParameter("page"));int pageSize = 3;if (currentPage <= 0) {currentPage = 1;}int currentResult = (currentPage - 1) * pageSize;System.out.println("*****requestUrl*******"+request.getRequestURL());System.out.println("*****QueryString*******"+request.getQueryString());PageInfo page = new PageInfo();page.setShowCount(pageSize);page.setCurrentResult(currentResult);List<Article> articles = userMapper.selectArticleListPage(page, 1);System.out.println("articles.size"+articles.size());System.out.println(page);int totalCount = page.getTotalResult();System.out.println("totalCount:"+totalCount);int lastPage = 0;if (totalCount % pageSize == 0) {System.out.println("wei 0");lastPage = totalCount / pageSize;} else {System.out.println("不为wei 0");lastPage = totalCount / pageSize + 1;}System.out.println("lastPage:"+lastPage);System.out.println("currentPage"+currentPage);if (currentPage == lastPage) {currentPage = lastPage-1;}String pageStr = "";pageStr = String.format("<a href=\"%s\">上一页</a> <a href=\"%s\">下一页</a>",request.getRequestURI() + "?page=" + (currentPage - 1),request.getRequestURI() + "?page=" + (currentPage + 1));model.addAttribute("articleList", articles);model.addAttribute("pageStr", pageStr);return "article/list";}
在浏览器中显示结果:
程序代码
<select id="getUserArticles" parameterType="Your_params" resultMap="resultUserArticleList">
select user.id,user.userName,user.userAddress,article.id aid,article.title,article.content from user,article
where user.id=article.userid and user.id=#{id} limit #{offset},#{pagesize}
</select>
0 0
- SpirngMvc+myBatis 物理分页
- spring+mybatis 物理分页
- Mybatis实现物理分页
- mybatis 物理分页技术
- Mybatis 实现物理分页
- mybatis 物理分页支持
- spring+mybatis 物理分页
- Mybatis实现物理分页
- MyBatis 物理分页
- MyBatis 物理分页
- MyBatis 物理分页
- mybatis实现物理分页
- mybatis实现物理分页
- Mybatis物理分页插件
- Mybatis物理分页插件
- mybatis分页插件(物理分页)
- mybatis物理分页的解决方法
- mybatis物理分页的实现
- 51单片机寄存器功能一览表
- Android开发之监听或获取手机短信内容
- Android ServiceConnection
- 测试之美(2)对测试的几点理解----谁是利益相关者?
- Linux根据端口号关闭相应的进程Shell脚本
- SpirngMvc+myBatis 物理分页
- R功能简介
- myeclipse2014破解安装
- SQL中的CASE使用方法
- TabStrip 的初学应用(简单页面切换)
- 0428_Struts2
- C++primer plus第六版课后编程题答案17.4
- OCP-1Z0-051 第53题 日期格式
- View not attached to window manager解决方案