扩展tk.mybatis的流式查询功能

绝地灬酷狼 2022-02-03 12:01 318阅读 0赞

mybatis查询默认是一次获取全部， 有时候需要查询上万上百万数据时，如果一次性读取到内存中，会容易导致OOM问题。这时候需要采用流式查询。以下扩展了tk.mybatis的流式查询功能。 直接上干货：

@Options注解是关键

import org.apache.ibatis.annotations.Options;
    import org.apache.ibatis.annotations.SelectProvider;
    import org.apache.ibatis.mapping.ResultSetType;
    import org.apache.ibatis.session.ResultHandler;
    
    /** * 通用Mapper接口,流式查询 * * @param <T> 不能为空 * @author sunchangtan */
    @tk.mybatis.mapper.annotation.RegisterMapper
    public interface SelectStreamByExampleMapper<T> { 
    
        /** * 根据example条件和RowBounds进行流式查询 * * @param example * @return */
        @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
        @SelectProvider(type = StreamExampleProvider.class, method = "dynamicSQL")
        void selectStreamByExampleMapper(Object example, ResultHandler resultHandler);
    
    }

带RowBounds的流式查询

import org.apache.ibatis.annotations.Options;
    import org.apache.ibatis.annotations.SelectProvider;
    import org.apache.ibatis.mapping.ResultSetType;
    import org.apache.ibatis.session.ResultHandler;
    import org.apache.ibatis.session.RowBounds;
    
    /** * 通用Mapper接口,流式查询 * * @param <T> 不能为空 * @author sunchangtan */
    @tk.mybatis.mapper.annotation.RegisterMapper
    public interface SelectStreamByExampleRowBoundsMapper<T> { 
    
        /** * 根据example条件和RowBounds进行流式查询 * * @param example * @return */
        @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
        @SelectProvider(type = StreamExampleProvider.class, method = "dynamicSQL")
        void selectStreamByExampleRowBoundsMapper(Object example, RowBounds rowBounds, ResultHandler resultHandler);
    
    }

流式ExampleProvider

import org.apache.ibatis.mapping.MappedStatement;
    import tk.mybatis.mapper.mapperhelper.MapperHelper;
    import tk.mybatis.mapper.provider.ExampleProvider;
    
    /** * 流式查询的SqlProvider * @author sunchangtan */
    
    public class StreamExampleProvider extends ExampleProvider { 
    
        public StreamExampleProvider(Class<?> mapperClass, MapperHelper mapperHelper) { 
            super(mapperClass, mapperHelper);
        }
    
    
        /** * 根据Example流式查询 * * @param ms * @return */
        public String selectStreamByExampleMapper(MappedStatement ms) { 
            return this.selectByExample(ms);
        }
    
        /** * 根据Example和RowBounds流式查询 * @param ms * @return */
        public String selectStreamByExampleRowBoundsMapper(MappedStatement ms) { 
            return this.selectByExample(ms);
        }
    
    }

将SelectStreamByExampleMapper和SelectStreamByExampleRowBoundsMapper组合成一个接口

/** * 流式查询接口 * @param <T> * @author sunchangtan */
    @tk.mybatis.mapper.annotation.RegisterMapper
    public interface StreamMapper<T> extends
            SelectStreamByExampleMapper<T>,
            SelectStreamByExampleRowBoundsMapper<T> { 
    }

在BaseMapper中加入StreamMapper

/** * Mapper的基类 * @author sunchangtan * @param <T> */
    public interface BaseMapper<T> extends Mapper<T>, MySqlMapper<T>, StreamMapper<T> { 
    }

使用例子：

this.userMapper.selectStreamByExampleRowBoundsMapper(example, new RowBounds(0, 1), resultContext -> { 
         User user= (User) resultContext.getResultObject();
         System.out.println(user);
     });
    
    
    this.userMapper.selectStreamByExampleMapper(example, resultContext -> { 
        User user= (User) resultContext.getResultObject();
        System.out.println(User);
    });