Mybatis手写分页代码

仅为代码记录。

方式一、Executor.class

<resultMap id="categoryVoResultMap" type="com.lw.familysystem.vo.VideoCategoryVo">
        <id property="categoryId" column="category_id"/>
        <result property="categoryName" column="category_name"/>
        <result property="createTimeFmt" column="create_time_fmt"/>
        <result property="updateTimeFmt" column="update_time_fmt"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

<select id="findCategoriesByPage" resultMap="categoryVoResultMap" parameterType="VideoCategoryVo">
        select t.*,
               date_format(t.create_time,'%Y/%m/%d %H:%i') as create_time_fmt,
               date_format(t.update_time,'%Y/%m/%d %H:%i') as update_time_fmt
        from video_category t

    </select>

补充方式一里面没有 xml的写法。

实践后补充说明

如果是分页还带有参数，按照方式一照抄就有问题，会出现报错一的问题，

<select id="findCategoriesByPage" resultMap="categoryVoResultMap" parameterType="VideoCategoryVo">
        select t.*,
               date_format(t.create_time,'%Y/%m/%d %H:%i') as create_time_fmt,
               date_format(t.update_time,'%Y/%m/%d %H:%i') as update_time_fmt
        from video_category t
        <where>
            <if test="vo.categoryName!=null and vo.categoryName!=''">
                and t.category_name = #{vo.categoryName}
            </if>
        </where>
    </select>

即使是把vo.categoryName改为 categoryName，也会报错，会报只有pageRequest,vo,param1,param2，找不到categoryName。

问题就是如下的两张图处，需要改为传递原始的参数，而不是转换后的。

问题就在于whereParam是一个对象，里面并没有封装传入的参数，而param是一个ParamMap，是继承于HashMap的一个Map，所以才能vo.XXX成功。

param对象里面的param1,param2这个两个key其实和方法的传参顺序有关。

Page<VideoCategoryVo> findCategoriesByPage(@Param("vo") VideoCategoryVo vo,PageRequest pageRequest);

List<VideoCategoryVo> findCategories(@Param("vo") VideoCategoryVo vo);

在Mapper.java中不分页查询，也可以用分页的SQL样式，如下

<select id="findCategories" resultMap="categoryVoResultMap" parameterType="VideoCategoryVo">
        select t.*,
        date_format(t.create_time,'%Y/%m/%d %H:%i') as create_time_fmt,
        date_format(t.update_time,'%Y/%m/%d %H:%i') as update_time_fmt
        from video_category t
        <where>
            <if test="vo.categoryName!=null and vo.categoryName!=''">
                and t.category_name = #{vo.categoryName}
            </if>
        </where>
    </select>

附上完整代码

Controller

 @RequestMapping("/findCategoriesByPage")
    @ResponseBody
    public ReturnInfo findCategoriesByPage(){
        Page<VideoCategoryVo> categories = this.videoService.findCategoriesByPage();
        return ReturnInfo.returnSuccessInfo(categories);
    }

Service

@Autowired
    private VideoCategoryMapper categoryMapper;

public Page<VideoCategoryVo> findCategoriesByPage(){
        VideoCategoryVo vo = new VideoCategoryVo();
        vo.setCategoryName("电影");
        PageRequest pageRequest = new PageRequest(0,3);
        return this.categoryMapper.findCategoriesByPage(vo,pageRequest);
    }

Mapper.java

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * 类别
 */
@Mapper
public interface VideoCategoryMapper {

    List<VideoCategoryVo> findAllCategories();
    Page<VideoCategoryVo> findCategoriesByPage(@Param("vo") VideoCategoryVo vo,PageRequest pageRequest);
}

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="com.lw.familysystem.video.mapper.VideoCategoryMapper">

    <resultMap id="categoryVoResultMap" type="com.lw.familysystem.vo.VideoCategoryVo">
        <id property="categoryId" column="category_id"/>
        <result property="categoryName" column="category_name"/>
        <result property="createTimeFmt" column="create_time_fmt"/>
        <result property="updateTimeFmt" column="update_time_fmt"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <select id="findAllCategories" resultMap="categoryVoResultMap">
        select t.*,
               date_format(t.create_time,'%Y/%m/%d %H:%i') as create_time_fmt,
               date_format(t.update_time,'%Y/%m/%d %H:%i') as update_time_fmt
        from video_category t
    </select>


    <select id="findCategoriesByPage" resultMap="categoryVoResultMap" parameterType="VideoCategoryVo">
        select t.*,
               date_format(t.create_time,'%Y/%m/%d %H:%i') as create_time_fmt,
               date_format(t.update_time,'%Y/%m/%d %H:%i') as update_time_fmt
        from video_category t
        <where>
            <if test="vo.categoryName!=null and vo.categoryName!=''">
                and t.category_name = #{vo.categoryName}
            </if>
        </where>
    </select>

</mapper>

MybatisPageInterceptor.java  拦截器

package com.lw.config;

import com.lw.config.mybatis.BoundSqlSqlSource;
import com.lw.config.mybatis.page.Page;
import com.lw.config.mybatis.page.PageRequest;
import com.mysql.cj.x.protobuf.MysqlxPrepare;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.executor.statement.BaseStatementHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.property.PropertyTokenizer;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.util.StringUtils;

import javax.xml.bind.PropertyException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * mybatis 分页拦截器
 * 自定义分页
 */
@Intercepts({@Signature(
        type = Executor.class,
        method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
)})
@Slf4j
public class MybatisPageInterceptor implements Interceptor {

    private static String dialect = "";
    private static String pageSqlId = "";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        if (mappedStatement.getId().matches(pageSqlId)) {
            //获取参数
            Object param = invocation.getArgs()[1];
            Object whereParam = getWhereParameter(param);
            BoundSql boundSql = mappedStatement.getBoundSql(param);

            if (log.isDebugEnabled()) {
                log.debug("[sql_id]=" + mappedStatement.getId());
                log.debug("[sql]=" + boundSql.getSql());
            }

            //获取查询SQL
            String sql = boundSql.getSql();

            //获取数据库连接
            Configuration config = mappedStatement.getConfiguration();
            Connection connection = config.getEnvironment().getDataSource().getConnection();

            //查询总数据
            String countSql = "select count(*) from (" + sql + ")  tmp_count";
            PreparedStatement countStmt = connection.prepareStatement(countSql);
            BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
            this.setParameters(countStmt, mappedStatement, countBS, boundSql.getParameterObject());
            ResultSet rs = countStmt.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt(1);
            }

            rs.close();
            countStmt.close();

            PageRequest pageRequest = this.getPageRequest(param);

            String pageSql = generatePageSql(sql, pageRequest);
            executeSql(invocation,pageSql);

            //重新执行新的sql
            Object result = invocation.proceed();
            connection.close();

            //处理新的结构
            Page<?> page = new Page((List)result, pageRequest, count);
            List<Page> returnResultList = new ArrayList<>();
            returnResultList.add(page);
            return returnResultList;
        }
        return invocation.proceed();
    }

    private void executeSql(Invocation invocation, String sql){
        final Object[] args = invocation.getArgs();
        MappedStatement statement = (MappedStatement) args[0];
        BoundSql boundSql = statement.getBoundSql(args[1]);
        MappedStatement newStatement = createNewMappedStatement(statement, new BoundSqlSqlSource(boundSql));
        MetaObject msObject = MetaObject.forObject(newStatement, new DefaultObjectFactory(), new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
        msObject.setValue("sqlSource.boundSql.sql", sql);
        args[0] = newStatement;
    }
    /**
     * 获取新的MappedStatement
     * @param ms
     * @param newSqlSource
     * @return
     */
    private MappedStatement createNewMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
        MappedStatement.Builder builder =
                new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
        builder.resource(ms.getResource());
        builder.fetchSize(ms.getFetchSize());
        builder.statementType(ms.getStatementType());
        builder.keyGenerator(ms.getKeyGenerator());
        if (ms.getKeyProperties() != null && ms.getKeyProperties().length != 0) {
            StringBuilder keyProperties = new StringBuilder();
            for (String keyProperty : ms.getKeyProperties()) {
                keyProperties.append(keyProperty).append(",");
            }
            keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
            builder.keyProperty(keyProperties.toString());
        }
        builder.timeout(ms.getTimeout());
        builder.parameterMap(ms.getParameterMap());
        builder.resultMaps(ms.getResultMaps());
        builder.resultSetType(ms.getResultSetType());
        builder.cache(ms.getCache());
        builder.flushCacheRequired(ms.isFlushCacheRequired());
        builder.useCache(ms.isUseCache());
        return builder.build();
    }

    /**
     * 从参数列表返回PageRequest
     */
    private PageRequest getPageRequest(Object paramMap) {
        if (paramMap == null) {
            return null;
        } else if (PageRequest.class.isAssignableFrom(paramMap.getClass())) {
            return (PageRequest) paramMap;
        } else {
            if (paramMap instanceof MapperMethod.ParamMap) {
                MapperMethod.ParamMap map = (MapperMethod.ParamMap) paramMap;
                Iterator iterator = map.entrySet().iterator();
                while (iterator.hasNext()) {
                    Map.Entry entry = (Map.Entry) iterator.next();
                    Object obj = entry.getValue();
                    if (obj != null && PageRequest.class.isAssignableFrom(obj.getClass())) {
                        return (PageRequest) obj;
                    }
                }
            }
            return null;
        }
    }

    private Object getWhereParameter(Object obj) {
        if (obj instanceof MapperMethod.ParamMap) {
            MapperMethod.ParamMap paramMap = (MapperMethod.ParamMap) obj;
            if (paramMap.size() == 4) {
                Iterator iterator = paramMap.entrySet().iterator();
                while (iterator.hasNext()) {
                    Map.Entry var4 = (Map.Entry) iterator.next();
                    Object var5 = var4.getValue();
                    if (PageRequest.class.isAssignableFrom(var5.getClass())) {
                        return paramMap.get("param1");
                    }
                }
            }
        }
        return obj;
    }

    private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException {
        ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
        List parameterMappings = boundSql.getParameterMappings();
        if (parameterMappings != null) {
            Configuration configuration = mappedStatement.getConfiguration();
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject);

            for (int i = 0; i < parameterMappings.size(); ++i) {
                ParameterMapping parameterMapping = (ParameterMapping) parameterMappings.get(i);
                if (parameterMapping.getMode() != ParameterMode.OUT) {
                    String propertyName = parameterMapping.getProperty();
                    PropertyTokenizer prop = new PropertyTokenizer(propertyName);
                    Object value = null;
                    if (parameterObject == null) {
                        value = null;
                    } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                        value = parameterObject;
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        value = boundSql.getAdditionalParameter(propertyName);
                    } else if (propertyName.startsWith("__frch_") && boundSql.hasAdditionalParameter(prop.getName())) {
                        value = boundSql.getAdditionalParameter(prop.getName());
                        if (value != null) {
                            value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length()));
                        }
                    } else {
                        value = metaObject == null ? null : metaObject.getValue(propertyName);
                    }

                    TypeHandler typeHandler = parameterMapping.getTypeHandler();
                    if (typeHandler == null) {
                        throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId());
                    }

                    typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());
                }
            }
        }

    }

    /**
     * 重新生成分页SQL
     *
     * @param sql
     * @param pageRequest
     * @return
     */
    private String generatePageSql(String sql, PageRequest pageRequest) {
        if (pageRequest != null && !StringUtils.isEmpty(dialect)) {
            StringBuffer pageSql = new StringBuffer();
            if ("mysql".equals(dialect)) {
                pageSql.append(sql);
                pageSql.append(" limit " + pageRequest.getOffset() + "," + pageRequest.getPageSize());
            } else if ("oracle".equals(dialect)) {
                pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from (");
                pageSql.append(sql);
                pageSql.append(" ) tmp_tb ");
                pageSql.append(") where row_id>");
                pageSql.append(pageRequest.getOffset());
                pageSql.append(" and row_id<= ");
                pageSql.append(pageRequest.getOffset() + pageRequest.getPageSize());
            } else if ("pg".equals(dialect)) {
                pageSql.append(sql);
                int offsetNum = 0;
                if (pageRequest != null && pageRequest.getOffset() != 0) {
                    offsetNum = pageRequest.getOffset() - 1;
                }

                pageSql.append(" limit " + pageRequest.getPageSize() + " offset " + offsetNum * pageRequest.getPageSize());
            }

            log.debug(pageSql.toString());
            return pageSql.toString();
        } else {
            return sql;
        }
    }

    public Object plugin(Object arg0) {
        return Plugin.wrap(arg0, this);
    }

    /**
     * 加载mybatis-config.xml中配置
     *
     * @param p
     */
    public void setProperties(Properties p) {
        dialect = p.getProperty("dialect");
        if (StringUtils.isEmpty(dialect)) {
            try {
                throw new PropertyException("dialect property is not found!");
            } catch (PropertyException var4) {
                var4.printStackTrace();
            }
        }

        pageSqlId = p.getProperty("pageSqlId");
        if (StringUtils.isEmpty(pageSqlId)) {
            try {
                throw new PropertyException("pageSqlId property is not found!");
            } catch (PropertyException var3) {
                var3.printStackTrace();
            }
        }

    }
}

Page.java

package com.lw.config.mybatis.page;

import lombok.Data;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * 分页封装返回
 * @param <T>
 */
@Data
public class Page<T> implements Serializable {
    private static final long serialVersionUID = 1625981207349025919L;
    //查询结果集
    private final List<T> content;
    //分页参数
    private  PageRequest pageRequest;
    //总记录数
    private  int total;
    public Page(List<T> content, PageRequest pageRequest, int total) {
        this.content = new ArrayList();
        if (null == content) {
            throw new IllegalArgumentException("Content must not be null!");
        } else {
            this.content.addAll(content);
            this.total = total;
            this.pageRequest = pageRequest;
        }
    }
}

PageRequest.java

package com.lw.config.mybatis.page;

import lombok.Data;

import java.io.Serializable;

/**
 * 分页参数
 */
@Data
public class PageRequest implements Serializable {
    private static final long serialVersionUID = -2464407342708149892L;
    /**
     *页码（从0开始）
     */
    private  int page;
    /**
     *每页显示数量
     */
    private  int size;

    public PageRequest() {
        this(0, 10);
    }
    public PageRequest(int page, int size) {
        if (page < 0) {
            page = 0;
        }
        if (size < 0) {
            size = 0;
        }
        this.page = page;
        this.size = size;
    }
    public int getPageSize() {
        return this.size;
    }
    public int getOffset() {
        return this.page * this.size;
    }
}

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="cacheEnabled" value="true" />
        <setting name="lazyLoadingEnabled" value="true" />
        <setting name="multipleResultSetsEnabled" value="true" />
        <!--日志打印，可以输出SQL-->
<!--        <setting name="logImpl" value="org.apache.ibatis.logging.stdout.StdOutImpl"/>-->
        <setting name="logImpl" value="com.lw.config.MybatisLogConfig"/>
        <setting name="vfsImpl" value="org.mybatis.spring.boot.autoconfigure.SpringBootVFS" />
    </settings>

    <!--自定义分页-->
    <plugins>
        <plugin interceptor="com.lw.config.MybatisPageInterceptor">
            <property name="dialect" value="mysql"/>
            <property name="pageSqlId" value=".*ByPage"/>
        </plugin>
    </plugins>
</configuration>

方式二、StatementHandler.class

在方式一上的变体，

package com.lw.config;

import com.lw.config.mybatis.page.Page;
import com.lw.config.mybatis.page.PageRequest;
import com.mysql.cj.x.protobuf.MysqlxPrepare;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.executor.statement.BaseStatementHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.property.PropertyTokenizer;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.util.StringUtils;

import javax.xml.bind.PropertyException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * mybatis 分页拦截器
 * 自定义分页
 */
@Intercepts({@Signature(
        type = StatementHandler.class,
        method = "prepare", args = {Connection.class, Integer.class}
)})
@Slf4j
public class MybatisPageInterceptor implements Interceptor {

    private static String dialect = "";
    private static String pageSqlId = "";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if (invocation.getTarget() instanceof RoutingStatementHandler) {
            RoutingStatementHandler statementHandler = (RoutingStatementHandler)invocation.getTarget();
            BaseStatementHandler delegate = (BaseStatementHandler)getValueByFieldName(statementHandler, "delegate");

            MappedStatement mappedStatement = (MappedStatement)getValueByFieldName(delegate, "mappedStatement");

            if (mappedStatement.getId().matches(pageSqlId)) {
                BoundSql boundSql = delegate.getBoundSql();
                if (log.isDebugEnabled()) {
                    log.debug("[sql_id]=" + mappedStatement.getId());
                    log.debug("[sql]=" + boundSql.getSql());
                }

                //获取参数
                String sql = boundSql.getSql();

                //获取相关配置
                Configuration config = mappedStatement.getConfiguration();
                Connection connection = config.getEnvironment().getDataSource().getConnection();

                //查询总数据
                String countSql = "select count(*) from (" + sql + ")  tmp_count";
                PreparedStatement countStmt = connection.prepareStatement(countSql);
                BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
                this.setParameters(countStmt, mappedStatement, countBS, boundSql.getParameterObject());
                ResultSet rs = countStmt.executeQuery();
                int count = 0;
                if (rs.next()) {
                    count = rs.getInt(1);
                }

                
                rs.close();
                countStmt.close();

                Object paramObject = boundSql.getParameterObject();
                PageRequest pageRequest = getPageRequest(paramObject);

                String pageSql = generatePageSql(sql, pageRequest);
                setValueByFieldName(boundSql, "sql", pageSql);

                //重新执行新的sql
                Object result = invocation.proceed();
                connection.close();

                pageRequest.setTotalCount(count);

                return result;
            }
        }
        return invocation.proceed();
    }

    /**
     * 从参数列表返回PageRequest
     */
    public PageRequest getPageRequest(Object paramMap) {
        if (paramMap == null) {
            return null;
        } else if (PageRequest.class.isAssignableFrom(paramMap.getClass())) {
            return (PageRequest)paramMap;
        } else {
            if (paramMap instanceof MapperMethod.ParamMap) {
                MapperMethod.ParamMap map = (MapperMethod.ParamMap)paramMap;
                Iterator iterator = map.entrySet().iterator();
                while(iterator.hasNext()) {
                    Map.Entry entry = (Map.Entry)iterator.next();
                    Object obj = entry.getValue();
                    if (obj != null && PageRequest.class.isAssignableFrom(obj.getClass())) {
                        return (PageRequest)obj;
                    }
                }
            }
            return null;
        }
    }

    private Object getWhereParameter(Object obj) {
        if (obj instanceof MapperMethod.ParamMap) {
            MapperMethod.ParamMap paramMap = (MapperMethod.ParamMap)obj;
            if (paramMap.size() == 4) {
                Iterator iterator = paramMap.entrySet().iterator();
                while(iterator.hasNext()) {
                    Map.Entry var4 = (Map.Entry)iterator.next();
                    Object var5 = var4.getValue();
                    if (PageRequest.class.isAssignableFrom(var5.getClass())) {
                        return paramMap.get("param1");
                    }
                }
            }
        }
        return obj;
    }

    private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException {
        ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
        List parameterMappings = boundSql.getParameterMappings();
        if (parameterMappings != null) {
            Configuration configuration = mappedStatement.getConfiguration();
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject);

            for(int i = 0; i < parameterMappings.size(); ++i) {
                ParameterMapping parameterMapping = (ParameterMapping)parameterMappings.get(i);
                if (parameterMapping.getMode() != ParameterMode.OUT) {
                    String propertyName = parameterMapping.getProperty();
                    PropertyTokenizer prop = new PropertyTokenizer(propertyName);
                    Object value = null;
                    if (parameterObject == null) {
                        value = null;
                    } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                        value = parameterObject;
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        value = boundSql.getAdditionalParameter(propertyName);
                    } else if (propertyName.startsWith("__frch_") && boundSql.hasAdditionalParameter(prop.getName())) {
                        value = boundSql.getAdditionalParameter(prop.getName());
                        if (value != null) {
                            value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length()));
                        }
                    } else {
                        value = metaObject == null ? null : metaObject.getValue(propertyName);
                    }

                    TypeHandler typeHandler = parameterMapping.getTypeHandler();
                    if (typeHandler == null) {
                        throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId());
                    }

                    typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());
                }
            }
        }

    }

    /**
     * 重新生成分页SQL
     * @param sql
     * @param pageRequest
     * @return
     */
    private String generatePageSql(String sql, PageRequest pageRequest) {
        if (pageRequest != null && !StringUtils.isEmpty(dialect)) {
            StringBuffer pageSql = new StringBuffer();
            if ("mysql".equals(dialect)) {
                pageSql.append(sql);
                pageSql.append(" limit " + pageRequest.getOffset() + "," + pageRequest.getPageSize());
            } else if ("oracle".equals(dialect)) {
                pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from (");
                pageSql.append(sql);
                pageSql.append(" ) tmp_tb ");
                pageSql.append(") where row_id>");
                pageSql.append(pageRequest.getOffset());
                pageSql.append(" and row_id<= ");
                pageSql.append(pageRequest.getOffset() + pageRequest.getPageSize());
            } else if ("pg".equals(dialect)) {
                pageSql.append(sql);
                int offsetNum = 0;
                if (pageRequest != null && pageRequest.getOffset()!= 0) {
                    offsetNum = pageRequest.getOffset() - 1;
                }

                pageSql.append(" limit " + pageRequest.getPageSize() + " offset " + offsetNum * pageRequest.getPageSize());
            }

            log.debug(pageSql.toString());
            return pageSql.toString();
        } else {
            return sql;
        }
    }

    public Object plugin(Object arg0) {
        return Plugin.wrap(arg0, this);
    }

    /**
     * 加载mybatis-config.xml中配置
     * @param p
     */
    public void setProperties(Properties p) {
        dialect = p.getProperty("dialect");
        if (StringUtils.isEmpty(dialect)) {
            try {
                throw new PropertyException("dialect property is not found!");
            } catch (PropertyException var4) {
                var4.printStackTrace();
            }
        }

        pageSqlId = p.getProperty("pageSqlId");
        if (StringUtils.isEmpty(pageSqlId)) {
            try {
                throw new PropertyException("pageSqlId property is not found!");
            } catch (PropertyException var3) {
                var3.printStackTrace();
            }
        }

    }


    private static Field getFieldByFieldName(Object obj, String fieldName) {
        Class<?> superClass = obj.getClass();

        while(superClass != Object.class) {
            try {
                return superClass.getDeclaredField(fieldName);
            } catch (NoSuchFieldException var4) {
                superClass = superClass.getSuperclass();
            }
        }

        return null;
    }

    private static Object getValueByFieldName(Object obj, String fieldName) throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException {
        Field field = getFieldByFieldName(obj, fieldName);
        Object value = null;
        if (field != null) {
            if (field.isAccessible()) {
                value = field.get(obj);
            } else {
                field.setAccessible(true);
                value = field.get(obj);
                field.setAccessible(false);
            }
        }

        return value;
    }

    private static void setValueByFieldName(Object obj, String fieldName, Object value) throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException {
        Field field = obj.getClass().getDeclaredField(fieldName);
        if (field.isAccessible()) {
            field.set(obj, value);
        } else {
            field.setAccessible(true);
            field.set(obj, value);
            field.setAccessible(false);
        }

    }


}

变体是拦截对应的方法，同时将数据总数写入pageRequest中。

mybatis-config.xml中也配置了拦截 ByPage结尾的方法，并标注了是mysql类型。

<!--自定义分页-->
    <plugins>
        <plugin interceptor="com.lw.config.MybatisPageInterceptor">
            <property name="dialect" value="mysql"/>
            <property name="pageSqlId" value=".*ByPage"/>
        </plugin>
    </plugins>

**Mapper.xml的写法没有改变，改变的是 **Mapper.java中方法的返回，不在是Page<T>,

List<VideoCategoryVo> findCategoriesByPage(VideoCategoryVo vo, PageRequest pageRequest);

总数据量就在PageRequest中。

总的来说，好像第一种方式更好，第二种变体污染了PageRequest。

posted @ 2022-11-16 15:39 伟衙内阅读(131) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 关于我在uni-app练手中遇到的坑

· 代码生成ktr文件

· 使用Mybatis拦截器实现分页功能

· mybatis实现分页的几种方式

· Mybatis的四种分页方式

阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？

2025年3月

日

一

二

三

四

五

六

weiyanei

Mybatis手写分页代码

方式一、Executor.class

实践后补充说明

附上完整代码

方式二、StatementHandler.class

我的标签

随笔分类