引入分页插件:
<!-- 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.6</version>
</dependency>
在mybatis.xml主配置中配置插件
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- config params as the following -->
<property name="helperDialect" value="mysql" />
</plugin>
</plugins>
如果是spring集成mybatis 在spring配置文件中配置插件:
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- other configuration -->
<property name="plugins">
<array>
<bean class="com.github.pagehelper.PageInterceptor">
<property name="properties">
<!-- config params as the following -->
<value> helperDialect=mysql</value>
</property>
</bean>
<array>
<bean>
在实际项目中应用插件:
@Test
void findPersonByNameByLike() {
PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
//pageNum 显示第几页 pageSize 每页显示数量
Page<Person> page = PageHelper.startPage(2,2).doSelectPage(
()->personMapper.findPersonByNameByLike("张"));
List<Person> personList = page.getResult();
System.out.println(page.getTotal());
System.out.println(page.getPageSize());
personList.forEach(person -> {
System.out.println(person);
});
}
查看下它的sql执行,后面会讲解分页插件原理,已经拦截器等:
2021-02-04 13:15:11 [ main:1134 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:101) Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@223aa2f7]
2021-02-04 13:15:11 [ main:1142 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Preparing: SELECT count(0) FROM t_person p, t_idcard idcard WHERE p.card_id = idcard.id AND p.name LIKE '%张%'
2021-02-04 13:15:11 [ main:1188 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters:
2021-02-04 13:15:11 [ main:1214 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <== Total: 1
2021-02-04 13:15:11 [ main:1219 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Preparing: SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code from t_person p,t_idcard idcard where p.card_id=idcard.id and p.name like '%张%' LIMIT ?, ?
2021-02-04 13:15:11 [ main:1220 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 2(Integer), 2(Integer)
2021-02-04 13:15:11 [ main:1225 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <== Total: 2
Mybatis采用的是逻辑分页,而非物理分页 要实现物理分页,就需要对String sql进行拦截并增强,Mybatis通过BoundSql对象存储String sql,而BoundSql则由StatementHandler对象获取。
public interface StatementHandler {
<E> List<E> query(Statement statement, ResultHandler resultHandler)
throws SQLException{
String sql = getBoundSql();
分页语句: sql+"limit 语句"
查询总数语句:"SELECT COUNT(1) "" +sql.substring(from语句之后)
};
BoundSql getBoundSql();
}
public class BoundSql {
public String getSql() {
return sql;
}
}