关于一条sql从mysql或postgresql等数据库中查出大量数据而不造成内存溢出的方法

场景：我们在做大量数据导出的时候，单条sql导出几千万级别以上数据容易造成内存溢出，采用limit有时候也不方便，而且如果在数据仓库中做这种操作的时候，复杂sql可能会很慢，这样分成limit后，每条sql都执行几分钟，那效率可也太低了。

解决关键: jdbc驱动的fetchsize,然后查询使用resultset遍历

1.对于mysql，我们只需要在driverurl后面加上:useCursorFetch=true&defaultFetchSize=500,开启fetchsize即可，这个会在服务端维护一个游标，分批返回数据到客户端。经本人测试，对mysql服务端性能影响极小，当然你担心可以从mysql从库上查询。

2.对于postgresql，这个需要设置连接的session 的autoCommit = false，指定fetchSize=500； 这个可能没那么简单，如果你使用mybaits或jdbctemplate，你需要对持久化框架session相关源码有一定的了解，而且这个session的autoCommit和fetchsize属性会受到开源数据库连接池的影响。

下面给到我在实际开发中使用postgresql，jdbctemplate，druid数据库连接池而构建的查询相关代码。

1.设置autocommit和fetchsize

import com.alibaba.druid.filter.config.ConfigTools;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;

import java.security.NoSuchAlgorithmException;
import java.security.NoSuchProviderException;
import java.sql.SQLException;

/**
 * Created by tanlei on 2018/12/20
 * postgresql使用pro数据源的时候指定 ： postgresql的fetchsize可用
 * mysql使用pro数据源的时候在url上指定fetchsize可用：jdbc:mysql://localhost:3306/{dbName}?useCursorFetch=true&defaultFetchSize=5000
 */
public class JdbcTemplateUtil {

    public static final Logger logger = LogManager.getLogger(JdbcTemplateUtil.class);

    /**  如果使用这个数据源，必须关闭自动提交,可以不用恢复 */
    public static String PG_DATASOURCE_PRO = "pgDcJdbcTemplatePro";

    /** */
    public static int DEFAULT_FETCHSIZE = 500;

    public static void setPgDataSourceProInfo(JdbcTemplate jdbcTemplate, int fetchSize) {
        if (fetchSize <= 0) {
            fetchSize = DEFAULT_FETCHSIZE;
        }
        try {
            jdbcTemplate.getDataSource().getConnection().setAutoCommit(false);//不起作用，在datasource加载处设置默认自动提交为false
            jdbcTemplate.setFetchSize(fetchSize);
        } catch (SQLException e) {
            logger.error("关闭postgresql自动提交和设置fetchsize错误：{}", e);
        }
    }
    
}

2.spring容器启动加载构建自己特殊的jdbctemplate，这个可以让你把连接信息配置到数据库等地方，所谓的spring-cloud配置中心也是类似这样的原理。

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.dubbo.common.json.JSONObject;
import com.alibaba.dubbo.common.json.ParseException;
import com.alibaba.fastjson.JSON;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.support.BeanDefinitionBuilder;
import org.springframework.beans.factory.support.DefaultListableBeanFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.context.ApplicationListener;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.event.ContextRefreshedEvent;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by tanlei-pc on 2018/5/29.
 * 初始化数据中心数据源
 */
@Component
public class DcDataSourceInitEvent implements ApplicationContextAware, ApplicationListener<ContextRefreshedEvent> {

    private ConfigurableApplicationContext applicationContext;

    @Resource
    private DataSourceMapper dataSourceMapper;


    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        this.applicationContext = (ConfigurableApplicationContext) applicationContext;
    }


    @Override
    public void onApplicationEvent(ContextRefreshedEvent contextRefreshedEvent)  {
        DataSourceCfg searchParam = new DataSourceCfg();
        searchParam.setDisabled(0);
        List<DataSourceCfg> dsList = dataSourceMapper.getDataSourceCfgList(searchParam);

        if (CollectionUtils.isNotEmpty(dsList)) {
            DefaultListableBeanFactory beanFactory = (DefaultListableBeanFactory) applicationContext.getBeanFactory();//定义spring相关信息

            for (DataSourceCfg dc : dsList) {
                String configJson = dc.getConfig();
                DruidDataSourceConfig config = JSON.parseObject(configJson, DruidDataSourceConfig.class);

                DruidDataSource ds = new DruidDataSource();
                ds.setDriverClassName(config.getDriverClassName());
                ds.setUrl(config.getUrl());
                ds.setUsername(config.getUsername());
                ds.setPassword(config.getPassword());
                ds.setInitialSize(config.getInitialSize());
                ds.setMinIdle(config.getMinIdle());
                ds.setMaxActive(config.getMaxActive());
                ds.setMaxWait(config.getMaxWait());
                ds.setTimeBetweenEvictionRunsMillis(config.getTimeBetweenEvictionRunsMillis());
                ds.setMinEvictableIdleTimeMillis(config.getMinEvictableIdleTimeMillis());
                ds.setMaxEvictableIdleTimeMillis(config.getMaxEvictableIdleTimeMillis());
                ds.setValidationQuery(config.getValidationQuery());
                ds.setTestWhileIdle(config.isTestWhileIdle());
                ds.setTestOnBorrow(config.isTestOnBorrow());
                ds.setTestOnReturn(config.isTestOnReturn());
                if (StringUtils.equals(JdbcTemplateUtil.PG_DATASOURCE_PRO, dc.getDataSource())) {//数据中心的读数据源关闭自动提交
                    ds.setDefaultAutoCommit(false);//关闭自动提交
                }
                try {
                    ds.setFilters(config.getFilters());
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                ds.setConnectionProperties("config.decrypt=true;config.decrypt.key=" + config.getPublicKey());
                ds.setConnectionInitSqls(config.getConnectionInitSqls());

                JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
                if (StringUtils.equals(JdbcTemplateUtil.PG_DATASOURCE_PRO, dc.getDataSource())) {
                    JdbcTemplateUtil.setPgDataSourceProInfo(jdbcTemplate, JdbcTemplateUtil.DEFAULT_FETCHSIZE);
                }
                beanFactory.registerSingleton(dc.getDataSource(), jdbcTemplate);//注册bean
            }
        }
    }

}

/**
 * Created by tanlei-pc on 2018/5/29.
 */
public class DataSourceCfg extends Entity {

    private String config;

    private String dataSource;

    private String type;

    private Integer disabled;

    public String getConfig() {
        return config;
    }

    public void setConfig(String config) {
        this.config = config;
    }

    public String getDataSource() {
        return dataSource;
    }

    public void setDataSource(String dataSource) {
        this.dataSource = dataSource;
    }

    public Integer getDisabled() {
        return disabled;
    }

    public void setDisabled(Integer disabled) {
        this.disabled = disabled;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }
}

比如我数据库有这样一段配置对应上面的实体类:

druid的config：

{
	"driverClassName": "org.postgresql.Driver",
	"filters": "config",
	"initialSize": 1,
	"maxActive": 20,
	"maxEvictableIdleTimeMillis": 3000000,
	"maxWait": 60000,
	"minEvictableIdleTimeMillis": 300000,
	"minIdle": 3,
	"password": "LcVkmR8FwPDUbxGGZredWFOUeMq576214vg5A5Lganl9nOx7IXw8S3t3zNMAG4J7poZ15HqxAfhDPknYUDIkfA==",
	"publicKey": "MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAIVMhWXIMXNsdKSGQ5d3AnbVSy3+Uv5HsMIp0lojhxBD9oAyDVOrNi+mqn6O1Rw1TjP7ASjdSHj9v3RBld/hud8CAwEAAQ==",
	"testOnBorrow": false,
	"testOnReturn": false,
	"testWhileIdle": true,
	"timeBetweenEvictionRunsMillis": 60000,
	"url": "jdbc:postgresql://x.x.x.x:1921/datacenter?reWriteBatchedInserts=true",
	"username": "postgres",
	"validationQuery": "SELECT 1"
}

data_source：pgDcJdbcTemplate；

在你需要使用到这个类的地方:@Resource这个pgDcJdbcTemplate或者是applicationContext.getBean("pgDcJdbcTemplate")即可，灵活性较强。

然后查询类似于:

selectJdbcTemplate.query(selectCoreSql, new RowCallbackHandler() {
                @Override
                public void processRow(ResultSet resultSet)  {

然后在里面resultSet.next遍历即可！