spring boot + mybatis 流式导出mysql数

springboot 版本：1.5.3.RELEASE

数据库配置

@Configuration
@MapperScan(basePackages = "com.xxxx.persistence.cloud", sqlSessionTemplateRef = "cloudSqlSessionTemplate")
public class DataSourceCloudConfig {

    @Bean(name = "cloudDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.cloud")
    @Primary
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "cloudSqlSessionFactory")
    @Primary
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("cloudDataSource") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        //下划线转驼峰
        org.apache.ibatis.session.Configuration cf = new org.apache.ibatis.session.Configuration();
        cf.setMapUnderscoreToCamelCase(true);
        bean.setConfiguration(cf);
        bean.setDataSource(dataSource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/cloud/**/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "cloudTransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("cloudDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "cloudSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(
            @Qualifier("cloudSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

配置文件需要配置数据库连接信息

spring.datasource.cloud.url=jdbc:mysql://xxxx/database?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.cloud.username=xxx
spring.datasource.cloud.password=xxx
spring.datasource.cloud.driver-class-name=com.mysql.cj.jdbc.Driver

mybatis 关键内容

mybatis 配置文件关键内容

  <sql id="exportCondition">
    <where>
      <if test="uid != null">
        and uid = #{uid}
      </if>
    </where>
  </sql>

  <resultMap id="BaseResultMap" type="xxx">
    <id column="id" property="id"/>
    <!--省略-->
  </resultMap>
  <!---其中：fetchSize必须等于-2147483648，等于 Interger.MIN--->
  <select id="exportByCondition" resultMap="BaseResultMap" fetchSize="-2147483648">
    select
   xxx(省略)
    from tb_robot_punish_record
    <include refid="exportCondition"/>
  </select>

mapper 接口

@Mapper
public interface ManageMapper {
  void exportByCondition(@Param("condition") RobotAuditQueryCondition condition);
}

service 代码

查询条件实体类

@Data
public class QueryCondition {
  private Long uid;
}

@Service
public class RecordService {
  @Autowired
  @Qualifier("cloudSqlSessionTemplate")
  private SqlSessionTemplate cloudSqlSessionTemplate;

 public void export2CSV(QueryCondition condition, HttpServletResponse response) throws Exception {

  
    ServletOutputStream outputStream = response.getOutputStream();
    OutputStreamWriter out = new OutputStreamWriter(outputStream, StandardCharsets.UTF_8);
    final BufferedWriter bufferedWriter = new BufferedWriter(out);
    try {

    String fileName ="xxx.csv";
    response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
    response.setContentType("application/octet-stream; charset=utf-8");
  
    //核心代码
    //com.xx.ManageMapper.exportByCondition 表示 maper接口全路径+方法名，condition 表示查询参数
    cloudSqlSessionTemplate.select("com.xx.ManageMapper.exportByCondition",
          condition, new ResultHandler() { //回调处理逻辑，汇总批量数据写入响应流
            @Override
            public void handleResult(ResultContext resultContext) {
              if (resultContext.getResultObject() != null) {
                Object obj = resultContext.getResultObject();//数据库查询出来的记录
                String line = convert2Line(obj)；//TODO将对象转换为csv或者对应的格式数据行
                bufferedWriter.write(obj);//写到流里面
              }
            }
          });
      bufferedWriter.flush();
    } catch (Exception e) {
      log.error("导出异常,req={}",condition, e);
    } finally {
      if (bufferedWriter != null) {
        IOUtils.closeQuietly(bufferedWriter);
      }
    }
  }
}

controller 层代码

@RestController
@Slf4j
public class RobotAuditPunishRecordController {

  @Autowired
  private RecordService recordService;

  @RequestMapping(value = "/xxx/export", method = RequestMethod.GET)
  public void export(QueryCondition queryParam, HttpServletResponse response) throws Exception {
    try {
      recordService.export2CSV(queryParam, response);
    } catch (Throwable e) {
      log.error("导出数据系统异常,req={}", queryParam, e);
      throw e;
    }
  }
}