搞清楚什么是分页(pagination)
例如,在数据库的某个表里有1000条数据,我们每次只显示100条数据,在第1页显示第0到第99条,在第2页显示第100到199条,依次类推,这就是分页。
分页可以分为逻辑分页和物理分页。逻辑分页是我们的程序在显示每页的数据时,首先查询得到表中的1000条数据,然后成熟根据当前页的“页码”选出其中的100条数据来显示。
物理分页是程序先判断出该选出这1000条的第几条到第几条,然后数据库根据程序给出的信息查询出程序需要的100条返回给我们的程序。
MyBatis 物理分页
MyBatis使用RowBounds实现的分页是逻辑分页,也就是先把数据记录全部查询出来,然在再根据 offset 和 limit 截断记录返回。
为了在数据库层面上实现物理分页,又不改变原来 MyBatis 的函数逻辑,可以编写 plugin 截获 MyBatis Executor 的 statementhandler,重写SQL来执行查询。
经常搭框架的人应该都清楚,框架搭建的核心就是配置文件。
在这里我们需要创建 web 工程。也需要用 mybatis与Spring mvc 集成起来,源码在本文结尾处下载,主要有以下几个方面的配置。
整个Mybatis分页示例要完成的步骤如下:
1、示例功能描述
2、创建工程
3、数据库表结构及数据记录
4、实例对象
5、配置文件
6、测试执行,输出结果
1、示例功能描述
在本示例中,需要使用 MyBatis和Spring MVC整合完成分页,完成这样的一个简单功能,即指定一个用户(ID=1),查询出这个用户关联的所有订单分页显示出来(使用的数据库是:MySQL)。
2、创建工程
首先创建一个工程的名称为:mybatis08-paging,在 src 源代码目录下建立文件夹 config,并将原来的 mybatis 配置文件 Configuration.xml 移动到这个文件夹中, 并在 config 文家夹中建立 Spring 配置文件:applicationContext.xml。工程结构目录如下:
3、数据库表结构及数据记录
在本示例中,用到两个表:用户表和订单表,其结构和数据记录如下:
CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(64) NOT NULL DEFAULT '', `mobile` varchar(16) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES ('1', 'yiibai', '13838009988'); INSERT INTO `user` VALUES ('2', 'saya', '13838009988');
订单表结构和数据如下:
CREATE TABLE `order` ( `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL DEFAULT '0', `order_no` varchar(16) NOT NULL DEFAULT '', `money` float(10,2) unsigned DEFAULT '0.00', PRIMARY KEY (`order_id`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of order -- ---------------------------- INSERT INTO `order` VALUES ('1', '1', '1509289090', '99.90'); INSERT INTO `order` VALUES ('2', '1', '1519289091', '290.80'); INSERT INTO `order` VALUES ('3', '1', '1509294321', '919.90'); INSERT INTO `order` VALUES ('4', '1', '1601232190', '329.90'); INSERT INTO `order` VALUES ('5', '1', '1503457384', '321.00'); INSERT INTO `order` VALUES ('6', '1', '1598572382', '342.00'); INSERT INTO `order` VALUES ('7', '1', '1500845727', '458.00'); INSERT INTO `order` VALUES ('8', '1', '1508458923', '1200.00'); INSERT INTO `order` VALUES ('9', '1', '1504538293', '2109.00'); INSERT INTO `order` VALUES ('10', '1', '1932428723', '5888.00'); INSERT INTO `order` VALUES ('11', '1', '2390423712', '3219.00'); INSERT INTO `order` VALUES ('12', '1', '4587923992', '123.00'); INSERT INTO `order` VALUES ('13', '1', '4095378812', '421.00'); INSERT INTO `order` VALUES ('14', '1', '9423890127', '678.00'); INSERT INTO `order` VALUES ('15', '1', '7859213249', '7689.00'); INSERT INTO `order` VALUES ('16', '1', '4598450230', '909.20');
4、实例对象
用户表和订单表分别对应两个实例对象,分别是:User.java 和 Order.java,它们都在 com.yiibai.pojo 包中。
User.java代码内容如下:
package com.yiibai.pojo; import java.util.List; /** * @describe: User * @author: Yiibai * @version: V1.0 * @copyright http://www.yiibai.com */ public class User { private int id; private String username; private String mobile; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getMobile() { return mobile; } public void setMobile(String mobile) { this.mobile = mobile; } }
Order.java代码内容如下:
package com.yiibai.pojo; /** * @describe: User - 订单 * @author: Yiibai * @version: V1.0 * @copyright http://www.yiibai.com */ public class Order { private int orderId; private String orderNo; private float money; private int userId; private User user; public int getUserId() { return userId; } public void setUserId(int userId) { this.userId = userId; } public int getOrderId() { return orderId; } public void setOrderId(int orderId) { this.orderId = orderId; } public User getUser() { return user; } public void setUser(User user) { this.user = user; } public String getOrderNo() { return orderNo; } public void setOrderNo(String orderNo) { this.orderNo = orderNo; } public float getMoney() { return money; } public void setMoney(float money) { this.money = money; } }
5、配置文件
这个实例中有三个重要的配置文件,它们分别是:applicationContext.xml , Configuration.xml 以及 UserMaper.xml。
applicationContext.xml 配置文件里最主要的配置:
<?xml version="1.0" encoding="utf-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd" default-autowire="byName" default-lazy-init="false"> <!--本示例采用DBCP连接池,应预先把DBCP的jar包复制到工程的lib目录下。 --> <context:property-placeholder location="classpath:/config/database.properties" /> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" p:driverClassName="com.mysql.jdbc.Driver" p:url="jdbc:mysql://127.0.0.1:3306/yiibai?characterEncoding=utf8" p:username="root" p:password="" p:maxActive="10" p:maxIdle="10"> </bean> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <!--dataSource属性指定要用到的连接池--> <property name="dataSource" ref="dataSource" /> <!--configLocation属性指定mybatis的核心配置文件--> <property name="configLocation" value="classpath:config/Configuration.xml" /> <!-- 所有配置的mapper文件 --> <property name="mapperLocations" value="classpath*:com/yiibai/mapepr/*.xml" /> </bean> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.yiibai.maper" /> </bean> </beans>
配置文件 Configuration.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> <typeAliases> <typeAlias alias="User" type="com.yiibai.pojo.User"/> <typeAlias alias="Order" type="com.yiibai.pojo.Order"/> </typeAliases> <!-- 与spring 集成之后,这些可以完全删除,数据库连接的管理交给 spring 去管理 --> <!-- <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis?characterEncoding=utf8" /> <property name="username" value="root"/> <property name="password" value="password"/> </dataSource> </environment> </environments> --> <!-- 这里交给sqlSessionFactory 的 mapperLocations属性去得到所有配置信息 --> <!-- <mappers> <mapper resource="com/yihaomen/mapper/User.xml"/> </mappers> --> </configuration>
UserMaper.xml 用于定义查询和数据对象映射,其内容如下:
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.yiibai.maper.UserMaper"> <!-- 为了返回list 类型而定义的returnMap --> <resultMap type="User" id="resultUser"> <id column="id" property="id" /> <result column="username" property="username" /> <result column="mobile" property="mobile" /> </resultMap> <!-- User 联合 Order 查询 方法的配置 (多对一的方式) --> <resultMap id="resultUserOrders" type="Order"> <id property="orderId" column="order_id" /> <result property="orderNo" column="order_no" /> <result property="money" column="money" /> <result property="userId" column="user_id" /> <association property="user" javaType="User"> <id property="id" column="id" /> <result property="username" column="username" /> <result property="mobile" column="mobile" /> </association> </resultMap> <select id="getUserOrders" parameterType="int" resultMap="resultUserOrders"> SELECT u.*,o.* FROM `user` u, `order` o WHERE u.id=o.user_id AND u.id=#{id} </select> <select id="getUserById" resultMap="resultUser" parameterType="int"> SELECT * FROM user WHERE id=#{id} </select> </mapper>
6、测试执行,输出结果
我们创建一个控制器类在包 com.yiibai.controller 下,类的名称为:UserController.java,这里新增了一个方法:pageList, 对应请求URL是 /orderpages,其代码如下:
package com.yiibai.controller; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.servlet.ModelAndView; import com.yiibai.maper.UserMaper; import com.yiibai.pojo.Order; import com.yiibai.util.Page; // http://localhost:8080/mybatis08-paging/user/orders @Controller @RequestMapping("/user") public class UserController { @Autowired UserMaper userMaper; /** * 某一个用户下的所有订单 * * @param request * @param response * @return */ @RequestMapping("/orders") public ModelAndView listall(HttpServletRequest request, HttpServletResponse response) { List<Order> orders = userMaper.getUserOrders(1); System.out.println("orders"); ModelAndView mav = new ModelAndView("user_orders"); mav.addObject("orders", orders); return mav; } /** * 订单分页 * * @param request * @param response * @return */ @RequestMapping("/orderpages") public ModelAndView pageList(HttpServletRequest request, HttpServletResponse response) { 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(request.getRequestURI()); System.out.println(request.getQueryString()); Page page = new Page(); page.setShowCount(pageSize); page.setCurrentResult(currentResult); List<Order> orders = userMaper.getOrderListPage(page, 1); System.out.println("Current page =>" + page); int totalCount = page.getTotalResult(); int lastPage = 0; if (totalCount % pageSize == 0) { lastPage = totalCount % pageSize; } else { lastPage = 1 + totalCount / pageSize; } if (currentPage >= lastPage) { currentPage = lastPage; } String pageStr = ""; pageStr = String.format( "<a href="%s">上一页</a> <a href="%s">下一页</a>", request .getRequestURI() + "?page=" + (currentPage - 1), request.getRequestURI() + "?page=" + (currentPage + 1)); // 制定视图,也就是list.jsp ModelAndView mav = new ModelAndView("pagelist"); mav.addObject("orders", orders); mav.addObject("pagelist", pageStr); return mav; } }
注意,在这个分页工程中,在 com.yiibai.util 包下新增了几个类,它们分别是:PagePlugin.java,Page.java, PageHelper.java,其中 PagePlugin 是针对 MyBatis 分页的插件。由于代码太多,这里列出 PagePlugin.java 的代码,其它两个类的代码有兴趣的读者可以在下载代码后阅读取研究。 PagePlugin.java 的代码如下所示:
package com.yiibai.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.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.scripting.xmltags.ForEachSqlNode; 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 NullYiibaierException("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(); Page page = null; if (parameterObject instanceof Page) { page = (Page) parameterObject; page.setTotalResult(count); } else if (parameterObject instanceof Map) { Map<String, Object> map = (Map<String, Object>) parameterObject; page = (Page) map.get("page"); if (page == null) page = new Page(); page.setTotalResult(count); } else { Field pageField = ReflectHelper.getFieldByFieldName( parameterObject, "page"); if (pageField != null) { page = (Page) ReflectHelper.getValueByFieldName( parameterObject, "page"); if (page == null) page = new Page(); 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, Page 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(); } } } }
接下来部署 mybatis08-paging 这个工程,启动 tomcat ,打开浏览器输入网址:http://localhost:8080/mybatis08-paging/user/orderpages ,显示结果如下: