自定义Mybatis分页插件

Bertha 。 2023-05-22 22:11 55阅读 0赞

### 自定义Mybatis分页插件 ###

*  目标：
 *  实现：
 *   *  一、前期准备
     *   *  1、加入依赖
         *  2、定义Country 类
         *  3、定义CountryMapper类
         *  4、定义CountryMapper.xml配置文件
         *  5、定义log4j配置文件
         *  6、定义mybatis-config配置文件
         *  7、定义PageUtil
         *  8、定义MyPageHelper
         *  9、定义测试类
 *  总结
 *   *  流程图
     *  重点及易错点
     *   *  1、一定要放行

# 目标： #

使用自定义插件完成分页功能。

# 实现： #

## 一、前期准备 ##

实现自定义分页插件，并用自定义分页插件模拟正常的业务逻辑，实现分页查询的功能。

### 1、加入依赖 ###

本项目使用`Mysql`，`Mybatis`等驱动包。源码如下：

> 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">
        <parent>
            <artifactId>mianshi</artifactId>
            <groupId>com.njust</groupId>
            <version>1.0-SNAPSHOT</version>
        </parent>
        <modelVersion>4.0.0</modelVersion>
    
        <artifactId>mybatisplug</artifactId>
        <properties>
            <java.version>1.8</java.version>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
                <scope>test</scope>
            </dependency>
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.4.1</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.38</version>
            </dependency>
    
    
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <configuration>
                        <source>${java.version}</source>
                        <target>${java.version}</target>
                    </configuration>
                </plugin>
            </plugins>
        </build>
    
    </project>

### 2、定义Country 类 ###

定义`Country`实体类，有`id,countryname,countrycode`等基本属性。源码如下：

> Country .java

package com.njust.mybatisplug.model;
    
    /** * @author Chen * @version 1.0 * @date 2020/4/18 9:52 * @description: */
    public class Country { 
        private Long id;
        private String countryname;
        private String countrycode;
    
        public Long getId() { 
            return id;
        }
    
        public void setId(Long id) { 
            this.id = id;
        }
    
        public String getCountryname() { 
            return countryname;
        }
    
        public void setCountryname(String countryname) { 
            this.countryname = countryname;
        }
    
        public String getCountrycode() { 
            return countrycode;
        }
    
        public void setCountrycode(String countrycode) { 
            this.countrycode = countrycode;
        }
    
        @Override
        public String toString() { 
            return "Country{" +
                    "id=" + id +
                    ", countryname='" + countryname + '\'' +
                    ", countrycode='" + countrycode + '\'' +
                    '}';
        }
    }

### 3、定义CountryMapper类 ###

定义`CountryMapper`类，实现`Mybatis`操作数据库。定义两个方法。`selectAll`方法不使用分页插件。`selectAllByPage`使用分页插件，其中分页参数通过`params`参数传递。源码如下：

> CountryMapper .java

package com.njust.mybatisplug.dao;
    
    import com.njust.mybatisplug.model.Country;
    
    import java.util.List;
    import java.util.Map;
    
    public interface CountryMapper { 
        List<Country> selectAll();
    
        List<Country> selectAllByPage(Map<String,Object> params);
    }

### 4、定义CountryMapper.xml配置文件 ###

定义`CountryMapper.xml`。定义操作数据库的`SQL`语句，都是查询数据库所有信息。源码如下：

> CountryMapper.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="com.njust.mybatisplug.dao.CountryMapper">
        <select id="selectAll" resultType="com.njust.mybatisplug.model.Country">
            select id, countryname, countrycode
            from country
        </select>
    
        <select id="selectAllByPage" resultType="com.njust.mybatisplug.model.Country">
            select id, countryname, countrycode
            from country
        </select>
    </mapper>

### 5、定义log4j配置文件 ###

定义`log4j`。配置打印`SQL`语句，便于调试。源码如下：

> log4j.properties

log4j.rootLogger=ERROR, stdout
    
    log4j.logger.tk.mybatis.simple.mapper=TRACE
    
    log4j.appender.stdout=org.apache.log4j.ConsoleAppender
    log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
    log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

### 6、定义mybatis-config配置文件 ###

定义`mybatis-config`。配置打印`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="LOG4J"/>
    	    <setting name="mapUnderscoreToCamelCase" value="true"/>
    	</settings>
        
        <typeAliases>
            <package name="com.njust.mybatisplug.model"/>
        </typeAliases>
    
    
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC">
                    <property name="" value=""/>
                </transactionManager>
                <dataSource type="UNPOOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                    <property name="username" value="root"/>
                    <property name="password" value="root"/>
                </dataSource>
            </environment>
        </environments>
    
        <mappers>
            <package name="com.njust.mybatisplug.dao"/>
        </mappers>
        
        
    </configuration>

### 7、定义PageUtil ###

定义`PageUtil`类，主要封装分页的基本信息。源码如下：

> PageUtil .java

package com.njust.mybatisplug.page;
    
    import lombok.Data;
    
    /** * @author Chen * @version 1.0 * @date 2020/4/18 13:28 * @description: */
    @Data
    public class PageUtil { 
        private int startNum;
        private int pageSize;
        private int count;
        private int limit;
    }

### 8、定义MyPageHelper ###

定义`MyPageHelper`类。实现自定义分页逻辑，首先拦截`StatementHandler`类的`prepare`方法，并配置相关参数。然后通过`MetaObject`获取`Mybatis`中配置的方法。根据自定义规则确定是否分页，如果不分页，则放行执行后续`Mybatis`逻辑。如果分页，则首先获取`Connection`连接以及`SQL`语句，然后拼接数量查询语句，接着使用`PreparedStatement`进行查询，并将值设置到`PageUtil`中。最后拼接分页查询语句，将`sql`语句设置给`delegate.boundSql.sql`，从而动态修改配置的`SQL`语句，实现分页查询效果。最后仍然放行执行`Mybatis`后续操作。源码如下：

> MyPageHelper .java

package com.njust.mybatisplug.page;
    
    import org.apache.ibatis.executor.parameter.ParameterHandler;
    import org.apache.ibatis.executor.statement.StatementHandler;
    import org.apache.ibatis.plugin.*;
    import org.apache.ibatis.reflection.DefaultReflectorFactory;
    import org.apache.ibatis.reflection.MetaObject;
    import org.apache.ibatis.reflection.SystemMetaObject;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.util.Map;
    import java.util.Properties;
    
    /** * @author Chen * @version 1.0 * @date 2020/4/18 13:29 * @description: */
    @Intercepts(@Signature(type = StatementHandler.class, method = "prepare",args = { Connection.class,Integer.class}))
    public class MyPageHelper implements Interceptor { 
        @Override
        public Object intercept(Invocation invocation) throws Throwable { 
            StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
    
    
            MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY,
                    SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
            String value = (String) metaObject.getValue("delegate.mappedStatement.id");
    // 自定义匹配规则 只要是ByPage结尾的就使用插件
            if (value.matches(".*ByPage$")) { 
                Connection connection = (Connection) invocation.getArgs()[0];
                String sql = statementHandler.getBoundSql().getSql();
    
                String countSql = "select count(0) from (" + sql + ") as a";
                PreparedStatement preparedStatement = connection.prepareStatement(countSql);
                //渲染参数
                ParameterHandler parameterHandler = statementHandler.getParameterHandler();
                parameterHandler.setParameters(preparedStatement);
                ResultSet resultSet = preparedStatement.executeQuery();
    
                //传入的map
                Map<String,Object> parameterObject = (Map<String,Object>)parameterHandler.getParameterObject();
                PageUtil pageUtil = (PageUtil) parameterObject.get("page");
    
                if (resultSet.next()) { 
                    int anInt = resultSet.getInt(1);
                    pageUtil.setCount(anInt);
                }
    
                String pageSql = sql + " limit " + pageUtil.getStartNum() + "," + pageUtil.getLimit() + "";
                metaObject.setValue("delegate.boundSql.sql", pageSql);
            }
    
    // 放行
            return invocation.proceed();
        }
    
        @Override
        public Object plugin(Object target) { 
            return Plugin.wrap(target, this);
        }
    
        @Override
        public void setProperties(Properties properties) { 
    
        }
    }

### 9、定义测试类 ###

定义`CountryMapperTest`类，首先加载配置文件，然后分别测试使用分页查询插件和不使用插件的情况。源码如下：

> CountryMapperTest .java

package com.njust.mybatisplug.dao;
    
    import com.njust.mybatisplug.model.Country;
    import com.njust.mybatisplug.page.PageUtil;
    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 org.junit.BeforeClass;
    import org.junit.Test;
    
    import java.io.IOException;
    import java.io.Reader;
    import java.util.HashMap;
    import java.util.List;
    
    
    /** * @author Chen * @version 1.0 * @date 2020/4/18 9:58 * @description: */
    public class CountryMapperTest { 
        private static SqlSessionFactory sqlSessionFactory;
    
        @BeforeClass
        public static void init() { 
            try { 
                Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
                reader.close();
            } catch (IOException ignore) { 
                ignore.printStackTrace();
            }
        }
    
        public SqlSession getSqlSession() { 
            return sqlSessionFactory.openSession();
        }
    
        @Test
        public void testSelectAll() { 
            SqlSession sqlSession = getSqlSession();
            try { 
                CountryMapper countryMapper = sqlSession.getMapper(CountryMapper.class);
    
                //调用 selectAll 方法查询所有用户
                List<Country> userList = countryMapper.selectAll();
                userList.forEach(System.out::println);
            } finally { 
                //不要忘记关闭 sqlSession
                sqlSession.close();
            }
        }
    
        @Test
        public void testSelectAllByPage() { 
            SqlSession sqlSession = getSqlSession();
            try { 
                CountryMapper countryMapper = sqlSession.getMapper(CountryMapper.class);
    
                PageUtil pageUtil = new PageUtil();
                pageUtil.setStartNum(0);
                pageUtil.setPageSize(0);
                pageUtil.setCount(0);
                pageUtil.setLimit(2);
    
    
    
                HashMap<String, Object> objectObjectHashMap = new HashMap<>();
                objectObjectHashMap.put("page", pageUtil);
    
                //调用 selectAll 方法查询所有用户
                List<Country> userList = countryMapper.selectAllByPage(objectObjectHashMap);
                userList.forEach(System.out::println);
            } finally { 
                //不要忘记关闭 sqlSession
                sqlSession.close();
            }
        }
    
        private void printCountryList(List<Country> countryList) { 
            for (Country country : countryList) { 
                System.out.printf("%-4d%4s%4s\n", country.getId(), country.getCountryname(), country.getCountrycode());
            }
        }
    
    }

运行程序。输出如下：

Sat Apr 18 14:37:06 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
    Country{ id=1, countryname='中国', countrycode='CN'}
    Country{ id=2, countryname='美国', countrycode='US'}
    Country{ id=3, countryname='俄罗斯', countrycode='RU'}
    Country{ id=4, countryname='英国', countrycode='GB'}
    Country{ id=5, countryname='法国', countrycode='FR'}
    
    Process finished with exit code 0

不使用分页，直接输出全部5条数据。

Sat Apr 18 14:37:56 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
    Country{ id=1, countryname='中国', countrycode='CN'}
    Country{ id=2, countryname='美国', countrycode='US'}
    
    Process finished with exit code 0

使用分页插件，配置只输出前两条数据。程序按照要求输出，符合预期。

# 总结 #

## 流程图 ##

Created with Raphaël 2.2.0开始拦截StatementHandler类的prepare方法，并配置相关参数。通过MetaObject获取Mybatis中配置的方法根据自定义规则判断是否使用分页查询?获取Connection连接以及SQL语句拼接数量查询语句使用PreparedStatement进行查询并将值设置到PageUtil中拼接分页查询语句，将sql语句设置给delegate.boundSql.sql放行结束yesno

## 重点及易错点 ##

### 1、一定要放行 ###

// 放行
            return invocation.proceed();

一定要在最后放行，否则后续`Mybatis`初始化逻辑将无法完成。  
  有问题欢迎各位读者批评指正。

点个赞再走呗！欢迎留言哦！