一:maven引入PageHelper与jsparser
<!--分页pagehelper-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
<!--jsqlparser 是pagehelper的底层依赖-->
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>3.2</version>
</dependency>
二:配置文件配置分页
1、mybatis-config.xml增加Plugin配置
<!--分页配置-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!--设置数据库类型-->
<property name="helperDialect" value="mysql"/>
<!--分页合理化-->
<property name="reasonable" value="true"/>
</plugin>
</plugins>
2、springboot的application.yml配置
# mybatis配置
mybatis:
configuration:
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
三:代码使用PageHelper.startPage()自动分页;测试用例
/**
* 分页查询
* @throws Exception
*/
@Test
public void testSelectPage() throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MybatisUtils.openSession();
//startPage方法会自动将下一次查询进行分页
PageHelper.startPage(1,10);
Page<Goods> page = (Page) sqlSession.selectList("goods.selectPage");
System.out.println("总页数:"+ page.getPages());
System.out.println("总记录数:"+ page.getTotal());
System.out.println("开始行号:"+ page.getStartRow());
System.out.println("结束行号:"+ page.getEndRow());
System.out.println("当前页码:"+ page.getPageNum());
//方式1:返回当前分页对象
List<Goods> data = page.getResult();
for (Goods g : data){
System.out.println(g.getTitle());
}
//方式2:获取分页结果
PageInfo pageInfo = new PageInfo(page);
if (pageInfo.getList().size()>0) {
System.out.println(pageInfo.getEndRow());
} else {
System.out.println("null");
}
}catch (Exception e){
throw e;
}finally {
MybatisUtils.closeSession(sqlSession);
}
}
四:goods.xml
<!--分页查询-->
<select id="selectPage" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods where current_price < 1000
</select>
五:goods实体类
package com.imooc.mybatis.entity;
import java.util.List;
/**
* 商品类
*/
public class Goods {
//商品编号
private Integer goodsId;
//标题
private String title;
//子标题
private String subTitle;
//原始价格
private Float originalCost;
//当前价格
private Float currentPrice;
//折扣率
private Float discount;
//是否包邮
private Integer isFreeDelivery;
//分类编号
private Integer categoryId;
//一对多,一个goods对象下有多个GoodsDetail对象
private List<GoodsDetail> goodsDetails;
public Integer getGoodsId() {
return goodsId;
}
public void setGoodsId(Integer goodsId) {
this.goodsId = goodsId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getSubTitle() {
return subTitle;
}
public void setSubTitle(String subTitle) {
this.subTitle = subTitle;
}
public Float getOriginalCost() {
return originalCost;
}
public void setOriginalCost(Float originalCost) {
this.originalCost = originalCost;
}
public Float getCurrentPrice() {
return currentPrice;
}
public void setCurrentPrice(Float currentPrice) {
this.currentPrice = currentPrice;
}
public Float getDiscount() {
return discount;
}
public void setDiscount(Float discount) {
this.discount = discount;
}
public Integer getIsFreeDelivery() {
return isFreeDelivery;
}
public void setIsFreeDelivery(Integer isFreeDelivery) {
this.isFreeDelivery = isFreeDelivery;
}
public Integer getCategoryId() {
return categoryId;
}
public void setCategoryId(Integer categoryId) {
this.categoryId = categoryId;
}
public List<GoodsDetail> getGoodsDetails() {
return goodsDetails;
}
public void setGoodsDetails(List<GoodsDetail> goodsDetails) {
this.goodsDetails = goodsDetails;
}
}
七:MybatisUtils工具类,创建全局唯一的SqlSessionFactory对象
package com.imooc.mybatis.utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.Reader;
/**
* MybatisUtils工具类,创建全局唯一的SqlSessionFactory对象
* @author lihaisong
* @version 1.0
* @date 2021/5/14 12:24
*/
public class MybatisUtils {
//利用static关键字,属于类不属于对象,且全局唯一
private static SqlSessionFactory sqlSessionFactory = null;
//利用静态块在初始化类时实例化sqlSessionFactory
static {
Reader reader = null;
try {
reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
//抛出初始化时出现的异常
throw new ExceptionInInitializerError(e);
}
}
/**
* openSession创建一个新的SqlSession对象
* @return
*/
public static SqlSession openSession(){
return sqlSessionFactory.openSession();
}
/**
* 是否一个有效的SqlSession对象
* @param sqlSession
*/
public static void closeSession(SqlSession sqlSession){
if (sqlSession != null){
sqlSession.close();
}
}
}
八:sql
/*
Navicat MySQL Data Transfer
Source Server : xampp
Source Server Version : 100140
Source Host : localhost:3306
Source Database : babytun
Target Server Type : MYSQL
Target Server Version : 100140
File Encoding : 65001
Date: 2021-05-15 15:15:36
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_category
-- ----------------------------
DROP TABLE IF EXISTS `t_category`;
CREATE TABLE `t_category` (
`category_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '分类id',
`category_name` varchar(64) NOT NULL COMMENT '分类名称',
`parent_id` int(10) DEFAULT NULL COMMENT '上级分类',
`category_level` int(2) DEFAULT NULL COMMENT '分类的级别',
`category_order` int(6) DEFAULT '0' COMMENT '前端顺序 数字大的优先显示',
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COMMENT='分类表';
-- ----------------------------
-- Records of t_category
-- ----------------------------
INSERT INTO `t_category` VALUES ('1', '母婴专区', null, '1', '100');
INSERT INTO `t_category` VALUES ('2', '美妆护肤', null, '1', '200');
INSERT INTO `t_category` VALUES ('3', '家装生活', null, '1', '300');
INSERT INTO `t_category` VALUES ('4', '食品营养', null, '1', '400');
INSERT INTO `t_category` VALUES ('5', '面膜', '2', '2', '0');
INSERT INTO `t_category` VALUES ('6', '面部护理', '2', '2', '0');
INSERT INTO `t_category` VALUES ('7', '孕产护肤', '2', '2', '0');
INSERT INTO `t_category` VALUES ('8', '纸尿裤', '1', '2', '0');
INSERT INTO `t_category` VALUES ('9', '彩妆', '2', '2', '0');
INSERT INTO `t_category` VALUES ('10', '身材护理', '2', '2', '0');
-- ----------------------------
-- Table structure for t_goods
-- ----------------------------
DROP TABLE IF EXISTS `t_goods`;
CREATE TABLE `t_goods` (
`goods_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '商品id',
`title` varchar(128) NOT NULL COMMENT '标题',
`sub_title` varchar(128) NOT NULL COMMENT '子标题',
`original_cost` double(9,2) NOT NULL COMMENT '原始价格',
`current_price` double(9,2) NOT NULL COMMENT '当前价格',
`discount` double(5,2) DEFAULT NULL COMMENT '折扣率',
`is_free_delivery` int(1) DEFAULT '0' COMMENT '是否包邮 1包邮 0不包邮',
`category_id` int(10) NOT NULL COMMENT '分类编号',
PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COMMENT='商品表';
-- ----------------------------
-- Records of t_goods
-- ----------------------------
INSERT INTO `t_goods` VALUES ('16', '数学15', '这个是⾯试专题概要', '9900.00', '500.00', '0.44', '0', '1');
INSERT INTO `t_goods` VALUES ('17', '数学16', '这个是⾯试专题概要', '9900.00', '632.00', '0.44', '0', '2');
INSERT INTO `t_goods` VALUES ('18', '数学17', '这个是⾯试专题概要', '635.00', '600.00', '0.44', '0', '3');
INSERT INTO `t_goods` VALUES ('20', '测试--xml-插入数据2', '这个是⾯试专题概要', '635.00', '555.00', '0.44', '0', '4');
INSERT INTO `t_goods` VALUES ('21', '测试--xml-插入数据3', '这个是⾯试专题概要', '635.00', '588.00', '0.44', '0', '3');
INSERT INTO `t_goods` VALUES ('22', '测试--xml-插入数据4', '这个是⾯试专题概要', '635.00', '566.00', '0.44', '0', '2');
INSERT INTO `t_goods` VALUES ('23', '测试--xml-插入数据5', '这个是⾯试专题概要', '635.00', '577.00', '0.44', '0', '2');
INSERT INTO `t_goods` VALUES ('24', '小滴课堂1', '不偶像', '400.00', '356.00', '0.44', '0', '1');
INSERT INTO `t_goods` VALUES ('25', '小滴课堂23', '不偶像2', '700.00', '666.00', '0.44', '0', '3');
INSERT INTO `t_goods` VALUES ('26', '小滴课堂155', '不偶像', '400.00', '388.00', '0.44', '0', '4');
INSERT INTO `t_goods` VALUES ('27', '小滴课堂26', '不偶像2', '700.00', '635.00', '0.44', '0', '4');
INSERT INTO `t_goods` VALUES ('28', '测试商品', '测试子标题', '200.00', '100.00', '0.50', '1', '3');
INSERT INTO `t_goods` VALUES ('30', '测试商品', '测试子标题', '200.00', '100.00', '0.50', '1', '3');
INSERT INTO `t_goods` VALUES ('31', '测试商品', '测试子标题', '200.00', '100.00', '0.50', '1', '3');
-- ----------------------------
-- Table structure for t_goods_detail
-- ----------------------------
DROP TABLE IF EXISTS `t_goods_detail`;
CREATE TABLE `t_goods_detail` (
`gd_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '详情id',
`goods_id` int(10) NOT NULL COMMENT '商品id',
`gd_pic_url` varchar(128) NOT NULL COMMENT '商品图片',
`gd_order` int(6) NOT NULL COMMENT '排序',
PRIMARY KEY (`gd_id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COMMENT='商品详情表';
-- ----------------------------
-- Records of t_goods_detail
-- ----------------------------
INSERT INTO `t_goods_detail` VALUES ('1', '16', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '1');
INSERT INTO `t_goods_detail` VALUES ('2', '16', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '1');
INSERT INTO `t_goods_detail` VALUES ('3', '16', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '1');
INSERT INTO `t_goods_detail` VALUES ('4', '16', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '1');
INSERT INTO `t_goods_detail` VALUES ('5', '16', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '2');
INSERT INTO `t_goods_detail` VALUES ('6', '17', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '2');
INSERT INTO `t_goods_detail` VALUES ('7', '17', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '2');
INSERT INTO `t_goods_detail` VALUES ('8', '26', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '2');
INSERT INTO `t_goods_detail` VALUES ('9', '26', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '2');
INSERT INTO `t_goods_detail` VALUES ('10', '26', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '2');