devbox
小蓝xlanll
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

自定义Mybatis分页插件_mybatis 自定义limt 位置

作者：小蓝xlanll | 2024-05-29 17:05:59

赞

踩

mybatis 自定义limt 位置

自定义Mybatis分页插件

目标：

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

实现：

一、前期准备

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

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>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51

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 + '\'' +
                '}';
    }
}



  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48

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);
}



  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

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>


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

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>



  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37

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;
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

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) {

    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71

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());
        }
    }

}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89

运行程序。输出如下：

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

不使用分页，直接输出全部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

  • 1
  • 2
  • 3
  • 4
  • 5

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

总结

流程图

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

重点及易错点

1、一定要放行

//        放行
        return invocation.proceed();

  • 1
  • 2

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

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小蓝xlanll/article/detail/643267
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号