JdbcCursor流式查询导出数据库千万级数据

引言

翻看mybatis的源代码时发现有一个selectCursor的接口，网上查了下是一个流式接口用于按顺序查询大量数据，其方法签名具有三个参数，statement（mapper）、parameter（参数）、rowBounds（结果映射函数）。目前这边有一个需求，通过执行数据库中配置的查询语句来获取查询结果，显然这种方式mybatis无法实现，应为语句都是写在mapper的xml文件中的，，当然也可以使用${}这种占位符的方式进行替换，但是这种方式进行？参数赋值就很麻烦，于是编写JdbcCursor工具实现该功能。

mybaitis的cursor接口源码如下：

  /**
   * A Cursor offers the same results as a List, except it fetches data lazily using an Iterator.
   * @param <T> the returned cursor element type.
   * @param statement Unique identifier matching the statement to use.
   * @param parameter A parameter object to pass to the statement.
   * @param rowBounds  Bounds to limit object retrieval
   * @return Cursor of mapped objects
   */
  <T> Cursor<T> selectCursor(String statement, Object parameter, RowBounds rowBounds);

${}替换方式：

<?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.monika.main.system.dao.CommonMapper">
    <select id="cursorQry" fetchSize="1000" parameterType="java.util.LinkedHashMap" resultType="java.util.LinkedHashMap">
        ${SQL}
    </select>
   
</mapper>

 @Test
    public void fn4() throws IOException {

        try(SqlSession sqlSession=sqlSessionFactory.openSession();){

            LinkedHashMap<String,String> sql=new LinkedHashMap<>();
            sql.put("SQL","select * from sys_user");
            try(Cursor<Map> cursor=sqlSession.selectCursor("com.monika.main.system.dao.CommonMapper.cursorQry",sql)){

                Iterator<Map> iterator = cursor.iterator();

                while (iterator.hasNext()){
                    System.out.println(iterator.next());
                }
            }
        }catch (Throwable t){
            throw t;
        }

    }

JdbcCursor流式查询实现

一、代码实现

package com.monika.main.system.util.cursor;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


import java.sql.*;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * @author:whh
 * @date: 2025-05-20 22:38
 * <p></p>
 */
public class JdbcCursor<T> implements Iterable<T>,AutoCloseable{


    public static Logger logger = LoggerFactory.getLogger(JdbcCursor.class);

    private Connection conn;

    private PreparedStatementSetter pst;
    private PreparedStatement ps;
    private ResultSet rs;

    private RowMapper rowMapper;




    private String sql;

    public JdbcCursor() {
    }
    public JdbcCursor<T> connection(Connection conn){
        this.conn=conn;
        return this;
    }

    public JdbcCursor<T> sql(String sql){
        this.sql=sql;
        return this;
    }



    public JdbcCursor<T> preparedStatementSetter(PreparedStatementSetter pst){
        this.pst=pst;
        return this;
    }


    public JdbcCursor<T> rowMapper(RowMapper rowMapper){
        this.rowMapper=rowMapper;
        return this;
    }

    public JdbcCursor<T> cursor() throws Exception {
        try{

            if (conn == null) {
                throw new IllegalArgumentException("connection cannot be null");
            }
            if (sql == null) {
                throw new IllegalArgumentException("sql cannot be null");
            }
            if(rowMapper == null){
                rowMapper= new DefaultMapRowMapper();
            }
            ps=conn.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ps.setFetchSize(50);
            ps.setFetchDirection(ResultSet.FETCH_FORWARD);//按游标从头到尾取数
            if(pst!=null){
                pst.setValues(ps);
            }
            rs=ps.executeQuery();
        }catch (Exception ex){
            close();
            throw ex;
        }

        return this;
    }







    @Override
    public void close()  {

       closeResultSet(rs);
       rs=null;
       closeStatement(ps);
       releaseConnection(conn);
    }

    @Override
    public Iterator<T> iterator() {
        return new DefaultIterator<>();
    }



    private class DefaultIterator<T> implements Iterator<T>{
        private int rowNum=0;
        @Override
        public boolean hasNext() {
            try {
                return rs.next();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        @Override
        public T next() {
            try {
                return (T) rowMapper.mapRow(rs, rowNum++);
            } catch (SQLException e) {
                return null;
            }
        }
    }










     public void closeStatement( Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            }
            catch (SQLException ex) {
                logger.trace("Could not close JDBC Statement", ex);
            }
            catch (Throwable ex) {
                // We don't trust the JDBC driver: It might throw RuntimeException or Error.
                logger.trace("Unexpected exception on closing JDBC Statement", ex);
            }
        }
    }



        public  void releaseConnection(Connection con) {
        try {
            if (con != null) {
                con.close();
            }
        }
        catch (SQLException ex) {
            logger.debug("Could not close JDBC Connection", ex);
        }
        catch (Throwable ex) {
            logger.debug("Unexpected exception on closing JDBC Connection", ex);
        }
    }



     public void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            }
            catch (SQLException ex) {
                logger.trace("Could not close JDBC ResultSet", ex);
            }
            catch (Throwable ex) {
                // We don't trust the JDBC driver: It might throw RuntimeException or Error.
                logger.trace("Unexpected exception on closing JDBC ResultSet", ex);
            }
        }
    }


    /**
     * 默认结果映射器
     */
    public class DefaultMapRowMapper implements RowMapper<Map<String, Object>> {

        @Override
        public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            Map<String, Object> mapOfColumnValues = createColumnMap(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                String column = rsmd.getColumnLabel(i);
                if (column == null || column.isEmpty()) {
                    column = rsmd.getColumnName(i);
                }

                mapOfColumnValues.putIfAbsent(getColumnKey(column), getColumnValue(rs, i));
            }
            return mapOfColumnValues;
        }

        protected Map<String, Object> createColumnMap(int columnCount) {
            return new LinkedHashMap<>(columnCount);
        }

        protected String getColumnKey(String columnName) {
            return columnName;
        }

        protected Object getColumnValue(ResultSet rs, int index) throws SQLException {
            Object obj = rs.getObject(index);
            String className = null;
            if (obj != null) {
                className = obj.getClass().getName();
            }
            if (obj instanceof Blob) {
                Blob blob = (Blob) obj;
                obj = blob.getBytes(1, (int) blob.length());
            }
            else if (obj instanceof Clob) {
                Clob clob = (Clob) obj;
                obj = clob.getSubString(1, (int) clob.length());
            }
            else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) {
                obj = rs.getTimestamp(index);
            }
            else if (className != null && className.startsWith("oracle.sql.DATE")) {
                String metaDataClassName = rs.getMetaData().getColumnClassName(index);
                if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
                    obj = rs.getTimestamp(index);
                }
                else {
                    obj = rs.getDate(index);
                }
            }
            else if (obj instanceof java.sql.Date) {
                if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
                    obj = rs.getTimestamp(index);
                }
            }
            return obj;
        }

    }


    public interface PreparedStatementSetter {

        /**
         * Set parameter values on the given PreparedStatement.
         * @param ps the PreparedStatement to invoke setter methods on
         * @throws SQLException if an SQLException is encountered
         * (i.e. there is no need to catch SQLException)
         */
        void setValues(PreparedStatement ps) throws SQLException;

    }


    public interface RowMapper<T> {

        /**
         * Implementations must implement this method to map each row of data
         * in the ResultSet. This method should not call {@code next()} on
         * the ResultSet; it is only supposed to map values of the current row.
         * @param rs the ResultSet to map (pre-initialized for the current row)
         * @param rowNum the number of the current row
         * @return the result object for the current row (may be {@code null})
         * @throws SQLException if an SQLException is encountered getting
         * column values (that is, there's no need to catch SQLException)
         */
        T mapRow(ResultSet rs, int rowNum) throws SQLException;

    }
}

二、测试（SQL无参设置）

 @Test
    public void fn1() throws SQLException {



        try( JdbcCursor<Map> cursor=new JdbcCursor().connection(dataSource.getConnection()).sql("select * from sys_user").cursor();){


            for (Map map : cursor) {
                System.out.println(map);
            }

        }catch (Exception ex){
            ex.printStackTrace();
        }

    }

三、测试（SQL有参设置）

 @Test
    public void fn2() throws SQLException {

        try( JdbcCursor<Map> cursor=new JdbcCursor()
                .connection(dataSource.getConnection())
                .sql("select * from sys_user where user_id=?")
                .preparedStatementSetter(pst->{
                    pst.setFetchSize(50);//设置FetchSize，最好不要超过100
                    pst.setInt(1,1);
                })
                .cursor();){


            for (Map map : cursor) {
                System.out.println(map);
            }

        }catch (Exception ex){
            ex.printStackTrace();
        }

    }