分页插件 Mybatis-PageHelper

淡淡的烟草味﹌ 2022-10-24 01:45 196阅读 0赞

MyBatis 的分页插件 Mybatis-PageHelper

官方文档：

[https://github.com/pagehelper/Mybatis-PageHelper/][https_github.com_pagehelper_Mybatis-PageHelper]

看下如何使用。

1、Maven 依赖

<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>
    
        <parent>
            <groupId>constxiong</groupId>
            <artifactId>demo</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </parent>
        <artifactId>mybatis-pageHelper</artifactId>
    
        <dependencies>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.21</version>
            </dependency>
    
            
            <dependency>
    		  <groupId>org.mybatis</groupId>
    		  <artifactId>mybatis</artifactId>
    		  <version>3.5.5</version>
    		</dependency>
    
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper</artifactId>
                <version>5.2.0</version>
            </dependency>
    
        </dependencies>
    
        <build>
    
        </build>
    </project>

2、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>
        <plugins>
            <plugin interceptor="com.github.pagehelper.PageInterceptor">
                <property name="helperDialect" value="mysql"/>
            </plugin>
        </plugins>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://172.31.32.184:3306/constxiong?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8"/>
                    <property name="username" value="root"/>
                    <property name="password" value="constxiong@123"/>
                </dataSource>
            </environment>
        </environments>
        <mappers>
            <mapper resource="constxiong/mapper/UserMapper.xml"/>
        </mappers>
    </configuration>

3、Mapper 接口 UserMapper

package constxiong.mapper;
    
    import constxiong.po.User;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    
    /**
     * UserMapper 接口，映射对数据库的操作
     */
    public interface UserMapper {
    
        List<User> selectUsers();
    
        int insertUser(User user);
    
        int deleteUsers();
    }

4、Mapper 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">
    <mapper namespace="constxiong.mapper.UserMapper">
    
        <select id="selectUsers" resultType="constxiong.po.User">
            select * from user
        </select>
    
        <insert id="insertUser" parameterType="constxiong.po.User">
            insert into user values(#{id}, #{name})
        </insert>
    
        <delete id="deleteUsers">
          delete from user
        </delete>
    
    </mapper>

5、User po 类

package constxiong.po;
    
    /**
     * 用户表模型
     */
    public class User {
        private Integer id;
    
        private String name;
    
        private String mc;
    
        public User(Integer id, String name) {
            this.id = id;
            this.name = name;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getMc() {
            return mc;
        }
    
        public void setMc(String mc) {
            this.mc = mc;
        }
    
        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", mc='" + mc + '\'' +
                    '}';
        }
    }

6、测试代码

package constxiong;
    
    import com.github.pagehelper.ISelect;
    import com.github.pagehelper.Page;
    import com.github.pagehelper.PageHelper;
    import constxiong.mapper.UserMapper;
    import constxiong.po.User;
    import org.apache.ibatis.datasource.pooled.PooledDataSource;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.mapping.Environment;
    import org.apache.ibatis.session.*;
    import org.apache.ibatis.transaction.TransactionFactory;
    import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
    
    import javax.sql.DataSource;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.List;
    
    /**
     * 测试 Mybatis 分页插件
     */
    public class Test {
    
        public static void main(String[] args) throws IOException {
            testXmlConfigAndPager();
        }
    
        /**
         * 测试 xml 配置方式分页
         */
        private static void testXmlConfigAndPager() throws IOException {
            String resource = "mybatis-config.xml";//xml配置文件路径
            InputStream inputStream = Resources.getResourceAsStream(resource);//读取配置
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//构建 SqlSessionFactory
            SqlSession sqlSession = sqlSessionFactory.openSession(true);//通过 SqlSessionfactory 获取 SqlSession
    
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);//通过 SqlSession 获取 Mapper 接口
    
            //删除 user
            userMapper.deleteUsers();
    
            //插入 user
            for (int i = 1; i <= 35; i++) {
                userMapper.insertUser(new User(i, "ConstXiong" + i));
            }
    
            //查询所有 user
            List<User> users = userMapper.selectUsers();
            System.out.println(users);
    
            //未打开 mybatis-config.xml 中 plugins 标签使用内存分页；打开标签使用插件的物理分页
            int pageNo = 2;
            int pageSize = 10;
            users = sqlSession.selectList("constxiong.mapper.UserMapper.selectUsers", null, new RowBounds((pageNo - 1) * pageSize, pageSize));
            System.out.println(users);
    
            //使用静态方法 startPage
            PageHelper.startPage(pageNo, pageSize);
            users = userMapper.selectUsers();
            System.out.println(users);
    
            //使用静态方法 offsetPage
            PageHelper.offsetPage(pageNo, pageSize);
            users = userMapper.selectUsers();
            System.out.println(users);
    
            //匿名类
            Page<User> page = PageHelper.startPage(pageNo, pageSize).doSelectPage(new ISelect() {
                @Override
                public void doSelect() {
                    userMapper.selectUsers();
                }
            });
            System.out.println(page);
            // lambda
            page = PageHelper.startPage(pageNo, pageSize).doSelectPage(() -> {
                userMapper.selectUsers();
            });
            System.out.println(page);
    
            //仅合计
            long count = PageHelper.count(new ISelect() {
                @Override
                public void doSelect() {
                    userMapper.selectUsers();
                }
            });
            System.out.println(count);
            //lambda
            count = PageHelper.count(() -> {
                userMapper.selectUsers();
            });
            System.out.println(count);
    
            //关闭 SqlSession
            sqlSession.close();
        }
    
    }

7、打印

[User{id=1, name='ConstXiong1', mc='null'}, User{id=2, name='ConstXiong2', mc='null'}, User{id=3, name='ConstXiong3', mc='null'}, User{id=4, name='ConstXiong4', mc='null'}, User{id=5, name='ConstXiong5', mc='null'}, User{id=6, name='ConstXiong6', mc='null'}, User{id=7, name='ConstXiong7', mc='null'}, User{id=8, name='ConstXiong8', mc='null'}, User{id=9, name='ConstXiong9', mc='null'}, User{id=10, name='ConstXiong10', mc='null'}, User{id=11, name='ConstXiong11', mc='null'}, User{id=12, name='ConstXiong12', mc='null'}, User{id=13, name='ConstXiong13', mc='null'}, User{id=14, name='ConstXiong14', mc='null'}, User{id=15, name='ConstXiong15', mc='null'}, User{id=16, name='ConstXiong16', mc='null'}, User{id=17, name='ConstXiong17', mc='null'}, User{id=18, name='ConstXiong18', mc='null'}, User{id=19, name='ConstXiong19', mc='null'}, User{id=20, name='ConstXiong20', mc='null'}, User{id=21, name='ConstXiong21', mc='null'}, User{id=22, name='ConstXiong22', mc='null'}, User{id=23, name='ConstXiong23', mc='null'}, User{id=24, name='ConstXiong24', mc='null'}, User{id=25, name='ConstXiong25', mc='null'}, User{id=26, name='ConstXiong26', mc='null'}, User{id=27, name='ConstXiong27', mc='null'}, User{id=28, name='ConstXiong28', mc='null'}, User{id=29, name='ConstXiong29', mc='null'}, User{id=30, name='ConstXiong30', mc='null'}, User{id=31, name='ConstXiong31', mc='null'}, User{id=32, name='ConstXiong32', mc='null'}, User{id=33, name='ConstXiong33', mc='null'}, User{id=34, name='ConstXiong34', mc='null'}, User{id=35, name='ConstXiong35', mc='null'}]
    Page{count=false, pageNum=2, pageSize=10, startRow=10, endRow=20, total=-1, pages=1, reasonable=false, pageSizeZero=false}[User{id=11, name='ConstXiong11', mc='null'}, User{id=12, name='ConstXiong12', mc='null'}, User{id=13, name='ConstXiong13', mc='null'}, User{id=14, name='ConstXiong14', mc='null'}, User{id=15, name='ConstXiong15', mc='null'}, User{id=16, name='ConstXiong16', mc='null'}, User{id=17, name='ConstXiong17', mc='null'}, User{id=18, name='ConstXiong18', mc='null'}, User{id=19, name='ConstXiong19', mc='null'}, User{id=20, name='ConstXiong20', mc='null'}]
    Page{count=true, pageNum=2, pageSize=10, startRow=10, endRow=20, total=35, pages=4, reasonable=false, pageSizeZero=false}[User{id=11, name='ConstXiong11', mc='null'}, User{id=12, name='ConstXiong12', mc='null'}, User{id=13, name='ConstXiong13', mc='null'}, User{id=14, name='ConstXiong14', mc='null'}, User{id=15, name='ConstXiong15', mc='null'}, User{id=16, name='ConstXiong16', mc='null'}, User{id=17, name='ConstXiong17', mc='null'}, User{id=18, name='ConstXiong18', mc='null'}, User{id=19, name='ConstXiong19', mc='null'}, User{id=20, name='ConstXiong20', mc='null'}]
    Page{count=true, pageNum=2, pageSize=10, startRow=2, endRow=12, total=35, pages=4, reasonable=false, pageSizeZero=false}[User{id=3, name='ConstXiong3', mc='null'}, User{id=4, name='ConstXiong4', mc='null'}, User{id=5, name='ConstXiong5', mc='null'}, User{id=6, name='ConstXiong6', mc='null'}, User{id=7, name='ConstXiong7', mc='null'}, User{id=8, name='ConstXiong8', mc='null'}, User{id=9, name='ConstXiong9', mc='null'}, User{id=10, name='ConstXiong10', mc='null'}, User{id=11, name='ConstXiong11', mc='null'}, User{id=12, name='ConstXiong12', mc='null'}]
    Page{count=true, pageNum=2, pageSize=10, startRow=10, endRow=20, total=35, pages=4, reasonable=false, pageSizeZero=false}[User{id=11, name='ConstXiong11', mc='null'}, User{id=12, name='ConstXiong12', mc='null'}, User{id=13, name='ConstXiong13', mc='null'}, User{id=14, name='ConstXiong14', mc='null'}, User{id=15, name='ConstXiong15', mc='null'}, User{id=16, name='ConstXiong16', mc='null'}, User{id=17, name='ConstXiong17', mc='null'}, User{id=18, name='ConstXiong18', mc='null'}, User{id=19, name='ConstXiong19', mc='null'}, User{id=20, name='ConstXiong20', mc='null'}]
    Page{count=true, pageNum=2, pageSize=10, startRow=10, endRow=20, total=35, pages=4, reasonable=false, pageSizeZero=false}[User{id=11, name='ConstXiong11', mc='null'}, User{id=12, name='ConstXiong12', mc='null'}, User{id=13, name='ConstXiong13', mc='null'}, User{id=14, name='ConstXiong14', mc='null'}, User{id=15, name='ConstXiong15', mc='null'}, User{id=16, name='ConstXiong16', mc='null'}, User{id=17, name='ConstXiong17', mc='null'}, User{id=18, name='ConstXiong18', mc='null'}, User{id=19, name='ConstXiong19', mc='null'}, User{id=20, name='ConstXiong20', mc='null'}]
    35
    35

完成代码：

[https://github.com/ConstXiong/toy/][https_github.com_ConstXiong_toy]

--------------------

### 【Java学习资源】整理推荐 ###

*  [Java 8 Stream API Demo][]
 *  [Druid 使用][Druid]
 *  [借助 libreoffice，word 转 pdf 体验][libreoffice_word _ pdf]
 *  [Java 动态代理][Java]
 *  [openjdk jol 工具打印 Java 对象内存布局][openjdk jol _ Java]
 *  [Spring整合MyBatis][Spring_MyBatis]
 *  [MyBatis 自定义插件][MyBatis]
 *  [MyBatis 延迟加载][MyBatis 1]
 *  [MyBatis 1对1 1对多 关联查询][MyBatis 1_1 1_]
 *  [MyBatis insert 获取自增主键][MyBatis insert]
 *  [MyBatis 批量插入][MyBatis 2]
 *  [分页插件 Mybatis-PageHelper][Mybatis-PageHelper]
 *  [MyBatis 原生使用][MyBatis 3]
 *  [Sharding-JDBC 实现分库分表][Sharding-JDBC]
 *  [JDK1.8 中的日期与时间 API][JDK1.8 _ API]
 *  [Sharding-JDBC 实现读写分离][Sharding-JDBC 1]

--------------------

### 【Java面试题与答案】整理推荐 ###

*  [基础与语法][Link 1]
 *  [集合][Link 2]
 *  [网络编程][Link 3]
 *  [并发编程][Link 4]
 *  [Web][]
 *  [安全][Link 5]
 *  [设计模式][Link 6]
 *  [框架][Link 7]
 *  [算法与数据结构][Link 8]
 *  [异常][Link 9]
 *  [文件解析与生成][Link 10]
 *  [Linux][]
 *  [MySQL][]
 *  [Oracle][]
 *  [Redis][]
 *  [Dubbo][]

[https_github.com_pagehelper_Mybatis-PageHelper]: https://github.com/pagehelper/Mybatis-PageHelper/
[https_github.com_ConstXiong_toy]: https://github.com/ConstXiong/toy/
[Java 8 Stream API Demo]: https://www.javanav.com/val/5be3f821d00e4cc5b6177c9d0e5dd30f.html
[Druid]: https://www.javanav.com/val/79860880b84442cfb0f6f5e094d53105.html
[libreoffice_word _ pdf]: https://www.javanav.com/val/501ababe5c48493780ed23dbbc7b2cbc.html
[Java]: https://www.javanav.com/val/ab11038d7de64b4b8c0e384d1d4a9ab0.html
[openjdk jol _ Java]: https://www.javanav.com/val/66e564087e244b2f99f85091de029605.html
[Spring_MyBatis]: https://www.javanav.com/val/687c224b31a34d3c9f99fee67e3d5bcc.html
[MyBatis]: https://www.javanav.com/val/a5535343f9b545eda9665f03d62345ba.html
[MyBatis 1]: https://www.javanav.com/val/973ded541e9244aa8b3169b9fb869d60.html
[MyBatis 1_1 1_]: https://www.javanav.com/val/a9fe4555c1614b40b0da07afeabf2a66.html
[MyBatis insert]: https://www.javanav.com/val/3ac331d2674b4c108469cce54ae126f3.html
[MyBatis 2]: https://www.javanav.com/val/2d21b1463f2e4faeaf0def0c49df34a4.html
[Mybatis-PageHelper]: https://www.javanav.com/val/a8a8009f878d47dc9167bf20b9e0ab57.html
[MyBatis 3]: https://www.javanav.com/val/07f60d7946204b3b893abd5dc8bd2fa3.html
[Sharding-JDBC]: https://www.javanav.com/val/eefddd846ae845668daf9d798755c057.html
[JDK1.8 _ API]: https://www.javanav.com/val/fefecf2f089e48b294708e99954c57a1.html
[Sharding-JDBC 1]: https://www.javanav.com/val/f05fe12cb8fd444aaa59fb7f747a3224.html
[Link 1]: https://www.javanav.com/interview/93b0069472fd479393006c0e73043fc4.html
[Link 2]: https://www.javanav.com/interview/4147d95e693945e7a50345fd33ec817e.html
[Link 3]: https://www.javanav.com/interview/68a57ffc78de4133bb890bdd502ac418.html
[Link 4]: https://www.javanav.com/interview/36a24fa3258e45c89873f474ce914912.html
[Web]: https://www.javanav.com/interview/4e27cb63adb440c6825a159d35cf4010.html
[Link 5]: https://www.javanav.com/interview/03affb2ce3f04fda8853f714e5764906.html
[Link 6]: https://www.javanav.com/interview/07a32a9ff17a4006a53550ce658cafed.html
[Link 7]: https://www.javanav.com/interview/5c466456e9b54ab0a749b5948e51f1d3.html
[Link 8]: https://www.javanav.com/interview/943f917041ee4f05add24539f5b861b2.html
[Link 9]: https://www.javanav.com/interview/d3f058cfc05d4c6dadd821b622e84d36.html
[Link 10]: https://www.javanav.com/interview/67a50996c28f418e83b109ea42fc3eff.html
[Linux]: https://www.javanav.com/interview/00cccb81729540d1923bd0a0ef49533a.html
[MySQL]: https://www.javanav.com/interview/d311e78502a845f1a4bb307bd6aec2b3.html
[Oracle]: https://www.javanav.com/interview/5f29e9677edf4a16be3dce30d257c9ab.html
[Redis]: https://www.javanav.com/interview/066c62b3af4d4827b2243a822c3084a9.html
[Dubbo]: https://www.javanav.com/interview/9a3e7a7740324757abaab953f7ebc455.html