Java 近百万数据查询和导出共用时2分钟左右

最新推荐文章于 2025-01-23 15:03:26 发布

原创最新推荐文章于 2025-01-23 15:03:26 发布 · 1k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#大量数据查询 #大量数据导出

本文介绍了一种使用SpringBoot、MyBatis和流式查询技术将70万条数据批量导出到Excel的方法。通过分批查询和导出，每次处理5万条数据，有效避免了内存溢出问题，最终实现高效的数据导出。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

特别感谢 https://www.cnblogs.com/barrywxx/p/10700283.html 提供的模板导出的技术和思路

测试环境：

1、jdk1.8 springboot mybatis mysql

2、70万条的数据34个字段，因业务需求是60万左右的数据量我这里只测试了70万的

技术和思路：

查询：采用流式查询，每次查询5万条数据

导出：采用模板的形式。用Excel的xml格式处理

代码展示：

controller层：


import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.antlr.stringtemplate.StringTemplate;
import org.antlr.stringtemplate.StringTemplateGroup;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import cn.stylefeng.guns.modular.zcManageTemp.service.XMLExportService;

@Controller
@RequestMapping("/batchExport")
public class XMLExport {
	@Autowired
	private XMLExportService XMLExportService;

	@ResponseBody
	@RequestMapping("/exprot")
	public void getData() throws Exception {
		long startTimne = System.currentTimeMillis();
		//模板对象。stringTemplate：只是一个标识，可随便命名
		StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");
		// 写入excel文件头部信息
		//这里获取的模板路径是在classpath下的。默认是 resources下的
		StringTemplate head = stGroup.getInstanceOf("template/head");
		//创建导出的文件，如果存在会覆盖
		File file = new File("D:/test/yunda/output11.xls");
		PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));
		//对模板里的参数进行设置。我这里就直接设置了要导出的数据行数，列数在模板里写死了也可在这里设置。
		//如果数据行数以及列数小于你要导出的数据，则文件打不开。大于等于则无影响
		head.setAttribute("rows", "700100");
		writer.print(head.toString());
		writer.flush();
		// 写入excel文件数据信息
		StringTemplate body = stGroup.getInstanceOf("template/body");
		//分批次查询数据和导出数据.导出70万的数据，每次查询导出5万。如果服务器性能好，可以设置大点
		int size = 700000;
		int cs = 50000;
		if (size > 0) {
			int sang = size / cs;
			int yu = size % cs;
			for (int i = 0; i <= sang; i++) {
				List<Map<String, Object>> selectZCTestData = null;
				if(i == 0) {
					selectZCTestData = new ArrayList<Map<String, Object>>(50001);
					//第一行行头
					selectZCTestData.addAll(getMap());//注意：这里不要放到for循环外面，否者会出现每次循环都会有这一行的数据（具体原因没找到）
				}else {
					selectZCTestData = new ArrayList<Map<String, Object>>(50000);
				}
				long l1 = System.currentTimeMillis();
				
				//数据查询分页：此处用到了分流查询。否则会出现内存溢出。
				//如果这里用了分流查询但是服务器的jvm内存太小会导致卡死的问题。我在本地电脑跑的给了1024*3的大小
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("star", i * cs);
				map.put("page", cs);
				if (i == sang) {
					if (yu > 0) {
						selectZCTestData.addAll(XMLExportService.selectZCTestData(map));
					}
				} else {
					selectZCTestData.addAll(XMLExportService.selectZCTestData(map));
				}
				System.out.println(i * cs);
				long l2 = System.currentTimeMillis();
				System.out.println("第" + i + "次查询用时=" + (l2 - l1));
				//数据写出
				if (selectZCTestData.size() > 0) {
					Worksheet worksheet = new Worksheet();//这个对现场是自己创建的，用于模板里
					worksheet.setRows(selectZCTestData);
					body.setAttribute("worksheet", worksheet);
					writer.print(body.toString());
					writer.flush();
					selectZCTestData.clear();
					selectZCTestData = null;
				}

				long l3 = System.currentTimeMillis();
				System.out.println("第" + i + "次导出用时=" + (l3 - l2));
				//清空数据
				XMLExportService.clearList();
			}
		}
		body = null;
		// 写入excel文件尾部：跟模板要匹配
		writer.print("</Table>");
		writer.print("</Worksheet>");
		writer.print("</Workbook>");
		writer.flush();
		writer.close();
		long endTime = System.currentTimeMillis();
		System.out.println("导出用时=" + ((endTime - startTimne) / 1000) + "秒");
	}
	private List<Map<String, Object>> getMap() {
		List<Map<String, Object>> retlist=new ArrayList<Map<String, Object>>();
		Map<String, Object> map = new HashMap<>();
		List<String> list = getList();
		list.forEach(p -> {
			map.put(p, p);
		});
		retlist.add(map);
		return retlist;
	}

	private List<String> getList() {
		List<String> list = new ArrayList<String>();
		list.add("code_id");
		list.add("zichan_code");
		list.add("ciji_num");
		list.add("pdzc_name");
		list.add("gongsi_code");
		list.add("bumen_code");
		list.add("zichan_name");
		list.add("zichan_nature");
		list.add("zichan_fenlei");
		list.add("zichan_guige");
		list.add("zichan_type");
		list.add("zichan_brand");
		list.add("supplier");
		list.add("laiyuan_way");
		list.add("zichan_value");
		list.add("zichan_zhejiu");
		list.add("zichan_netvalue");
		list.add("zichan_num");
		list.add("zichan_durable");
		list.add("baoxiuqi");
		list.add("jilaing_unit");
		list.add("zhuzichan");
		list.add("caigou_code");
		list.add("ruku_riqi");
		list.add("ruzhang_riqi");
		list.add("shebei_code");
		list.add("zeren_man");
		list.add("zeren_bumen");
		list.add("zichan_suoshu");
		list.add("zichan_cunfang");
		list.add("shiyong_man");
		list.add("shiyong_bumen");
		list.add("zichan_state");
		list.add("remark");
		return list;
	}
}

service层：

private List<Map<String, Object>> selectZCTestDataList = new ArrayList<Map<String, Object>>(50000);
	@Override
	public List<Map<String, Object>> selectZCTestData(Map<String, Object> map) {
		//流式读取
		GxidResultHandler gxidResultHandler = new GxidResultHandler();
		try {
			this.baseMapper.selectZCTestData(map, gxidResultHandler);
			selectZCTestDataList.addAll(gxidResultHandler.getData());
		} finally {
			gxidResultHandler.end();
		}
		return selectZCTestDataList;
	}

	public void clearList() {
		selectZCTestDataList.clear();
	}

流式获取结果类：

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;

public class GxidResultHandler implements ResultHandler<Map<String, Object>> {
	// 存储每批数据的临时容器
	private List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(50000);
	int size = 0;
	@Override
	public void handleResult(ResultContext<? extends Map<String, Object>> resultContext) {
		/** 回调处理逻辑 */
		list.add(resultContext.getResultObject());
	}
	
	public List<Map<String, Object>> getData() {
		return list;
	}

	// 这个方法给外面调用，用来完成最后一批数据处理
	public void end() {
		list.clear();
	}
}

dao层：

void selectZCTestData(Map<String, Object> map,ResultHandler<Map<String, Object>> handler);

worksheet:

package cn.stylefeng.guns.modular.zcManageTemp.controller;

import java.util.List;
import java.util.Map;

/**
 * 类功能描述：Excel sheet Bean
 *
 */
public class Worksheet {
    private String sheet;
    private int columnNum;
    private int rowNum;
    private List<Map<String,Object>> rows;

    public String getSheet() {
        return sheet;
    }
    public void setSheet(String sheet) {
        this.sheet = sheet;
    }

    public List<Map<String,Object>> getRows() {
        return rows;
    }
    public void setRows(List<Map<String,Object>> rows) {
        this.rows = rows;
    }

    public int getColumnNum() {
        return columnNum;
    }
    public void setColumnNum(int columnNum) {
        this.columnNum = columnNum;
    }

    public int getRowNum() {
        return rowNum;
    }
    public void setRowNum(int rowNum) {
        this.rowNum = rowNum;
    }
}

文件导出的模板：

head.st

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>8988</WindowHeight>
  <WindowWidth>23040</WindowWidth>
  <WindowTopX>32767</WindowTopX>
  <WindowTopY>32767</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Center"/>
   <Borders/>
   <Font ss:FontName="等线" x:CharSet="134" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="sheet1">
  <Table ss:ExpandedColumnCount="34" ss:ExpandedRowCount="$rows$" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="13.8">

body.st

$worksheet:{
   $it.rows:{
   	<Row>
		<Cell><Data ss:Type="String">$it.code_id$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_code$</Data></Cell>
		<Cell><Data ss:Type="String">$it.ciji_num$</Data></Cell>
		<Cell><Data ss:Type="String">$it.pdzc_name$</Data></Cell>
		<Cell><Data ss:Type="String">$it.gongsi_code$</Data></Cell>
		<Cell><Data ss:Type="String">$it.bumen_code$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_name$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_nature$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_fenlei$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_guige$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_type$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_brand$</Data></Cell>
		<Cell><Data ss:Type="String">$it.supplier$</Data></Cell>
		<Cell><Data ss:Type="String">$it.laiyuan_way$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_value$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_zhejiu$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_netvalue$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_num$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_durable$</Data></Cell>
		<Cell><Data ss:Type="String">$it.baoxiuqi$</Data></Cell>
		<Cell><Data ss:Type="String">$it.jilaing_unit$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zhuzichan$</Data></Cell>
		<Cell><Data ss:Type="String">$it.caigou_code$</Data></Cell>
		<Cell><Data ss:Type="String">$it.ruku_riqi$</Data></Cell>
		<Cell><Data ss:Type="String">$it.ruzhang_riqi$</Data></Cell>
		<Cell><Data ss:Type="String">$it.shebei_code$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zeren_man$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zeren_bumen$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_suoshu$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_cunfang$</Data></Cell>
		<Cell><Data ss:Type="String">$it.shiyong_man$</Data></Cell>
		<Cell><Data ss:Type="String">$it.shiyong_bumen$</Data></Cell>
		<Cell><Data ss:Type="String">$it.zichan_state$</Data></Cell>
		<Cell><Data ss:Type="String">$it.remark$</Data></Cell>
   </Row>
   }$
}$

结果展示：

0
第0次查询用时=2276
第0次导出用时=3575
50000
第1次查询用时=2090
第1次导出用时=4427
100000
第2次查询用时=2004
第2次导出用时=3705
150000
第3次查询用时=2229
第3次导出用时=3561
200000
第4次查询用时=2374
第4次导出用时=3502
250000
第5次查询用时=2816
第5次导出用时=3452
300000
第6次查询用时=3094
第6次导出用时=2871
350000
第7次查询用时=3085
第7次导出用时=3530
400000
第8次查询用时=2956
第8次导出用时=3148
450000
第9次查询用时=4073
第9次导出用时=3009
500000
第10次查询用时=4384
第10次导出用时=3163
550000
第11次查询用时=5824
第11次导出用时=3053
600000
第12次查询用时=4513
第12次导出用时=3689
650000
第13次查询用时=32630
第13次导出用时=3628
700000
第14次查询用时=0
第14次导出用时=0
导出用时=129秒

结束

从这次结果看，最后一次的查询耗时非常多，可能原因：1、jvm内存 2、主机性能。总体来说勉强能用，但是还需要继续优化