mybatis分页插件PageHelper的使用(ssm整合pagehelper)

mybatis分页插件的使用

1.回去mysql和oracle分页的区别

• mysql分页语句

select * from table_name limit M,N

说明:

(1)mysql的M,N只能数字,不能有括号和运算符,否则报错

(2)M,N表示第M条记录开始检索N条记录

• oracle分页语句

select * from(
		SELECT A.*, ROWNUM RN
		FROM (
		
		SELECT * FROM DEMO
		
		) A
		WHERE ROWNUM <= 
		(#{pageIndex}*#{pageSize})
		)
			RN >=
			((#{pageIndex}-1)*#{pageSize})+1

说明:

其中的select * from demo 就是查询的结果集,这个模板是固定的,直接使用就行

其中pageIndex是当前的页码值,pageSize是每页应该存放的记录数

2.准备测试表

测试前说明

2.1下面的测试用mysql数据库,oracle同理,都是可以的

2.2框架用的是spring+springmvc+mybatis

2.3创建表的语句

-- 建测试的数据库
create database yonghedb charset utf8;
-- 建测试的表
create table demo(
id int unsigned, 
name varchar(50)
);

说明:

摘自阿里开发手册
任何字段如果为非负数，必须是 unsigned。

2.4模拟向表中插入95条数据

• dao层方法

Integer insertDemo(Demo demo);

• mapper.xml

<insert id="insertDemo"
		parameterType="com.fcmap.ssm.domain.Demo" useGeneratedKeys="true"
		keyProperty="id">
		insert into DEMO (id,name) values (#{id},#{name});
	</insert>

• 测试方法添加95条数据

@Test
	public void testinsert95demoData() {
	
		for(int i = 0 ; i < 95 ; i++) {
			demoDao.insertDemoReturnPrimaryKey1(new Demo(null,"demo的name"+i+1));
		}
	}

3.ssm整合pagehelper插件

3.1引入pagehelper依赖

<!-- pagehelper插件 -->
	 <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>4.1.4</version>
    </dependency>

说明:

由此看出,pagehelper是github上面的一个开源项目

3.2在mybatis配置文件中配置pagehelper的一些参数,直接cv就可以,下面是要配置的内容

• 我的mybatis配置文件名: mybatis-config.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>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING" />
        <!-- 解决插入部分字段为空的问题  -->
        <setting name="jdbcTypeForNull" value="NULL"/>
        <setting name="callSettersOnNulls" value="true"/>
    </settings>
     <!--  <typeAliases>
			<typeAlias type="com.queen.mybatis.bean.User"/>
			package：为某个包下所有类批量起别名
			     name：指定包名(为当前包以及下面所有的后代包的每一个类都起一个默认别名,默认小写)
			
				<package name="com.fcmap.ssm.model"/>
		</typeAliases> -->
		<plugins>
		<plugin interceptor="com.github.pagehelper.PageHelper">
			<property name="dialect" value="mysql" />
			<property name="offsetAsPageNum" value="false" />
			<property name="rowBoundsWithCount" value="false" />
			<property name="pageSizeZero" value="true" />
			<property name="reasonable" value="false" />
			<property name="supportMethodsArguments" value="false" />
			<property name="returnPageInfo" value="none" />
		</plugin>
	</plugins>
</configuration>

• 在spring配置文件中引入mybaits-config.xml

 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
        <property name="dataSource" ref="dataSource" />  
        <!-- 读取mybatis配置 -->
         <property name="configLocation" value="classpath:mybatis-config.xml"></property>  
        <!-- 自动扫描mapping.xml文件 -->  
        <property name="mapperLocations" value="classpath:com/fcmap/ssm/mapper/*.xml"></property>  
    </bean>  

• pagehelper的核心类pageInfo

public class PageInfo<T> implements Serializable {
    private static final long serialVersionUID = 1L;
    //当前页
    private int pageNum;
    //每页的数量
    private int pageSize;
    //当前页的数量
    private int size;
    //排序
    private String orderBy;

    //由于startRow和endRow不常用，这里说个具体的用法
    //可以在页面中"显示startRow到endRow 共size条数据"

    //当前页面第一个元素在数据库中的行号
    private int startRow;
    //当前页面最后一个元素在数据库中的行号
    private int endRow;
    //总记录数
    private long total;
    //总页数
    private int pages;
    //结果集
    private List<T> list;

    //第一页
    private int firstPage;
    //前一页
    private int prePage;
    //下一页
    private int nextPage;
    //最后一页
    private int lastPage;

    //是否为第一页
    private boolean isFirstPage = false;
    //是否为最后一页
    private boolean isLastPage = false;
    //是否有前一页
    private boolean hasPreviousPage = false;
    //是否有下一页
    private boolean hasNextPage = false;
    //导航页码数
    private int navigatePages;
    //所有导航页号
    private int[] navigatepageNums;
    ....下面的方法省略,自己可以看

参照这个进行分页信息的获取

4.进行分页的测试

4.1 dao层的代码

List<Demo> queryDemoByNameList(@Param("name") String name);

4.2 mapper.xml的sql书写

<select id="queryDemoByNameList" resultType="com.fcmap.ssm.domain.Demo" parameterType="java.lang.String">
		select * from demo
		<where>
			<if test="name!=null and name!='' and name!='all'">
				name like concat('%',#{name},'%')
			</if>
		</where>
	</select>

说明:

如果name为null 或者"“或者"all”,那么就是查询demo表的所有

4.3 service层的代码

• service接口

public interface DemoService {
PageInfo<Demo> selectByPageByName(String name,int pageIndex, int pageSize);
}

• service的实现类

@Service
public class DemoServiceImpl implements DemoService {
	@Autowired
	DemoDao demoDao;
	@Override
	public PageInfo<Demo> selectByPageByName(String name, int pageIndex, int pageSize) {
		PageHelper.startPage(pageIndex,pageSize);
		List<Demo> demoList = demoDao.queryDemoByNameList(name);
		PageInfo<Demo> pageInfo = new PageInfo<Demo>(demoList);
		return pageInfo;
	}

}

4.4 controller测试

@Controller
@RequestMapping("/page")
public class DemoController {

	@Autowired
	DemoService demoService;

//测试的url:http://localhost:8080/ssm_mysql/page/demo/query
	@RequestMapping("/demo/query")
	@ResponseBody
	public Msg queryDemoByPageByRequestParam(
			@RequestParam(value = "name", required = false, defaultValue = "all") String name,
			@RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize,
			@RequestParam(value = "pageIndex", defaultValue = "1") Integer pageIndex) {
		return Msg.success().add("page信息", demoService.queryDmeoRecoredsByNamePage(name, pageSize, pageIndex));
	}

}

页面返回结果

1. 测试的url:http://localhost:8080/ssm_mysql/page/demo/query

{
code: 100,
msg: "执行成功！",
data: {
page信息: {
pageNum: 1,
pageSize: 10,
size: 10,
orderBy: null,
startRow: 1,
endRow: 10,
total: 95,
pages: 10,
list: [
{
id: 1,
name: "demo的name1"
},
{
id: 2,
name: "demo的name2"
},
{
id: 3,
name: "demo的name3"
},
{
id: 4,
name: "demo的name4"
},
{
id: 5,
name: "demo的name5"
},
{
id: 6,
name: "demo的name6"
},
{
id: 7,
name: "demo的name7"
},
{
id: 8,
name: "demo的name8"
},
{
id: 9,
name: "demo的name9"
},
{
id: 10,
name: "demo的name10"
}
],
firstPage: 1,
prePage: 0,
nextPage: 2,
lastPage: 8,
isFirstPage: true,
isLastPage: false,
hasPreviousPage: false,
hasNextPage: true,
navigatePages: 8,
navigatepageNums: [
1,
2,
3,
4,
5,
6,
7,
8
]
}
}
}

2. 测试的url:http://localhost:8080/ssm_mysql/page/demo/query?pageSize=10&pageIndex=10

{
code: 100,
msg: "执行成功！",
data: {
page信息: {
pageNum: 10,
pageSize: 10,
size: 5,
orderBy: null,
startRow: 91,
endRow: 95,
total: 95,
pages: 10,
list: [
{
id: 91,
name: "demo的name91"
},
{
id: 92,
name: "demo的name92"
},
{
id: 93,
name: "demo的name93"
},
{
id: 94,
name: "demo的name94"
},
{
id: 95,
name: "demo的name95"
}
],
firstPage: 3,
prePage: 9,
nextPage: 0,
lastPage: 10,
isFirstPage: false,
isLastPage: true,
hasPreviousPage: true,
hasNextPage: false,
navigatePages: 8,
navigatepageNums: [
3,
4,
5,
6,
7,
8,
9,
10
]
}
}
}

到此ssm整合pagehelper成功