coding……
但行好事 莫问前程

Mybatis分页查询

分页查询作为数据库交互最常用的几种操作之一,在日常开发中是非常常见的,比如前段请求需要一个分页的列表,往往有两种方式,一是把所有的数据都给到前段,前段分页。另外一种方式是前端通过传分页信息给后端,后端查询时进行分页,并将相应页的数据返给前端。第一种方式如果数据规模比较小的情况下可以使用,如果数据量较大,对内存、网络传输的消耗都是非常大的,所以实际开发中一般很少使用。第二种方式是后端进行分页,后端分页的实现又可以分为逻辑分页和物理分页,逻辑分页就是在进行数据库查询时一次性将数据查出来,然后将相应页的数据挑出返回,物理分页就是通过在查询时就查询相应的页的数据(比如直接在mysql查询语句添加limit)。很明显逻辑分页跟第一种前端分页的方式有着相同的弊端。

之前写了好几篇关于Mybatis的文章了,其实mybatis原生也是支持分页的,但为了与数据库语法解耦,实现的是逻辑分页,首先将所有结果查询出来,然后通过计算offset和limit,只返回部分结果,操作在内存中进行,所以也叫内存分页,Mybatis逻辑分页是通过RowBounds实现的。而物理分页一般是通过为sql添加limit实现的,具体可以通过拦截器在对其后的第一个执行sql进行拦截,并自动拼接上分页的sql语句,也可以直接改造mapper.xml文件添加limit的方式实现。本文会分别介绍一下RowBounds逻辑分页、拦截器物理分页、改造mapper.xml这三种分页方式的使用方法。

1. 逻辑分页——RowBounds

通过RowBounds类可以实现Mybatis逻辑分页,原理是首先将所有结果查询出来,然后通过计算offset和limit,只返回部分结果,操作在内存中进行,所以也叫内存分页。弊端很明显,当数据量比较大的时候,肯定是不行的,所以一般不会去使用RowBounds进行分页查询,这里仅展示一下RowBounds用法。Mybatis Generator原生支持RowBounds查询,生成的Mapper接口中存在一个方法selectByExampleWithRowbounds就是通过RowBounds进行分页查询。

1.1 项目结构

|   pom.xml
|   springboot-08-mybatis-rowbounds.iml
|
+---src
|   +---main
|   |   +---java
|   |   |   \---com
|   |   |       \---zhuoli
|   |   |           \---service
|   |   |               \---springboot
|   |   |                   \---mybatis
|   |   |                       \---rowbounds
|   |   |                           |   SpringBootMybatisRowBoundsApplicationContext.java
|   |   |                           |
|   |   |                           +---controller
|   |   |                           |       UserController.java
|   |   |                           |
|   |   |                           +---repository
|   |   |                           |   +---conf
|   |   |                           |   |       DataSourceConfig.java
|   |   |                           |   |
|   |   |                           |   +---mapper
|   |   |                           |   |       UserMapper.java
|   |   |                           |   |
|   |   |                           |   +---model
|   |   |                           |   |       User.java
|   |   |                           |   |       UserExample.java
|   |   |                           |   |
|   |   |                           |   \---service
|   |   |                           |       |   UserRepository.java
|   |   |                           |       |
|   |   |                           |       \---impl
|   |   |                           |               UserRepositoryImpl.java
|   |   |                           |
|   |   |                           \---service
|   |   |                               |   UserControllerService.java
|   |   |                               |
|   |   |                               \---impl
|   |   |                                       UserControllerServiceImpl.java
|   |   |
|   |   \---resources
|   |       |   application.properties
|   |       |
|   |       +---autogen
|   |       |       generatorConfig_zhuoli.xml
|   |       |
|   |       \---base
|   |           \---com
|   |               \---zhuoli
|   |                   \---service
|   |                       \---springboot
|   |                           \---mybatis
|   |                               \---rowbounds
|   |                                   \---repository
|   |                                       \---mapper
|   |                                               UserMapper.xml
|   |
|   \---test
|       \---java

1.2 pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.zhuoli.service</groupId>
    <artifactId>springboot-08-mybatis-rowbounds</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!-- Spring Boot 启动父依赖 -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.RELEASE</version>
    </parent>

    <build>
        <plugins>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.5</version>
                <!--如果不配置configuration节点,配置文件名字必须为generatorConfig.xml-->
                <configuration>
                    <!--可以自定义generatorConfig文件名-->
                    <configurationFile>src/main/resources/autogen/generatorConfig_zhuoli.xml</configurationFile>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <!-- Exclude Spring Boot's Default Logging -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.2</version>
            <scope>provided</scope>
        </dependency>

    </dependencies>

</project>

1.3 数据源配置

@Configuration
@MapperScan(basePackages = "com.zhuoli.service.springboot.mybatis.rowbounds.repository.mapper", sqlSessionFactoryRef = "sqlSessionFactory")
public class DataSourceConfig {
    @Value("${test.datasource.url}")
    private String url;

    @Value("${test.datasource.username}")
    private String user;

    @Value("${test.datasource.password}")
    private String password;

    @Value("${test.datasource.driverClassName}")
    private String driverClass;

    @Bean(name = "dataSource")
    public DataSource dataSource() {
        PooledDataSource dataSource = new PooledDataSource();
        dataSource.setDriver(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "transactionManager")
    public DataSourceTransactionManager dataSourceTransactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);

        /*设置mapper文件位置*/
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:base/com/zhuoli/service/springboot/mybatis/rowbounds/repository/mapper/*.xml"));

        /*设置打印sql*/
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setLogImpl(StdOutImpl.class);
        sessionFactory.setConfiguration(configuration);

        return sessionFactory.getObject();
    }
}

为了展示RowBounds为逻辑分页,特地设置将sql控制台打印。

1.4 Repository定义

@Repository
@AllArgsConstructor
public class UserRepositoryImpl implements UserRepository {

    private UserMapper userMapper;

    @Override
    public List<User> getUserByRowBounds(String userName, String description, RowBounds rowBounds) {
        UserExample example = new UserExample();
        /*动态sql,userName和description不为null,则作为查询条件查询*/
        UserExample.Criteria criteria = example.createCriteria();
        if (!StringUtils.isNullOrEmpty(userName)) {
            criteria.andUserNameLike("%" + userName + "%");
        }
        if (!StringUtils.isNullOrEmpty(description)) {
            criteria.andDescriptionEqualTo(description);
        }
        return userMapper.selectByExampleWithRowbounds(example, rowBounds);
    }
}

1.5 Service层调用

@Service
@AllArgsConstructor
public class UserControllerServiceImpl implements UserControllerService {
    private UserRepository userRepository;

    @Override
    public List<User> getByRowBounds(String userName, String description, Integer pageNum, Integer pageSize) {
        RowBounds rowBounds = new RowBounds((pageNum - 1) * pageSize, pageSize);
        return userRepository.getUserByRowBounds(userName, description, rowBounds);
    }
}

1.6 控制台信息

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5cdf4b86] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.jdbc.JDBC4Connection@373e86a1] will not be managed by Spring
==>  Preparing: select id, user_name, description, is_deleted from user WHERE ( user_name like ? ) 
==> Parameters: %zhuoli%(String)
<==    Columns: id, user_name, description, is_deleted
<==        Row: 6, zhuoli, zhuoli is a programer, 0
<==        Row: 7, zhuoli1, zhuoli1 is a programer, 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5cdf4b86]

sql查询时并没有添加limit,也验证了之前讲的RowBounds分页原理是首先将所有结果查询出来,然后通过计算offset和limit,只返回部分结果,是一种逻辑分页。至于Mybatis RowBounds分页原理,请查看Mybatis逻辑分页原理解析RowBounds,写的挺明白的。

2. 物理分页——直接为sql添加limit

如果可以在查询时直接在sql中指定limit,name肯定是只查询相应页的数据。所以就有一种直观的现象,比如使用mybatis,如果可以在mapper.xml中添加limit属性,那生成的sql肯定是可以直接查询到相应页的数据的。结合之前使用的Mybatis Generator,可以这样实现:首先在生成的XxxExample中加入两个属性limit和offset,同时加上set和get方法,然后在XxxMapper.xml中在通过selectByExample查询时,添加limit,大概就是这种样子:

/*XxxExample*/
private Integer limit;
private Integer offset;
public void setLimit(Integer limit) {
    this.limit = limit;
}
public Integer getLimit() {
    return limit;
}
public void setOffset(Integer offset) {
    this.offset = offset;
}
public Integer getOffset() {
    return offset;
}

/*XxxMapper.xml*/
<select id="selectByExample" parameterType="com.xxg.bean.XxxExample" resultMap="BaseResultMap">
  ...
  <if test="limit != null">
    <if test="offset != null">
      limit ${offset}, ${limit}
    </if>
    <if test="offset == null">
      limit ${limit}
    </if>
  </if>
</select>

其实手动去加工作量也不大,但是如果表比较多,添加起来还是有一定工作量的。而且加入下次表结构变更,重新通过Mybatis Generator生成的话,这些信息也要重新加入。为了避免这些麻烦,有大神写了一个Mybatis Generator插件MySQLLimitPlugin,可以在Mybatis Generator生成文件的时候自动生成上述信息,本片文章就使用MySQLLimitPlugin插件进行生成。

2.1 pom.xml

项目结构跟RowBounds一致,这里不单独放出来了,首先来看一下pom.xml配置。为了使用MySQLLimitPlugin插件,这里要声明MySQLLimitPlugin仓库地址,并为Mybaits Generator添加MySQLLimitPlugin依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.zhuoli.service</groupId>
    <artifactId>springboot-08-mybatis-limitplugin</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!-- Spring Boot 启动父依赖 -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.RELEASE</version>
    </parent>

    <!--声明MysqlLimitPlugin maven仓库地址-->
    <pluginRepositories>
        <pluginRepository>
            <id>mybatis-generator-limit-plugin-mvn-repo</id>
            <url>https://raw.github.com/wucao/mybatis-generator-limit-plugin/mvn-repo/</url>
        </pluginRepository>
    </pluginRepositories>

    <build>
        <plugins>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.5</version>
                <!--如果不配置configuration节点,配置文件名字必须为generatorConfig.xml-->
                <configuration>
                    <!--可以自定义generatorConfig文件名-->
                    <configurationFile>src/main/resources/autogen/generatorConfig_zhuoli.xml</configurationFile>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>

                <!--为MybatisGenerator添加MySQLLimitPlugin,为生成的Example类添加limit和offset属性,为生成的mapper.xml文件selctByExample添加Limit-->
                <dependencies>
                    <dependency>
                        <groupId>com.xxg</groupId>
                        <artifactId>mybatis-generator-plugin</artifactId>
                        <version>1.0.0</version>
                    </dependency>
                </dependencies>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <!-- Exclude Spring Boot's Default Logging -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.2</version>
            <scope>provided</scope>
        </dependency>

    </dependencies>

2.2 Mybatis Generator配置文件添加MySQLLimitPlugin依赖

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>

    <!--注意:本地需要有mysql-connector-java-5.1.45-bin.jar-->
    <classPathEntry location="D:\\mysql-connector-java-5.1.45-bin.jar"/>
    <context id="DB2Tables" targetRuntime="MyBatis3">

        <!-- 省略 -->

        <plugin type="org.mybatis.generator.plugins.RowBoundsPlugin"/>
        <plugin type="org.mybatis.generator.plugins.SerializablePlugin"/>
        <!--添加MySQLLimitPlugin,为生成的Example类添加limit和offset属性,为生成的mapper.xml文件selctByExample添加Limit-->
        <plugin type="com.xxg.mybatis.plugins.MySQLLimitPlugin"/>

        <!-- 省略 -->

    </context>
</generatorConfiguration>

2.3 Repository定义

数据源定义跟RowBounds数据源定义一致,这里不单独放出来了,直接看一下limit在respository的使用:

@Repository
@AllArgsConstructor
public class UserRepositoryImpl implements UserRepository {

    private UserMapper userMapper;

    @Override
    public List<User> getUserByExampleLimit(String userName, String description, Integer pageNum, Integer pageSize) {
        UserExample example = new UserExample();
        /*动态sql,userName和description不为null,则作为查询条件查询*/
        UserExample.Criteria criteria = example.createCriteria();
        if (!StringUtils.isNullOrEmpty(userName)) {
            criteria.andUserNameLike("%" + userName + "%");
        }
        if (!StringUtils.isNullOrEmpty(description)) {
            criteria.andDescriptionEqualTo(description);
        }
        example.setOffset((pageNum - 1) * pageSize);
        example.setLimit(pageSize);
        return userMapper.selectByExample(example);
    }
}

2.4 Service层调用

@Service
@AllArgsConstructor
public class UserControllerServiceImpl implements UserControllerService {
    private UserRepository userRepository;

    @Override
    public List<User> getUserByExampleLimit(String userName, String description, Integer pageNum, Integer pageSize) {
        return userRepository.getUserByExampleLimit(userName, description, pageNum, pageSize);
    }
}

2.5 控制台信息

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@706f956b] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.jdbc.JDBC4Connection@7234b5ae] will not be managed by Spring
==>  Preparing: select id, user_name, description, is_deleted from user WHERE ( user_name like ? ) limit 0, 10 
==> Parameters: %zhuoli%(String)
<==    Columns: id, user_name, description, is_deleted
<==        Row: 6, zhuoli, zhuoli is a programer, 0
<==        Row: 7, zhuoli1, zhuoli1 is a programer, 0
<==        Row: 8, zhuoli2, zhuoli2 is a programer, 0
<==      Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@706f956b]

可以看到sql查询时,limit参数是固定的,也就是说通过Example成功将limit参数添加到生成的sql中,这种方式的分页是一种物理分页,有些情况也是必须要这么做的。比如我之前做过一个使用Zebra进行分库分表的项目,在使用拦截器进行分页时,并不起作用,原因不明,到最后只好通过这种方式实现分库分表的分页查询。至于原因,一直没来及查清楚,回头有时间的话,我会用一篇文章讲述。

3. 物理分页——拦截器PageHelper

PageHelper是一款好用的开源免费的Mybatis第三方物理分页插件,在配置了PageHelper的page number和size,调用完startPage后,它会通过PageInterceptor对其后的第一个执行sql进行拦截,比如List<User> list = userService.findAllUser(),这里原本的sql可能是 select * from users,它会自动拼接上分页的sql语句,比如mysql环境的话,就是拼接上limit语句,随后执行,最后的结果,可以通过PageInfo和Page进行获取。

3.1 pom.xml

项目结构跟RowBounds一致,这里不单独放出来了,首先来看一下pom.xml配置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.zhuoli.service</groupId>
    <artifactId>springboot-08-mybatis-pagehelper</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!-- Spring Boot 启动父依赖 -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.RELEASE</version>
    </parent>

    <build>
        <plugins>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.5</version>
                <!--如果不配置configuration节点,配置文件名字必须为generatorConfig.xml-->
                <configuration>
                    <!--可以自定义generatorConfig文件名-->
                    <configurationFile>src/main/resources/autogen/generatorConfig_zhuoli.xml</configurationFile>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <!-- Exclude Spring Boot's Default Logging -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!--pagehelper -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.2</version>
            <scope>provided</scope>
        </dependency>

    </dependencies>

</project>

3.2 Repository定义

@Repository
@AllArgsConstructor
public class UserRepositoryImpl implements UserRepository {

    private UserMapper userMapper;

    @Override
    public List<User> getUserByCondition(String userName, String description) {
        UserExample example = new UserExample();
        /*动态sql,userName和description不为null,则作为查询条件查询*/
        UserExample.Criteria criteria = example.createCriteria();
        if (!StringUtils.isNullOrEmpty(userName)) {
            criteria.andUserNameLike("%" + userName + "%");
        }
        if (!StringUtils.isNullOrEmpty(description)) {
            criteria.andDescriptionEqualTo(description);
        }
        return userMapper.selectByExample(example);
    }
}

可以看到,respository层没有任何分页相关的信息,使用Rowbounds要传入一个RowBounds参数,使用MySQLLimitPlugin要把pageNum和pageSize作为参数传入。所以可以很明显看到一个好处是,使用PageHelper是非侵入的,假如respository层有N个查询方法,在做分页时,不用改造respository层代码,使方法的通用性更高。

3.3 service层调用

@Service
@AllArgsConstructor
public class UserControllerServiceImpl implements UserControllerService {
    private UserRepository userRepository;

    @Override
    public PageInfo<User> getByCondition(String userName, String description, Integer pageNum, Integer pageSize) {
        //分页
        PageHelper.startPage(pageNum, pageSize);
        List<User> queryResult = userRepository.getUserByCondition(userName, description);
        return new PageInfo<>(queryResult);
    }
}

3.4 控制台信息

SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2057e3b5] was not registered for synchronization because synchronization is not active
Cache Hit Ratio [SQL_CACHE]: 0.0
JDBC Connection [com.mysql.jdbc.JDBC4Connection@487a7b05] will not be managed by Spring
==>  Preparing: SELECT count(0) FROM user WHERE (user_name LIKE ?) 
==> Parameters: %zhuoli%(String)
<==    Columns: count(0)
<==        Row: 3
<==      Total: 1
==>  Preparing: select id, user_name, description, is_deleted from user WHERE ( user_name like ? ) LIMIT ?, ? 
==> Parameters: %zhuoli%(String), 2(Integer), 2(Integer)
<==    Columns: id, user_name, description, is_deleted
<==        Row: 8, zhuoli2, zhuoli2 is a programer, 0
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2057e3b5]

3.5 PageInfo输出

{
  "total": 3,
  "list": [
    {
      "id": 8,
      "userName": "zhuoli2",
      "description": "zhuoli2 is a programer",
      "isDeleted": 0
    }
  ],
  "pageNum": 2,
  "pageSize": 2,
  "size": 1,
  "startRow": 3,
  "endRow": 3,
  "pages": 2,
  "prePage": 1,
  "nextPage": 0,
  "isFirstPage": false,
  "isLastPage": true,
  "hasPreviousPage": true,
  "hasNextPage": false,
  "navigatePages": 8,
  "navigatepageNums": [
    1,
    2
  ],
  "navigateFirstPage": 1,
  "navigateLastPage": 2,
  "firstPage": 1,
  "lastPage": 2
}

可以到,分页相关的基本信息都拿到了,可以说是非常方便的。

示例代码:

码云 – 卓立 – Mybatis使用RowBounds分页

码云 – 卓立 – Mybatis使用MySQLLimitPlugin分页

码云 – 卓立 – Mybatis使用PageHelper分页

参考链接:

  1. Mybatis逻辑分页原理解析RowBounds
  2. Mybatis最入门—分页查询(逻辑分页与SQL语句分页)
  3. MyBatis Generator实现MySQL分页插件
  4. Mybatis3.4.x技术内幕(二十):PageHelper分页插件源码及原理剖析

赞(0) 打赏
Zhuoli's Blog » Mybatis分页查询
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

zhuoli's blog

联系我关于我

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏