1 实现控制台版本分页
一:通过发生两条SQL:
1):查询符合条件的当前页面的结果集
SELECT * FROM table_name WHERE 条件 ... limit ?,?
第一个?:beginIndex:当前页开始索引数:(currentPage - 1)*pageSize.
第二个?:pageSize:每页显示条数
2):查询符合条件的结果总数:
SELECT COUNT(*) FROM table_name WHERE 条件 ...
二:计算三个分页值:
总页数:Integer totalPage = totalCount % pageSize == 0 ? totalCount / pageSize
: totalCount/pageSize + 1;
上一页:Integer prePage = currentPage - 1 >= 1? currentPage - 1 : 1;
下一页:Integer nextPage = currentPage + 1 <= totalCount? currentPage + 1 : totalPage ;
测试实现类:
@Test
public void testPage() throws Exception {
SqlSession session = MyBatisUtil.INSTANCE.openSession();
//前两个是用户自己传入的数据!
Integer currentPage = 1;//当前页
Integer pageSize = 5;//每页条数
//查询结果总数
Integer totalCount = session.selectOne("cn.itsource.shopping.mapper.ProductMapper.getTotalCount");
Integer beginPage = 1;//首页
//总页数/末页
Integer totalPage = totalCount % pageSize == 0 ? totalCount / pageSize
: totalCount/pageSize + 1;
//上页
Integer prePage = currentPage - 1 >= 1? currentPage - 1 : 1;
//下页
Integer nextPage = currentPage + 1 <= totalCount? currentPage + 1 : totalPage ;
//===========================
//查询结果集
//===========================
Map<String,Object> limitMap = new HashMap<>();//封装limit?,?:的信息
limitMap.put("beginIndex",(currentPage-1)*pageSize);
limitMap.put("pageSize", pageSize);
List<Object> selectList = session.selectList("cn.itsource.shopping.mapper.ProductMapper.getPageResult", limitMap);
for (Object p : selectList) {
System.out.println(p);
}
//===========================
//打印分页条信息
System.out.print("首页"+beginPage);
System.out.print("上页"+prePage);
System.out.print("下页"+nextPage);
System.out.print("末页"+totalPage);
System.out.println("当前页"+currentPage+"/"+totalPage+"页");
System.out.println("一共"+totalCount+"条记录");
session.close();
}
ProductMapper.xml映射文件
两个查询的SQL
<?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">
<!-- 映射Product对象和对应表关系的,操作该表/对象的SQL全部都写在这里 -->
<mapper namespace="cn.itsource.shopping.mapper.ProductMapper">
<!-- 查询结果总数 -->
<select id="getTotalCount" resultType="int">
SELECT COUNT(*) FROM product
</select>
<!-- 查询当前页 的结果集 -->
<select id="getPageResult" parameterType="map" resultType="Product">
SELECT * FROM product limit #{beginIndex},#{pageSize}
</select>
</mapper>
2 使用Map实现后台分页设计
操作步骤:
①:封装分页结果对象PageList
②:在IProductDAO中定义,分页查询的方法;
PageList query (Integer currentPage, Integer pageSize);
③:实现ProductDAOImpl中的query方法,并测试。
public class PageList {
private List ListData;//结果集数据:SQL查询出来
private Integer totalCount;//结果总数:SQL查询出来
private Integer currentPage = 1;//当前页:前台用户传入的
private Integer pageSize = 5;//每页多少条:前台用户传入的
private Integer beginPage = 1;//首页
//private Integer prePage; //上一页 计算出来的
//private Integer nextPage; //下一页 计算出来的
//private Integer totalPage;//总页数/末页:计算出来的
//计算上一页
public Integer getPrePage() {
return currentPage - 1 >= 1? currentPage -1 : 1;
}
//计算下一页
public Integer getNextPage() {
return currentPage + 1 <= getTotalPage()? currentPage+1:getTotalPage();
}
//计算总页数
public Integer getTotalPage() {
return totalCount % pageSize == 0? totalCount / pageSize : totalCount/ pageSize +1;
}
public PageList(Integer currentPage,Integer pageSize,List ListData,Integer totalCount){
this.currentPage=currentPage;
this.pageSize=pageSize;
this.ListData=ListData;
this.totalCount=totalCount;
}
public List getListData() {
return ListData;
}
public void setListData(List listData) {
ListData = listData;
}
public Integer getTotalCount() {
return totalCount;
}
public void setTotalCount(Integer totalCount) {
this.totalCount = totalCount;
}
public Integer getCurrentPage() {
return currentPage;
}
public void setCurrentPage(Integer currentPage) {
this.currentPage = currentPage;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getBeginPage() {
return beginPage;
}
}
Dao实现接口
public interface IProductDAO {
/**
* 分页查询
* @param currentPage 当前页
* @param pageSize 每页显示条数
*/
PageList query (Integer currentPage,Integer pageSize);
}
Dao实现类
public class ProductDAOImpl implements IProductDAO {
public PageList query(Integer currentPage, Integer pageSize) {
SqlSession session = MyBatisUtil.INSTANCE.openSession();
try {
//查询结果总数
Integer totalCount = session.selectOne("cn.itsource.shopping.mapper.ProductMapper.getTotalCount");
//查询结果集
Map<String,Object> limitMap = new HashMap<>();
limitMap.put("beginIndex", (currentPage-1)*pageSize);
limitMap.put("pageSize", pageSize);
List<Object> listData = session.selectList("cn.itsource.shopping.mapper.ProductMapper.getPageResult", limitMap);
return new PageList(currentPage,pageSize,listData,totalCount);
} finally {
session.close();
}
}
}
测试类
@Test
public void testPageQuery(){
Integer currentPage = 1;
Integer pageSize = 5;
PageList pList = dao.query(currentPage, pageSize);
for (Object p : pList.getListData()) {
System.out.println(p);
}
}
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">
<!-- 映射Product对象和对应表关系的,操作该表/对象的SQL全部都写在这里 -->
<mapper namespace="cn.itsource.shopping.mapper.ProductMapper">
<!-- 查询结果总数 -->
<select id="getTotalCount" resultType="int">
SELECT COUNT(*) FROM product
</select>
<!-- 查询当前页 的结果集 -->
<select id="getPageResult" parameterType="map" resultType="Product">
SELECT * FROM product limit #{beginIndex},#{pageSize}
</select>
</mapper>
3 封装分页信息到Query对象并与高级查询整合
封装分页信息到Query对象操作步骤:
1.让查询对象封装分页信息
把currentPage和pageSize作为ProductQueryObject的字段并提供getter/setter.但是分页是所有查询对象共用的行为。应给把currentPage和pageSize抽离到BaseQueryObject中。并暴露一个用于MySQL分页的开始索引:beginIndex属性。
2.在IproductDAO接口中,定义高级查询+分页的操作方法:
PageList query (ProductQueryObject qo)
3.在ProductDAOImpl中实现该方法。
高级查询+分页的操作步骤:
public PageList query(ProductQueryObject qo)方法,可以同时完成高级查询和分页操作。
参数:qo,qo对象封装了所有的高级查询参数和分页信息。
操作步骤:
1.修改映射文件,实质支持带条件的查询(高级查询)添加了$(querySql) 跟 prameterType
2.再修改ProductDAOImpl的query方法,使之支持查询查询符合条件的结果总数:查询结果总数的totalCount的方法里面添加了参数qo对象。
排序的时候用$以后一般都用#,$没有防止SQL注入功能,不安全!
DAO实现接口
public interface IProductDAO {
/**
* 高级查询+分页的合体
* @param qo 包括了高级查询信息和分页信息
* @return
*/
PageList query(ProductQueryObject qo);
}
DAO的实现类
public class ProductDAOImpl implements IProductDAO {
public PageList query(ProductQueryObject qo) {
SqlSession session = MyBatisUtil.INSTANCE.openSession();
try {
//查询结果总数
Integer totalCount = session.selectOne("cn.itsource.shopping.mapper.ProductMapper.getTotalCount",qo);
//查询结果集
List listData = session.selectList("cn.itsource.shopping.mapper.ProductMapper.getResultList", qo);
return new PageList(qo.getCurrentPage(),qo.getPageSize(),listData,totalCount);
} finally {
session.close();
}
}
}
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">
<!-- 映射Product对象和对应表关系的,操作该表/对象的SQL全部都写在这里 -->
<mapper namespace="cn.itsource.shopping.mapper.ProductMapper">
<!-- 查询结果总数 -->
<select id="getTotalCount" resultType="int"
parameterType="cn.itsource.shopping.query.ProductQueryObject">
SELECT COUNT(*) FROM product ${querySql}
</select>
<!-- 查询当前页 的结果集 -->
<select id="getResultList" resultType="Product"
parameterType="cn.itsource.shopping.query.ProductQueryObject" >
SELECT * FROM product ${querySql} limit #{beginIndex},#{pageSize}
</select>
</mapper>
测试类
//分页+高级查询的方法测试
@Test
public void testPageList() throws Exception {
ProductQueryObject qo = new ProductQueryObject();
//封装查询和分页信息
qo.setCurrentPage(1);//设置当前页面
qo.setPageSize(5);//设置每页显示条数
qo.setProductName("iphone8s");//用于设置查询条件where name like 'iphone8s'
PageList pList = dao.query(qo);
for (Object p : pList.getListData()) {
System.out.println(p);
}
}
ProductQueryObject的父类
public class BaseQueryObject {
private List<String> conditions = new ArrayList<>();
private Integer currentPage = 1;//当前页:前台用户传入的
private Integer pageSize = 5;//每页多少条:前台用户传入的
//提供一个属性:beginIndex:返回当前页的开始索引
public Integer getBeginIndex(){
return (currentPage-1)*pageSize;
}
//拼接查询条件的SQL
public String getQuerySql() {
//拼接SQL
StringBuilder sql = new StringBuilder();
this.customizedQuery();
for (int i = 0; i < conditions.size(); i++) {
if (i == 0) {
sql.append(" WHERE ");
} else {
sql.append(" AND ");
}
sql.append(conditions.get(i));
}
return sql.toString();
}
/**
* 专门暴漏给子类,用于编写自身的查询条件
*/
protected void customizedQuery(){
}
/**
* 专门暴漏给子类一个方法,用于添加查询条件
*/
protected void addQuery(String condition){
conditions.add(condition);
}
public Integer getCurrentPage() {
return currentPage;
}
public void setCurrentPage(Integer currentPage) {
this.currentPage = currentPage;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
}
下面的是没有改动的代码:
ProductQueryObject对象类
//商品的高级查询对象,封装了商品高级查询表单的数据。
public class ProductQueryObject extends BaseQueryObject{
//抽离出的自身的订制查询
public void customizedQuery() {
//若输入了商品名称
if (StringUtil.hasLength(productName)) {//名字占位符
super.addQuery(" productName LIKE CONCAT('%',#{productName},'%')");
}
//最低零售价
if (minSalePrice != null) {
super.addQuery(" salePrice >= #{minSalePrice}");
}
//最高零售价
if (maxSalePrice != null) {
super.addQuery(" salePrice <= #{maxSalePrice}");
}
}
private String productName;//商品名称
private BigDecimal minSalePrice;//最低商品零售价
private BigDecimal maxSalePrice;//最高商品零售价
public String getProductName() {
return productName;
}
public BigDecimal getMinSalePrice() {
return minSalePrice;
}
public BigDecimal getMaxSalePrice() {
return maxSalePrice;
}
public void setProductName(String productName) {
this.productName = productName;
}
public void setMinSalePrice(BigDecimal minSalePrice) {
this.minSalePrice = minSalePrice;
}
public void setMaxSalePrice(BigDecimal maxSalePrice) {
this.maxSalePrice = maxSalePrice;
}
}
PageList对象
public class PageList {
private List ListData;//结果集数据:SQL查询出来
private Integer totalCount;//结果总数:SQL查询出来
private Integer currentPage = 1;//当前页:前台用户传入的
private Integer pageSize = 5;//每页多少条:前台用户传入的
private Integer beginPage = 1;//首页
//private Integer prePage; //上一页 计算出来的
//private Integer nextPage; //下一页 计算出来的
//private Integer totalPage;//总页数/末页:计算出来的
//计算上一页
public Integer getPrePage() {
return currentPage - 1 >= 1? currentPage -1 : 1;
}
//计算下一页
public Integer getNextPage() {
return currentPage + 1 <= getTotalPage()? currentPage+1:getTotalPage();
}
//计算总页数
public Integer getTotalPage() {
return totalCount % pageSize == 0? totalCount / pageSize : totalCount/ pageSize +1;
}
public PageList(Integer currentPage,Integer pageSize,List ListData,Integer totalCount){
this.currentPage=currentPage;
this.pageSize=pageSize;
this.ListData=ListData;
this.totalCount=totalCount;
}
public List getListData() {
return ListData;
}
public void setListData(List listData) {
ListData = listData;
}
public Integer getTotalCount() {
return totalCount;
}
public void setTotalCount(Integer totalCount) {
this.totalCount = totalCount;
}
public Integer getCurrentPage() {
return currentPage;
}
public void setCurrentPage(Integer currentPage) {
this.currentPage = currentPage;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getBeginPage() {
return beginPage;
}
}
Product对象
public class Product {
private Long id;
private String productName;
private String brand;
private String supplier;
private BigDecimal salePrice;
private BigDecimal costPrice;
private Double cutoff;
private Long dir_id;//分类编号
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getProductName() {
return productName;
}
public void setProductName(String productName) {
this.productName = productName;
}
public String getBrand() {
return brand;
}
public void setBrand(String brand) {
this.brand = brand;
}
public String getSupplier() {
return supplier;
}
public void setSupplier(String supplier) {
this.supplier = supplier;
}
public BigDecimal getSalePrice() {
return salePrice;
}
public void setSalePrice(BigDecimal salePrice) {
this.salePrice = salePrice;
}
public BigDecimal getCostPrice() {
return costPrice;
}
public void setCostPrice(BigDecimal costPrice) {
this.costPrice = costPrice;
}
public Double getCutoff() {
return cutoff;
}
public void setCutoff(Double cutoff) {
this.cutoff = cutoff;
}
public Long getDir_id() {
return dir_id;
}
public void setDir_id(Long dir_id) {
this.dir_id = dir_id;
}
public String toString() {
return "Product [id=" + id + ", productName=" + productName + ", brand=" + brand + ", suppliet=" + supplier
+ ", salePrice=" + salePrice + ", costPrice=" + costPrice + ", cutoff=" + cutoff + ", dir_id=" + dir_id
+ "]";
}
}
MyBatisUtil类
public enum MyBatisUtil {
INSTANCE;
private static SqlSessionFactory sessionFactory = null;
static {
try {
sessionFactory = new SqlSessionFactoryBuilder().build(Resources
.getResourceAsStream("MyBatis-config.xml"));
} catch (IOException e) {
e.printStackTrace();
}
}
public SqlSession openSession() {
return sessionFactory.openSession();
}
}
MyBatis的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>
<!-- 引入db.properties文件 -->
<properties resource="db.properties" />
<!-- 为类型起别名 -->
<typeAliases>
<typeAlias type="cn.itsource.shopping.domain.Product" alias="Product" />
</typeAliases>
<!-- 环境配置 -->
<environments default="development">
<!-- 连接数据的基本信息 -->
<environment id="development">
<!-- 事务管理器:JDBC的管理机制 -->
<transactionManager type="JDBC" />
<!-- 配置连接池(数据源) -->
<dataSource type="POOLED">
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${usename}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
</environments>
<!-- 关联映射文件 -->
<mappers>
<mapper resource="cn\itsource\shopping\domain\ProductMapper.xml" />
</mappers>
</configuration>