在项目中使用PageHelper实现分页功能
1.1 项目中使用PageHelper
1.1.1 第一步:添加依赖
1.1.2 第二步:在applicationContext.xml中配置拦截器插件
<!--3. 创建SqlSessionFactoryBean,注入连接池-->
<bean class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
<!--PageHelper分页插件-->
<property name="plugins">
<array>
<bean class="com.github.pagehelper.PageInterceptor">
<property name="properties">
<props>
<prop key="helperDialect">oracle</prop>
<prop key="reasonable">true</prop>
</props>
</property>
</bean>
</array>
</property></bean>
- 完整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:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
<!--1.扫描service包-->
<context:component-scan base-package="com.sunny.service"></context:component-scan>
<!--加载jdbc.properties配置文件-->
<context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>
<!--2. 创建连接池-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${jdbc.driver}"></property>
<property name="url" value="${jdbc.url}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
<property name="initialSize" value="${jdbc.initialSize}"></property>
<property name="maxActive" value="${jdbc.maxActive}"></property>
</bean>
<!--3. 创建SqlSessionFactoryBean,注入连接池-->
<bean class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
<!--PageHelper分页插件-->
<property name="plugins">
<array>
<bean class="com.github.pagehelper.PageInterceptor">
<property name="properties">
<props>
<prop key="helperDialect">oracle</prop>
<prop key="reasonable">true</prop>
</props>
</property>
</bean>
</array>
</property>
</bean>
<!--4. 创建包扫描器,指定扫描的dao接口所在包-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.sunny.dao"></property>
</bean>
<!--5. Spring提供的事务管理器-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--6. 开启声明式事务注解支持-->
<tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>
<!--7. 加载其他的配置文件-->
<import resource="classpath:spring-security.xml"/>
</beans>
2. 订单分页
- Oracle中创建订单表的SQL语句:-- 订单表
CREATE TABLE orders(
id int PRIMARY KEY,
orderNum VARCHAR2(20) NOT NULL UNIQUE,
orderTime date,
peopleCount INT,
orderDesc VARCHAR2(500),
payType INT,
orderStatus INT,
productId int,
FOREIGN KEY (productId) REFERENCES product(id)
);
- 实体类Order
2.1 dao
使用PageHelper分页,dao只需要查询全部,不需要手动写分页SQL。
dao查询方法已经有了:
- -- product与Orders一对一关系查询
select o.id oid,o.ordernum,o.ordertime,o.peoplecount,o.orderdesc,
o.paytype,o.orderstatus,o.productid,p.* from orders o inner join product p on o.productid=p.id;
/**
* 使用PageHelper分页,dao只需要查询全部,不需要手动写SQL。
*/
public interface IOrderDao {
/**
* 查询全部
*/
@Select("select o.id oid,o.ordernum,o.ordertime,o.peoplecount,o.orderdesc,o.paytype,o.orderstatus\n" +
",p.* from orders o inner join product p on o.productid=p.id")
@Results({
// 订单信息,只要指定id列与属性的映射关系,其他列会自动封装(属性与列一致)
@Result(property = "id", column = "oid"),
// 产品信息
@Result(property = "product.id", column = "id"),
@Result(property = "product.productNum", column = "productNum"),
@Result(property = "product.productName", column = "productName"),
@Result(property = "product.cityName", column = "cityName"),
@Result(property = "product.departureTime", column = "departureTime"),
@Result(property = "product.productPrice", column = "productPrice"),
@Result(property = "product.productDesc", column = "productDesc"),
@Result(property = "product.productStatus", column = "productStatus"),
})
List<Order> findAll();
}
2.2 service接口、实现
2.2.1 service接口
public interface IOrderService {
/**
* 查询全部
*/
List<Order> findAll();
/**
* 分页查询
* @param pageNum 当然页
* @param pageSize 页大小
* @return 返回PageHelper提供的封装分页参数的PageInfo对象
*/
PageInfo<Order> findByPage(int pageNum, int pageSize);
}
2.2.2 service实现
@Service
@Transactional
public class OrderServiceImpl implements IOrderService {
// 注入dao
@Autowired
private IOrderDao orderDao;
@Override
public List<Order> findAll() {
return orderDao.findAll();
}
@Override
public PageInfo<Order> findByPage(int pageNum, int pageSize) {
// 分页查询(紧跟在其后的第一条查询sql将会被分页)
PageHelper.startPage(pageNum,pageSize);
// 查询全部
List<Order> list = orderDao.findAll();
// 返回结果
PageInfo<Order> pageInfo = new PageInfo<>(list);
return pageInfo;
}
}
2.3 Controller
@Controller
@RequestMapping("/order")
public class OrderController {
// 注入service
@Autowired
private IOrderService orderService;
/**
* 1.查询全部
*/
@RequestMapping("/findAll")
public ModelAndView findAll() {
//1.1 调用service
List<Order> list = orderService.findAll();
//1.2 返回结果
ModelAndView mv = new ModelAndView();
mv.setViewName("order-list");
mv.addObject("list", list);
return mv;
}
@RequestMapping("/findByPage")
public ModelAndView findByPage(
@RequestParam(defaultValue = "1") int pageNum,
@RequestParam(defaultValue = "2") int pageSize) {
//1.1 调用service
PageInfo<Order> pageInfo = orderService.findByPage(pageNum, pageSize);
//1.2 返回结果
ModelAndView mv = new ModelAndView();
mv.setViewName("order-list");
mv.addObject("pageInfo", pageInfo);
return mv;
}
}
2.4 简单测试
--- (1)批量插入Order表数据脚本
declare
i integer;
begin
i := 1;
loop
INSERT INTO orders VALUES(i,'O9'||i,
to_date('2018-09-09 20:10','yyyy-MM-dd HH24:mi'),3,'无',1,1,1);
/* 参数递增 */
i := i + 1;
/* 停止条件 */
exit when i > 50;
end loop;
commit;
end;-- 查看
select * from orders;
2.5 order-list.jsp
2.4.1 分页达到的效果
页面效果:只显示当前页前后5页的页码。最多显示的页码共11个。
2.6 分页设计
- 因为每一个模块的列表都要求有分页,所以把分页的公共代码抽取出去放到page.jsp
- 其它模块如果要分页,直接引入page.jsp页面即可。
2.7 page.jsp
分页条公用页面
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<div class="box-footer">
<div class="pull-left">
<div class="form-group form-inline">
总共${pageInfo.pages}页,共${pageInfo.total} 条数据。
每页
<select class="form-control" onchange="gotoPage(1,this.value)">
<c:forEach var="temp" begin="1" end="10">
<option ${pageInfo.pageSize==temp?'selected':''} value="${temp}">${temp}</option>
</c:forEach>
</select>
条
</div>
</div>
<div class="box-tools pull-right">
<ul class="pagination">
<li><a href="javascript:gotoPage(1,'${pageInfo.pageSize}')" aria-label="Previous">首页</a></li>
<li><a href="javascript:gotoPage('${pageInfo.prePage}','${pageInfo.pageSize}')">上一页</a></li>
<%--处理页码--%>
<c:forEach var="num"
begin="${(pageInfo.pageNum-5>0?(pageInfo.pageNum-5):1)}"
end="${(pageInfo.pageNum+5<=pageInfo.pages)?(pageInfo.pageNum+5):pageInfo.pages}">
<li>
<a href="javascript:gotoPage('${num}','${pageInfo.pageSize}')">
<%--判断:主要是为了给当前页显示红色--%>
<c:choose>
<c:when test="${num==pageInfo.pageNum}">
<span style="color: red">${num}</span>
</c:when>
<c:otherwise>${num}</c:otherwise>
</c:choose>
</a>
</li>
</c:forEach>
<li><a href="javascript:gotoPage('${pageInfo.nextPage}','${pageInfo.pageSize}')">下一页</a></li>
<li><a href="javascript:gotoPage('${pageInfo.pages}','${pageInfo.pageSize}')" aria-label="Next">尾页</a></li>
</ul>
</div>
</div>
<script type="text/javascript">
//分页的方法
//参数1:当然页
//参数2:页大小
function gotoPage(pageNum,pageSize){
// 给表单隐藏域赋值
$("#pageNum").val(pageNum);
$("#pageSize").val(pageSize);
// 提交表单
// document.forms[0] 获取页面的第一个表单
document.forms[0].submit();
}
</script>
2.8 order-list.jsp页面
如果页面要分页,只需要引入下面代码即可:
2.9 测试